DEV Community: Chloe

8 Excel Automation Tasks Every C# Developer Should Know

Chloe — Mon, 29 Jun 2026 08:59:36 +0000

Excel files are everywhere in business workflows — reports, invoices, data exports, configuration sheets. As a C# developer, you'll almost inevitably run into a requirement to generate, read, or manipulate them programmatically. The good news is that the .NET ecosystem offers mature, well-maintained libraries that make Excel automation straightforward, without ever needing Microsoft Office installed on the server.

In this article, we'll walk through eight practical Excel automation tasks that every C# developer should know. Rather than focusing on a single library, we'll use the tool that best fits each scenario, giving you a broader view of the available options and helping you choose the right approach for your own projects.

Task 1: Create an Excel Workbook from Scratch

Creating an Excel workbook from scratch is one of the most common Excel automation tasks in C#. Whether you're generating monthly sales reports, exporting customer records, or producing invoices, the process usually starts with creating a new workbook and populating it with data.

For simple workbook creation, ClosedXML is an excellent choice. It provides a clean, intuitive API that makes it easy to create worksheets, write values, and save Excel files without dealing with the complexity of the underlying Open XML structure.

The following example creates a new workbook, adds a worksheet named "Sales Report", writes a few rows of sample data, and saves the workbook as an Excel file.

using ClosedXML.Excel;

var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Sales Report");

// Add headers
sheet.Cell("A1").Value = "Product";
sheet.Cell("B1").Value = "Quantity";
sheet.Cell("C1").Value = "Revenue";

// Add data rows
sheet.Cell("A2").Value = "Laptop";
sheet.Cell("B2").Value = 25;
sheet.Cell("C2").Value = 32500;

sheet.Cell("A3").Value = "Monitor";
sheet.Cell("B3").Value = 18;
sheet.Cell("C3").Value = 5400;

workbook.SaveAs("SalesReport.xlsx");

After running the code, you'll have a standard .xlsx file that can be opened in Microsoft Excel or other compatible spreadsheet applications.

While this example uses only a few rows of data, the same approach works just as well for exporting thousands of records from a database, API, or business application. Once you've mastered workbook creation, you can build on it by adding formatting, formulas, charts, and other advanced features covered in the following sections.

Task 2: Read Data from an Existing Excel File

Creating Excel files is only half of the story. Many applications also need to read data from existing spreadsheets, whether users are uploading employee records, importing product catalogs, or processing financial reports.

Several mature .NET libraries handle this well. NPOI is a good option if you need to support both .xls and .xlsx formats, or if you're working in environments where licensing matters. EPPlus is another popular choice — it has a clean API and handles large files efficiently.

For this task, we'll use EPPlus, a popular .NET library that makes reading Excel data simple and efficient. Its worksheet API is straightforward, allowing you to access cells, iterate through rows, and retrieve values with just a few lines of code.

Here's an example that reads data from the first worksheet and prints each row to the console:

using OfficeOpenXml;

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

using var package = new ExcelPackage(new FileInfo("SalesReport.xlsx"));
var sheet = package.Workbook.Worksheets[0];

int rowCount = sheet.Dimension.Rows;
int colCount = sheet.Dimension.Columns;

for (int row = 1; row <= rowCount; row++)
{
    for (int col = 1; col <= colCount; col++)
    {
        var cellValue = sheet.Cells[row, col].Text;
        Console.Write(cellValue.PadRight(15));
    }
    Console.WriteLine();
}

This approach works well for structured spreadsheets where each row represents a record. In real-world applications, the data can then be validated, mapped to objects, or imported into a database for further processing.

When reading Excel files, keep in mind that user-generated spreadsheets aren't always clean. Empty rows, missing values, unexpected data types, and inconsistent date formats are all common issues. Adding basic validation before processing the data can help prevent runtime errors and improve the reliability of your import workflow.

Task 3: Format Cells (Fonts, Colors, Borders)

Raw data is useful, but a well-formatted spreadsheet is much easier to read and share. Applying consistent fonts, colors, borders, and number formats can make reports look more professional and help users quickly identify important information.

For formatting Excel worksheets, Spire.XLS for .NET provides a comprehensive set of styling APIs. You can customize almost every aspect of a worksheet, from cell fonts and background colors to borders, alignment, and number formats.

Here's an example that creates a simple sales table with formatted headers and styled data rows:

using Spire.Xls;
using System.Drawing;

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sales Report";

// Define headers
string[] headers = { "Product", "Quantity", "Revenue" };
for (int col = 0; col < headers.Length; col++)
{
    CellRange cell = sheet.Range[1, col + 1];
    cell.Text = headers[col];

    // Bold white font
    cell.Style.Font.IsBold = true;
    cell.Style.Font.Color = Color.White;

    // Dark blue background
    cell.Style.Color = Color.FromArgb(31, 73, 125);

    // Center alignment
    cell.Style.HorizontalAlignment = HorizontalAlignType.Center;
}

// Add data rows
object[,] data = {
    { "Laptop",  25, 32500 },
    { "Monitor", 18,  5400 },
    { "Keyboard", 60, 3600 }
};

for (int row = 0; row < 3; row++)
{
    for (int col = 0; col < 3; col++)
    {
        CellRange cell = sheet.Range[row + 2, col + 1];
        if (col == 0)
            cell.Text = data[row, col].ToString();
        else
            cell.NumberValue = Convert.ToDouble(data[row, col]);

        // Light grey background for data rows
        cell.Style.Color = Color.FromArgb(217, 225, 242);

        // Add borders
        cell.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
        cell.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
        cell.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
        cell.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
    }
}

// Auto-fit column widths
sheet.AllocatedRange.AutoFitColumns();

workbook.SaveToFile("FormattedReport.xlsx", ExcelVersion.Version2016);

With just a few formatting settings, the worksheet becomes much easier to read and presents the data more professionally. In production applications, you can also apply alternating row colors, conditional formatting, custom number formats, and cell alignment to create reports that are both visually appealing and user-friendly.

Investing a little extra effort in formatting can significantly improve the user experience, especially for reports that are shared with customers, managers, or other non-technical stakeholders.

Task 4: Work with Formulas

One of Excel's biggest strengths is its built-in formula engine. Instead of calculating values in your C# code, you can write formulas directly into worksheets and let Excel perform the calculations automatically. This makes reports easier to maintain and allows users to update data without modifying your application.

For working with formulas, NPOI provides support for writing common Excel formulas, making it a practical option when generating spreadsheets programmatically.

Here's an example that writes a sales table and adds summary formulas at the bottom:

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

var workbook = new XSSFWorkbook();
var sheet = workbook.CreateSheet("Sales Report");

// Headers
var headerRow = sheet.CreateRow(0);
headerRow.CreateCell(0).SetCellValue("Product");
headerRow.CreateCell(1).SetCellValue("Quantity");
headerRow.CreateCell(2).SetCellValue("Revenue");

// Data rows
var data = new (string Product, int Quantity, double Revenue)[]
{
    ("Laptop",   25, 32500),
    ("Monitor",  18,  5400),
    ("Keyboard", 60,  3600),
};

for (int i = 0; i < data.Length; i++)
{
    var row = sheet.CreateRow(i + 1);
    row.CreateCell(0).SetCellValue(data[i].Product);
    row.CreateCell(1).SetCellValue(data[i].Quantity);
    row.CreateCell(2).SetCellValue(data[i].Revenue);
}

// Summary row with formulas
int summaryRow = data.Length + 1;
var summary = sheet.CreateRow(summaryRow);
summary.CreateCell(0).SetCellValue("Total");

var quantityCell = summary.CreateCell(1);
quantityCell.SetCellFormula($"SUM(B2:B{summaryRow})");

var revenueCell = summary.CreateCell(2);
revenueCell.SetCellFormula($"SUM(C2:C{summaryRow})");

using var stream = new FileStream("SalesWithFormulas.xlsx", FileMode.Create);
workbook.Write(stream);

When you open the resulting file in Excel, the SUM formulas will evaluate automatically. NPOI also supports more complex formulas like AVERAGE, IF, VLOOKUP, and COUNTIF — just assign the formula string the same way. One thing to note: NPOI writes the formula expressions but doesn't evaluate them at generation time, so if you need to read back calculated values programmatically without opening Excel, you'll want a library that supports formula evaluation, such as EPPlus.

Task 5: Insert Charts

Charts make reports easier to understand by transforming raw numbers into visual insights. They're commonly used in sales reports, KPI dashboards, and financial summaries, allowing users to spot trends without manually creating charts in Excel.

EPPlus supports many common chart types and is suitable for straightforward reporting scenarios. If you need more advanced customization, a broader selection of chart types, or higher-fidelity rendering, Spire.XLS for .NET provides a more comprehensive charting API. It doesn't require Excel to be installed, which matters in server-side scenarios.

Here's an example that builds a sales table and inserts a column chart above the data:

using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;

namespace ColumnChart
{
    class Program
    {
        static void Main(string[] rgs)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Add data to specified cells
            sheet.Range["A1"].Value = "Country";
            sheet.Range["A2"].Value = "Cuba";
            sheet.Range["A3"].Value = "Mexico";
            sheet.Range["A4"].Value = "France";
            sheet.Range["A5"].Value = "Germany";

            sheet.Range["B1"].Value = "Jun";
            sheet.Range["B2"].NumberValue = 5000;
            sheet.Range["B3"].NumberValue = 8000;
            sheet.Range["B4"].NumberValue = 9000;
            sheet.Range["B5"].NumberValue = 8500;

            sheet.Range["C1"].Value = "Aug";
            sheet.Range["C2"].NumberValue = 3000;
            sheet.Range["C3"].NumberValue = 5000;
            sheet.Range["C4"].NumberValue = 7000;
            sheet.Range["C5"].NumberValue = 6000;

            //Set cell styles
            sheet.Range["A1:C1"].Style.Font.IsBold = true;
            sheet.Range["A1:C1"].Style.KnownColor = ExcelColors.Black;
            sheet.Range["A1:C1"].Style.Font.Color = Color.White;
            sheet.Range["A1:C5"].Style.HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["A1:C5"].Style.VerticalAlignment = VerticalAlignType.Center;

            //Set number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";

            //Add a column chart to the worksheet
            Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);

            //Set data range for the chart
            chart.DataRange = sheet.Range["A1:C5"];
            chart.SeriesDataFromRange = false;

            //Set position of the chart
            chart.LeftColumn = 1;
            chart.TopRow = 7;
            chart.RightColumn = 11;
            chart.BottomRow = 29;

            //Set and format chart title
            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.Size = 13;
            chart.ChartTitleArea.IsBold = true;

            //Set and format category axis
            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.Color = Color.Blue;

            //Set and format value axis
            chart.PrimaryValueAxis.Title = "Sales(USD)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;

            //Show data labels for data points 
            foreach (ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            //Set position of chart legend
            chart.Legend.Position = LegendPositionType.Top;

            //Save the result file
            workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010);
        }
    }
}

After running this, the workbook will contain both the data table and a column chart positioned to the right of it. Spire.XLS supports a range of other chart types — line, bar, pie, area, scatter — by swapping the ExcelChartType enum value, so it's easy to adapt this to whatever visualization fits your data best.

Task 6: Freeze Panes and Apply Auto Filters

A well-structured spreadsheet isn't just about the data — it should also be easy to navigate. As reports grow to hundreds or even thousands of rows, users often struggle to keep track of column headers or quickly locate the information they need.

Two simple features can dramatically improve usability: Freeze Panes and Auto Filters. Freezing the top row keeps column headings visible while scrolling, and filters let users sort or narrow down data without modifying the worksheet.

ClosedXML and EPPlus both support these features with straightforward APIs. We'll use ClosedXML here:

Here's an example that generates a product list with a frozen header row and filters enabled on all columns:

using ClosedXML.Excel;

var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Products");

// Write headers
sheet.Cell("A1").Value = "Product";
sheet.Cell("B1").Value = "Category";
sheet.Cell("C1").Value = "Price";
sheet.Cell("D1").Value = "Stock";

// Write data
var data = new (string Product, string Category, double Price, int Stock)[]
{
    ("Laptop",   "Electronics", 1300, 25),
    ("Monitor",  "Electronics",  300, 18),
    ("Keyboard", "Accessories",   60, 60),
    ("Mouse",    "Accessories",   20, 45),
    ("Webcam",   "Accessories",   80, 30),
};

for (int i = 0; i < data.Length; i++)
{
    int row = i + 2;
    sheet.Cell(row, 1).Value = data[i].Product;
    sheet.Cell(row, 2).Value = data[i].Category;
    sheet.Cell(row, 3).Value = data[i].Price;
    sheet.Cell(row, 4).Value = data[i].Stock;
}

// Freeze the header row
sheet.SheetView.FreezeRows(1);

// Enable auto filter on the data range
sheet.RangeUsed().SetAutoFilter();

workbook.SaveAs("ProductList.xlsx");

When you open the file, the first row stays locked in place as you scroll, and each column header shows a dropdown arrow for filtering.

Although these features require only a few lines of code, they can significantly improve the user experience—especially for large reports that are reviewed by analysts, managers, or business users. Small usability enhancements like these often make automated Excel reports feel much more polished and practical.

Task 7: Protect Worksheets and Workbooks

After generating a well-formatted workbook, the next step is making sure its contents can't be modified accidentally. This is especially important for reports, templates, financial statements, and other documents where formulas or critical data should remain unchanged.

Excel provides built-in protection features that allow you to lock worksheets, restrict editing, and require a password before changes can be made. These features help preserve data integrity while still allowing users to view the workbook.

For workbook protection, Spire.XLS for .NET provides a straightforward API for securing Excel files.

The following example protects a workbook with a password.

using Spire.Xls;

//Create a Workbook object
Workbook workbook = new Workbook();

//Load an Excel file
workbook.LoadFromFile("sample.xlsx");

// Protect the worksheet
workbook.Protect("psd-123");

//Save the workbook to another Excel file
workbook.SaveToFile("ProtectedReport.xlsx", ExcelVersion.Version2016);

When users open the workbook, they are required to enter the correct password to view or edit its contents. This helps prevent unauthorized access to the workbook while keeping its contents protected.

You can also lock only specific cells while leaving input fields editable, giving users the flexibility to enter data without affecting the structure or calculations of the workbook.

Task 8: Convert Excel to PDF

Excel files are great for editing and data analysis, but they're not always the best format for sharing. Layouts can vary between devices, formulas may be modified accidentally, and recipients might not even have Excel installed.

That's why many business applications export spreadsheets as PDF before sending them to customers, managers, or stakeholders. PDF files preserve the original layout, are easier to print, and ensure that everyone sees the document exactly as intended.

For Excel-to-PDF conversion, Spire.XLS for .NET provides a simple way to generate high-quality PDF files while preserving formatting, charts, images, and page layouts.

The following example loads an existing Excel workbook and exports it directly as a PDF document.

using Spire.Xls;

Workbook workbook = new Workbook();
workbook.LoadFromFile("SalesReport.xlsx");

workbook.ConverterSetting.SheetFitToWidth = true;

// Convert to PDF
workbook.SaveToFile("SalesReport.pdf", FileFormat.PDF);

With just a few lines of code, you can create a PDF version of an Excel workbook that's ready to print, archive, or share. The resulting document preserves the workbook's appearance, making it ideal for reports that need consistent formatting across different devices.

Beyond PDF, it's also common to export Excel workbooks as HTML or images, making it easier to reuse the same spreadsheet across different applications and delivery scenarios.

Conclusion

Excel automation in C# doesn't have to be complicated. As this article shows, the .NET ecosystem has mature libraries for virtually every spreadsheet task — from generating and formatting workbooks to embedding charts, protecting content, and exporting to PDF.

Whether you're developing internal business systems, reporting platforms, or customer-facing applications, mastering these Excel automation techniques can save time, reduce manual work, and deliver a better experience for your users.

Hopefully, this guide gives you a solid starting point for building more powerful Excel workflows in your own C# projects.

Extract Tables from PDF in Python: A Practical Guide

Chloe — Mon, 22 Jun 2026 07:32:46 +0000

If you've ever worked with reports, invoices, or financial statements, you've probably run into PDF files that contain tables you need to analyze.

The problem is that PDF documents are designed for viewing, not data processing. Copying table data manually into Excel or another system quickly becomes tedious, especially when you're dealing with multiple files.

In many automation workflows, developers need to extract tables from PDF files and convert them into structured data for analysis, reporting, or database import.

In this article, you'll learn how to extract tables from a PDF in Python using Spire.PDF for Python. We'll cover the installation process, basic usage, and a practical example to extract tables from a sample PDF.

Spire.PDF for Python

Extracting tables from PDFs can be surprisingly challenging. Depending on the document structure, you may need to deal with page layouts, text positioning, or even OCR for scanned files.

To simplify the process, this tutorial uses Spire.PDF for Python. The library includes a built-in table extraction feature that can identify tables on PDF pages and retrieve their cell data with just a few lines of code.

Beyond table extraction, Spire.PDF also supports common PDF automation tasks such as text extraction, document conversion, form processing, and PDF splitting or merging.

Note: Spire.PDF works with text-based PDFs. Scanned PDFs require OCR before table extraction can be performed.

How to Extract Tables from PDF in Python: Step-by-Step

Let's begin a step-by-step tutorial for extracting table data from a PDF file.

Step 1: Create a Python Project

Before we start extracting data from a PDF, let's create a simple Python project.

If you're using Visual Studio 2022:

Open Visual Studio 2022 → Click Create a new project → Select Python Application → Enter a project name and choose a location → Click Create.

Once the project is ready, we can install the required library.

Step 2: Install Spire.PDF for Python

Open a terminal or command prompt and run:

pip install spire.pdf

Now that the library is installed, let's load the PDF document and prepare it for table extraction.

Step 3: Extract Tables from the PDF

We'll use a sample sales report that contains the following table:

The following code loads the PDF document, creates a table extractor, and reads all tables from every page in the file.

from spire.pdf import PdfDocument, PdfTableExtractor

# Load PDF document
pdf = PdfDocument()
pdf.LoadFromFile("sales-report.pdf")

# Create a PdfTableExtractor object
table_extractor = PdfTableExtractor(pdf)

# Extract tables from each page
for i in range(pdf.Pages.Count):
    tables = table_extractor.ExtractTable(i)

    if tables is None:
        continue

    for table_index, table in enumerate(tables):
        print(f"Table {table_index + 1} on page {i + 1}:")
        for row in range(table.GetRowCount()):
            row_data = []
            for col in range(table.GetColumnCount()):
                text = table.GetText(row, col).replace("\n", " ")
                row_data.append(text.strip())
            print("\t".join(row_data))

pdf.Close()

This code starts by importing the required classes from Spire.PDF. It then loads the PDF file into a PdfDocument object and creates a PdfTableExtractor instance.

The extractor scans each page of the document and detects any tables that are present. For every table found, the code loops through all rows and columns, retrieves the cell values using the GetText() method, and prints the extracted data to the console.

After running the script, you'll see output similar to the following, with each detected table printed row by row:

Step 4: Filter Rows — Annual Target Completion ≥ 50%

In many real-world scenarios, extracting the table is only the first step. Once the data is available, you'll often need to filter, validate, or transform it before exporting it to another system.

As an example, let's keep only the products whose Annual Target Completion rate is at least 50%.

The filter logic:

Skip the header row (row index 0)
For each data row, read the last column (col index 4)
Strip the % character and convert to float
Keep the row only if the value is >= 50

# Step 4: Filter rows where Annual Target Completion >= 50%
COMPLETION_COL = 4      # Column index for "Annual Target Completion"
THRESHOLD = 50.0
HEADER_ROW = 0

for page_index in range(pdf.Pages.Count):
    tables = table_extractor.ExtractTable(page_index)

    if tables is None:
        continue

    for table_index, table in enumerate(tables):
        print(f"\n--- Page {page_index + 1}, Table {table_index + 1} ---")

        # Print header
        header = [table.GetText(HEADER_ROW, col) for col in range(table.GetColumnCount())]
        print(header)

        # Filter and print data rows
        for row in range(1, table.GetRowCount()):
            row_data = [table.GetText(row, col) for col in range(table.GetColumnCount())]
            completion_str = row_data[COMPLETION_COL].replace("%", "").strip()
            try:
                completion = float(completion_str)
                if completion >= THRESHOLD:
                    print(row_data)
            except ValueError:
                continue

pdf.Close()

Console output:

Tablet Mini (48.9%) is excluded. The three remaining products have all crossed the 50% threshold. The try/except ValueError block ensures the loop doesn't break if a cell contains unexpected text or an empty string.

This approach can easily be adapted to other filtering scenarios. For example, you might want to find products with a completion rate above 80%, identify underperforming products below 40%, or apply multiple conditions based on different columns in the table.

You can also export the extracted rows to CSV, Excel, or a database once the filtering step is complete.

What Else Can You Do with Spire.PDF?

Table extraction is only one part of a typical PDF automation workflow. You can also extract plain text from any page, merge or split PDF files, add watermarks and annotations, and convert PDFs to Word documents or images — all without leaving Python. See the Spire.PDF for Python documentation for the full API reference.

Conclusion

In this tutorial, we used Spire.PDF for Python to extract tables from a PDF document and filter the results based on a simple business rule.

While the example focused on a sales report, the same approach works for invoices, financial statements, and many other structured PDF documents. Once the data is extracted, you can further analyze it, export it to Excel, store it in a database, or integrate it into automated workflows.

With just a few lines of code, Python can turn static PDF tables into usable data.

10 Common PDF Tasks You Can Automate with C#

Chloe — Mon, 15 Jun 2026 09:52:55 +0000

If you've worked on business applications long enough, you've probably dealt with PDFs.

Invoices, contracts, reports, onboarding forms, customer statements — PDF files tend to show up everywhere.

What surprised me is how many teams still handle common PDF tasks manually. People merge files by hand, copy data from documents, fill forms, or apply security settings one document at a time.

Most of these tasks can be automated with relatively little code.

In this article, I'll walk through 10 PDF processing tasks that .NET developers commonly automate in real-world applications. These examples cover everything from document generation and text extraction to file compression, digital signatures, and automated reporting workflows.

For consistency, all code examples use Spire.PDF for .NET, but the automation concepts discussed here can be applied with most PDF libraries available in the .NET ecosystem.

1. Generating PDFs from Templates
2. Splitting and Merging PDF Files
3. Extracting Text from PDFs
4. Filling PDF Forms Programmatically
5. Adding Watermarks and Stamps
6. Converting PDFs to Other Formats
7. Compressing PDF File Size
8. Extracting and Replacing Images
9. Adding Digital Signatures and Encryption
10. Automating End-to-End PDF Workflows
What About Other PDF Libraries?

Task 1: Generating PDFs from Templates

Manually producing documents like invoices, contracts, or certificates means someone is opening a template, filling in fields, saving, and exporting — every single time. At low volume that's annoying. At scale, it's a bottleneck that introduces inconsistencies and errors. Automating template-based PDF generation lets your application produce hundreds of accurate, consistently formatted documents on demand.

Typical Challenges

Fonts and alignment shift when placeholder text is replaced with content of different lengths
Multi-page repeating sections (like invoice line items) require dynamic layout logic, not simple find-and-replace

Code Example

The following example loads a PDF template, locates text placeholders, and replaces them with real data:

using Spire.Pdf;
using Spire.Pdf.Texts;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("invoice_template.pdf");

foreach (PdfPageBase page in pdf.Pages)
{
    // Find and replace placeholder text
    PdfTextReplacer replacer = new PdfTextReplacer(page);
    replacer.ReplaceText("{CustomerName}", "Acme Corp");
    replacer.ReplaceText("{InvoiceDate}", DateTime.Now.ToString("yyyy-MM-dd"));
}

pdf.SaveToFile("invoice_output.pdf");
pdf.Close();

Common Use Cases

Invoice and receipt generation in e-commerce or billing systems
Contract population from CRM data before signing
Certificate issuance for training platforms or HR onboarding

Once PDF generation is automated, documents can be created instantly from application data, making it much easier to scale business processes.

Task 2: Splitting and Merging PDF Files

Document workflows rarely deal with perfectly sized files. A 200-page legal bundle needs splitting before it goes to different reviewers. A batch of individually generated reports needs merging before it hits the client's inbox. Doing this manually — in Acrobat or any desktop tool — doesn't belong in a production pipeline. Automating split and merge operations keeps your document flow consistent and hands-free.

Typical Challenges

Splitting by logical sections requires knowing where each section starts, not just page ranges
Merging documents with different page sizes or font embeddings can produce inconsistent output

Code Example

To split a PDF, you can save individual pages or page ranges as separate files.

using Spire.Pdf;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("bundle.pdf");

// Split each page into separate PDF files.
// The first parameter is the output file pattern.
// {0} will be replaced by the page number starting from 1.
pdf.Split("Output/Page_{0}.pdf", 1);

pdf.Close();

The following example merges multiple PDF files into a single document:

using Spire.Pdf;

string[] files = { "report_q1.pdf", "report_q2.pdf", "report_q3.pdf" };

PdfDocumentBase pdf = PdfDocument.MergeFiles(files);

pdf.Save("merge_full_year_report.pdf", FileFormat.PDF);

Common Use Cases

Splitting multi-customer billing runs into individual statements
Merging supporting documents into a single submission package
Combining nightly batch outputs into a consolidated daily report

PDF splitting and merging are among the most frequently automated document-processing tasks because they fit naturally into business workflows and require very little user intervention once implemented.

Task 3: Extracting Text from PDFs

Many organizations store valuable information inside PDF documents, including invoices, contracts, reports, and compliance records. Extracting text programmatically allows applications to search, analyze, validate, and process this information without requiring manual review.

Instead of copying content by hand, developers can automate text extraction and integrate it into existing business systems.

Common challenges include:

Scanned PDFs require OCR before any text extraction is possible
Multi-column layouts and complex formatting often break reading order in extracted output

Despite these challenges, extracting text from standard PDFs is usually straightforward with the right library.

Code Example

The following example extracts text page by page from a native PDF:

using Spire.Pdf;
using Spire.Pdf.Texts;
using System.IO;
using System.Text;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("task 3.pdf");

StringBuilder extractedText = new StringBuilder();

foreach (PdfPageBase page in pdf.Pages)
{
    PdfTextExtractor extractor = new PdfTextExtractor(page);
    PdfTextExtractOptions option = new PdfTextExtractOptions
    {
        IsExtractAllText = true
    };

    string text = extractor.ExtractText(option);
    extractedText.AppendLine(text);
}

File.WriteAllText("ExtractedText.txt", extractedText.ToString());
pdf.Close();

Common Use Cases

Feeding contract content into compliance or review workflows
Indexing PDF knowledge bases for search
Extracting invoice data for accounts payable automation
Migrating legacy document content into structured databases

Task 4: Filling PDF Forms Programmatically

PDF forms are still widely used across industries, from employee onboarding and insurance applications to tax forms and customer registration documents. While users can fill out forms manually, organizations often need to populate PDF forms automatically using data already stored in databases or business systems.

Automating form filling reduces repetitive data entry, improves accuracy, and helps streamline document workflows.

Typical Challenges

AcroForm field names are not always predictable, requiring inspection before you can map data to fields reliably
Some PDFs use XFA forms (XML-based) rather than AcroForms, which require a different handling approach entirely
Checkboxes and radio buttons have non-obvious value formats that vary by how the form was originally authored

Code Example

The following example loads a PDF form and fills several fields programmatically:

using Spire.Pdf;
using Spire.Pdf.Fields;
using Spire.Pdf.Widget;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("application_form.pdf");

PdfFormWidget form = pdf.Form as PdfFormWidget;

foreach (PdfField field in form.FieldsWidget)
{
    switch (field.Name)
    {
        case "FullName":
            (field as PdfTextBoxFieldWidget).Text = "Jane Smith";
            break;
        case "Email":
            (field as PdfTextBoxFieldWidget).Text = "jane.smith@example.com";
            break;
        case "Agreed":
            (field as PdfCheckBoxWidgetFieldWidget).Checked = true;
            break;
    }
}

pdf.SaveToFile("filled_application.pdf");
pdf.Close();

Common Use Cases

Pre-filling HR onboarding documents from employee records
Populating compliance forms from internal databases
Automating client intake forms in legal or financial platforms
Generating pre-filled applications in self-service portals

Task 5: Adding Watermarks and Stamps

Organizations often need to mark PDF documents with additional information before sharing them internally or externally. Watermarks and stamps help communicate document status, ownership, or confidentiality without modifying the original content.

Instead of manually editing each document, developers can apply watermarks automatically during document generation or distribution.

Typical Challenges

Text watermarks need to be visually present without obscuring the underlying content — getting opacity, rotation, and font size right takes iteration
Image stamps (like approval seals or logos) must be positioned consistently across documents with varying page sizes
Batch watermarking large document sets needs to be memory-efficient to avoid performance issues in production

Code Example

The following example adds a text watermark to every page in a PDF document:

using Spire.Pdf;
using Spire.Pdf.Graphics;
using System.Drawing;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("document.pdf");

PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial", 48f));
PdfBrush brush = new PdfSolidBrush(Color.FromArgb(80, Color.Red));

foreach (PdfPageBase page in pdf.Pages)
{
    page.Canvas.SetTransparency(0.3f);
    page.Canvas.TranslateTransform(page.ActualSize.Width / 2, page.ActualSize.Height / 2);
    page.Canvas.RotateTransform(-45);
    page.Canvas.DrawString("CONFIDENTIAL", font, brush, new PointF(-120, 0));
}

pdf.SaveToFile("watermarked_document.pdf");
pdf.Close();

Common Use Cases

Marking documents as DRAFT or CONFIDENTIAL before internal review
Stamping APPROVED or VOID on processed forms in approval workflows
Adding company logos or branding to outbound documents
Applying expiry or version stamps to time-sensitive materials

Automated watermarking helps organizations maintain document security and communicate document status consistently across large collections of PDF files.

Task 6: Converting PDFs to Other Formats

Although PDFs are excellent for sharing and preserving document layouts, they're not always the best format for editing or further processing. Many business workflows require PDF content to be converted into editable formats such as Word, Excel, or image files.

Automating these conversions eliminates manual copy-and-paste work and makes it easier to reuse information stored in PDF documents.

Typical Challenges

Complex layouts with tables or embedded graphics rarely survive conversion with perfect fidelity
Excel conversion is particularly tricky when PDF tables lack clear cell boundaries or span multiple pages
Image-based PDFs produce poor output without an OCR step first
Batch conversion needs to handle failures gracefully — one malformed file shouldn't stall the entire job

Code Example

The following example converts a PDF document to Word format:

using Spire.Pdf;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("sample.pdf");

pdf.SaveToFile("to-word.docx", FileFormat.DOCX);
pdf.Close();

You can similarly convert PDFs to Excel, images, HTML, and other formats depending on your workflow requirements.

Common Use Cases

Converting archived PDF reports into editable Word documents for revision
Extracting tabular data from PDF statements into Excel for analysis
Generating page thumbnails or previews for document management systems
Feeding PDF content into image processing or OCR pipelines

Automated conversion helps bridge the gap between static PDF files and editable business documents.

Task 7: Compressing PDF File Size

Large PDF files can create problems throughout the document lifecycle. They consume more storage space, take longer to upload and download, and may exceed email attachment limits.

Automating PDF compression helps improve performance while reducing storage and bandwidth costs.

Typical Challenges

Aggressive image downsampling can produce visibly degraded output for client-facing documents
Compression ratios vary significantly depending on what's inside the PDF — a text-heavy document compresses very differently from one full of high-resolution photos
PDFs with embedded fonts or duplicate resources inflate file size without contributing to content quality

Code Example

The following example compresses a PDF by optimizing embedded image resolution and removing redundant resources:

using Spire.Pdf;
using Spire.Pdf.Conversion.Compression; 

PdfCompressor compressor = new PdfCompressor("large_report.pdf");

TextCompressionOptions textCompression = compressor.Options.TextCompressionOptions;
textCompression.CompressFonts = true; 
textCompression.UnembedFonts = true;

ImageCompressionOptions imageCompression = compressor.Options.ImageCompressionOptions;
imageCompression.CompressImage = true; 

imageCompression.ImageQuality = ImageQuality.Medium;

imageCompression.ResizeImages = true; 

compressor.CompressToFile("compressed_report.pdf");

Common Use Cases

Reducing file size before attaching PDFs to outbound emails
Optimizing documents for upload to client portals or document management systems
Compressing archived PDFs in bulk to cut storage costs
Preparing mobile-friendly document previews for web or app delivery

For organizations handling thousands of documents, automated compression can significantly reduce storage requirements while improving document delivery performance.

Task 8: Extracting and Replacing Images in PDFs

Images often contain valuable business content, including product photos, logos, diagrams, charts, and marketing assets. In some cases, developers need to extract these images for reuse. In others, organizations may need to replace outdated branding or update visual content across large collections of documents.

Automating image processing eliminates the need to manually edit PDFs one file at a time.

Typical Challenges

Images are stored as page content stream resources, not file attachments, making them harder to locate than expected
A single image resource can be referenced multiple times across different pages, so replacing it in one place may not update all occurrences
Extracting images preserves the compressed form they're stored in, which may require additional decoding before the image is usable in other contexts

Code Example

The following example extracts images from a PDF document:

using Spire.Pdf;
using Spire.Pdf.Utilities;
using System.Drawing;
using System.Drawing.Imaging;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("brochure.pdf");

PdfImageHelper imageHelper = new PdfImageHelper();

int imageIndex = 0;

for (int i = 0; i < pdf.Pages.Count; i++)
{
    PdfPageBase page = pdf.Pages[i];

    PdfImageInfo[] imageInfos = imageHelper.GetImagesInfo(page);

    foreach (PdfImageInfo imageInfo in imageInfos)
    {
        Image img = imageInfo.Image;
        string outputPath = $"extracted_{imageIndex++}.png";
        img.Save(outputPath, ImageFormat.Png);
    }
}

pdf.Close();

Common Use Cases

Updating logos across a document library after a rebrand
Extracting product images from PDF catalogs for use in other systems
Redacting or removing images from documents before public release
Auditing document libraries to inventory embedded visual assets

Automated image extraction and replacement make it easier to manage visual assets embedded within PDF documents at scale.

Task 9: Adding Digital Signatures and Encryption

As PDF documents move through approval processes, legal reviews, and customer-facing workflows, security becomes increasingly important. Organizations often need to verify document authenticity, protect sensitive information, and prevent unauthorized modifications.

By automating digital signatures and encryption, developers can integrate security directly into document workflows instead of relying on manual steps.

Typical Challenges

Certificate expiry in production is easy to overlook until signatures start failing
Signature appearance (position, size, visible stamp vs. invisible signature) needs to match document layout, which varies across templates
Encryption permission levels need deliberate configuration — overly restrictive settings can break legitimate downstream workflows

Code Example

The following example encrypts a PDF document with a password:

using Spire.Pdf;
using Spire.Pdf.Security;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("sensitive_report.pdf");

string userPassword = "userpass123";
string ownerPassword = "ownerpass456"; 
PdfSecurityPolicy securityPolicy = new PdfPasswordSecurityPolicy(userPassword, ownerPassword);

securityPolicy.EncryptionAlgorithm = PdfEncryptionAlgorithm.AES_256;

securityPolicy.DocumentPrivilege = PdfDocumentPrivilege.AllowAll;

securityPolicy.DocumentPrivilege.AllowPrint = false;

pdf.Encrypt(securityPolicy);

pdf.SaveToFile("encrypted_report.pdf");
pdf.Close();

Digital signatures can also be applied using a certificate file (such as a PFX certificate) to verify the identity of the document sender and detect unauthorized changes.

Adding a digital signature using a PFX certificate:

using Spire.Pdf;
using Spire.Pdf.Security;
using System.Drawing;

PdfDocument pdf = new PdfDocument();
pdf.LoadFromFile("contract.pdf");

PdfCertificate certificate = new PdfCertificate("signing_cert.pfx", "certpassword");
PdfSignature signature = new PdfSignature(pdf, pdf.Pages[0], certificate, "AuthorSignature");

signature.Bounds = new RectangleF(300, 700, 200, 80);
signature.Certificated = true;
signature.DocumentPermissions = PdfCertificationFlags.ForbidChanges;

pdf.SaveToFile("signed_contract.pdf");
pdf.Close();

Common Use Cases

Signing contracts generated by your application before delivery
Encrypting sensitive reports — payroll, legal, medical — before distribution
Locking down finalized documents to prevent unauthorized edits
Meeting compliance requirements (HIPAA, GDPR, SOX) that mandate document-level access controls

Automating PDF security helps organizations protect sensitive data while ensuring that documents remain trustworthy throughout their lifecycle.

Task 10: Automating End-to-End PDF Workflows

Most real-world PDF automation projects don't stop at a single task. Instead, they combine multiple operations into a complete workflow that runs with minimal human intervention.

For example, an application may retrieve data from a database, generate a PDF report, apply a watermark, encrypt the file, and then distribute it to customers automatically.

This is where PDF automation delivers its greatest value—not by automating one action, but by automating an entire business process.

Typical Challenges

Dynamic data means dynamic layout — tables that grow across pages need automatic pagination
Keeping document styling consistent (fonts, colors, column widths) across varying data volumes requires separating layout logic from data logic
Scheduled workflows need explicit error handling; a failed run that produces no output must surface clearly, not silently disappear

Code Example

The following example demonstrates a typical workflow:

using Spire.Pdf;
using Spire.Pdf.Graphics;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;

// Step 1: Fetch business data
DataTable sales = new DataTable();
string connectionString = "your_connection_string";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(
        "SELECT Region, Product, Revenue FROM SalesSummary WHERE Month = @Month",
        conn
    );
    adapter.SelectCommand.Parameters.AddWithValue("@Month", DateTime.Now.Month);
    adapter.Fill(sales);
}

// Step 2: Generate PDF from data
PdfDocument pdf = new PdfDocument();
PdfPageBase page = pdf.Pages.Add();

PdfTrueTypeFont titleFont = new PdfTrueTypeFont(new Font("Arial", 18f, FontStyle.Bold));
PdfTrueTypeFont bodyFont = new PdfTrueTypeFont(new Font("Arial", 11f, FontStyle.Regular));
PdfBrush black = PdfBrushes.Black;

float y = 40f;
page.Canvas.DrawString("Monthly Sales Report", titleFont, black, new PointF(40, y));
y += 40f;

foreach (DataRow row in sales.Rows)
{
    string line = $"{row["Region"],-20}{row["Product"],-25}{row["Revenue"],10:C}";
    page.Canvas.DrawString(line, bodyFont, black, new PointF(40, y));
    y += 22f;
}

// Step 3: Apply watermark to mark document status
PdfTrueTypeFont watermarkFont = new PdfTrueTypeFont(new Font("Arial", 48f, FontStyle.Regular));
PdfBrush watermarkBrush = new PdfSolidBrush(Color.FromArgb(80, Color.Gray));

page.Canvas.SetTransparency(0.3f);
page.Canvas.TranslateTransform(page.ActualSize.Width / 2, page.ActualSize.Height / 2);
page.Canvas.RotateTransform(-45);
page.Canvas.DrawString("INTERNAL USE ONLY", watermarkFont, watermarkBrush, new PointF(-160, 0));

// Step 4: Save final output
string outputPath = $"sales_report_{DateTime.Now:yyyyMMdd}.pdf";
pdf.SaveToFile(outputPath);
pdf.Close();

Common Use Cases

Nightly or weekly sales and operations reports delivered to management
Monthly client statements generated from billing or CRM data
Automated compliance reports triggered by regulatory deadlines
On-demand reporting in self-service portals where users request their own data exports

By combining document generation, processing, security, and delivery into a single workflow, organizations can dramatically reduce manual effort while improving consistency and reliability.

What About Other PDF Libraries?

Some popular options include PDFsharp, QuestPDF, iText 7, PDFPig, Aspose.PDF, IronPDF, and Spire.PDF.

Each library has different strengths.

PDFsharp is a lightweight option for basic PDF creation and manipulation.
QuestPDF is particularly popular for code-based PDF generation and modern document layouts.
iText 7 provides a powerful feature set for advanced PDF processing and enterprise workflows.
Aspose.PDF Comprehensive commercial PDF toolkit.
IronPDF Focused on HTML-to-PDF and rendering scenarios.
Spire.PDF provides a broad set of PDF processing features—including generation, conversion, forms, security, and document manipulation—through a consistent API.

Conclusion

PDF automation is no longer a niche requirement. From generating invoices and filling forms to extracting content, securing documents, and delivering reports, PDF processing plays a critical role in many modern .NET applications.

The ten tasks covered in this article represent some of the most common PDF automation scenarios developers encounter in real-world projects. While you may not need all of them today, chances are that several already exist somewhere in your current workflow.

By automating repetitive document operations, teams can reduce manual effort, improve consistency, and build more scalable business processes. Start with the task that causes the most friction in your application, then gradually expand your automation pipeline from there.

Which PDF task are you currently automating in your .NET applications?

Did I miss any PDF workflows that your team handles regularly?

Let me know in the comments.

12 Excel Import Mistakes Every ASP.NET Core Developer Should Avoid

Chloe — Mon, 08 Jun 2026 09:20:38 +0000

Excel import often seems straightforward—until it reaches production.

A spreadsheet uploaded by one user works perfectly, while another triggers validation failures, duplicate records, incorrect dates, or performance issues. Reading Excel files is usually the easy part. The real challenge is handling inconsistent, user-generated data reliably and at scale.

In this article, we'll explore the most common pitfalls developers encounter when importing Excel files in ASP.NET Core applications and discuss practical ways to avoid them. We'll cover file validation, worksheet parsing, data validation, duplicate detection, performance optimization, and security considerations.

The examples use Spire.XLS for worksheet parsing, but the concepts apply equally to EPPlus, ClosedXML, NPOI, MiniExcel, and other Excel libraries.

The Typical Excel Import Pipeline

Many developers think of Excel import as:

Read Excel → Save to Database

In production, the process is usually more complex:

Upload File
    ↓
Validate File
    ↓
Parse Worksheet
    ↓
Transform Data
    ↓
Validate Business Rules
    ↓
Detect Duplicates
    ↓
Bulk Insert
    ↓
Generate Import Report

Each stage introduces different risks. Understanding the pipeline makes it easier to identify where problems occur and how to prevent them.

Secure File Uploads

Pipeline Stage: Upload File

Before reading worksheets and validating rows, make sure the uploaded file itself is safe to process.

Many import implementations focus heavily on parsing logic while performing little validation on the uploaded file. This can lead to security issues, resource exhaustion, and unexpected runtime failures.

Pitfall #1 — Trusting the File Extension

A file named employees.xlsx is not necessarily a valid Excel file.

Users may accidentally upload the wrong file, and malicious actors can rename almost any file to use an .xlsx extension. If your application trusts the filename alone, invalid files may reach your parser and cause failures.

Instead of validating only the extension, verify the file content itself.

Modern .xlsx files are ZIP-based packages and begin with the magic number 50 4B 03 04.

private static bool IsValidExcelFile(IFormFile file)
{
    using var stream = file.OpenReadStream();

    byte[] magicBytes = new byte[4];
    int bytesRead = stream.Read(magicBytes, 0, 4);

    if (bytesRead < 4)
        return false;

    return magicBytes[0] == 0x50 &&
           magicBytes[1] == 0x4B &&
           magicBytes[2] == 0x03 &&
           magicBytes[3] == 0x04;
}

You should also validate MIME types:

private static readonly string[] AllowedMimeTypes =
{
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "application/vnd.ms-excel"
};

private static bool HasValidMimeType(IFormFile file) =>
    AllowedMimeTypes.Contains(file.ContentType);

A simple rule:

Never trust the filename. Always validate the file itself.

Pitfall #2 — No Upload Size Limit

Excel files are usually small during development.

In production, users may upload spreadsheets containing tens of thousands of rows, embedded images, or multiple worksheets. Without limits, a single upload can consume excessive server resources.

ASP.NET Core allows upload limits directly on the endpoint:

[HttpPost("import")]
[RequestSizeLimit(10_000_000)] // 10 MB
[RequestFormLimits(MultipartBodyLengthLimit = 10_000_000)]
public async Task<IActionResult> Import(IFormFile file)
{
    if (file == null || file.Length == 0)
        return BadRequest("No file uploaded.");

    if (!HasValidMimeType(file))
        return BadRequest("Invalid file type.");

    if (!IsValidExcelFile(file))
        return BadRequest("File does not appear to be a valid Excel document.");

    // proceed to parsing
}

The exact limit depends on your business requirements, but having no limit at all is rarely a good idea.

Reading Excel Data Reliably

Pipeline Stage: Parse Worksheet

Once a file passes upload validation, the next challenge is extracting data correctly.

Many import implementations work perfectly with carefully prepared sample files but fail when real users upload spreadsheets containing blank rows, reordered columns, or unexpected formatting.

Pitfall #3 — Hardcoded Column Indexes

A common approach is reading data using fixed column positions:

var name = row[0].Value;
var email = row[3].Value;
var department = row[5].Value;

This works until someone rearranges the worksheet.

If the Email column is moved, your import logic silently starts mapping the wrong data.

A safer approach is header-based mapping:

var headerRow = sheet.Rows[0];
var headers = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);

for (int i = 0; i < headerRow.Columns.Count; i++)
{
    var headerValue = headerRow.Columns[i].Value?.ToString()?.Trim();

    if (!string.IsNullOrEmpty(headerValue))
        headers[headerValue] = i;
}

var requiredHeaders = new[] { "Name", "Email", "Department" };

var missingHeaders = requiredHeaders
    .Where(h => !headers.ContainsKey(h))
    .ToList();

if (missingHeaders.Any())
    return BadRequest(
        $"Missing required columns: {string.Join(", ", missingHeaders)}");

for (int i = 1; i < sheet.Rows.Count; i++)
{
    var row = sheet.Rows[i];

    var name = row.Columns[headers["Name"]].Value?.ToString();
    var email = row.Columns[headers["Email"]].Value?.ToString();
    var department = row.Columns[headers["Department"]].Value?.ToString();
}

With this approach, column order becomes irrelevant.

Pitfall #4 — Assuming Row 1 Is Always the Header

Real-world spreadsheets often contain title rows, metadata, or blank lines before the actual header row.

Instead of assuming the first row contains headers, search for the required column names before processing data.

private static int FindHeaderRowIndex(
    Worksheet sheet,
    IEnumerable<string> expectedHeaders)
{
    for (int i = 0; i < Math.Min(sheet.Rows.Count, 10); i++)
    {
        var row = sheet.Rows[i];

        var cellValues = row.Columns
            .Select(c => c.Value?.ToString()?.Trim() ?? string.Empty)
            .ToList();

        if (expectedHeaders.All(h =>
            cellValues.Any(v =>
                v.Equals(h, StringComparison.OrdinalIgnoreCase))))
        {
            return i;
        }
    }

    return -1;
}

Once the header row is located, begin importing from the next row.

This small improvement makes your import process much more tolerant of user-generated spreadsheets.

Pitfall #5 — Empty Rows in the Middle of Data

Users frequently insert blank rows to visually separate sections of data.

Without proper checks, blank rows may cause:

Validation errors
Null reference exceptions
Empty database records

Verify that a row contains data before processing it.

private static bool IsRowEmpty(CellRange row)
{
    return row.Columns.All(c =>
        string.IsNullOrWhiteSpace(c.Value?.ToString()));
}

Then skip empty rows:

for (int i = headerRowIndex + 1; i < sheet.Rows.Count; i++)
{
    var row = sheet.Rows[i];

    if (IsRowEmpty(row))
        continue;

    // process row
}

Filtering empty rows early keeps downstream validation logic much cleaner.

Pitfall #6 — Mixed Data Types in a Column

Excel allows users to enter almost anything into a cell.

Consider a Quantity column:

100
250
N/A
-

Direct conversions can easily throw exceptions:

int quantity = int.Parse(cell.Value);

A safer approach is defensive parsing:

private static int? ParseInt(string value)
{
    if (string.IsNullOrWhiteSpace(value))
        return null;

    return int.TryParse(
        value.Trim(),
        out var result)
        ? result
        : null;
}

Even better, record invalid values as validation errors and allow users to correct them later.

The goal isn't to trust Excel data—it's to assume the data may be imperfect and handle it gracefully.

Validation & Error Reporting

Pipeline Stage: Validate Business Rules

Successfully reading worksheet data doesn't mean the data is valid.

Rows may contain missing fields, malformed emails, invalid dates, negative quantities, or values that violate business rules. The real challenge is deciding how your import process should respond when those errors occur.

Pitfall #7 — Inadequate Validation Strategy

If you've ever received an Excel file with 100 rows and multiple data issues, you've probably experienced this.

The importer finds the first error.

The user fixes it.

Uploads again.

Then discovers the second error.

And repeats that cycle over and over.

Problem 1: Failing Fast

A common mistake is stopping the import as soon as the first error is encountered:

foreach (var row in rows)
{
    if (string.IsNullOrEmpty(row.Email))
        throw new Exception($"Row {row.Index}: Email is required.");

    Save(row);
}

While simple, this approach creates a poor user experience.

Imagine a spreadsheet containing hundreds of rows. The user fixes the first error, uploads again, discovers another error, fixes that one, uploads again, and repeats the process several times.

Instead, collect all validation errors and return them together.

A simple pattern is to separate valid rows from invalid ones:

public class ImportResult<T>
{
    public List<T> ValidRows { get; set; } = new();
    public List<RowError> Errors { get; set; } = new();
    public int TotalRows { get; set; }

    public bool HasErrors => Errors.Any();
    public int SuccessCount => ValidRows.Count;
    public int ErrorCount => Errors.Count;
}

public class RowError
{
    public int RowNumber { get; set; }
    public string Column { get; set; }
    public string Message { get; set; }
}

This allows users to see every issue in a single import attempt.

Problem 2: Missing Business Validation

Another common mistake is validating file structure but not validating business rules.

Consider:

Name  = ""
Email = "abc"
Age   = -10

Excel can read these values successfully, but they clearly violate business requirements.

A dedicated validator for each imported entity keeps validation logic organized and maintainable.

Using FluentValidation:

public class EmployeeRowValidator : AbstractValidator<EmployeeRow>
{
    public EmployeeRowValidator()
    {
        RuleFor(x => x.Name)
            .NotEmpty()
            .MaximumLength(100);

        RuleFor(x => x.Email)
            .NotEmpty()
            .EmailAddress();

        RuleFor(x => x.Age)
            .InclusiveBetween(18, 100);
    }
}

Integrate validation into the import process:

var validator = new EmployeeRowValidator();
var result = new ImportResult<EmployeeRow>();

for (int i = 0; i < dataRows.Count; i++)
{
    var row = dataRows[i];
    var rowNumber = i + 2;

    var validationResult = validator.Validate(row);

    if (!validationResult.IsValid)
    {
        foreach (var error in validationResult.Errors)
        {
            result.Errors.Add(new RowError
            {
                RowNumber = rowNumber,
                Column = error.PropertyName,
                Message = error.ErrorMessage
            });
        }
    }
    else
    {
        result.ValidRows.Add(row);
    }
}

Turning Errors into Actionable Feedback

Once validation is complete, return structured error information that helps users fix problems quickly:

if (result.HasErrors)
{
    return UnprocessableEntity(new
    {
        totalRows = result.TotalRows,
        successCount = result.SuccessCount,
        errorCount = result.ErrorCount,
        errors = result.Errors
    });
}

A response like this allows the frontend to display row numbers, column names, and validation messages in a user-friendly error report.

Handling Duplicate Records

Pipeline Stage: Detect Duplicates

Import failures are obvious.

Duplicate records are often more dangerous because they can go unnoticed for a long time.

Pitfall #8 — The Duplicate Problem Nobody Notices

Consider:

Name          Email
John Smith    john@example.com
Sarah Jones   sarah@example.com
John Smith    john@example.com

Without duplicate detection, both rows may be inserted successfully.

Fix #1 — Detect Duplicates Within the File

Track unique values during processing.

For example, if email addresses must be unique:

var seenEmails = new HashSet<string>();

foreach (var employee in employees)
{
    if (!seenEmails.Add(employee.Email))
    {
        errors.Add(new RowError
        {
            Message = $"Duplicate email: {employee.Email}"
        });
    }
}

Fix #2 — Enforce Database Constraints

Application-level validation should never be your only protection.

Create a database constraint:

CREATE UNIQUE INDEX IX_Employees_Email
ON Employees(Email);

Even if duplicate records slip through application validation, the database prevents them from being stored.

When Upserts Make Sense

In some scenarios, users regularly re-import existing datasets.

Instead of rejecting existing records, consider an upsert strategy where existing records are updated while new records are inserted. This approach prevents duplicate data while allowing recurring imports to remain efficient and user-friendly.

Performance at Scale

Pipeline Stage: Parse Worksheet + Bulk Insert

Most Excel import features are tested with files containing only a few hundred rows.

Everything works perfectly—until someone uploads a spreadsheet with 50,000 rows.

Pitfall #9 — Loading the Entire Workbook into Memory

Many Excel libraries load the entire workbook before processing begins.

For large files, this can dramatically increase memory usage and reduce scalability.

If you only need sequential access to rows, consider streaming instead.

MiniExcel supports asynchronous streaming:

using var stream = file.OpenReadStream();

await foreach (var row in MiniExcel.QueryAsync<EmployeeRow>(stream))
{
    // Validate and process row
}

Streaming reduces memory consumption and makes large imports much more efficient.

As a general rule:

If you expect imports containing tens of thousands of rows, avoid keeping the entire dataset in memory whenever possible.

Pitfall #10 — When SaveChanges() Becomes the Bottleneck

Another common bottleneck appears during database insertion.

The following pattern generates one database transaction per row:

foreach (var row in result.ValidRows)
{
    _dbContext.Employees.Add(new Employee { ... });
    await _dbContext.SaveChangesAsync();
}

For large imports, performance quickly deteriorates.

A much better option is bulk insertion.

SqlBulkCopy

For SQL Server, SqlBulkCopy is one of the fastest approaches available:

private async Task BulkInsertAsync(List<EmployeeRow> rows)
{
    var table = new DataTable();

    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Email", typeof(string));
    table.Columns.Add("Department", typeof(string));

    foreach (var row in rows)
    {
        table.Rows.Add(
            row.Name,
            row.Email,
            row.Department);
    }

    using var connection = new SqlConnection(_connectionString);

    await connection.OpenAsync();

    using var transaction = connection.BeginTransaction();
    using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction)
    {
        DestinationTableName = "Employees",
        BatchSize = 1000
    };

    bulkCopy.ColumnMappings.Add("Name", "Name");
    bulkCopy.ColumnMappings.Add("Email", "Email");
    bulkCopy.ColumnMappings.Add("Department", "Department");

    try
    {
        await bulkCopy.WriteToServerAsync(table);
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Bulk operations can reduce import times dramatically when processing large datasets.

Consider Background Processing for Very Large Imports

Even with streaming and bulk inserts, imports containing hundreds of thousands of rows may still take time to complete.

For these scenarios, consider processing files asynchronously using tools such as Hangfire, .NET Worker Services, or Azure Functions. Returning a job ID immediately and performing the import in the background improves user experience and makes retries easier when failures occur.

Culture & Formatting Issues

Pipeline Stage: Clean & Transform

Some of the most difficult import bugs are caused by data that appears valid but is interpreted differently depending on culture or locale.

Pitfall #11 — Culture-Specific Formatting

Date parsing is one of the most common examples.

The value:

01/02/2025

could represent:

January 2, 2025
February 1, 2025

depending on locale.

The safest approach is to define a standard format and enforce it consistently.

For example:

Date of Joining (yyyy-MM-dd)

Then parse using an explicit format:

private static DateTime? ParseDate(string value)
{
    if (string.IsNullOrWhiteSpace(value))
        return null;

    if (DateTime.TryParseExact(
            value.Trim(),
            "yyyy-MM-dd",
            CultureInfo.InvariantCulture,
            DateTimeStyles.None,
            out var result))
    {
        return result;
    }

    return null;
}

This ensures consistent behavior regardless of server configuration.

Decimal Separator Confusion

Decimal values present a similar challenge.

Examples:

1,234.56   (US)
1.234,56   (EU)
1 234,56   (Some locales)

Without normalization, the same value may be interpreted differently.

A defensive parsing approach:

private static decimal? ParseDecimal(string value)
{
    if (string.IsNullOrWhiteSpace(value))
        return null;

    var normalized = value.Trim();

    if (normalized.Contains(',') && normalized.Contains('.'))
    {
        int lastComma = normalized.LastIndexOf(',');
        int lastPeriod = normalized.LastIndexOf('.');

        if (lastComma > lastPeriod)
        {
            normalized = normalized
                .Replace(".", "")
                .Replace(",", ".");
        }
        else
        {
            normalized = normalized.Replace(",", "");
        }
    }

    return decimal.TryParse(
        normalized,
        NumberStyles.Any,
        CultureInfo.InvariantCulture,
        out var result)
        ? result
        : null;
}

For applications where numeric precision is critical, the best solution is often to enforce a consistent format through import templates and validation rules.

The Excel Security Risk Most Developers Ignore

Pipeline Stage: Parse + Store

File validation protects your application from invalid uploads, but it doesn't eliminate all security risks. Even a valid Excel file may contain malicious content or exploit weaknesses in the parsing process.

Uploaded spreadsheets are external input, and external input should never be trusted.

Pitfall #12 — Formula Injection

Formula injection occurs when user-supplied values are later exported back to Excel and interpreted as formulas.

For example:

=CMD|'/C calc'!A0

Your import process may treat this as a normal string and store it successfully. Later, when the data is exported and opened in Excel, the value may be executed as a formula.

The characters that trigger formula evaluation are:

=
+
-
@

A simple mitigation is to sanitize any string beginning with these characters.

private static readonly char[] FormulaStartChars =
{
    '=', '+', '-', '@'
};

private static string SanitizeCell(string value)
{
    if (string.IsNullOrEmpty(value))
        return value;

    if (FormulaStartChars.Contains(value[0]))
        return "'" + value;

    return value;
}

Prefixing the value with an apostrophe forces Excel to treat it as plain text instead of a formula.

Apply sanitization during the transformation stage before validation and persistence:

private static EmployeeRow TransformRow(RawRow raw)
{
    return new EmployeeRow
    {
        Name = SanitizeCell(raw.Name?.Trim()),
        Email = SanitizeCell(raw.Email?.Trim()),
        Department = SanitizeCell(raw.Department?.Trim()),
        Notes = SanitizeCell(raw.Notes?.Trim())
    };
}

Processing Malicious Files Without Isolation

Formula injection targets the data inside a workbook.

A different risk comes from the workbook itself.

Malformed .xlsx files can trigger excessive resource consumption or exploit vulnerabilities in parsing libraries. While actively maintained libraries reduce this risk, production systems should still take precautions.

For most applications, the practical minimum is:

Keep parsing libraries updated
Apply upload size limits
Use rate limiting
Consider background processing or isolated workers for large imports

Example rate limiting configuration:

builder.Services.AddRateLimiter(options =>
{
    options.AddFixedWindowLimiter("import", limiter =>
    {
        limiter.PermitLimit = 5;
        limiter.Window = TimeSpan.FromMinutes(1);
    });
});

These safeguards help reduce the impact of malicious or resource-intensive uploads.

Testing Your Import Pipeline

By this point, we've covered file validation, parsing, validation rules, duplicate detection, performance optimization, formatting issues, and security concerns.

The final step is ensuring those protections continue working as your application evolves.

A simple strategy is maintaining a collection of test files that target common edge cases:

testdata/
├── empty.xlsx
├── duplicate-emails.xlsx
├── invalid-dates.xlsx
└── 100k-rows.xlsx

Whenever a production issue is discovered, add a corresponding test file to the collection.

For example:

[Fact]
public void Validation_CollectsAllErrors_NotJustFirst()
{
    var file = ExcelTestFixtures.CreateWithRows(new[]
    {
        new EmployeeRow
        {
            Name = "",
            Email = "notanemail"
        },
        new EmployeeRow
        {
            Name = "Valid",
            Email = "valid@test.com"
        }
    });

    var result = ImportPipeline.Run(file);

    Assert.Equal(1, result.SuccessCount);
    Assert.Equal(2, result.ErrorCount);
}

A useful rule of thumb:

Every import bug you fix should become a test case.

Over time, this creates a safety net that prevents old issues from reappearing.

Final Thoughts

If there's one lesson I've learned from building Excel import features, it's this:

Reading Excel is rarely the hard part.

The difficult part is dealing with everything that happens after the file is uploaded.

Validation.
Duplicate detection.
Data transformation.
Performance.
Security.
Error reporting.

Those are the areas that determine whether an importer survives real-world usage.

The good news is that most production issues aren't caused by complex algorithms. They're usually caused by assumptions.

Assumptions about file structure.
Assumptions about data quality.
Assumptions about scale.

Question those assumptions early, and your Excel import pipeline will be far more reliable when real users start uploading real spreadsheets.

How to Import Excel Data in ASP.NET Core (Upload, Parse and Read Worksheets)

Chloe — Mon, 01 Jun 2026 08:35:15 +0000

Importing data from Excel is a common requirement in ASP.NET Core applications. Whether you're building an HR system, inventory platform, CRM, or financial dashboard, chances are you'll eventually need to process spreadsheet data uploaded by users.

At first glance, reading an Excel file seems straightforward: upload the file, open the workbook, and loop through the rows. In practice, real-world imports often involve validation, missing values, and data transformation before records can be used by the application.

In this article, we'll walk through the complete process of reading Excel files in ASP.NET Core. You'll learn how to:

Upload an Excel file from a web application
Open and access worksheets
Read data from rows and columns
Convert spreadsheet data into C# objects
Prepare imported data for further processing

By the end, you'll have a practical foundation for building Excel import features in your own applications.

Understanding the Excel Import Workflow

Before diving into code, it's helpful to understand the typical workflow of an Excel import feature.

Upload File
    ↓
Open Workbook
    ↓
Select Worksheet
    ↓
Read Rows
    ↓
Extract Values
    ↓
Map to Objects

Most ASP.NET Core applications follow a similar process. Users upload a spreadsheet, the application reads the workbook, extracts data from specific worksheets, and then converts that data into business objects that can be validated, processed, or stored in a database.

In this tutorial, we'll use a simple employee import example to demonstrate each step.

Setting Up the Project

There are several libraries available for reading Excel files in .NET, including Open XML SDK, EPPlus, ClosedXML, and Spire.XLS.

For this tutorial, we'll use Spire.XLS for .NET because it provides a straightforward API for working with Excel files and supports common spreadsheet operations such as reading data, creating workbooks, formatting cells, and converting Excel documents.

If you're evaluating Excel libraries for a production project, the best choice will depend on your requirements, such as licensing, feature set, performance, and deployment needs. However, the overall workflow demonstrated in this article is similar regardless of the library you choose.

We'll use the following sample worksheet throughout this tutorial:

Name	Email	Department
John Smith	john@company.com	Sales
Sarah Lee	sarah@company.com	HR
David Chen	david@company.com	IT

Our goal is to read each row and convert it into a C# object that can be processed by the application.

Install Spire.XLS via NuGet:

Install-Package Spire.XLS

Once installed, we're ready to build the upload and import functionality.

Uploading an Excel File in ASP.NET Core

The first step is allowing users to upload an Excel file to your application.

ASP.NET Core makes this straightforward through the IFormFile interface, which represents a file sent with an HTTP request.

Here's a simple API endpoint that accepts an uploaded Excel file:

[HttpPost("import")]
public IActionResult Import(IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        return BadRequest("Please upload a valid Excel file.");
    }

    // Excel processing logic goes here

    return Ok();
}

Before attempting to read the workbook, it's a good idea to perform some basic validation:

Ensure a file was uploaded
Check the file extension (.xlsx or .xls)
Verify the file size is within acceptable limits

var extension = Path.GetExtension(file.FileName);

if (extension != ".xlsx" && extension != ".xls")
{
    return BadRequest("Only Excel files are supported.");
}

These checks help prevent unnecessary processing and provide better feedback to users.

Opening the Workbook and Accessing a Worksheet

Once the file has been uploaded, the next step is to load it into memory and access the workbook.

A workbook represents the entire Excel file, while a worksheet represents an individual sheet inside that workbook.

The following example loads the uploaded file and retrieves the first worksheet:

using Spire.Xls;

using var stream = file.OpenReadStream();

Workbook workbook = new Workbook();
workbook.LoadFromStream(stream);

Worksheet sheet = workbook.Worksheets[0];

At this point, the workbook has been loaded and is ready for worksheet access and data extraction.

If you're working with spreadsheets provided by end users, it's often worth checking that the expected worksheet actually exists before continuing.

For example, an import template might require a sheet named "Employees":

Worksheet sheet = workbook.Worksheets["Employees"];

Using a known worksheet name can make imports more predictable and easier to maintain.

Reading Data from Rows and Columns

Now comes the core part of the import process: reading values from rows and columns.

Most Excel import scenarios involve a header row followed by data rows.

For example:

Name	Email	Department
John Smith	john@company.com	Sales
Sarah Lee	sarah@company.com	HR
David Chen	david@company.com	IT

We can loop through the worksheet and extract values from each row:

int lastRow = sheet.LastDataRow;
for (int row = 2; row <= sheet.LastRow; row++)
{
    string name = sheet.Range[row, 1].Text;
    string email = sheet.Range[row, 2].Text;
    string department = sheet.Range[row, 3].Text;

    Console.WriteLine($"{name} - {email} - {department}");
}

Notice that we start at row 2 because row 1 contains column headers.

Depending on the workbook, the last used row may include rows that contain formatting but no meaningful data, so additional validation is often recommended in production imports.

The .Text property returns the displayed value of a cell, making it a convenient choice for importing user-entered data.

Running the code above would produce output similar to:

John Smith - john@company.com - Sales
Sarah Lee - sarah@company.com - HR
David Chen - david@company.com - IT

At this stage, we're successfully reading data from Excel. However, simply printing values isn't very useful in a real application. Typically, the next step is converting those values into business objects that can be validated, processed, and saved.

Converting Excel Data into C# Objects

In most applications, the goal isn't simply to read cell values—it's to transform spreadsheet data into objects that the rest of the application can work with.

Let's start by defining a simple model:

public class Employee
{
    public string Name { get; set; }

    public string Email { get; set; }

    public string Department { get; set; }
}

Instead of writing values directly to the console, we can create an Employee object for each row and add it to a collection.

List<Employee> employees = new List<Employee>();

for (int row = 2; row <= sheet.LastRow; row++)
{
    Employee employee = new Employee
    {
        Name = sheet.Range[row, 1].Text,
        Email = sheet.Range[row, 2].Text,
        Department = sheet.Range[row, 3].Text
    };

    employees.Add(employee);
}

After the loop completes, the employees list contains all records from the worksheet and can be used throughout the application.

For example:

foreach (var employee in employees)
{
    Console.WriteLine(employee.Name);
}

This approach keeps the Excel-reading logic separate from your business logic and makes the imported data easier to validate, process, and store.

Handling Empty Cells and Missing Values

Real-world spreadsheets are rarely perfect.

Users may leave cells blank, accidentally delete values, or upload partially completed templates.

For example:

Name	Email	Department
John Smith	john@company.com	Sales
Sarah Lee		HR
David Chen	david@company.com

If your application assumes every cell contains data, these missing values can cause problems later in the import process.

A simple approach is to normalize empty cells when reading them:

string name = sheet.Range[row, 1].Text?.Trim() ?? "";
string email = sheet.Range[row, 2].Text?.Trim() ?? "";
string department = sheet.Range[row, 3].Text?.Trim() ?? "";

You can then decide how your application should handle incomplete records.

For example:

if (string.IsNullOrWhiteSpace(email))
{
    continue;
}

In this example, rows without an email address are skipped.

Whether you skip, reject, or flag incomplete records depends on your business requirements, but it's important to account for missing values from the start.

Saving Imported Data

Once the spreadsheet data has been converted into objects, the next step is usually storing it somewhere.

A typical workflow looks like this:

Excel File
     ↓
Employee Objects
     ↓
Validation
     ↓
Database

For example, after building the employee collection, you might save the records using Entity Framework Core:

foreach (var employee in employees)
{
    dbContext.Employees.Add(employee);
}

await dbContext.SaveChangesAsync();

Because database operations are typically asynchronous, the import endpoint would usually be implemented as an async action method.

The exact implementation will vary depending on your project, but the important point is that Excel data should first be converted into strongly typed objects before being persisted.

This keeps your application easier to maintain and reduces the likelihood of data-related issues later on.

Complete Example

The following example combines all the steps we've covered so far:

[HttpPost("import")]
public IActionResult Import(IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        return BadRequest("Please upload a valid Excel file.");
    }

    var extension = Path.GetExtension(file.FileName);

    if (extension != ".xlsx" && extension != ".xls")
    {
        return BadRequest("Only Excel files are supported.");
    }

    List<Employee> employees = new();

    using var stream = file.OpenReadStream();

    Workbook workbook = new Workbook();
    workbook.LoadFromStream(stream);

    Worksheet sheet = workbook.Worksheets[0];

    int lastRow = sheet.LastDataRow;
    for (int row = 2; row <= sheet.Last; row++)
    {
        string name = sheet.Range[row, 1].Text?.Trim() ?? "";
        string email = sheet.Range[row, 2].Text?.Trim() ?? "";
        string department = sheet.Range[row, 3].Text?.Trim() ?? "";

        if (string.IsNullOrWhiteSpace(email))
        {
            continue;
        }

        employees.Add(new Employee
        {
            Name = name,
            Email = email,
            Department = department
        });
    }

    return Ok(employees);
}

This example demonstrates a simple but practical import workflow:

Receive the uploaded file
Open the workbook
Access the worksheet
Read rows and columns
Create C# objects
Return the imported data

In a production application, you would typically add validation, error handling, and database persistence, but the overall process remains the same.

Common Issues When Reading Excel Files

Reading data from an Excel worksheet is usually the easy part. The real challenges often appear once users start uploading files created by different people, departments, or systems.

Some of the most common issues include:

Empty Rows

Many spreadsheets contain blank rows between records or at the end of the worksheet. If these rows aren't handled properly, your import process may create empty objects or unnecessary database entries.

Mixed Data Types

Unlike a database table, Excel allows different cells within the same column to contain different data types. A column that is expected to contain numeric values may suddenly include text such as:

This can lead to conversion errors during processing.

Inconsistent Date Formats

Dates are one of the most common sources of import issues.

Different users may enter dates in different formats:

2026-01-15
01/15/2026
15-Jan-2026

Problems are especially common when dates are stored as text rather than native Excel date values.

Duplicate Records

Importing customer, employee, or product data often introduces duplicate records.

For example, the same email address might appear multiple times in a worksheet, creating conflicts when data is stored in a database.

Large Excel Files

A spreadsheet containing a few hundred rows is usually easy to process. However, files containing tens of thousands of rows may require additional considerations around performance, memory usage, and request timeouts.

These issues don't prevent Excel imports from working, but they do require additional validation and error-handling logic in production environments.

Conclusion

Reading Excel files in ASP.NET Core isn't particularly difficult. In fact, with the right library, you can start extracting data from spreadsheets in just a few lines of code.

The bigger challenge is what happens after the data is read.

In real applications, imported data is often incomplete, inconsistent, or simply incorrect. That's why a successful Excel import feature isn't just about opening a workbook and looping through rows—it's about building a workflow that can handle real-world data reliably.

A good import process should not only read data, but also validate it, report errors clearly, and integrate smoothly with the rest of your application.

Once you've mastered the basics of reading Excel files, you'll be in a much better position to build robust import features for customer records, inventory systems, financial reports, and other business workflows.

What looks like a simple file upload today often becomes a critical part of an application's data pipeline tomorrow.

Best C# Libraries for Excel Automation in 2026 (Free vs Paid)

Chloe — Mon, 25 May 2026 09:40:41 +0000

Excel automation in .NET seems easy—until you're generating 500,000 rows on a Linux server at 2 AM without crashing.

Memory spikes, slow exports, broken charts in Docker, licensing traps, and Microsoft.Office.Interop.Excel failures can turn a simple feature into a maintenance nightmare.

Unlike traditional Office Interop automation, all libraries covered here support Office-free server-side processing across modern .NET environments.

This guide skips the noise and focuses on 7 proven C# Excel libraries actively used in real-world .NET projects in 2026—whether you're building ASP.NET backends, reporting systems, ERP platforms, or financial dashboards.

No fluff. Just the right library for your actual scenario.

Quick Comparison Table
What Actually Matters in 2026
Best Free C# Libraries for Excel Automation
Best Commercial Excel Libraries for .NET
Head-to-Head: Deep Comparison
Choosing the Right Excel Library for ASP.NET and Backend Systems
Performance Considerations for Large Excel Files
Common Pitfalls Developers Often Miss
Conclusion

Quick Comparison Table

Before diving into the details, here's a high-level overview of all 7 libraries:

Library	License	.xlsx	Large Files	Charts	Formula	Best For
EPPlus	Polyform	✅	⚠️	✅	✅	Mid-size projects with rich formatting needs
ClosedXML	MIT	✅	⚠️	⚠️	✅	Clean API, low onboarding cost
Open XML SDK	MIT	✅	✅	⚠️	❌	Fine-grained document structure control
MiniExcel	Apache 2.0	✅	✅✅	❌	❌	High-volume streaming exports
Aspose.Cells	Commercial	✅	✅	✅	✅	Enterprise-grade reporting
Spire.XLS	Commercial/Free*	✅	✅	✅	✅	Enterprise-grade Excel document processing solution
Syncfusion XlsIO	Commercial/Free*	✅	✅	✅	✅	Teams already using Syncfusion's ecosystem

⚠️ = Partial or limited support

Spire.XLS Free Edition is limited to 5 sheets and 200 rows. Syncfusion offers a free Community License for qualifying individuals and small businesses.

What Actually Matters in 2026

Modern Excel automation is no longer just about writing cells.

Modern .NET applications increasingly need libraries that work reliably in:

ASP.NET backend services
Docker containers
Linux environments
cloud-native deployments
high-volume reporting systems

So when evaluating an Excel library in 2026, developers usually care about a few practical concerns:

Can it run on Linux / Docker?
Modern Excel processing increasingly runs inside containers and Linux-based backend services. Libraries that depend on Microsoft Office or Windows-only rendering components can become unreliable in production environments.

Will it OOM on large files?
Large spreadsheets can quickly exhaust memory when libraries rely on full in-memory workbook models. For high-volume exports, streaming support often matters more than raw processing speed.

Are there licensing risks?
Some libraries are technically free — until you deploy commercially. Licensing changes, feature limitations, and commercial restrictions can become expensive surprises later.

Is the API actually pleasant to use?
Some libraries provide enormous flexibility but require hundreds of lines of verbose code. Others focus on developer experience and let you build reports quickly with minimal boilerplate.

Does it support advanced features?
Requirements like charts, pivot tables, formula calculation, Excel-to-PDF conversion, and VBA support vary widely between libraries.

Can it handle concurrent workloads safely?
Behavior under concurrent ASP.NET workloads, memory pressure, and containerized deployments often matters more than simple local benchmarks.

Best Free C# Libraries for Excel Automation

1. EPPlus ⭐ Most Popular

EPPlus has long been one of the most widely used Excel libraries in the .NET ecosystem. It offers one of the richest feature sets among open-source options — conditional formatting, charts, pivot tables, data validation, and formula calculation support all work out of the box.

Core capabilities:

Read/write .xlsx files
Charts, styles, and conditional formatting
Formula calculation engine
Data validation and named ranges

Code example — generate a formatted report:

using OfficeOpenXml;
using OfficeOpenXml.Style;

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

using var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Sales Report");

// Header row
sheet.Cells["A1"].Value = "Product";
sheet.Cells["B1"].Value = "Revenue";
sheet.Cells["A1:B1"].Style.Font.Bold = true;
sheet.Cells["A1:B1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells["A1:B1"].Style.Fill.BackgroundColor.SetColor(Color.SteelBlue);

// Data rows
sheet.Cells["A2"].Value = "Widget A";
sheet.Cells["B2"].Value = 42500;

await package.SaveAsAsync(new FileInfo("report.xlsx"));

⚠️ License note: EPPlus 5.x and above uses a Polyform NonCommercial license. If you're shipping commercial software, you'll need a paid subscription. Projects still on 4.x can use LGPL, but staying on an unmaintained version carries its own risks.

Use EPPlus if: your project is non-commercial, or you're willing to purchase a license and need rich formatting and chart support.

Avoid EPPlus if: you need a truly free solution for commercial use, or your primary concern is handling very large files efficiently.

2. ClosedXML ⭐ Clean API

Built on top of Open XML SDK, ClosedXML hides much of the underlying XML complexity and provides a far cleaner API.

ClosedXML is especially popular for internal tools, dashboards, and lightweight reporting systems.

Core capabilities:

Read/write .xlsx files
Formulas, styles, and tables
Named ranges and data validation
No Office installation required

While ClosedXML supports basic chart preservation and manipulation, chart capabilities remain limited compared with EPPlus and commercial Excel libraries.

Code example — read data and write back:

using ClosedXML.Excel;

using var workbook = new XLWorkbook("data.xlsx");
var sheet = workbook.Worksheet("Orders");

foreach (var row in sheet.RowsUsed().Skip(1)) // skip header
{
    var orderId = row.Cell(1).GetValue<int>();
    var amount = row.Cell(3).GetValue<decimal>();

    if (amount > 10000)
        row.Cell(4).Value = "High Value";
}

workbook.Save();

Use ClosedXML if: you want a clean, readable API under MIT license, and your files stay within a manageable size range.

Avoid ClosedXML if: you need built-in chart generation, or you're working with files that push into the hundreds of thousands of rows.

3. Open XML SDK Microsoft Official

Open XML SDK is Microsoft's own library for working with Office file formats. It operates directly on the underlying XML structure of .xlsx files, which gives you complete control — but also a far more verbose programming model.

The SDK can write formulas into worksheets, but it does not provide a native formula calculation engine. In practice, this means formula evaluation is typically deferred to Excel itself or another processing layer.

Open XML SDK is powerful, but usually better suited for low-level document manipulation and edge-case workflows than day-to-day Excel automation.

Core capabilities:

Direct access to .xlsx internal XML structure
No high-level abstraction — full control over document parts
Reliable for complex structural operations other libraries can't handle
MIT licensed, maintained by Microsoft

Use Open XML SDK if: you need precise control over document structure, or you're dealing with edge cases that higher-level libraries can't handle.

Avoid Open XML SDK if: you're building something where development speed matters — the API is verbose by design, and routine tasks like styling a cell or adding a chart require substantially more code than alternatives.

4. MiniExcel 🚀 Hidden Gem

MiniExcel takes a different approach from every other library on this list. MiniExcel intentionally prioritizes streaming throughput over full Excel feature parity. Instead of loading the entire file into memory, it reads and writes using streaming — which means memory consumption stays flat even when you're processing a million rows.

Core capabilities:

Streaming read/write for .xlsx and .csv
Exceptionally low memory footprint
Simple, minimal API
Template-based export support

Code example — export 1 million rows:

using MiniExcelLibs;

var data = GetMillionRows(); // IEnumerable<OrderRow>
await MiniExcel.SaveAsAsync("large-export.xlsx", data);

That's it. No setup, no configuration, no memory spike.

Benchmark context: On a typical export of 1 million rows, MiniExcel consistently uses a fraction of the memory in streaming scenarios that EPPlus or ClosedXML require for the same operation — the gap becomes significant above 100,000 rows.

Use MiniExcel if: you're building data exports, ETL pipelines, or any feature where file size and memory efficiency are the primary constraints.

Avoid MiniExcel if: you need charts, complex styling, or formula calculation — MiniExcel is purpose-built for data throughput, not rich formatting.

Best Commercial Excel Libraries for .NET

1. Aspose.Cells 👑 Enterprise Standard

Aspose.Cells is arguably the most feature-complete Excel library available for .NET. It doesn't just read and write spreadsheets — it includes a full formula calculation engine, VBA macro support, chart rendering, and the ability to convert Excel files to PDF without any Office dependency.

Core capabilities:

Read/write .xlsx, .xls, .csv, .ods and more
Full formula calculation engine (400+ Excel functions)
Chart creation and rendering
VBA macro read/write support
Excel to PDF/HTML/image conversion
Cross-platform: Windows, Linux, Docker, macOS

Code example — Excel to PDF conversion:

using Aspose.Cells;

var workbook = new Workbook("report.xlsx");
workbook.Save("report.pdf", SaveFormat.Pdf);

Pricing: Aspose operates on a per-developer license model. It's not cheap — but for teams building financial systems, ERP integrations, or any product where Excel output is a core deliverable, the licensing cost is typically justified by the reduction in development and maintenance time.

Use Aspose.Cells if: you're working on enterprise-grade reporting, need Excel-to-PDF conversion, or require reliable support for complex formulas and charts in a production environment.

Avoid Aspose.Cells if: your Excel automation needs are straightforward — the cost is hard to justify for simple read/write operations that free libraries handle well.

2. Spire.XLS Practical Enterprise Choice

Spire.XLS is a mature .NET Excel component focused on practical enterprise document workflows — including spreadsheet generation, formatting, charting, template-based reporting, and Excel-to-PDF conversion — without requiring Microsoft Office.

Compared with lower-level document APIs, Spire.XLS focuses more on developer productivity and rapid implementation, especially for teams building internal systems, reporting platforms, or Office document workflows inside .NET applications.

Core capabilities:

Read/write .xlsx and .xls
Charts, formulas, and cell styles
Excel to PDF/HTML/image conversion
Cross-platform support

Code example — Excel to HTML conversion:

using Spire.Xls;

var workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
var sheet = workbook.Worksheets[0];

sheet.SaveToHtml("ExcelToHTML.html");

Free Edition limits: 5 worksheets and 200 rows per sheet. Sufficient for local testing and evaluation, but not for production use with real datasets.

Use Spire.XLS if: you need a balanced Excel processing library with strong document-generation features, Office-free deployment, and a faster path to implementing business reporting workflows.

Avoid Spire.XLS if: your project depends heavily on macro-heavy workflows, highly specialized Excel compatibility edge cases, or advanced VBA automation requirements.

3. Syncfusion XlsIO Ecosystem Play

Syncfusion XlsIO is part of Syncfusion's broader .NET component suite. As a standalone Excel library, it's solid — good chart support, reliable formula handling, and strong large file performance. Syncfusion XlsIO becomes especially attractive for teams already using the broader Syncfusion stack, though it also stands well on its own as a capable enterprise Excel library.

Core capabilities:

Read/write .xlsx and .xls
Charts, formulas, and conditional formatting
Excel to PDF/HTML conversion
Strong performance on large files

Code example — create a worksheet with a formula:

using Syncfusion.XlsIO;

using var excelEngine = new ExcelEngine();
var app = excelEngine.Excel;
app.DefaultVersion = ExcelVersion.Xlsx;

var workbook = app.Workbooks.Create(1);
var sheet = workbook.Worksheets[0];

sheet.Range["A1"].Value = "Q1 Sales";
sheet.Range["A2"].Number = 15000;
sheet.Range["A3"].Number = 22000;
sheet.Range["A4"].Formula = "=SUM(A2:A3)";

workbook.SaveAs("output.xlsx");

Community License: Syncfusion offers a free Community License for individual developers and small businesses with under $1 million in annual revenue. For qualifying teams, this makes XlsIO effectively free.

Use Syncfusion XlsIO if: your team is already in the Syncfusion ecosystem, or you qualify for the Community License and need a capable commercial-grade library at no cost.

Avoid Syncfusion XlsIO if: you have no existing Syncfusion investment and are evaluating libraries purely on Excel automation capability — the value proposition is less clear outside of the broader ecosystem context.

Head-to-Head: Deep Comparison

Feature Completeness

Not all Excel libraries aim for the same goals.

Feature	EPPlus	ClosedXML	Open XML SDK	MiniExcel	Aspose.Cells	Spire.XLS	Syncfusion XlsIO
Charts	✅	⚠️	⚠️	❌	✅	✅	✅
Pivot Tables	✅	⚠️	⚠️	❌	✅	⚠️	✅
Formula Calculation	✅	✅	❌	❌	✅	✅	✅
Conditional Formatting	✅	✅	⚠️	❌	✅	✅	✅
Excel → PDF	❌	❌	❌	❌	✅	✅	✅
VBA Support	❌	❌	⚠️	❌	✅	⚠️	❌
.xls (Legacy)	❌	❌	❌	❌	✅	✅	✅

3 things the table tells you:

📄 Need Excel → PDF? Commercial libraries only.
🔧 Need VBA support? Aspose.Cells is your only reliable option.
📁 Need legacy .xls? Free libraries on this list won't cover you.

Developer Experience

This is one area developers often underestimate.

Library	API Friendliness	Documentation	Community Support
EPPlus	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐
ClosedXML	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐
Open XML SDK	⭐⭐	⭐⭐⭐	⭐⭐⭐
MiniExcel	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐
Aspose.Cells	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Spire.XLS	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Syncfusion XlsIO	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐

Choosing the Right Excel Library for ASP.NET and Backend Systems

All the details covered so far boil down to one question: what does your project actually need?

Work through this decision tree to find your answer:

Is budget a constraint?
│
├── YES (free/open-source only)
│   │
│   ├── Dealing with files over 100K rows?
│   │   └── YES → MiniExcel
│   │
│   ├── Need charts or rich formatting?
│   │   └── YES → EPPlus (verify license for commercial use)
│   │
│   ├── Need clean API under MIT license?
│   │   └── YES → ClosedXML
│   │
│   └── Need fine-grained XML control?
│       └── YES → Open XML SDK
│
└── NO (commercial options available)
    │
    ├── Enterprise-grade reporting, PDF export, or VBA?
    │   └── YES → Aspose.Cells
    │
    ├── Already using Syncfusion components?
    │   └── YES → Syncfusion XlsIO
    │
    └── Need strong Office-free Excel processing with fast implementation?
        └── YES → Spire.XLS

Performance Considerations for Large Excel Files

When working with large spreadsheets, memory behavior usually matters more than raw processing speed.

Libraries like EPPlus and ClosedXML primarily use an in-memory workbook model, which is convenient for formatting and random access, but can become expensive once files grow into hundreds of thousands of rows.

MiniExcel is optimized for streaming scenarios and maintains a much smaller memory footprint during large exports.

Commercial libraries such as Aspose.Cells, Spire.XLS, and Syncfusion XlsIO generally provide more mature large-file handling options, including streaming APIs and optimized server-side processing.

Scenario	Recommendation
Under 50K rows	Most libraries work well
50K–500K rows	Consider memory usage under concurrent load
500K+ rows	Prefer libraries with explicit streaming support

In production environments, architecture often matters more than micro-benchmarks:

stream data whenever possible
avoid loading entire workbooks unnecessarily
dispose workbook objects promptly

Common Pitfalls Developers Often Miss

🪤 The EPPlus license trap

EPPlus 4.x was LGPL — free for commercial use. EPPlus 5.x changed to a Polyform NonCommercial license. If your project upgraded without noticing, you may be shipping commercial software under a license that requires a paid subscription. Always verify licensing requirements before deployment.

💀 Forgetting to dispose workbook objects

Every library on this list holds file handles and memory buffers until explicitly released. In a web application handling concurrent requests, this compounds quickly.

// Always use 'using' — applies to every library in this article
using var package = new ExcelPackage();
using var workbook = new XLWorkbook();

🐧 Font and chart rendering issues on Linux

Styles and charts that render perfectly on Windows can break in a Linux Docker container. The root cause is often missing system fonts. A reliable fix:

apt-get update && apt-get install -y \
    libfontconfig1 \
    fonts-liberation

If your app generates charts server-side, test explicitly in your target container environment — not just locally on Windows.

📦 Loading large files without streaming

Calling new XLWorkbook("huge-file.xlsx") on a 300MB file loads everything into memory at once. If large file reading is part of your use case, check whether your library supports a streaming or lightweight read mode — and use it deliberately.

🔢 The Excel date serialization trap

Excel stores dates as floating point numbers (days since January 1, 1900). If you read a date cell as a raw value without explicitly converting it, you'll get a number like 45678 instead of a DateTime. Always specify the expected type when reading date cells:

// ClosedXML
var date = sheet.Cell("A1").GetValue<DateTime>();

// EPPlus
var date = sheet.Cells["A1"].GetValue<DateTime>();

Conclusion

There is no universally perfect Excel library for .NET.

The best choice depends entirely on your project's priorities.

Some libraries prioritize clean APIs and developer productivity, while others focus on streaming performance, advanced Excel features, or enterprise-scale document processing.

In modern backend systems, factors like memory usage, Linux compatibility, licensing, and large-file handling often matter as much as feature lists.

Choosing the right library early can help avoid performance bottlenecks, deployment issues, and maintenance surprises later on.

7 Document Processing Tasks Every .NET Developer Should Automate

Chloe — Wed, 20 May 2026 07:40:31 +0000

Document processing is one of the most repetitive parts of many .NET applications.

From generating PDF invoices and Excel reports to filling Word templates and converting files, developers often spend far more time on these repetitive workflows than they expect. Many teams still rely on manual steps, desktop tools, or fragile Office automation that becomes difficult to maintain as systems grow.

The good news is that modern .NET libraries make these tasks far easier to automate. In this article, we'll look at 7 document processing tasks that are especially worth automating in real-world .NET applications.

Task #1 — Generate PDF Reports Automatically

PDF generation is one of the most common requirements in business-focused .NET applications.

Typical use cases include:

Invoices
Financial reports
Order confirmations
Analytics dashboards
Downloadable business documents

Yet many teams still rely on browser print dialogs or desktop tools to get the job done.

The problem is that these approaches don't scale well. They're hard to trigger from background jobs, impossible to batch, and painful to maintain when layouts change.

A more scalable approach is to define document layouts in code and render them entirely server-side.

Popular options include QuestPDF, iText, IronPDF, and Spire.PDF, each with different strengths depending on rendering complexity, deployment requirements, and licensing considerations.

The following example uses QuestPDF to generate a simple invoice PDF:

Document.Create(container =>
{
    container.Page(page =>
    {
        page.Size(PageSizes.A4);
        page.Margin(40);

        page.Content().Column(col =>
        {
            col.Item().Text("Invoice #1042")
               .FontSize(20).Bold();

            col.Item().Text($"Date: {DateTime.Now:yyyy-MM-dd}")
               .FontSize(11).FontColor(Colors.Grey.Medium);

            col.Item().PaddingTop(20).Table(table =>
            {
                table.ColumnsDefinition(c =>
                {
                    c.RelativeColumn(3);
                    c.RelativeColumn(1);
                    c.RelativeColumn(1);
                });

                table.Header(header =>
                {
                    header.Cell().Text("Description").Bold();
                    header.Cell().Text("Qty").Bold();
                    header.Cell().Text("Price").Bold();
                });

                table.Cell().Text("Annual Subscription");
                table.Cell().Text("1");
                table.Cell().Text("$299.00");
            });
        });
    });
})
.GeneratePdf("invoice.pdf");

This runs entirely server-side, works in Docker, and can be triggered from any background job or API endpoint.

⚡ Quick Win: Install QuestPDF via NuGet (dotnet add package QuestPDF), copy the invoice example above, and you'll have a working PDF endpoint in under 15 minutes.

Task #2 — Export Excel Reports from ASP.NET

Excel exports are everywhere in business applications.

Sales summaries, inventory reports, and financial reconciliation data are all common Excel export scenarios in business applications. The classic mistake is reaching for Excel Interop, which requires Office installed on the server and tends to become unstable in Docker or cloud-hosted environments.

Modern ASP.NET applications usually take a different approach by generating Excel files directly in code using libraries such as:

ClosedXML
EPPlus
NPOI
Spire.XLS

These libraries work without Microsoft Office and are far better suited for Docker, Linux, cloud hosting, and high-concurrency environments.

The following example uses Spire.XLS to generate a formatted Excel report in ASP.NET Core:

using Spire.Xls;

[HttpGet("export")]
public IActionResult ExportSalesReport()
{
    Workbook workbook = new Workbook();
    Worksheet sheet = workbook.Worksheets[0];
    sheet.Name = "Sales Report";

    sheet.Range["A1"].Value = "Product";
    sheet.Range["B1"].Value = "Units Sold";
    sheet.Range["C1"].Value = "Revenue";

    sheet.Range["A1:C1"].Style.Font.IsBold = true;
    sheet.Range["A1:C1"].Style.Color = Color.LightBlue;

    sheet.Range["A2"].Value = "Product A";
    sheet.Range["B2"].NumberValue = 120;
    sheet.Range["C2"].NumberValue = 3600;

    sheet.Range["A3"].Value = "Product B";
    sheet.Range["B3"].NumberValue = 85;
    sheet.Range["C3"].NumberValue = 4250;

    sheet.AllocatedRange.AutoFitColumns();

    using var stream = new MemoryStream();
    workbook.SaveToStream(stream, FileFormat.Version2016);

    return File(
        stream.ToArray(),
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        "sales-report.xlsx");
}

The result is a properly formatted, immediately downloadable .xlsx file — styled headers, currency formatting, auto-fitted columns — all generated in a single request with no external tools.

Task #3 — Merge and Split PDF Files

Merging and splitting PDFs comes up more often than you'd expect.

Common examples include combining contract attachments, splitting bulk uploads into individual records, or archiving multi-document packages. In many companies, someone still opens Acrobat or an online tool just to merge a few files together. That process may seem harmless at first, but it becomes inefficient very quickly when documents are generated daily.

Automating PDF merging and splitting makes document workflows much easier to scale. Instead of relying on manual operations, applications can process files automatically in the background.

PdfSharp is a lightweight open-source option that handles both merging and splitting cleanly, with no external dependencies.

The following example merges multiple PDF files into a single document:

using PdfSharp.Pdf;
using PdfSharp.Pdf.IO;

public void MergePdfs(string[] inputPaths, string outputPath)
{
    using var output = new PdfDocument();

    foreach (var path in inputPaths)
    {
        using var input = PdfReader.Open(path, PdfDocumentOpenMode.Import);
        foreach (var page in input.Pages)
            output.AddPage(page);
    }

    output.Save(outputPath);
}

You can also split PDFs by page range:

public void SplitPdf(string inputPath, int fromPage, int toPage, string outputPath)
{
    using var input = PdfReader.Open(inputPath, PdfDocumentOpenMode.Import);
    using var output = new PdfDocument();

    for (int i = fromPage; i <= toPage; i++)
        output.AddPage(input.Pages[i]);

    output.Save(outputPath);
}

These operations are easy to integrate into API endpoints or background jobs, making high-volume PDF workflows far easier to manage.

Task #4 — Fill Word Templates with Dynamic Data

Generating Word documents from templates is common in HR systems, legal workflows, and internal business applications. The traditional workflow usually involves manually editing old files and replacing values one by one. This quickly becomes repetitive and error-prone.

Instead of manually editing documents each time, teams can use reusable Word templates with dynamic placeholders.

In practice, developers can prepare a reusable .docx template and let the application fill in the data automatically. This keeps formatting consistent while dramatically reducing manual editing work.

Libraries such as OpenXML SDK, DocX, and Spire.Doc all support template-based document automation.

The following example uses Spire.Doc to replace placeholders dynamically:

using Spire.Doc;

string template = @"template.docx";
string output = "contract-filled.docx";

FillContractTemplate(template, output, "Linda", "50000", "2026-05-20");

static void FillContractTemplate(
    string templatePath,
    string outputPath,
    string clientName,
    string amount,
    string date)
{
    Document document = new Document();
    document.LoadFromFile(templatePath);

    document.Replace("${ClientName}", clientName, true, true);
    document.Replace("${Amount}", amount, true, true);
    document.Replace("${Date}", date, true, true);

    document.SaveToFile(outputPath);
}

One major advantage of this approach is that non-developers can still edit the template layout without touching application code.

⚡ Quick Win: Convert one frequently reused Word document into a template with placeholders and automate the replacement process.

Task #5 — Convert Documents Between Formats

In real-world systems, applications rarely deal with just a single document format.

Many developers first handle document conversion manually — opening files locally and exporting them one by one. That approach quickly breaks down once uploads, background processing, or customer-facing downloads enter the picture.

Users may upload Word files, download PDFs, preview Excel reports in the browser, or request HTML versions of business documents. As systems grow, document conversion quickly becomes an essential part of the workflow.

In practice, Word-to-PDF conversion is the most common requirement, but many systems also need Excel-to-PDF rendering, HTML exports, or PDF preview images.

The challenge is doing this reliably on the server without installing Microsoft Office.

A reliable server-side conversion workflow should ideally:

Support background processing
Run in Docker or Linux environments
Handle batch conversion tasks efficiently

A common example is converting uploaded Word documents into PDFs for browser preview and long-term storage entirely server-side:

using Spire.Doc;

Document document = new Document();
document.LoadFromFile("Report.docx");

document.SaveToFile("Report.pdf", FileFormat.PDF);

Once format conversion is automated, developers no longer need to rely on manual export steps or external desktop tools to keep document workflows running smoothly.

Task #6 — Extract Text and Data from PDFs

PDF text extraction powers a surprising number of workflows.

Common examples include invoice parsing, full-text search indexing, automated data entry, and content archiving. Without it, PDFs are essentially black boxes: you can store them, but you can't act on their contents.

For text-based PDFs, many .NET libraries can extract content directly.

using UglyToad.PdfPig;

public string ExtractText(string pdfPath)
{
    using var document = PdfDocument.Open(pdfPath);
    var sb = new StringBuilder();

    foreach (var page in document.GetPages())
    {
        var text = string.Join(" ", page.GetWords().Select(w => w.Text));
        sb.AppendLine(text);
    }

    return sb.ToString();
}

For scanned PDFs where text isn't embedded, you'll need OCR. Tesseract works well for many local OCR scenarios, but for production workloads with higher accuracy requirements, Azure Document Intelligence is worth the investment — it handles complex layouts, tables, and handwriting reliably.

var client = new DocumentAnalysisClient(
    new Uri(endpoint), new AzureKeyCredential(apiKey));

var operation = await client.AnalyzeDocumentFromUriAsync(
    WaitUntil.Completed, "prebuilt-invoice", new Uri(fileUrl));

var result = operation.Value;
foreach (var field in result.Documents[0].Fields)
    Console.WriteLine($"{field.Key}: {field.Value.Content}");

Once extracted, the content can power search systems, automated processing pipelines, or downstream business workflows.

Task #7 — Validate and Clean Excel Data Automatically

Data import via Excel is convenient for users but painful for developers. Files come in with duplicate emails, missing required fields, invalid URLs, and inconsistent formats. Most teams handle this manually — reviewing spreadsheets, filtering rows by hand, and flagging problematic data for cleanup. It works for 50 rows, not for 5,000.

Automating data validation helps catch these issues before the data reaches databases or reporting pipelines.

Modern Excel libraries make it possible to apply validation rules and formatting directly in code, allowing applications to automatically mark problematic data for users.

For example, duplicate values can be highlighted automatically during report generation:

using ClosedXML.Excel;

var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Users");

worksheet.Cell("A1").Value = "Email";
worksheet.Cell("A2").Value = "john@example.com";
worksheet.Cell("A3").Value = "john@example.com";

worksheet.Range("A2:A100")
    .AddConditionalFormat()
    .WhenIsDuplicate()
    .Fill.SetBackgroundColor(XLColor.LightPink);

workbook.SaveAs("validated-users.xlsx");

Automated validation not only improves data quality, but also reduces the amount of manual cleanup work required before importing or analyzing spreadsheets.

Automate Document Workflows with Background Jobs

The sections above focus on individual document operations, but real automation goes one step further — running these workflows on a schedule, in the background, without manual triggers.

Once those workflows are in place, the next step is running them without manual intervention.

Popular approaches in .NET include:

Hangfire
Quartz.NET
BackgroundService
Queue-based processing

For example, a reporting system might generate all weekly Excel reports overnight and automatically email them to users the next morning.

A simple Hangfire example could look like this:

// Register in Program.cs
builder.Services.AddHangfire(config =>
    config.UseSqlServerStorage(connectionString));
builder.Services.AddHangfireServer();

// Schedule a nightly report job
RecurringJob.AddOrUpdate<ReportService>(
    "nightly-report",
    service => service.GenerateAndEmailReportAsync(),
    Cron.Daily(hour: 2));

public class ReportService
{
    public async Task GenerateAndEmailReportAsync()
    {
        var pdf = _pdfGenerator.GenerateMonthlyReport();
        var excel = _excelExporter.ExportSalesSummary();
        await _emailService.SendWithAttachmentsAsync(pdf, excel);
    }
}

This becomes especially powerful when combined with the document workflows above — nightly PDF reports, scheduled Excel exports, or automatic PDF merging on upload — allowing these processes to run automatically in the background with minimal manual intervention.

⚡ Quick Win: Install Hangfire (dotnet add package Hangfire), pick your highest-frequency manual document task, and schedule it as a recurring job. The first night it runs unattended is a good feeling.

Tools & Libraries Quick Reference

The table below provides a quick starting point for common .NET document processing scenarios:

Task	Open-Source	Commercial / Cloud
PDF generation	QuestPDF	iText, IronPDF, Spire.PDF
Excel export	ClosedXML, NPOI	EPPlus, Spire.XLS
PDF merge / split	PdfSharp	IronPDF, iText
Word templates	Open XML SDK, DocX	Spire.Doc
File conversion	LibreOffice CLI, OpenXML	Spire.Doc
Text extraction	PdfPig, Tesseract	Azure Document Intelligence
Excel validation	ClosedXML	Aspose.Cells
Job scheduling	Hangfire, Quartz.NET	Azure Logic Apps

For many teams, a combination of lightweight open-source libraries and a few specialized commercial tools is often the most practical approach.

Conclusion

Document workflows may seem minor at first, but repetitive manual tasks quickly become difficult to maintain as applications grow. Automating PDF generation, Excel exports, Word templates, file conversion, and data validation helps reduce errors, improve consistency, and save significant development time. With modern .NET libraries, teams can build scalable, server-safe document workflows entirely in code — without relying on Microsoft Office or fragile desktop automation. The earlier these workflows are automated, the easier it becomes to scale applications without accumulating fragile manual processes and maintenance overhead later on.

Why Excel Interop Is a Bad Idea for ASP.NET Applications

Chloe — Mon, 11 May 2026 07:38:53 +0000

What breaks in production — and what developers use instead

It usually starts with a simple requirement.

"Upload an Excel file, process it on the server, and export a report."

So you reach for Microsoft.Office.Interop.Excel. It's familiar. It's part of the Microsoft ecosystem. And it works — at least on your machine.

You open a workbook. You read a few cells. You write a test. Everything passes.

Then you deploy to production.

Within days, you start noticing things:

Excel processes quietly pile up in Task Manager, never exiting
Some requests hang indefinitely with no error, no timeout, no explanation
IIS recycles unexpectedly under moderate load
A background job that worked fine in staging fails intermittently
Deploying to a new server means installing Microsoft Office — and hoping the license key is somewhere

None of these failures are dramatic at first. They're subtle. They show up gradually. And by the time the pattern is obvious, they're already embedded in production infrastructure.

This is the Excel Interop trap. It looks like a solution. The API feels simple — until it becomes part of your production infrastructure.

1. What Is Excel Interop?
2. Why Excel Interop Fails on Servers
- 2.1 Microsoft Does Not Recommend It
- 2.2 Excel Requires a Desktop Environment
- 2.3 COM Objects Cause Memory Leaks
- 2.4 Not Thread-Safe
- 2.5 Incompatible with Modern Deployments
- 2.6 Security Concerns
3. What Developers Use Instead
4. Example: Reading Excel Files Without Interop
5. When Interop Still Makes Sense
6. How to Migrate Away from Interop
Conclusion

1. What Is Excel Interop?

Excel Interop is part of the Microsoft Office automation API, exposed through the Microsoft.Office.Interop.Excel namespace. It lets C# code control Excel the same way a human user would — opening workbooks, reading and writing cells, triggering formulas, running macros, and saving files.

Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(@"C:\reports\data.xlsx");
Worksheet sheet = (Worksheet)workbook.Sheets[1];
string value = ((Range)sheet.Cells[1, 1]).Value?.ToString();

Under the hood, it works through COM Automation. Your .NET process doesn't parse the Excel file format directly — it launches a real Excel instance and sends instructions to it over the COM interface. Excel is running. It has a window (even if hidden). It manages its own memory, its own file handles, and its own process lifetime.

For desktop automation — a local tool that an individual runs interactively — this can work reasonably well.

For ASP.NET applications and server environments, it's a very different story.

2. Why Excel Interop Fails on Servers

The problems aren't edge cases. They're structural — and they compound each other in production.

2.1 Microsoft Does Not Recommend It

This isn't opinion. Microsoft has explicitly stated that Office Automation is unsupported in unattended server environments, including ASP.NET and Windows Services.

Office applications were built for interactive desktop use. When something breaks in production, you're not debugging a bug — you're operating outside the supported boundaries of the software itself.

2.2 Excel Requires a Desktop Environment

Calling new Application() doesn't open a file. It launches a full Excel process — with GUI infrastructure, user profile dependencies, and desktop session requirements that don't reliably exist on a server.

A Windows update, a Group Policy change, or a permission tweak can silently break everything overnight. The error messages are vague. The fix is never obvious.

2.3 COM Objects Cause Memory Leaks

Every Excel object you touch is a COM object that the .NET GC can't clean up on its own. Miss one Marshal.ReleaseComObject call — in an exception handler, inside a loop, in a helper method — and you get orphaned EXCEL.EXE processes that never exit.

// You have to do this manually. For every object. Every time.
Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(workbook);
excel.Quit();
Marshal.ReleaseComObject(excel);

Over hours, these processes accumulate. Memory climbs. Files stay locked. Someone RDPs in and kills processes by hand.

If your production runbook includes "check for orphaned Excel processes," that's a sign something is fundamentally wrong.

2.4 Not Thread-Safe

ASP.NET processes requests concurrently. Excel Interop uses a Single-Threaded Apartment (STA) model and was never designed for concurrent access.

Under load, this means deadlocks, hanging requests, and IIS process recycling. You can add manual locking to serialize access — but then you've just built a bottleneck on top of a fragile foundation.

2.5 Incompatible with Modern Deployments

Excel Interop requires Windows, a full Office installation, and a valid license on every machine that runs your code — including CI/CD agents, staging servers, and auto-scaled instances.

There's no Docker image for this. A surprising number of modernization projects get blocked by a single Excel dependency.

2.6 Security Concerns

Three risks that are easy to overlook:

Macro and DDE execution — Excel may run embedded macros or DDE links in uploaded files, opening a code execution path on your server
Overprivileged process — the Excel process inherits your app pool identity, widening the blast radius of a malicious file
Temp file leakage — Excel writes AutoRecover and clipboard data to disk; in shared environments, this data can persist longer than expected

3. What Developers Use Instead

The important shift is this:

Modern libraries work with the Excel file format itself — not the Excel desktop application.

That means no COM automation, no hidden Excel processes, and no Office installation on the server.

The good news is that the .NET ecosystem already has several mature options for this.

Capability	Excel Interop	Modern Libraries
Requires Microsoft Office	Yes	No
Runs in Docker/Linux	No	Yes
COM Cleanup Needed	Yes	No
Designed for Server Use	Risky	Yes
Background Excel Processes	Yes	No

Here's a practical overview of the common ones.

Open XML SDK

Microsoft's official open-source library for reading and writing Office file formats. It gives you direct access to the underlying XML structure of .xlsx files.

Good for: developers who want full control over document structure and don't mind a lower-level API.

Worth knowing: the API is verbose. Simple operations — reading a cell value, writing a row — require significantly more code than you'd expect. For complex spreadsheets, it can become difficult to maintain.

EPPlus

One of the most widely used Excel libraries in the .NET ecosystem. The API is intuitive, the documentation is solid, and it handles most common spreadsheet operations cleanly.

Good for: teams that need a productive API and are comfortable with its licensing model.

Worth knowing: EPPlus moved to a commercial license for business use starting with version 5. It's still free for non-commercial projects, but production use in a commercial application requires a paid license.

NPOI

A .NET port of the Java Apache POI library. It's free, open-source, and supports both .xls and .xlsx formats.

Good for: teams migrating older codebases, or projects that need to support legacy .xls files alongside modern .xlsx.

Worth knowing: the API reflects its Java origins — it works, but it doesn't feel native to C#. Community activity has slowed in recent years.

Office-Independent Libraries

A category of libraries — including Spire.XLS and others — that implement Excel format support entirely in managed code, with no dependency on Office, COM, or Windows-specific APIs.

In practice, these libraries usually offer:

A higher-level API than Open XML SDK
Reliable behavior in server and containerized environments
Support for a wider range of Excel features out of the box

Good for: ASP.NET applications that generate reports, process uploaded files, or run scheduled export jobs in production — especially where deployment simplicity matters.

Worth knowing: some libraries in this category have per-developer or per-server licensing. Check the terms before committing to one for a commercial project.

Quick Comparison

Library	License	Office Required	.xls Support	API Level
Open XML SDK	MIT	No	No	Low
EPPlus	Commercial (v5+)	No	No	High
NPOI	Apache 2.0	No	Yes	Medium
Spire.XLS	Commercial	No	Yes	High

4. Example: Reading Excel Files Without Interop

Let's make this concrete. Here's the same task implemented two ways — first with Interop, then without it.

Scenario: an ASP.NET application receives an uploaded Excel file, reads the first sheet, and returns the value of cell A1.

The Interop Way

[HttpPost("upload")]
public IActionResult Upload(IFormFile file)
{
    var tempPath = Path.GetTempFileName() + ".xlsx";
    using (var stream = new FileStream(tempPath, FileMode.Create))
    {
        file.CopyTo(stream);
    }

    Application excel = null;
    Workbook workbook = null;
    Worksheet sheet = null;
    Range cell = null;

    try
    {
        excel = new Application();
        excel.Visible = false;
        workbook = excel.Workbooks.Open(tempPath);
        sheet = (Worksheet)workbook.Sheets[1];
        cell = (Range)sheet.Cells[1, 1];

        string value = cell.Value?.ToString();
        return Ok(value);
    }
    finally
    {
        if (cell != null) Marshal.ReleaseComObject(cell);
        if (sheet != null) Marshal.ReleaseComObject(sheet);
        if (workbook != null)
        {
            workbook.Close(false);
            Marshal.ReleaseComObject(workbook);
        }
        if (excel != null)
        {
            excel.Quit();
            Marshal.ReleaseComObject(excel);
        }
        System.IO.File.Delete(tempPath);
    }
}

This is the minimum viable version — and it's already fragile. Real production code usually ends up even more defensive than this. A single exception in the wrong place can prevent the cleanup code from running. The Excel process stays open. The temp file stays locked.

In production, under concurrent load, this gets worse fast.

Without Interop

Using Spire.XLS as an example — other libraries follow a similar pattern:

// Install-Package Spire.XLS
[HttpPost("upload")]
public IActionResult Upload(IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        return BadRequest("No file uploaded.");
    }

    using var stream = file.OpenReadStream();

    var workbook = new Workbook();
    workbook.LoadFromStream(stream);

    var sheet = workbook.Worksheets[0];
    string value = sheet.Range["A1"].Text;

    return Ok(value);
}

No COM objects. No manual cleanup. No temp files. No Excel process running in the background.

The same result, in a fraction of the code — and it behaves predictably under concurrent load, inside a container, and on a Linux host.

The Pattern Holds for Writing Too

Reading is the simple case. Here's generating an Excel report and returning it as a download:

[HttpGet("report")]
public IActionResult DownloadReport()
{
    var workbook = new Workbook();
    var sheet = workbook.Worksheets[0];

    sheet.Range["A1"].Text = "Product";
    sheet.Range["B1"].Text = "Revenue";
    sheet.Range["A2"].Text = "Widget A";
    sheet.Range["B2"].NumberValue = 84500;

    using var stream = new MemoryStream();
    workbook.SaveToStream(stream, FileFormat.Version2016);

    return File(stream.ToArray(),
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        "report.xlsx");
}

No installed Office. No cleanup ritual. Just code that does what it says.

Why This Matters Beyond the Code

The difference isn't just syntax. It's operational:

No Excel process means nothing to leak, nothing to orphan, nothing to kill
No Office dependency means the same code runs in dev, CI, staging, and production without special setup
Stream-based API means you can process uploaded files directly without writing to disk

Other libraries in the same category work on the same principles. The specific API differs, but the underlying advantage is the same: you're working with a file format, not remote-controlling a desktop application.

5. When Interop Still Makes Sense

It's worth being honest about this: Interop isn't always the wrong choice.

If you're building an internal desktop tool — one that runs on a machine where Office is already installed, used interactively by a single person, and never deployed to a server — Interop can be a reasonable option. You're using Excel as Excel was designed to be used.

Similarly, if your use case genuinely requires Excel-specific behavior that no file-format library supports — certain macro interactions, OLE embedding, or very specific rendering fidelity — Interop may be the only practical path.

The problem isn't the technology itself. It's the mismatch between what Interop was designed for and where it gets used.

For ASP.NET applications, background services, APIs, and anything that runs on a server or in a container, the mismatch is fundamental. The operational costs — fragile deployments, memory leaks, concurrency issues, licensing overhead — consistently outweigh the familiarity of the API.

6. How to Migrate Away from Interop

If Interop is already embedded in your codebase, a full rewrite isn't necessary. Most teams migrate incrementally — and that works well in practice.

A reasonable approach looks like this:

Identify Interop-heavy workflows — search for patterns like using Excel = Microsoft.Office.Interop.Excel or new Excel.Application(). These are reliable indicators of where Office automation is hiding in the codebase.
Wrap existing logic behind interfaces — don't rewrite Interop calls in place. Encapsulate them behind a service boundary first, so the replacement becomes a contained swap rather than a scattered refactor.
Replace operations incrementally — start with the highest-risk areas: anything in the ASP.NET request pipeline. Desktop utilities and internal tools can wait.
Validate file compatibility carefully — different libraries handle edge cases differently. Run your existing test files through the new implementation before switching over in production.
Load test under production-like conditions — concurrency issues that were masked by Interop's serialized behavior can surface during migration. Test early.

In practice, most Interop-heavy systems only use Excel for a handful of common tasks — reading uploads, generating reports, exporting spreadsheets — all of which modern libraries already handle well without launching Excel itself.

Conclusion

Excel Interop survives in production codebases for one reason: it works locally, and by the time the problems surface, it's already deeply embedded.

But the issues covered in this post aren't bad luck or misconfiguration. Orphaned processes, hanging requests, concurrency failures, deployment blockers — these are the predictable consequences of running a desktop application in a server environment. They don't go away with more careful coding. They go away when you stop using the wrong tool for the job.

The .NET ecosystem has mature, server-safe alternatives for working with Excel files. They're easier to deploy, easier to reason about, and they don't require a production runbook entry that says "kill orphaned Excel processes."

If you're starting fresh, the choice is straightforward. If you're maintaining existing code, the migration path is incremental — but the direction is worth committing to.

The spreadsheet may still be Excel. Your architecture doesn't have to be.

How to Merge PDF Files in ASP.NET Core Using C#

Chloe — Wed, 06 May 2026 09:57:18 +0000

Need to merge PDF files in a .NET application?

If you're running on ASP.NET Core, Docker, Azure App Service, or Linux, relying on Microsoft Office or Interop isn't an option. Office automation was never designed for server-side workloads, and Microsoft explicitly discourages using Office applications in unattended environments.

Fortunately, PDF merging can be done entirely in managed code.

In fact, the merge itself usually takes only a few lines of code. The harder part is making sure it survives production workloads.

In this guide, you'll learn how to:

Merge multiple PDFs in C#
Avoid common PDF merging pitfalls
Handle large documents safely
Build a PDF merge API with ASP.NET Core
Extend merged PDFs with page selection, watermarks, and numbering

Let's start with the simplest implementation.

Quick Start: Merge Multiple PDFs in C

For the examples below, we'll use Spire.PDF. The same concepts apply to most modern .NET PDF libraries.

Install the package:

dotnet add package Spire.PDF

Then merge multiple PDF files in C# with just a few lines of code:

using Spire.Pdf;

namespace MergePDFs
{
    class Program
    {
        static void Main(string[] args)
        {
            // Specify the PDF files to be merged
            string[] files = new string[] {"sample0.pdf", "sample1.pdf", "sample2.pdf"};

            // Merge PDF files 
            PdfDocumentBase pdf = PdfDocument.MergeFiles(files);

            // Save the result file
            pdf.Save("MergePDF.pdf", FileFormat.PDF);
        }
    }
}

That's all you need for a basic merge.

In a real application, however, PDF merging often becomes more complicated once larger files, different document sources, and production workloads are involved.

Why Developers Move Away from Office Automation

Many developers first encounter document automation through Microsoft Office Interop.

It often works fine on a local machine.

Then the application gets deployed.

Suddenly, you start seeing:

Missing Office installations
Permission issues
Random COM exceptions
Processes that never terminate
High memory consumption
Deployment headaches in containers

PDF merging doesn't require Office at all, so introducing Office dependencies simply adds complexity without providing any benefit.

A dedicated PDF library is typically more reliable, easier to deploy, and better suited for server environments.

Common PDF Merging Problems (And How to Fix Them)

Even with a straightforward API, PDF merging can go wrong in a few predictable ways. This section covers the most common ones — starting with the issues that tend to cause the most confusion.

Mistake #1: Forgetting to Dispose of PDF Objects

The merged file is generated, but when you try to open it, your PDF reader throws an error, or the file size looks suspiciously small.

Why does this happen?

The most common cause is that SaveToFile() or Close() was never called — or was called in the wrong order. If the program exits or throws an exception before the file is properly finalized, the output will be incomplete.

How to fix it

Always release resources after use.

sourceDocument.Close();
mergedDocument.Close();

👉 Tip: Use using statements where possible to ensure proper disposal.

Mistake #2: Assuming Fonts Will Always Exist

Text in the merged PDF appears garbled, substituted with a fallback font, or missing entirely — even though the original files looked fine.

Why does this happen?

PDFs may reference fonts without embedding them. When those fonts aren’t available in the runtime environment, the merged result can display incorrectly.

How to fix it

The most reliable solution is to ensure fonts are embedded in the source PDFs before merging. This guarantees consistent rendering across environments.

If you don’t control the source files, process and merge them using a consistent environment, and avoid relying on system-installed fonts.

PdfDocument mergedDocument = new PdfDocument();

foreach (string file in inputFiles)
{
    PdfDocument temp = new PdfDocument(file);
    mergedDocument.AppendPage(temp);
    temp.Close();
}

mergedDocument.SaveToFile(outputFile);
mergedDocument.Close();

👉 In production, always test with real-world documents—not just simple PDFs.

Mistake #3: Loading Every PDF into Memory

The merge works fine for small files, but throws a System.OutOfMemoryException — or causes noticeable memory pressure — when the input files are large or numerous.

Why does this happen?

By default, loading a PdfDocument reads the entire file into memory. If you're merging ten 50MB PDFs, you're potentially holding 500MB in memory at once — before the output document is even written.

How to fix it

Process files one at a time and release each one before loading the next. Avoid holding all source documents open simultaneously:

PdfDocument merged = new PdfDocument();

foreach (string file in inputFiles)
{
    using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
    {
        PdfDocument temp = new PdfDocument(fs);
        merged.AppendPage(temp);
        temp.Close();
    }
}

merged.SaveToFile(outputFile);
merged.Close();

Going further

For very large batches, consider merging in chunks — combine files in groups of 5–10, write each chunk to a temp file, then do a final merge of the temp files. This keeps peak memory usage bounded regardless of input size.

Handling Large Volumes Efficiently

Async Batch Processing for Large Volumes

For pipelines that process many files at once, avoid blocking threads on I/O. Here's a simple async wrapper:

public async Task<byte[]> MergePdfsAsync(IEnumerable<string> filePaths)
{
    // Since Spire.PDF's MergeFiles doesn't support async I/O,
    // you can omit the async wrapper in practice, or just add a caching layer
    return await Task.Run(() =>
    {
        var filesArray = filePaths.ToArray();

        // Use the officially recommended MergeFiles method
        using var mergedDocument = PdfDocument.MergeFiles(filesArray);

        using var output = new MemoryStream();
        mergedDocument.SaveToStream(output);
        mergedDocument.Close();

        return output.ToArray();
    });
}

For high-throughput scenarios, pair this with a queue (e.g., Azure Service Bus or a simple Channel<T>) to process merge jobs one at a time rather than in parallel — PDF merging is memory-intensive enough that concurrency can backfire.

Real-World Scenario: Exposing Merge as an ASP.NET Core API Endpoint

If you're building a service that accepts PDF uploads and returns a merged file, here's a minimal endpoint structure:

app.MapPost("/merge-stream", async (HttpRequest request) =>
{
    var files = request.Form.Files;
    if (files.Count < 2)
        return Results.BadRequest("At least two files are required.");

    PdfDocument merged = new PdfDocument();

    try
    {
        foreach (var file in files)
        {
            using var stream = file.OpenReadStream();
            // Load PDF from stream
            using var tempDoc = new PdfDocument(stream);

            // Insert all pages
            for (int i = 0; i < tempDoc.Pages.Count; i++)
            {
                merged.InsertPage(tempDoc, i, i);
            }
        }

        using var output = new MemoryStream();
        merged.SaveToStream(output);
        merged.Close();

        output.Position = 0;
        return Results.File(output.ToArray(), "application/pdf", "merged.pdf");
    }
    catch (Exception ex)
    {
        merged.Close();
        return Results.Problem($"Error merging PDFs: {ex.Message}");
    }
});

A few things to keep in mind for production use:

Validate file types before processing
Set a reasonable size limit per upload
Consider running the merge in a background job for large files rather than blocking the request thread

Beyond Basic Merging

In many real-world workflows, merging PDFs is only the first step.

Once the final document has been assembled, developers often need to perform additional operations such as selecting specific pages, applying watermarks, or adding page numbers. Most modern PDF libraries support these tasks without requiring major changes to the merge workflow.

Merging Specific Pages Only

Sometimes you don't need the entire document — just merge a range of pages from each source file.

A typical implementation looks like this:

PdfDocument newPDF = new PdfDocument();

// Insert selected pages
newPDF.InsertPageRange(pdfs[0], 1, 2);  // Pages 1-2 from first PDF
newPDF.InsertPage(pdfs[1], 0);          // First page from second PDF
newPDF.InsertPage(pdfs[2], 1);          // Second page from third PDF

newPDF.SaveToFile("SelectivePageMerging.pdf");

This is useful when you're pulling specific sections from multiple reports into a single summary document.

Adding a Watermark During Merge

If you need to stamp each page with a watermark — a company name, "CONFIDENTIAL", a draft label — the merge step is a natural place to do it.

foreach (var page in pdf.Pages)
{
     page.Canvas.SetTransparency(0.5f);
     page.Canvas.TranslateTransform(page.Canvas.Size.Width / 2, page.Canvas.Size.Height / 2);
     page.Canvas.RotateTransform(-45);
     page.Canvas.DrawString("CONFIDENTIAL", font, PdfBrushes.DarkGray, -150, -25);
     page.Canvas.SetTransparency(1f);
}

Adjust the transparency, font size, and position to suit your layout.

Adding Page Numbers After Merging

Page numbers should reflect the final document, not the originals — so add them after the merge is complete.

A simplified implementation might look like this:

for (int i = 0; i < total; i++)
{
     var page = pdf.Pages[i];
     string text = $"Page {i + 1} of {total}";
     float x = page.Canvas.ClientSize.Width - font.MeasureString(text).Width - 50f;
     page.Canvas.DrawString(text, font, PdfBrushes.Black, x, page.Canvas.ClientSize.Height - 50f);
}

Choosing a PDF Library

For simple PDF merging, most modern .NET libraries will get the job done.

The differences become more noticeable when you need advanced features, commercial support, or specific licensing models.

Here's a quick rule of thumb:

Spire.PDF — easy to integrate and well-suited for general-purpose PDF processing
PDFsharp — a good free option for basic PDF workflows
iText 7 — ideal for advanced document manipulation and enterprise requirements
Aspose.PDF — a feature-rich choice for large document-processing projects

For most ASP.NET Core applications, choosing a library that's stable, actively maintained, and compatible with your licensing requirements matters more than the merging API itself.

FAQs

Can I merge PDFs on Linux?

Yes. Most modern .NET PDF libraries work on Linux without requiring Microsoft Office.

Can I merge PDFs inside Docker containers?

Yes. Dedicated PDF libraries are generally much better suited for containerized deployments than Office automation.

Do I need Microsoft Office installed?

No. PDF merging can be performed entirely in managed code.

Is Microsoft.Office.Interop suitable for ASP.NET Core?

Generally, no. Microsoft does not recommend using Office automation in unattended server environments. For ASP.NET Core applications, dedicated PDF libraries are typically more reliable and easier to deploy.

What is the biggest challenge when merging PDFs?

In production environments, memory usage and document quality (such as fonts and embedded resources) usually cause more issues than the merge operation itself.

Conclusion

The actual code required to merge PDFs in ASP.NET Core is surprisingly small.

What usually causes problems in production isn't the merge operation itself—it's everything around it:

Resource cleanup
Memory usage
Font availability
Upload validation

By avoiding Office dependencies and understanding these common pitfalls, you can build a PDF merge service that runs reliably in ASP.NET Core, Docker containers, and cloud environments.

With the right PDF library, merging PDFs in ASP.NET Core can be completely independent of Microsoft Office, making deployment simpler and far more reliable across servers, containers, and cloud platforms.

Best .NET PDF Libraries in 2026: Free vs Paid (What Actually Works)

Chloe — Mon, 27 Apr 2026 03:38:54 +0000

It usually starts with something simple:

"We just need to export a PDF."

Sounds straightforward. Until it isn't.

At first, everything seems fine. You pick a library, write a few lines of code, and get a PDF output. Done.

Then the real problems begin.

Not in development — but the moment you deploy.

The layout looks completely different from your HTML.
Fonts are missing or rendering incorrectly.
It works on your machine… but fails in Docker or Linux.
Performance drops when processing larger files.
And suddenly, licensing becomes a concern you didn't plan for.

What felt like a quick task turns into a series of unexpected trade-offs.

The truth is, choosing a PDF library in .NET isn't about features — it's about what actually works in production.

In this article, we'll take a practical look at some of the most popular .NET PDF libraries in 2026, compare free and paid options, and — more importantly — help you figure out which one actually fits your use case.

The Real Problem — Why Choosing a PDF Library Is Harder Than It Looks

On the surface, most PDF libraries look similar: create documents, add text, insert images, maybe even support HTML rendering.

But in practice, the differences start to matter very quickly.

The challenge isn't generating a PDF — it's generating the right PDF, under real-world conditions.

Here are a few trade-offs developers often run into:

Feature Complexity vs. Simplicity

Some libraries give you full control over every element — layout, positioning, rendering.
That's powerful, but it also means more code, more setup, and a steeper learning curve.

Others are much easier to use, but become limiting once your requirements grow beyond simple documents.

HTML Rendering vs. Manual Layout

If your PDFs are based on existing HTML (invoices, reports, web pages), HTML-to-PDF support becomes critical.

But not all libraries handle this well.

Some don't support HTML at all.
Some only support partial or inconsistent rendering.
Others rely on embedded browsers, which introduces performance and deployment considerations.

Local Development vs. Production Environments

This is where a lot of library choices fall apart in practice.

Common issues include:

Missing system dependencies in Linux or Docker.
Font rendering differences.
Headless environment limitations.
Unexpected crashes under load.

Free vs. Paid — More Than Just Cost

At first glance, the decision seems simple: use a free library if possible.

But "free" often comes with trade-offs:

Limited features.
More time spent on implementation.
Licensing restrictions (especially for commercial use).

On the other hand, paid libraries usually offer more complete solutions — but introduce cost and vendor dependency.

All of this leads to one important conclusion:

There is no universally "best" PDF library in .NET — only the one that best fits your specific use case.

Free vs Paid — What's the Actual Difference?

At some point, almost every developer asks the same question:

"Can I just use a free library?"

The answer is: yes — but it depends on what you need.

The difference between free and paid PDF libraries isn't just about cost. It's about where you spend your effort.

🟢 Free / Open-Source Libraries

Free libraries are often the first choice — and for good reason.

They're accessible, flexible, and backed by active communities.

What they do well:

No upfront cost.
Full control over implementation.
Suitable for simple or well-defined use cases.

Where they get challenging:

Limited or no HTML-to-PDF support.
More manual work (layout, pagination, styling).
Inconsistent behavior across environments.
Licensing constraints (e.g., AGPL in some cases).

In practice, they work best when:

You're generating structured documents (reports, invoices).
You don't rely heavily on HTML rendering.
You're okay investing more development time.

🔵 Paid / Commercial Libraries

Commercial libraries take a different approach: they aim to solve the entire problem out of the box.

Instead of building everything yourself, you get a more complete toolkit.

What they do well:

Reliable HTML-to-PDF rendering.
Better handling of fonts, layouts, and pagination.
Consistent behavior across environments (Windows, Linux, Docker).
Dedicated support and documentation.

Trade-offs to consider:

Licensing cost.
Vendor lock-in.
Sometimes heavier dependencies.

They're typically a better fit when:

You need production-ready stability.
You're building backend services or automation pipelines.
Time-to-market matters more than implementation flexibility.

The Real Trade-Off

This is where most comparisons miss the point.

It's not:

Free vs Paid

It's actually:

Time vs Money

In most real-world projects, you don't really choose a library — you choose your trade-offs.

Free libraries often save money upfront, but require more engineering effort.

Paid libraries cost more initially, but can significantly reduce development time and risk.

If you're building something quick or highly customized, a free library might be enough.

But if you're building something that needs to run reliably in production, the decision becomes less about cost — and more about stability.

Library Breakdown — What Actually Works

Enough context. Let's get into the libraries themselves.

Before we dive into specific libraries, one important note: what follows isn't a rehash of each library's feature page. It's an honest assessment of where each one shines, where it struggles, and what kind of project it's actually suited for.

🟢 Open-Source Options

QuestPDF

QuestPDF is probably the most exciting thing to happen to .NET PDF generation in recent years. It takes a code-first, Fluent API approach — you describe your document structure in C#, and it renders it precisely and consistently.

The results are clean, the API is genuinely pleasant to work with, and it handles things like pagination and repeating headers without the usual pain. For structured documents — invoices, financial reports, delivery notes — it's hard to beat at any price.

The limitation is equally clear: QuestPDF doesn't do HTML rendering. If your documents are driven by Razor templates or existing HTML/CSS, this isn't your tool. You'll be rebuilding your layout in C# from scratch, which is a significant investment depending on complexity.

👍 Beautiful Fluent API, precise layout control, great for structured docs.
👎 No HTML support, requires code-based layout for everything.
✅ Best for: Invoices, reports, any document with a predictable structure.

PDFsharp

PDFsharp is the "old reliable" of the .NET open-source PDF world. It's been around for years, it's lightweight, and it does what it says.

That said, it's showing its age. The API is lower-level than modern alternatives, documentation is sparse, and complex layout work requires a lot of manual effort. It's not a library you'd choose for a serious production system in 2026 — but for simple one-off PDFs or quick prototypes, it gets the job done without ceremony.

👍 Lightweight, no frills, easy to get started.
👎 Low-level API, limited features, not actively developed.
✅ Best for: Simple PDF generation, quick prototypes, low-complexity needs.

iText 7 (AGPL)

iText is the most powerful open-source PDF library in the .NET ecosystem — and also the most misunderstood.

The functionality is exceptional. Fine-grained control over PDF structure, solid form handling, digital signatures, PDF/A compliance — if you need to do something with a PDF, iText can probably do it. The API has a learning curve, but it rewards the investment.

The real issue is the license. iText 7 is released under AGPL, which requires any software using it to also be open-sourced under AGPL. For commercial applications, that's almost certainly not acceptable — which means you'd need their commercial license, at which point it's no longer a "free" option.

Use it for open-source projects freely. For everything else, price out the commercial license before you build anything on top of it.

👍 Extremely powerful, precise PDF control, handles complex requirements.
👎 AGPL license is a serious constraint for commercial projects.
✅ Best for: Open-source projects, or commercial projects with budget for the paid license.

🔵 Commercial Options

IronPDF

If your primary use case is converting HTML to PDF, IronPDF is hard to ignore. It's built on Chromium, which means what you get out is very close to what a real browser would render — CSS, web fonts, JavaScript-rendered content, all of it.

For teams that maintain their document templates as HTML and want pixel-accurate output, that's a genuinely compelling proposition.

The tradeoff is resource usage. Chromium is not a lightweight dependency. Cold start times are noticeable, memory footprint is higher than other options, and in high-throughput scenarios you'll need to think carefully about how you manage instances. It's not a dealbreaker, but it's something to test against your actual load before committing.

👍 Best-in-class HTML rendering, near-perfect browser fidelity.
👎 High resource usage, slower cold starts, heavier infrastructure footprint.
✅ Best for: HTML-heavy documents, web page to PDF conversion, template-driven output.

Aspose.PDF

Aspose is the enterprise choice — and it earns that label in both directions.

The feature set is genuinely comprehensive: PDF generation, editing, conversion to and from dozens of formats, OCR, digital signatures, PDF/A and PDF/UA compliance, redaction. If you have a complex document processing requirement, Aspose almost certainly supports it. The library is mature, well-maintained, and the documentation is thorough.

The cost reflects that. Aspose is among the most expensive options in this space, and the API can feel heavy for simpler use cases. If you're building a straightforward report generator, it's more library than you need.

But for large-scale systems where document processing is a core part of the product — legal platforms, financial systems, document management pipelines — the investment tends to pay off. You're not going to hit a wall with Aspose.

👍 Most complete feature set, enterprise-grade reliability, extensive format support.
👎 Highest price point, API complexity, more than most projects need.
✅ Best for: Enterprise systems, complex document workflows, organizations with serious compliance requirements.

Spire.PDF

Spire.PDF sits in an interesting position: it's not the flashiest option, and it doesn't get talked about as much as IronPDF or Aspose — but in practice, it quietly handles a lot of real-world scenarios well.

HTML to PDF conversion is stable and doesn't require a full browser engine. The API is straightforward without being simplistic. It runs cleanly on Linux and inside Docker containers without needing special configuration — which is exactly where some browser-based solutions start to show friction, usually at the worst possible time: during deployment.

It won't win any design awards for its API, but for backend automation — generating documents server-side, processing PDFs in bulk, integrating into pipelines — it's a practical, reliable choice that won't surprise you in production.

👍 Stable HTML to PDF, clean Linux/Docker support, straightforward API.
👎 Less community presence, not as well known as larger alternatives.
✅ Best for: Server-side automation, backend document generation, Docker-based deployments.

Comparison Table

If you just want the quick answer, here's how they compare:

Library	HTML to PDF	License	Ease	Performance	Deployment	Best For
QuestPDF	❌	MIT	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	Structured reports, invoices
PDFsharp	❌	MIT	⭐⭐⭐	⭐⭐⭐	⭐⭐⭐⭐	Simple PDF generation
iText	⚠️ Partial	AGPL / Paid	⭐⭐	⭐⭐⭐⭐	⭐⭐⭐	Advanced PDF control
IronPDF	✅	Commercial	⭐⭐⭐⭐	⭐⭐⭐	⭐⭐	HTML to PDF (web content)
Aspose	✅	Commercial	⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	Enterprise-level solutions
Spire.PDF	✅	Commercial	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	Automation, backend services

A few notes on the table:

HTML to PDF — ⚠️ on iText means partial support exists but isn't a core strength. Don't build an HTML-driven workflow around it without testing first. ⚠️ on IronPDF for Linux/Docker means it works, but requires more setup than other options — Chromium dependencies need to be explicitly handled in your container.

So… Which One Should You Choose?

If you don’t want to overthink it, here's the short version:

👉 You need to generate structured documents — invoices, reports, statements

Go with QuestPDF

Clean API
Great layout control
No need for HTML

👉 Your documents are HTML-based and you need accurate rendering

Go with IronPDF if output fidelity is the priority and you can absorb the resource overhead.
If you're running in a lightweight backend environment and need something more predictable, Spire.PDF is worth evaluating — the rendering isn't browser-identical, but it's consistent and far less demanding on infrastructure.

👉 You need complete control over PDF internals — signatures, compliance, low-level manipulation

Go with iText

Powerful but complex
Best for advanced scenarios

👉 You're building backend automation — bulk generation, scheduled jobs, document pipelines

Go with a commercial library (Spire, IronPDF, Aspose)

More stable across environments
Less time spent debugging edge cases

👉 You just need something simple and fast

Go with PDFsharp

Lightweight
Easy to set up
Good enough for basic tasks

Real-World Advice — What the Docs Won't Tell You

By this point, the differences between libraries should be clearer. But in practice, a few factors matter more than anything listed in a feature comparison.

1. Don't choose based on features alone

Most libraries claim to support similar things.
The real difference shows up when you run them in production.

2. Test in your actual environment early

Especially if you're deploying to:

Linux
Docker
Cloud environments

What works locally may not work there.

3. Pay attention to licensing

This is often overlooked - until it becomes a problem.

Some "free" libraries have strict licenses (like AGPL)
Commercial use may require purchasing a license

4. Start with a small proof of concept (POC)

Before committing to a library, try:

Rendering a real document
Testing performance
Checking output consistency

A few hours of testing can save days of refactoring later.

5. Think long-term, not just quick wins

The wrong library usually doesn't fail immediately — it fails when you scale.

Closing Thoughts

There's no perfect PDF library for .NET. There's only the right one for what you're actually building.

If this article has a single takeaway, it's this: start with your constraints, not the feature list. Your deployment environment, your license requirements, your document structure, your team's capacity — those filters will do most of the work for you. By the time you've answered those questions honestly, the shortlist usually writes itself.

If you're working on document generation or automation in .NET, taking the time to choose the right library early can save you a lot of trouble later.

And in the end, the best PDF library isn't the most powerful one — it's the one that keeps working when everything else starts to break.

Why Microsoft Office Interop Fails for PDF Generation in .NET (And What to Use Instead)

Chloe — Tue, 21 Apr 2026 01:59:09 +0000

Microsoft Office Interop is still widely used for PDF generation in .NET because it feels like a quick win. It works on a local machine, requires minimal code, and produces accurate results by leveraging Microsoft Office itself.

The problems start in production. Applications begin to hang, background processes like WINWORD.EXE accumulate, and stability degrades under load—not because of application logic, but because Interop was built for desktop automation rather than server-side workloads.

Built for desktop automation, Interop depends on a GUI environment and system states that don’t exist in modern backend architectures. This article explains why it fails—and what to use instead.

What Is Microsoft Office Interop (And Why It's So Common)

Microsoft Office Interop is a set of .NET assemblies that allow applications to automate Microsoft Office programs such as Word and Excel. In the context of PDF generation, it is often used to open a document and export it directly to PDF using the built-in capabilities of Office.

Its popularity comes from a few practical advantages. First, it feels familiar—developers are effectively controlling tools they already know. Second, the API surface is relatively straightforward, making it easy to get a working solution with minimal code. And because the output is generated by Microsoft Office itself, the formatting accuracy is typically high.

This combination makes Interop especially appealing for quick implementations or internal tools. However, the same characteristics that make it convenient during development can become limitations once the application is deployed to a production environment.

Why Interop Fails in Production

The issues with Microsoft Office Interop are not incidental—they are a direct result of its design.

The core problems fall into a few categories:

Fundamentally Not Designed for Server Environments

Interop doesn't just use Office — it is Office. Every API call drives a full desktop application running in the background. Microsoft's own documentation explicitly states that Office is not designed for unattended server-side execution and is unsupported in that context.

Server environments don't have desktop sessions, logged-in users, or displays. Interop assumes all three. When those assumptions break, dialog boxes appear with no one to dismiss them, file pickers block indefinitely, and processes stall waiting for input that never comes.

Requires Microsoft Office Installation (Hard Dependency)

Every machine that runs your code needs a licensed Office installation — your web server, your build agent, your Docker container. In practice this means two problems: you can't containerize cleanly, and version inconsistencies between Office 2019 and Microsoft 365 mean the same document can render differently across environments.

Stability Issues (COM Lifecycle Complexity)

COM objects don't clean themselves up. Every Document and Application object needs explicit Marshal.ReleaseComObject() calls. Miss one, and the Office process keeps running after your code exits.

finally
{
    if (doc != null) Marshal.ReleaseComObject(doc);
    if (app != null) { app.Quit(); Marshal.ReleaseComObject(app); }
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

Even this pattern isn't bulletproof. Under certain error conditions the process still doesn't exit, and orphaned WINWORD.EXE instances accumulate quietly until the server runs out of memory. Interop is also not thread-safe — in a multi-threaded web server, race conditions are a matter of when, not if.

Poor Scalability (Process-Per-Request Model)

Each conversion request starts a new Office instance. Under any real load, this means multiple full Office processes running simultaneously, each consuming hundreds of megabytes. Batch processing is no better — failures become silent and unpredictable once you're iterating over large file sets.

Interop works fine for demos — but breaks under real production workloads.

What a Modern PDF Solution Should Provide

A production-ready PDF solution should meet the expectations of modern backend applications:

No Office dependency — a NuGet package, nothing more
Server environment support — ASP.NET Core, Docker, Linux, no display required
Stability under load — bounded memory, no process leaks, thread-safe
Clean .NET API — idiomatic C#, no COM artifacts
Streaming and batch support — MemoryStream for web APIs, stable iteration for batch jobs

These requirements reflect how applications are built and deployed today—and highlight why Interop struggles in these scenarios.

The Landscape: Standalone .NET PDF Libraries

Library	Strengths	Limitations
iText 7	Feature-complete, large community	AGPL — commercial use requires paid license
PdfSharp + MigraDoc	Fully open source	Weak Word conversion support
QuestPDF	Elegant fluent API for building documents	Weak support for converting existing Word/Excel files
Spire.Doc / Spire.PDF for .NET	No Office dependency, reliable conversion, cross-platform	Free tier has page limit
Puppeteer	Flexible HTML→PDF	Heavy runtime (browser dependency)

Different tools fit different scenarios. For generating PDFs from scratch, libraries like QuestPDF offer a clean developer experience. For HTML-based workflows, headless browsers provide flexibility.

However, when the requirement is reliable server-side conversion of existing Word or Excel documents, the priorities change: no Office dependency, consistent behavior across environments, and stable performance under load. This is where standalone libraries designed specifically for backend processing, like Spire.Doc and Spire.PDF for .NET, become the most practical choice. In practice, different libraries handle different parts of the workflow—for example, Spire.Doc for Word-to-PDF conversion and Spire.PDF for creating or manipulating PDF documents directly.

Practical Examples: Interop vs a Modern Approach

These limitations become obvious when you compare Interop with modern libraries in real-world scenarios.

Scenario A: Creating a PDF Report from Scratch

While Interop isn't typically used for creating PDFs from scratch, this comparison highlights how different the programming models are.

❌ Using Interop

var app = new Microsoft.Office.Interop.Word.Application();
var doc = app.Documents.Add();
var para = doc.Paragraphs.Add();
para.Range.Text = "Quarterly Report";
doc.SaveAs2(outputPath, WdSaveFormat.wdFormatPDF);
doc.Close();
app.Quit();
Marshal.ReleaseComObject(doc);
Marshal.ReleaseComObject(app);

Even for a simple document, you're launching a full Office instance, managing COM object lifecycles manually, and hoping nothing throws before the cleanup code runs.

This approach is error-prone and difficult to maintain in backend code.

✅ Using Spire.PDF

PdfDocument pdf = new PdfDocument();
PdfPageBase page = pdf.Pages.Add();
PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial", 14f));
PdfBrush brush = PdfBrushes.Black;
page.Canvas.DrawString("Quarterly Report", font, brush, 10, 10);
pdf.SaveToFile("report.pdf");

Creating PDFs becomes a straightforward, in-process operation.

Scenario B: Converting Existing Documents to PDF

This is where Interop causes the most production pain. The typical implementation looks like this:

❌ Using Interop

var word = new Application { Visible = false };
var doc = word.Documents.Open(inputPath);
doc.SaveAs2(outputPath, WdSaveFormat.wdFormatPDF);
doc.Close();
word.Quit();
Marshal.ReleaseComObject(doc);
Marshal.ReleaseComObject(word);

Locally, this works. In production, it requires Office installed, runs single-threaded, leaks processes under error conditions, and can lock files in ways that require manual cleanup on the server.

✅ Using Spire.Doc

var doc = new Document();
doc.LoadFromFile("report.docx");
doc.SaveToFile("report.pdf", FileFormat.PDF);

No Office. No COM cleanup. No process management. The same three lines work identically on Windows, Linux, and inside a Docker container.

This is where most Interop-based solutions start to break.

Scenario C: Returning a PDF from an ASP.NET Core Endpoint

❌ Using Interop

Each request spins up a new Office process. Concurrent requests interfere with each other. There's no clean way to write to a response stream — you're saving to disk and reading it back. Process cleanup in an async context is unreliable.

✅ Using Spire.Doc with MemoryStream

[HttpGet("report")]
public IActionResult GetReport()
{
    var doc = new Document();
    doc.LoadFromFile("template.docx");

    var stream = new MemoryStream();
    doc.SaveToStream(stream, FileFormat.PDF);
    stream.Position = 0;

    return File(stream, "application/pdf", "report.pdf");
}

The conversion (handled by Spire.Doc) happens entirely in memory. No temp files, no disk I/O, no process lifecycle to manage. This pattern scales horizontally without any changes.

This is where standalone libraries truly outperform Interop.

Interop vs Modern Libraries: A Quick Comparison

Feature	Interop	Modern Library
Requires Office	✅ Yes	❌ No
Server / Docker Support	❌ No	✅ Yes
Thread Safety	❌ No	✅ Yes
Stability Under Load	🔴 Low	🟢 High
Deployment Complexity	🔴 High	🟢 Low

When You Might Still Use Interop

Interop isn't universally wrong. It's a reasonable choice for local desktop automation tools, single-user scripts, and scenarios where Office is already present and concurrency isn't a concern. If you need to manipulate macros or preserve highly complex Office-specific formatting, it may still be the only option.

Interop isn't wrong — it's just used in the wrong context.

Conclusion: Move Beyond Interop

Microsoft Office Interop remains convenient for quick solutions, but its reliance on desktop components, lack of scalability, and instability under load make it unsuitable for modern backend systems.

Today's .NET systems are built to run in cloud environments, containers, and stateless services—where dependencies on GUI-based software simply don't fit. Instead of working around these constraints, it's more effective to adopt tools designed for server-side document processing from the ground up. Standalone libraries—such as Spire.Doc and Spire.PDF for .NET—exist precisely to address these challenges.

How to Highlight Duplicates in Excel: A Developer-Friendly Guide

Chloe — Mon, 13 Apr 2026 09:42:53 +0000

If you've ever received a spreadsheet from a product manager and been asked to "just check for duplicates," you know the feeling — it sounds trivial, but the moment you open the file, you realize what you're dealing with: 10,000 rows, multiple columns, no clear definition of what “duplicate” actually means in this context.

Duplicate detection is one of those tasks that sits at the intersection of Excel power users and backend developers. It's too manual to ignore, yet too tightly coupled to a .xlsx file to fit cleanly into your usual data tooling.

In this guide, we'll walk through a set of approaches that scale with your needs—from quick UI tricks to automation and developer-friendly solutions.

What Counts as "Duplicates" in Excel?
Method 1: Conditional Formatting (Built-in UI)
Method 2: COUNTIF Formula-Driven Conditional Formatting
Method 3: VBA Macro — Client-Side Automation
Method 4: Power Query & Dynamic Arrays
Method 5: Highlight Duplicates in Excel Using C# (Server-Side Automation)
Method Comparison at a Glance
Conclusion

What Counts as "Duplicates" in Excel?

Before jumping into solutions, it's worth clarifying what we actually mean by "duplicates," because the definition can vary depending on the scenario.

Different Types of Duplicates

In most real-world cases, duplicates fall into one of three categories:

Single-column duplicates: the same value appears more than once in a column, such as repeated email addresses
Full-row duplicates: every cell in two or more rows is identical.
Composite duplicates: a combination of columns forms a duplicate key, even if no individual column is duplicated on its own. For example, the same '(user_id, date)' pair appearing twice in a transaction log.

The distinction matters, because Excel’s built-in tools don’t always handle these cases the same way.

Common Gotchas

Even when the logic seems straightforward, a few subtle issues can lead to misleading results.

For example, Excel is not case-sensitive by default, which means Apple and apple are treated as the same value. That's convenient in many cases, but it can also hide inconsistencies.

Hidden characters are another common culprit. A value like "123" is not the same as "123 " if there's a trailing space—something that's easy to miss visually.

You'll also run into type mismatches, where "123" (text) and 123 (number) are treated differently. And yes, even Excel isn’t immune to floating-point quirks—calculations like 0.1 + 0.2 may not always behave exactly as expected.

Taken together, these edge cases are why “finding duplicates” is often more nuanced than it appears.

Method 1: Conditional Formatting (Built-in UI)

The fastest way to highlight duplicates — no formulas, no code. Best for one-off checks where you just need to see the problem quickly.

How to do it:

Select the column or range you want to check, then follow this path:

Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values

A dialog appears letting you choose a highlight color. Click OK and Excel immediately colors every cell whose value appears more than once in your selection.

One behavior to know:

If you select multiple columns at once, Excel checks each cell individually — not each row. So if column A has alice twice and column B has bob twice, both get highlighted, but a row where (alice, bob) appears twice would only be caught if both cells independently repeat. If you need row-level or composite duplicate detection, skip ahead to Method 2.

Customizing the format:

The default is a light red fill, but you can set any fill color, font color, or border style. In the same dialog, open the format dropdown and select "Custom Format" to access the full cell formatting options.

Limitations:

No case sensitivity control — Alice and alice are treated as the same
Dynamic ranges aren't tracked automatically — if you add rows later, you'll need to reapply or extend the rule manually
Rules stack: if you have multiple conditional formatting rules on the same range, order matters. Manage them under Home → Conditional Formatting → Manage Rules

Still, for quick, one-off checks, it's often the most efficient option.

Method 2: COUNTIF Formula-Driven Conditional Formatting

When the built-in UI isn't flexible enough — you need case sensitivity, composite keys, or more precise control over which occurrences get flagged — COUNTIF gives you that control without leaving Excel.

The idea is simple: instead of letting Excel decide what counts as a duplicate, you write a formula that returns TRUE or FALSE for each cell, and conditional formatting applies the highlight based on your logic.

Basic setup:

Select your data range, for example B2:B16, then create a new conditional formatting rule:

Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format."

Enter this formula:

=COUNTIF($B$2:$B$16, B2) > 1

This counts how many times the value in B2 appears across the entire range. If it appears more than once, the cell gets highlighted. The $ signs lock the range while letting the row reference shift as the rule applies to each cell.

If you're familiar with SQL, this is the spreadsheet equivalent of:

SELECT value
FROM table
GROUP BY value
HAVING COUNT(*) > 1

Highlight only the second occurrence onwards:

The basic formula flags every occurrence, including the first. If you want to mark only the duplicates — leaving the original untouched — use this instead:

=COUNTIF($B$2:B2, B2) > 1

The key difference is $B$2:B2 — the start of the range is anchored but the end expands as the rule moves down each row. By the time Excel evaluates row 5, the range is $B$2:B5, so it only counts occurrences up to the current row. The first appearance is never flagged; only repeats are.

Composite duplicate detection:

To check whether a combination of columns forms a duplicate — say (email, signup_date) — concatenate the values into a single key using &:

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2) > 1

Or with string concatenation across more columns:

=COUNTIF($A$2:$A$100 & $B$2:$B$100, A2 & B2) > 1

This is the closest you'll get to a composite primary key check inside native Excel.

Case-sensitive duplicate detection:

COUNTIF is case-insensitive by default. For case-sensitive matching, use EXACT wrapped in SUMPRODUCT:

=SUMPRODUCT((EXACT($A$2:$A$100, A2)) * 1) > 1

This iterates over every value in the range and checks for an exact match, including case, returning a count. More expensive on large datasets, but it works.

Method 3: VBA Macro — Client-Side Automation

The three methods above all live inside Excel's UI. They work well for interactive use, but they're hard to trigger programmatically, difficult to version control, and impossible to run without opening Excel manually. If you need duplicate highlighting to happen automatically — on file open, on a button click, or as part of a repeatable workflow — VBA is the natural next step.

Basic implementation:

The core logic is straightforward: iterate over a column, track how many times each value has appeared using a Dictionary, then go back and color any cell whose value appeared more than once.

Sub HighlightDuplicates()

    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim dict As Object
    Dim highlightColor As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A2:A1000")
    highlightColor = RGB(255, 199, 206)

    Set dict = CreateObject("Scripting.Dictionary")

    For Each cell In rng
        If cell.Value <> "" Then
            Dim key As String
            key = Trim(CStr(cell.Value))
            If dict.exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell

    For Each cell In rng
        If cell.Value <> "" Then
            If dict(CStr(cell.Value)) > 1 Then
                cell.Interior.Color = highlightColor
            Else
                cell.Interior.ColorIndex = xlNone
            End If
        End If
    Next cell

End Sub

The first loop builds the frequency map. The second loop applies or removes the highlight based on the count. Splitting into two passes keeps the logic clean and makes it easy to add a "clear all highlights first" step before reapplying.

Making it reusable:

Hard-coding the sheet name and range is fine for a one-off script, but if you plan to reuse this across files, parameterize the key variables:

Sub HighlightDuplicates(sheetName As String, columnRange As String, highlightColor As Long)
    Dim ws As Worksheet
    Dim rng As Range
    Set ws = ThisWorkbook.Sheets(sheetName)
    Set rng = ws.Range(columnRange)
    ' ... same logic as above
End Sub

Call it from elsewhere in your project:

Call HighlightDuplicates("Sheet1", "A2:A1000", RGB(255, 199, 206))

Triggering automatically on file open:

To run the macro every time the workbook opens, add it to the Workbook_Open event in ThisWorkbook:

Private Sub Workbook_Open()
    Call HighlightDuplicates("Sheet1", "A2:A1000", RGB(255, 199, 206))
End Sub

Now every time someone opens the file, duplicates are flagged automatically — no manual steps required.

Performance on large datasets:

VBA loops are slow by default because Excel recalculates and redraws after every change. For datasets over a few thousand rows, disable these before running and re-enable them after:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' ... your loop here ...

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

On a 50,000-row dataset, this alone can reduce runtime from 30+ seconds to 2–3 seconds.

Where VBA falls short:

VBA runs inside Excel, which means it requires Excel to be installed and open. It can't be triggered from a CI pipeline, a backend service, or a scheduled task on a server. If your use case involves processing Excel files without a desktop environment, the next method is what you need.

Method 4: Power Query & Dynamic Arrays

While the previous methods work well within Excel itself, they are harder to integrate into data pipelines or produce clean, structured outputs. If your goal is to output a deduplicated table, feed results into another process, or refresh the analysis automatically when data updates, Power Query and dynamic arrays are better tools.

Dynamic arrays (Excel 365 only):

If you're on Microsoft 365, the UNIQUE() and FILTER() functions let you extract duplicate values directly into a new range — no conditional formatting involved.

To get a list of values that appear more than once:

=FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1)

COUNTIF(A2:A100, A2:A100) returns an array of counts — one per row. FILTER() then keeps only the rows where that count exceeds 1. The result spills automatically into adjacent cells.

Note: On very large datasets, this approach may become slow because COUNTIF is evaluated across the entire range for each element.

To get just the unique values among those duplicates — without repetition:

=UNIQUE(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100) > 1))

This is clean, readable, and updates live as your source data changes. The main constraint is that it requires Excel 365 — it won't work on Excel 2016 or 2019.

Power Query:

Power Query works on Excel 2016 and later, and is better suited for larger datasets or more complex transformations. The approach is to group rows by the columns you care about, count occurrences, then filter for groups with more than one row.

To open Power Query:

Data → Get Data → From Table/Range

Select your data range and click OK. In the Power Query editor:

Select the column you want to check for duplicates
Go to Home → Group By
Group by your target column, add a count column using Count Rows
Click OK — you now have a summary table with each value and its occurrence count
Filter the count column to show only rows where count > 1

Click Close & Load to output the results to a new sheet. Every time you refresh the query (Data → Refresh All), it re-runs against the latest data.

For composite duplicate detection, select multiple columns before grouping — Power Query will treat the combination as a single key.

👉 If you're familiar with SQL, you can think of this as a GROUP BY + HAVING COUNT > 1 workflow, but expressed through a visual interface.

When to use which:

Dynamic arrays are ideal when you want a live formula that updates automatically and the data fits within a single worksheet. Power Query is the better choice when the dataset is large, the transformation logic is complex, or you need a repeatable process that non-technical users can trigger with a single Refresh click.

Neither method requires code, but both offer a significant step up in capability — and both output clean data rather than just painting cells.

Method 5: Highlight Duplicates in Excel Using C# (Server-Side Automation)

Up to this point, all approaches still depend on Excel as the execution environment. That works for manual workflows, but starts to break down when you need to process files in a backend service, a CI/CD pipeline, or any environment where Microsoft Office isn't available.

In these scenarios, using a standalone .NET library becomes the most practical approach. Instead of relying on Excel itself, you work directly with the file format in code — making the solution easier to automate, deploy, and scale.

One option here is Spire.XLS, a .NET library that reads and writes Excel files independently of Office. No COM interop, no Excel process in the background, and no dependency on a desktop environment. You install it via NuGet, point it at a file, and manipulate it like any other object in your codebase.

Setup:

Install the package via NuGet:

dotnet add package Spire.XLS

Or via the NuGet Package Manager in Visual Studio:

Install-Package Spire.XLS

No further configuration needed. The library handles .xlsx and .xls formats out of the box.

Core object model:

Before writing the implementation, it helps to understand three objects you'll use throughout:

Workbook — represents the entire Excel file
Worksheet — a single sheet within the workbook
CellRange — a cell or range of cells, with properties for value, style, and formatting

Basic implementation:

The logic mirrors the VBA approach: build a frequency map, then iterate again to highlight duplicates. The difference is that this runs as a standalone C# program — no Excel required.

using Spire.Xls;
using System.Collections.Generic;
using System.Drawing;

class Program
{
    static void Main(string[] args)
    {
        Workbook workbook = new Workbook();
        workbook.LoadFromFile("data.xlsx");

        Worksheet sheet = workbook.Worksheets[0];
        int startRow = 2;
        int endRow = sheet.LastRow;
        int targetColumn = 2;

        // Build frequency map
        Dictionary<string, List<int>> valueMap = new Dictionary<string, List<int>>();

        for (int row = startRow; row <= endRow; row++)
        {
            CellRange cell = sheet.Range[row, targetColumn];
            string value = cell.Value?.Trim();

            if (string.IsNullOrEmpty(value)) continue;

            if (!valueMap.ContainsKey(value))
                valueMap[value] = new List<int>();

            valueMap[value].Add(row);
        }

        // Apply highlighting to duplicate rows
        Color highlightColor = Color.FromArgb(255, 199, 206);

        foreach (var entry in valueMap)
        {
            if (entry.Value.Count > 1)
            {
                foreach (int row in entry.Value)
                {
                    CellRange cell = sheet.Range[row, targetColumn];
                    cell.Style.Color = highlightColor;
                }
            }
        }

        workbook.SaveToFile("data_highlighted.xlsx", ExcelVersion.Version2013);
        System.Console.WriteLine("Done. Duplicates highlighted.");
    }
}

The Dictionary<string, List<int>> stores each unique value alongside the row numbers where it appears. After the first pass, any key with more than one row number in its list is a duplicate. The second pass iterates over those row numbers and applies the fill color.

Composite duplicate detection:

To check for duplicates across multiple columns — say columns 1 and 2 together — concatenate the cell values into a composite key:

string value = $"{sheet.Range[row, 1].Value?.Trim()}|{sheet.Range[row, 2].Value?.Trim()}";

The | separator prevents false matches where concatenating two different pairs could produce the same string. For example, ("ab", "c") and ("a", "bc") would both produce "abc" without a separator, but "ab|c" and "a|bc" are distinct.

Make sure the separator character does not appear in your actual data, or choose a more robust delimiter.

Skipping the header row:

The startRow = 2 in the implementation above already handles this — adjust the value to match however many header rows your file has.

Custom highlight colors:

Color.FromArgb(r, g, b) accepts any RGB value. To match the default Excel duplicate highlight:

Color highlightColor = Color.FromArgb(255, 199, 206); // light red fill
Color fontColor = Color.FromArgb(156, 0, 6);          // dark red text

cell.Style.Color = highlightColor;
cell.Style.Font.Color = fontColor;

Integrating into a .NET service:

Because this is plain C#, it slots naturally into any .NET application — an ASP.NET endpoint that processes uploaded files, a background worker that validates reports on a schedule, or a console tool invoked from a CI pipeline:

# Example GitHub Actions step
- name: Validate Excel report
  run: dotnet run --project ./ExcelValidator -- --file reports/output.xlsx

The process exits with a non-zero code if duplicates are found, which causes the pipeline step to fail — giving you automatic duplicate detection as a CI gate.

Spire.XLS vs. VBA — when to use which:

VBA is the right tool when the work stays on a desktop, the file is opened interactively, and the person running it has Excel. Spire.XLS is the right tool when the file is processed on a server, the environment has no Office installation, or you need the logic to be testable, version-controlled, and integrated into a larger codebase.

Method Comparison at a Glance

Five methods is a lot to hold in your head. Here's a summary to help you pick the right one for your situation:

Method	Requires coding	Needs Office	Automation	Best for
Conditional Formatting	No	Yes	Manual	Quick one-off visual check
COUNTIF formula	No (formula)	Yes	Semi-auto	Flexible logic, stays in Excel
Power Query / Dynamic arrays	No	Yes (365)	Auto-refresh	Periodic reports, clean output
VBA macro	Yes	Yes	Full auto	Desktop automation, event-driven
C# Spire.XLS	Yes	No	Full auto	Server-side, CI/CD, batch processing

For quick decision-making, here's a simple rule of thumb:

Spot-checking a file someone sent you → Conditional Formatting.
Logic is complex but you're staying in Excel → COUNTIF formula.
You need a refreshable, clean output table → Power Query.
Desktop automation, triggered on open or button click → VBA.
Server-side, no Office, CI/CD pipeline → C# Spire.XLS.

Conclusion

Highlighting duplicates in Excel may seem like a small task, but it often reveals deeper issues in how data is structured and validated. What starts as a quick visual check can quickly turn into questions about consistency, edge cases, and reliability. Depending on your workflow, the right approach can range from simple conditional formatting to more controlled, repeatable solutions using formulas, Power Query, or code. As your data grows and processes become more automated, duplicate detection stops being just an Excel trick and becomes part of a broader data quality strategy. Choosing the right method is less about the tools themselves, and more about where your data logic naturally belongs as your workflow evolves.