Excel Operations in C# — ClosedXML

In this article, we’ll dive into working with Excel files using the ClosedXML library in C#. We’ll cover reading and writing Excel files, manipulating data, setting values and formulas, and adding new worksheets.

Each code section will be accompanied by an explanation of the resulting .xlsx output.

Prerequisites

Ensure your .NET project has ClosedXML installed:

dotnet add package ClosedXML

ClosedXML

Step 1: Reading Excel Data

Here we read an existing Excel file Sample.xlsx, assuming it has a simple table with some data.

using ClosedXML.Excel;
using System;

namespace ClosedXMLExample
{
    public class ExcelHandler
    {
        public void ReadExcelFile(string filePath)
        {
            using (var workbook = new XLWorkbook(filePath))
            {
                var worksheet = workbook.Worksheet(1);
                int rowCount = worksheet.RowCount();
                int colCount = worksheet.ColumnCount();

                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        Console.WriteLine($"Value at R{row}C{col}: {worksheet.Cell(row, col).Value}");
                    }
                }
            }
        }
    }
}

Output in Sample.xlsx:

| Name    | Age |
|---------|-----|
| Alice   | 30  |
| Bob     | 25  |

Step 2: Manipulating Data and Setting Formulas

We will add a new column that calculates a simple operation, setting both direct values and a formula.

public void ManipulateAndAddFormula(string filePath)
{
    using (var workbook = new XLWorkbook(filePath))
    {
        var worksheet = workbook.Worksheet(1);
        worksheet.Cell(1, 3).Value = "Double Age";
        
        for (int row = 2; row <= worksheet.RowCount(); row++)
        {
            int age = Convert.ToInt32(worksheet.Cell(row, 2).Value);
            worksheet.Cell(row, 3).FormulaA1 = $"={worksheet.Cell(row, 2).Address}*2";
        }
    }
}

Updated Sample.xlsx Output:

| Name    | Age | Double Age |
|---------|-----|------------|
| Alice   | 30  | =B2*2      |
| Bob     | 25  | =B3*2      |

Step 3: Adding a New Worksheet

Now, let’s add a new worksheet and populate it with some initial data.

public void AddNewWorksheet(string filePath)
{
    using (var workbook = new XLWorkbook(filePath))
    {
        var newSheet = workbook.AddWorksheet("NewSheet");
        newSheet.Cell(1, 1).Value = "Timestamp";
        newSheet.Cell(1, 2).Value = "Event";
        newSheet.Cell(2, 1).Value = DateTime.Now;
        newSheet.Cell(2, 2).Value = "Created sheet";

        workbook.SaveAs(filePath);
    }
}

New Sample.xlsx Output with Additional Sheet NewSheet:

• Sheet1:

| Name    | Age | Double Age |
|---------|-----|------------|
| Alice   | 30  | 60         |
| Bob     | 25  | 50         |

• NewSheet:

| Timestamp           | Event        |
|---------------------|--------------|
| 06/21/2024 12:34:56 | Created sheet|

Putting It All Together

Here’s a simple program that uses all the previously defined methods.

class Program
{
    static void Main(string[] args)
    {
        var handler = new ExcelHandler();
        string filePath = "Sample.xlsx";

        handler.ReadExcelFile(filePath);
        handler.ManipulateAndAddFormula(filePath);
        handler.AddNewWorksheet(filePath);
    }
}

These operations enable developers to integrate complex Excel functionalities into their .NET applications, suitable for both data manipulation and reporting tasks.

I trust this information has been valuable to you. 🌟 Wishing you an enjoyable and enriching learning journey!

📚 For more insights like these, feel free to 👏 follow 👉 Merwan Chinta