In C#, the operation of using the NPOI library to import table data into Excel is relatively direct. NPOI is a .NET version of POI that can help us read and write Office files such as xls and xlsx without Microsoft Office installed. Here are the basic steps to export DataTable to Excel using NPOI:
Create a workbook: First, you need to create an IWorkbook object, which is the beginning of the Excel file.
Create a worksheet: Create a new worksheet using ("Sheet1").
Create a table header: Create a table header of an Excel file by traversing the columns of the DataTable.
Fill data: Iterate through each row of the DataTable and fill in data for each cell.
Write to file: Finally, write the workbook to the file stream and save it as an Excel file.
Here is a simple example code showing how to export DataTable to Excel file:
using ; // Used to process xlsx filesusing ; // Used to process xls filesusing ;
using ;
public void ExportToExcel(DataTable dt, string filePath)
{
IWorkbook workbook;
// Select the workbook type according to the file extension if ((filePath).Equals(".xlsx", ))
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
ISheet sheet = ("Sheet1");
// Create a header IRow headerRow = (0);
for (int i = 0; i < ; i++)
{
(i).SetCellValue([i].ColumnName);
}
// Fill in data for (int i = 0; i < ; i++)
{
IRow row = (i + 1);
for (int j = 0; j < ; j++)
{
(j).SetCellValue([i][j].ToString());
}
}
// Write to the file using (FileStream fileStream = new FileStream(filePath, , ))
{
(fileStream);
}
}In the above code, we first check the file extension to decide on creatingXSSFWorkbook(For.xlsxFile) orHSSFWorkbook(For.xlsdocument). We then create a worksheet and populate the header and data. Finally, we write the workbook to the specified file path.
The first step in practice
private void BtnDerive_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
= "Excel files (*.xlsx)|*.xlsx";
= "Select the location to save the export file";
= GetFileName();
if (() == )
{
Export(dataGridView1, );
}
}
private string GetFileName()
{
return $"Product Type List_{:yyyyMMddHHmmss}.xlsx";
}
private void Export(DataGridView dgv, string fileName)
{
try
{
IWorkbook wb = new XSSFWorkbook();
//NPOI uses the HSSFWorkbook class to handle xls, and the XSSFWorkbook class to handle xlsx. //They all inherit the interface IWorkbook, and use IWorkbook to uniformly process files in xls and xlsx formats. ISheet sheet = ();
IRow row;
//Get the column name of the DataGridView, where i represents a low-level column, i starts from 0 row = (0);
// Generate the title, note that the first column is the "Operation" column, and it will not be processed for (int k = 1; k < ; k++)
{
(k - 1).SetCellValue([k].());
}
for (int i = 0; i < ; i++)
{
row = (i + 1);
for (int k = 1; k < ; k++)
{
string value = [i].Cells[k].Value == null ? "" : [i].Cells[k].();
(k - 1).SetCellValue(value);
}
}
using (var fs = new FileStream(fileName, , ))
{
(fs); //Write to Excel }
("Save successfully, file name:" + fileName, "hint", , );
}
catch (Exception ex)
{
(, "mistake", , );
}
}c# Write DataTable to DataTable using NPOI
Method 1
using System;
using ;
using ;
using ;
public class ExcelWriter
{
public static void WriteDataTableToExcel(DataTable dataTable, string filePath)
{
// Create a new workbook IWorkbook workbook = new XSSFWorkbook();
// Create a worksheet ISheet sheet = ("Sheet1");
// Create a title line IRow headerRow = (0);
for (int i = 0; i < ; i++)
{
(i).SetCellValue([i].ColumnName);
}
// Fill in the data rows int rowIndex = 1;
foreach (DataRow row in )
{
IRow dataRow = (rowIndex++);
for (int i = 0; i < ; i++)
{
DataColumn column = [i];
ICell cell = (i);
switch (())
{
case "string":
(row[i].ToString());
break;
case "int32":
case "int64":
(Convert.ToInt32(row[i]));
break;
case "double":
((row[i]));
break;
case "datetime":
((row[i]));
break;
case "boolean":
((row[i]));
break;
default:
(row[i].ToString());
break;
}
}
}
// Write the workbook to a file using (var fs = new FileStream(filePath, , ))
{
(fs);
}
}
}Method 2
public static void CreateSheet(IWorkbook workbook, DataTable dt, int num)
{
ISheet sheet = () ? ("Sheet" + num) : ();
ICellStyle style = ();//Set the table style = ;//The lower border line (After adding background color to the table, the original border will be covered, and setting the border style can solve this problem) = ; //Left border line = ;//Right border line = ;//The upper border line
= 9;//A4 paper printing = true;//Horizontal printing (default is false, that is, vertical)
//Set automatically adjust to one page width = false;// Must be set = true; //One page wide = 1;
= ;
//Table header IRow row = (0);
for (int i = 0; i < ; i++)
{
ICell cell = (i);
([i].ColumnName);
= style;
}
//data for (int i = 0; i < ; i++)
{
IRow row1 = (i + 1);
for (int j = 0; j < ; j++)
{
ICell cell = (j);
= style;
if ([j].(typeof(bool)) && ([i][j].ToString(), out bool b))
{
(b);
}
else if (([i][j].ToString(), out double d))//Number format {
(d);
}
else
{
([i][j].ToString());
}
}
}
for (int columnIndex = 0; columnIndex < ; columnIndex++)
{
(columnIndex); //Column width adaptation }
}This is the end of this article about C# using NPOI to import data from tables into Excel. For more related content on importing C# NPOI data into Excel, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!