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

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.

3 Comments

Join the conversation! Your thoughts help the community grow.

Hemant Sharma7y ago
Please suggest the coding to get the activeCell no. from Excel (.xls) with NPOI DLL in C#.
Mallela Vlogger7y ago
Getting "System.ObjectDisposedException: 'Cannot access a closed Stream.'" exception. ASAP need help please
Viknaraj Manogararajah8y ago
Nice Article, Thank you for sharing