Export DataTable To Excel using NPOI

Today, in this article we shall cover a simple use case where we will be connecting the SQL server and exporting SQL Table To Excel using NPOI.

We will Export DataTable or DataSet To Excel files using the NPOI Nuget library programmatically.

In our last post, we saw the usage of the NPOI Nuget package usage to work with Excel files.

We looked at the basic read and write of an Excel file using C# in .NET or .NET Core application.

Today in this article, we will cover below aspects,

Getting Started

Let’s create a .NET Core project, you can choose any project template. Here we shall use the .NET Core 3.1 Console project.

NuGet package name is NPOI. Let’s install this package,

PM> Install-Package NPOI -Version 2.5.1

OR

Please install from Nuget Manager,

Connecting SQL Server

Let’s now connect the SQL server and get the data filled as DataSet.

I have already discussed how to connect to the SQL server using SQLClient or EFCore in the below article.

• Connect SQL Server ADO.NET C# Example

• Getting Started with EFCore in ASP.NET Core

Please use the below method which takes Dataset as input and saves each table as a separate sheet in the Excel sheet.

Export excel as SQL table

 public static void SaveDataSetAsExcel(DataTable dataTable, string exceloutFilePath)
        {
                using (var fs = new FileStream(exceloutFilePath, FileMode.Append, FileAccess.Write))
                {
                    IWorkbook workbook = new XSSFWorkbook();
                    ISheet excelSheet = workbook.CreateSheet(dataTable.TableName);
                    List<string> columns = new List<string>();
                    IRow row = excelSheet.CreateRow(0);
                    int columnIndex = 0;
                    
                    foreach (System.Data.DataColumn column in dataTable.Columns)
                    {
                        columns.Add(column.ColumnName);
                        row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
                        columnIndex++;
                    }

                    int rowIndex = 1;
                    foreach (DataRow dsrow in dataTable.Rows)
                    {
                        row = excelSheet.CreateRow(rowIndex);
                        int cellIndex = 0;
                        foreach (String col in columns)
                        {
                            row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
                            cellIndex++;
                        }

                        rowIndex++;
                    }
                    workbook.Write(fs);
                }
        }

Finally, you shall see Table is stored as an Excel sheet as below,

In the above code, we are creating each sheet as a table name and its data.

References:

• Read Excel as JSON using NPOI

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.