How C # Exports a DataTable to an Excel solution _c# tutorial

Recently, due to the need to back up all the logs of user operations within the system, and in view of the possible need for restoration later, the final decision was made to back up the log data to Excel.

Here is the entire code for this class in my project, which makes it easy to import data from a DataTable into an Excel method. Excel.cs

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

Copy Code code as follows:

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" > Table Head </param>
Public Excel (DataColumnCollection columns)
{
_columns = columns;
Instanceworkbook ();
}
private void Instanceworkbook ()
{
Cretate Workbook
_workbook = new Hssfworkbook ();
var dsi = propertysetfactory.createdocumentsummaryinformation ();
Dsi.company = "Baiyitimes";
_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 = Verticalalignment.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 ();
}
}
}

Here is a small demo for a total of reference:

Copy Code code as follows:

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 more data into Excel (hundreds of thousands of or millions of rows), all of the time in the DataTable may be a large memory consumption, it is recommended that the data to import the best not more than 1000, you can take a paging query to import data into Excel.

Advantages: In the 1997-2003 version of XLS, each form maximum support only 65536 lines, 2010 can support 1048576 rows, considering the version installed on the client is not the same, so the Excel object each form the maximum support 65536 rows, when the form reaches the maximum number of rows, A new form is automatically created inside an Excel object, which is not considered when writing data to Excel, so it is easier to call