8 Excel Automation Tasks Every C# Developer Should Know

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.