SoFunction
Updated on 2025-03-07

Several common ways to export Excel and detailed implementation steps for C#

Common Excel export methods

In C#, commonly used Excel file export methods include:

  1. Using Microsoft Office Interop Excel Components: This is a way to create and edit Excel files using the Microsoft Excel application object model. It provides powerful features but requires the installation of Microsoft Excel or Microsoft Office suite.
  2. Using NPOI Components: NPOI is an open source component on the C#/.NET platform that can read and write documents in Microsoft Office format, including Excel, Word, and PowerPoint. Compared to Interop Excel components, NPOI is lighter and does not need to rely on Microsoft Excel applications.
  3. Using EPPlus Components: EPPlus is a free and open source component that creates and reads Excel files. It provides some convenient methods, such as operating on cell formats, charts, formulas, etc. EPPlus supports .NET Framework 3.5 and later and does not need to rely on Microsoft Excel applications.
  4. Using ClosedXML Components: ClosedXML is an easy-to-use open source component that can read, create, and modify Excel files. Similar to EPPlus, it also provides some convenient ways to manipulate Excel files. ClosedXML supports .NET Framework 4.0 and later.

The above are several commonly used C# methods to export Excel files. Each method has its own characteristics and advantages and disadvantages. Just choose a method that suits your needs for development. The following lists specific steps and demos for each export method.

1. Export Excel files using the Microsoft Office Interop Excel component

The steps to export Excel files using the Microsoft Office Interop Excel component are as follows:

  1. Reference Namespace: Add a reference to the project, or install it using the NuGet package manager.
  2. Create an Excel application object: UseApplicationThe class creates an Excel application object.
  3. Create a workbook object: Use()Method creates a new workbook object.
  4. Get the worksheet object: UseProperties get the worksheet collection and use the index or name to get the worksheet object.
  5. Write data to a cell: UseAttributes to get cell objects and useAttribute orProperties write data to cells.
  6. Save Excel file: Use()Method Save Excel files to disk.
  7. Close Excel application and workbook objects: Use()Method closes the workbook object and uses()Method Closes the Excel application object.
  8. Release resources: Use()Method Frees the resources of Excel application objects and workbook objects.

Here is a sample code that demonstrates how to export Excel files using the Microsoft Office Interop Excel component:

using System;
using ;
using ;
using ;
namespace ExportExcelDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a DataTable object to store data            DataTable dataTable = new DataTable("MyData");
            // Add column to DataTable            ("Name", typeof(string));
            ("Age", typeof(int));
            // Add data rows to DataTable            ("John Doe", 30);
            ("Jane Smith", 25);
            // Export Excel files using Microsoft Office Interop Excel components            Application excelApp = new Application();
            Workbook workbook = ();
            Worksheet worksheet = (Worksheet)[1];
            int row = 1;
            foreach (DataRow dataRow in )
            {
                [row, 1] = dataRow["Name"].ToString();
                [row, 2] = dataRow["Age"];
                row++;
            }
            // Save Excel files to disk            string fileName = @"C:\temp\";
            (fileName);
            // Close Excel application and workbook objects and free resources            ();
            ();
            (worksheet);
            (workbook);
            (excelApp);
        }
    }
}

In this example code, we useApplicationThe class creates an Excel application object and uses it to create a new workbook object. Then, we useThe property gets the worksheet collection and uses the index to get the first worksheet object. Next, we useAttributes to get cell objects and useAttribute orProperties write data to cells. Finally, we use()Method saves Excel files to disk, closes the workbook objects and Excel application objects, and frees their resources.

2. Export Excel files using NPOI components

The steps to export an Excel file using the NPOI component are as follows:

  1. Reference NPOI namespace: Add a reference to NPOI in your project, or install it using the NuGet package manager.
  2. Create a workbook object: UseHSSFWorkbookorXSSFWorkbookThe class creates a new workbook object that corresponds to Excel's .xls and .xlsx formats, respectively.
  3. Get the worksheet object: UseCreateSheet()Method creates a new worksheet object and sets the worksheet name.
  4. Set cell style: Can be usedCellStyleClass andFontClass to style the cell.
  5. Write data to a cell: UseCreateRow()Method creates a new row object, usingCreateCell()Method creates a new cell object and usesSetValue()Method writes data to the cell.
  6. Save Excel file: UseFileStreamThe class saves the Excel file to disk.
  7. Release resources: UseDispose()Method frees resources for workbook objects and FileStream objects.

Here is a sample code that demonstrates how to export an Excel file using an NPOI component:

using System;
using ;
using ;
using ;
using ;
namespace ExportExcelDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a DataTable object to store data            DataTable dataTable = new DataTable("MyData");
            // Add column to DataTable            ("Name", typeof(string));
            ("Age", typeof(int));
            // Add data rows to DataTable            ("John Doe", 30);
            ("Jane Smith", 25);
            // Export Excel files using NPOI components            IWorkbook workbook = new HSSFWorkbook();
            ISheet worksheet = ("MySheet");
            int row = 0;
            foreach (DataRow dataRow in )
            {
                IRow newRow = (row);
                (0).SetCellValue(dataRow["Name"].ToString());
                (1).SetCellValue(Convert.ToInt32(dataRow["Age"]));
                row++;
            }
            // Save Excel files to disk            string fileName = @"C:\temp\";
            using (FileStream fs = new FileStream(fileName, , ))
            {
                (fs);
            }
            // Free up resources            ();
        }
    }
}

In this example code, we useHSSFWorkbookThe class creates a new workbook object and uses it to create a worksheet object named "MySheet". Then, we useCreateRow()Methods andCreateCell()Method creates new row objects and cell objects and usesSetValue()Method writes data to the cell. Finally, we useFileStreamThe class saves the Excel file to disk and usesDispose()Method Frees the resource of the workbook object. If you need to generate a file in .xlsx format, you can useXSSFWorkbookClass substitutionHSSFWorkbookkind.

3. Use the EPPlus component to export Excel files

The steps to export Excel files using the EPPlus component are as follows:

  1. Reference EPPlus namespace: Add a reference to EPPlus in your project, or install it using the NuGet package manager.
  2. Create a workbook object: UseExcelPackageThe class creates a new workbook object.
  3. Get the worksheet object: Use()Method creates a new worksheet object and sets the worksheet name.
  4. Set cell style: Can be usedProperties to style the cell.
  5. Write data to a cell: UseSetValue()Method writes data to the cell.
  6. Save Excel file: Use()Method Save Excel files to disk.
  7. Release resources: UseDispose()Method Frees the resource of the workbook object.

Here is a sample code that demonstrates how to export an Excel file using the EPPlus component:

using System;
using ;
using ;
using OfficeOpenXml;
namespace ExportExcelDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a DataTable object to store data            DataTable dataTable = new DataTable("MyData");
            // Add column to DataTable            ("Name", typeof(string));
            ("Age", typeof(int));
            // Add data rows to DataTable            ("John Doe", 30);
            ("Jane Smith", 25);
            // Export Excel files using the EPPlus component            using (ExcelPackage excelPackage = new ExcelPackage())
            {
                ExcelWorksheet worksheet = ("MySheet");
                int row = 1;
                foreach (DataRow dataRow in )
                {
                    [row, 1].Value = dataRow["Name"].ToString();
                    [row, 2].Value = Convert.ToInt32(dataRow["Age"]);
                    row++;
                }
                // Save Excel files to disk                string fileName = @"C:\temp\";
                FileInfo fileInfo = new FileInfo(fileName);
                (fileInfo);
            }
            // Free up resources        }
    }
}

In this example code, we useExcelPackageThe class creates a new workbook object and uses it to create a worksheet object named "MySheet". Then, we useCellsAttributes to get cell objects and useSetValue()Method writes data to the cell. Finally, we use()Methods to save Excel files to disk and useDispose()Method Frees the resource of the workbook object.

4. Export Excel files using ClosedXML components

The steps to export an Excel file using the ClosedXML component are as follows:

  1. Reference ClosedXML namespace: Add a reference to ClosedXML in the project, or install it using the NuGet package manager.
  2. Create a workbook object: UseXLWorkbookThe class creates a new workbook object.
  3. Get the worksheet object: UseAddWorksheet()Method creates a new worksheet object and sets the worksheet name.
  4. Set cell style: Can be usedIXLStyleInterface andIXLFontInterface to set cell styles.
  5. Write data to a cell: UseSetValue()Method writes data to the cell.
  6. Save Excel file: UseSaveAs()Method Save Excel files to disk.
  7. Release resources: UseDispose()Method Frees the resource of the workbook object.

Here is a sample code that demonstrates how to export an Excel file using the ClosedXML component:

using System;
using ;
using ;
using ;
namespace ExportExcelDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a DataTable object to store data            DataTable dataTable = new DataTable("MyData");
            // Add column to DataTable            ("Name", typeof(string));
            ("Age", typeof(int));
            // Add data rows to DataTable            ("John Doe", 30);
            ("Jane Smith", 25);
            // Export Excel files using ClosedXML components            using (XLWorkbook workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet = ("MySheet");
                int row = 1;
                foreach (DataRow dataRow in )
                {
                    (row, 1).Value = dataRow["Name"].ToString();
                    (row, 2).Value = Convert.ToInt32(dataRow["Age"]);
                    // Set cell style                    (row, 2). = true;
                    (row, 2). = ;
                    row++;
                }
                // Save Excel files to disk                string fileName = @"C:\temp\";
                (fileName);
            }
            // Free up resources        }
    }
}

In this example code, we useXLWorkbookThe class creates a new workbook object and uses it to create a worksheet object named "MySheet". Then, we useCell()Method to get the cell object and useSetValue()Method writes data to the cell. Next, we useIXLStyleInterface andIXLFontInterface to set cell styles. Finally, we useSaveAs()Methods to save Excel files to disk and useDispose()Method Frees the resource of the workbook object.

Summarize

This is the article about several common ways to export Excel and detailed implementation steps for C#. For more relevant common methods for exporting Excel, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!