Convert C# Datatable To Excel File Using NPOI DLL C#

Description: In this post, we will learn about how to export or convert DataTable data into excel file using NPOI in c#. First, we need to install these packages like NPOI, Newtonsoft for install these packages Nuget link added in this post. After those packages install we need to add some namespace for access NPOI classes and Newtonsoft for convert JSON to List. After all packages and namespaces added than create one class for convert JSON to List and then set the name of columns and set one for loop for get and set data into excel cell. See below complete step by step source code for generating excel file. Let's start code.

In this post, we will learn about how to export or convert DataTable data into Excel files using NPOI in C#. First, we need to install these packages like NPOI and Newtonsoft. For installing these packages a Nuget link has been added in this post.

 

After these packages are installed, we need to add some namespace for accessing NPOI classes and Newtonsoft for converting JSON to List. After all packages and namespaces are added, then create one class for converting JSON to List and then set the name of columns and set one for loop for getting and setting data into Excel cell. See the below complete step by step source code to generate the Excel file. Let's start the code.

 

The first step is to install the below NuGet packages in your project.

Install-Package NPOI -Version 2.3.0

Install-Package Newtonsoft.Json -Version 11.0.2

After installing the above packages, add the below namespaces for accessing the class and methods of Excel generated and save in specific path.

using System.Data;
using NPOI.HSSF.UserModel;
using Newtonsoft.Json;
using System.IO;

The second step is getting data from database and storing it in the table. For this example, here, generate static Datatable and add 1 record for testing. See the below code for generated datatable.

DataTable dt1 = new DataTable();

dt1.Columns.Add("ID");
dt1.Columns.Add("Name");

DataRow dr = dt.NewRow();
dr["ID"] = "1";
dr["Name"] = "Test";

dt.Rows.Add(dr);

The next step is to create a class for converting a data table into the list using Newtonsoft. See the below class code for converting the datatable.

public class SummaryClass
{
   public string ID { get; set; }
   public string Name { get; set; }
}

Now, create one function for generating the Excel file from datatable. See the below function code for converting the datatable into a list and converting a list into an Excel file. 

public void GenerateExcelFile()
{
    // Below code is create datatable and add one row into datatable.
    DataTable dt = new DataTable();

    dt.Columns.Add("ID");
    dt.Columns.Add("Name");

    DataRow dr = dt.NewRow();
    dr["ID"] = "1";
    dr["Name"] = "Test";

    dt.Rows.Add(dr);

    // Declare HSSFWorkbook object for create sheet
    var workbook = new HSSFWorkbook();
    var sheet = workbook.CreateSheet("NameOfYourSheet");

    // Convert datatable into json
    string JSON = JsonConvert.SerializeObject(dt);

    // Convert json into SummaryClass class list
    var items = JsonConvert.DeserializeObject<List<SummaryClass>>(JSON);

    // Set column name this column name use for fetch data from list
    var columns = new[] { "ID", "Name" };

    // Set header name this header use for set name in excel first row
    var headers = new[] { "ID", "Name" };

    var headerRow = sheet.CreateRow(0);

    //Below loop is create header
    for (int i = 0; i < columns.Length; i++)
    {
        var cell = headerRow.CreateCell(i);
        cell.SetCellValue(headers[i]);
    }

    //Below loop is fill content
    for (int i = 0; i < items.Count; i++)
    {
        var rowIndex = i + 1;
        var row = sheet.CreateRow(rowIndex);

        for (int j = 0; j < columns.Length; j++)
        {
            var cell = row.CreateCell(j);
            var o = items[i];
            cell.SetCellValue(o.GetType().GetProperty(columns[j]).GetValue(o, null).ToString());
        }
    }

    // Declare one MemoryStream variable for write file in stream
    var stream = new MemoryStream();
    workbook.Write(stream);

    string FilePath = "SetYourFileSavePath - With File Name"

    //Write to file using file stream
    FileStream file = new FileStream(FilePath, FileMode.CreateNew, FileAccess.Write);
    stream.WriteTo(file);
    file.Close();
    stream.Close();
}

Summary

The above code has generated the Excel file and saved it in a specific path.