C # import DataTable to Excel

Recently, due to the need to store and back up all the logs operated by users in the system in the company project, we decided to back up the log data to Excel, considering that we may need to restore the logs in the future.

The following is all the code of the Excel. cs class in my project. This class can easily import the data in the DataTable into the Excel method.

First, you must download the NPOI. dll assembly,
The class code is as follows:

[Csharp]
Using System;
 
Using NPOI. HSSF;
Using NPOI. HPSF;
Using NPOI. HSSF. UserModel;
Using NPOI. HSSF. Util;
Using NPOI. SS. UserModel;
Using System. Collections;
Using System. IO;
Using System. Data;
 
Namespace BackupAttach
{
 
Public class Excel
{
Private HSSFWorkbook _ workBook;
Private ISheet _ wbSheet = null;
Private DataColumnCollection _ columns = null;
 
 
Private int _ col = 0; // total columns
Private int _ row = 0; // total rows
Private int _ sheet = 0; // total sheets
Private int _ sheetRowNum = 65536; // each sheet allow rows
 
Public Excel ()
{
InstanceWorkBook ();
}
 
/// <Summary>
/// Instance method
/// </Summary>
/// <Param name = "sheetRowNum"> maximum number of rows allowed by a single form </param>
Public Excel (int sheetRowNum)
{
_ SheetRowNum = sheetRowNum;
InstanceWorkBook ();
}
 
/// <Summary>
/// Instance method
/// </Summary>
/// <Param name = "columns"> header </param>
Public Excel (DataColumnCollection columns)
{
_ Columns = columns;
InstanceWorkBook ();
}
 
Private void InstanceWorkBook ()
{
//// Cretate WorkBook
_ WorkBook = new HSSFWorkbook ();
 
Var dsi = PropertySetFactory. CreateDocumentSummaryInformation ();
Dsi. Company = "yyitimes ";
_ WorkBook. DocumentSummaryInformation = dsi;
 
/// Create a entry of SummaryInformation
Var si = PropertySetFactory. CreateSummaryInformation ();
Si. Subject = "Etimes Secure Document System Log Backup ";
_ WorkBook. SummaryInformation = si;
}
 
Private DataColumnCollection GetColumns (DataColumnCollection columns)
{
Return columns = null | columns. Count = 0? _ Columns: columns;
}
 
Private ISheet GetSheet (ISheet sheet)
{
Return sheet = null? _ WbSheet: sheet;
}
 
Private void CreateHeader (ISheet sheet, DataColumnCollection columns)
{
_ Columns = GetColumns (columns );
 
//// Create row of column
Var oRow = sheet. CreateRow (0 );
 
Foreach (DataColumn column in _ columns)
{
Var oCell = oRow. CreateCell (_ col );
 
Var style1 = _ workBook. CreateCellStyle ();
Style1.FillForegroundColor = HSSFColor. BLUE. index2;
Style1.FillPattern = FillPatternType. SOLID_FOREGROUND;
Style1.Alignment = HorizontalAlignment. CENTER;
Style1.VerticalAlignment = verticalignment. CENTER;
 
Var font = _ workBook. CreateFont ();
Font. Color = HSSFColor. WHITE. index;
Style1.SetFont (font );
 
OCell. CellStyle = style1;
Var name = column. ColumnName;
OCell. SetCellValue (name. ToString ());
 
_ Col ++;
}
//// Header belong to rows
_ Row ++;
}
 
Private void CreateHeader (ISheet sheet)
{
CreateHeader (sheet, null );
}
 
Public ISheet CreateSheet ()
{
Return CreateSheet (null );
}
 
Public ISheet CreateSheet (DataColumnCollection columns)
{
_ WbSheet = _ workBook. CreateSheet (_ sheet + 1). ToString ());
CreateHeader (_ wbSheet, columns );
_ Sheet ++;
Return _ wbSheet;
}
 
Public void SetRowValue (DataRowCollection rows, ISheet sheet)
{
_ WbSheet = GetSheet (sheet );
Foreach (DataRow row in rows)
{
SetRowValue (row );
}
}
 
Public void SetRowValue (DataRowCollection rows)
{
SetRowValue (rows, null );
}
 
Public void SetRowValue (DataRow row)
{
// Create a new sheet
If (_ row % _ sheetRowNum = 0)
{
CreateSheet ();
}
Var oRow = _ wbSheet. CreateRow (_ row % _ sheetRowNum );
Var obj = string. Empty;
Var cell = 0;
Foreach (DataColumn column in _ columns)
{
Obj = row [column. ColumnName]. ToString ();
ORow. CreateCell (cell). SetCellValue (obj );
Cell ++;
}
_ Row ++;
}
 
Public void SetProtectPassword (string password, string username)
{
_ WorkBook. WriteProtectWorkbook (password, username );
}
 
Public void SaveAs (string filePath)
{
If (File. Exists (filePath) File. Delete (filePath );
Var file = new FileStream (filePath, FileMode. Create );
_ WorkBook. Write (file );
File. Close ();
}
}
}

The following is a reference for the Demo:
[Csharp]
Public void DataTableToExcel (DataTable dt, string path)
{
// Instance excel object
// Excel excel = new Excel (65536 );
Excel excel = new Excel ();
 
// Create a sheet
Excel. CreateSheet (dt. Columns );
 
// Write value into rows
// Excel. SetRowValue (dt. Rows );
Foreach (DataRow row in dt. Rows)
{
Excel. SetRowValue (row );
}
 
// Set excel protected
Excel. SetProtectPassword ("etimes2011 @", "baiyi ");
 
// Save excel file to local
Excel. SaveAs (path );
}

Disadvantage: If you want to import a large amount of data (hundreds of thousands or millions of rows) to an Excel file, it may consume a lot of memory to put all the data into the able at one time, we recommend that you import up to 1000 entries of data each time. You can use the paging query method to import the data to Excel.
Advantage: in xls of version 1997-2003, each form supports a maximum of 65536 rows and 2010 rows. Considering that the versions installed on the client are different, each form of an Excel Object supports a maximum of 1048576 rows, when the maximum number of rows of a form is reached, a new form is automatically created in the Excel object. You do not need to consider this when writing data to the Excel file. This makes it easier to call the form.