C# uses ClosedXML to read and write excel operations

Project Introduction

ClosedXML is an open source library on the .NET platform that enables programmers to easily create, read and modify Excel files (.xlsx format). Without directly interacting with Microsoft Office Interop, ClosedXML provides a lightweight and high-performance way to process Excel data, compatible with .NET Framework and .NET Core.

Technical Analysis

Ease of use: ClosedXML provides a clear and intuitive API, making it easy to create tables, set styles, insert formulas, etc. For example, just a few lines of code can create a new workbook and fill in data:

using (var workbook = new XLWorkbook())
{
    var worksheet = ("Sample Sheet");
    ("A1").Value = "Hello";
    ("B1").Value = "World";
    ("");
}

Performance Optimization: ClosedXML improves performance with memory-mapped files and latency computing strategies, which means data is computed or written until needed, reducing unnecessary resource consumption.

Comprehensive functions: ClosedXML supports handling complex Excel functions, such as charts, data verification, conditional formatting, hyperlinks, etc. In addition, it also supports reading and writing data tables and processing large data sets.

Version Compatibility: This library is adapted to .NET Framework 4.0+ and .NET Core, ensuring cross-platform compatibility and future development potential.

Community Active: ClosedXML has an active development team and community that constantly fixes bugs, adds new features, and responds positively to user feedback.

Application scenarios

Data report generation: Quickly export database data to Excel and generate reports for custom templates.

File parsing: used to read existing Excel files, extract the data in them for analysis or import them to other systems.

Front-end download function: In conjunction with web applications, users can download customized Excel files.

Features

Concise API: Make code easier to write and understand.

Powerful performance: Avoid dependence on Office Interop, improving running speed and memory efficiency.

Integrity: Overwrites most of the functional features of Excel.

Scalability: Allows custom styles and behaviors to meet specific needs.

Cross-platform support: Can run on Windows, Linux, and macOS.

C# read and write excel using ClosedXML

First install ClosedXML using NuGet, and then add a reference to the program;

1. Export data to Excel

public void ExportToExcel(DataTable dt)
{
   using (var workbook = new XLWorkbook())
   {
        if ( == "")  = "sheet1";
        var worksheet = ();
        var header = ();
        for (int i = 0; i < ; ++i)
        {
            (1, i + 1).Value = [i].ColumnName;
            (1, i + 1). = ;
            (1, i + 1). = ;
        }
        for (int i = 0; i < ; ++i)
        {
            for (int j = 0; j < ; ++j)
            {
                (i + 2, j + 1).Value = [i][j].ToString();
                (i + 2, j + 1). = ;
                (i + 2, j + 1). = ;
             }
         }
         ();
     }         
}

2. Read data from Excel

public DataTable ImportExcelToDataTable(string filepath)
{
   DataTable dt = new DataTable();
   DataColumn dataColumn=new DataColumn("columnName1");
   (dataColumn);
   dataColumn=new DataColumn("columnName2");
   (dataColumn);
   dataColumn=new DataColumn("columnName3");
   (dataColumn);
   dataColumn=new DataColumn("columnName4");
   (dataColumn);
   using (XLWorkbook workBook = new XLWorkbook(filepath))
   {
       IXLWorksheet workSheet = (1);                  
       bool isFirstRow = true;
       foreach (var row in ())
       {
           if (isFirstRow)
           { 
                isFirstRow = false;
                continue;
           }
           else
           {
                ();
                int i = 0;
                foreach (IXLCell cell in ())
                {
                     if(i>-1)
                     {
                        break;
                     }
                     [ - 1][i] = ();
                     i++;
                }
            }
         }
   }
   return dt;
}

3. Read content mapped to DataTable

using ;
using .;
using ;
using System;
using ;
using ;
using ;
 
public class ExcelReader
{
    /// &lt;summary&gt;
    /// Read the content of the Excel file and return a Dictionary list containing multiple worksheets    /// &lt;/summary&gt;
    /// <param name="excelFilePath">Excel file path</param>    /// <param name="sheetNames">List of worksheet names that need to be read</param>    /// <returns>Worksheet: Worksheet data</returns>    public Dictionary&lt;string, DataTable&gt; ReadExcel(string excelFilePath, List&lt;string&gt; sheetNames)
    {
        Dictionary&lt;string, DataTable&gt; result = new Dictionary&lt;string, DataTable&gt;();
 
        try
        {
            using (var workbook = new XLWorkbook(excelFilePath))
            {
                // Iterate through all worksheets that need to be read                foreach (var sheetName in sheetNames)
                {
                    // Determine whether the worksheet exists                    if ((sheetName))
                    {
                        var worksheet = (sheetName);
                        DataTable dataTable = ConvertWorksheetToDataTable(worksheet);
                        (sheetName, dataTable);
                    }
                    else
                    {
                        ($"Worksheet {sheetName} Does not exist！");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            ($"Read Excel An error occurred while file: {}");
        }
 
        return result;
    }
 
    /// &lt;summary&gt;
    /// Convert worksheets to DataTable    /// &lt;/summary&gt;
    /// <param name="worksheet">worksheet</param>    /// <returns>Converted DataTable</returns>    private DataTable ConvertWorksheetToDataTable(IXLWorksheet worksheet)
    {
        DataTable dataTable = new DataTable();
        bool isHeaderRow = true;
 
        // Iterate through every row of the worksheet        foreach (var row in ())
        {
            if (isHeaderRow)
            {
                // The first non-empty cell is the column header                foreach (var cell in ())
                {
                    (());
                }
                isHeaderRow = false;
            }
            else
            {
                // Each line corresponds to a row of DataTable                DataRow dataRow = ();
                int columnIndex = 0;
 
                foreach (var cell in ())
                {
                    dataRow[columnIndex] = ();
                    columnIndex++;
                }
 
                (dataRow);
            }
        }
 
        return dataTable;
    }
}
 
public class Program
{
    public static void Main()
    {
        string excelFilePath = @"C:\Users\Mike\Desktop\";
        List&lt;string&gt; sheetNames = new List&lt;string&gt; { "Import", "Import 2" };
 
        ExcelReader excelReader = new ExcelReader();
        var result = (excelFilePath, sheetNames);
        foreach (var item in result)
        {
            ($"Worksheet：{}");
            var dt = ;
            foreach (DataRow row in )
            {
                //Print the contents of the first, second and third columns of the row                ($"{row[0]}_{row[1]}_{row[2]}");
            }
            ();
        }
    }
}

4. Read the Sheet name list

class Program
{
    static void Main()
    {
        string filePath = "C:\\Users\\XCKJ\\Desktop\\Import.xlsx"; // Please replace the path to your file        var list = GetSheetNameList(filePath);// Get all sheet names    }
 
    private static List&lt;string&gt; GetSheetNameList(string filePath)
    {
        // Open Excel file        using (var workbook = new XLWorkbook(filePath))
        {
            // Get all sheet names            List&lt;string&gt; sheetNames = new List&lt;string&gt;();
            foreach (var worksheet in )
            {
                ();
            }
            return sheetNames;
        }
    }
}

This is the article about C# using ClosedXML for reading and writing excel operations. For more related C# ClosedXML for reading and writing excel, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!