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.
  1. Install-Package NPOI -Version 2.3.0
  3. 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.
  1. using System.Data;
  2. using NPOI.HSSF.UserModel;
  3. using Newtonsoft.Json;
  4. 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.
  1. DataTable dt1 = new DataTable();
  3. dt1.Columns.Add("ID");
  4. dt1.Columns.Add("Name");
  6. DataRow dr = dt.NewRow();
  7. dr["ID"] = "1";
  8. dr["Name"] = "Test";
  10. 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.
  1. public class SummaryClass
  2. {
  3. public string ID { get; set; }
  4. public string Name { get; set; }
  5. }
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.
  1. public void GenerateExcelFile()
  2. {
  3. // Below code is create datatable and add one row into datatable.
  4. DataTable dt = new DataTable();
  6. dt.Columns.Add("ID");
  7. dt.Columns.Add("Name");
  9. DataRow dr = dt.NewRow();
  10. dr["ID"] = "1";
  11. dr["Name"] = "Test";
  13. dt.Rows.Add(dr);
  15. // Declare HSSFWorkbook object for create sheet
  16. var workbook = new HSSFWorkbook();
  17. var sheet = workbook.CreateSheet("NameOfYourSheet");
  19. // Convert datatable into json
  20. string JSON = JsonConvert.SerializeObject(dt);
  22. // Convert json into SummaryClass class list
  23. var items = JsonConvert.DeserializeObject<List<SummaryClass>>(JSON);
  25. // Set column name this column name use for fetch data from list
  26. var columns = new[] { "ID", "Name" };
  28. // Set header name this header use for set name in excel first row
  29. var headers = new[] { "ID", "Name" };
  31. var headerRow = sheet.CreateRow(0);
  33. //Below loop is create header
  34. for (int i = 0; i < columns.Length; i++)
  35. {
  36. var cell = headerRow.CreateCell(i);
  37. cell.SetCellValue(headers[i]);
  38. }
  40. //Below loop is fill content
  41. for (int i = 0; i < items.Count; i++)
  42. {
  43. var rowIndex = i + 1;
  44. var row = sheet.CreateRow(rowIndex);
  46. for (int j = 0; j < columns.Length; j++)
  47. {
  48. var cell = row.CreateCell(j);
  49. var o = items[i];
  50. cell.SetCellValue(o.GetType().GetProperty(columns[j]).GetValue(o, null).ToString());
  51. }
  52. }
  54. // Declare one MemoryStream variable for write file in stream
  55. var stream = new MemoryStream();
  56. workbook.Write(stream);
  58. string FilePath = "SetYourFileSavePath - With File Name"
  60. //Write to file using file stream
  61. FileStream file = new FileStream(FilePath, FileMode.CreateNew, FileAccess.Write);
  62. stream.WriteTo(file);
  63. file.Close();
  64. stream.Close();
  65. }
Summary

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