How C # Exports a DataTable to an Excel solution

Recently, since all logs for user operations in the system need to be backed up in a company project, the final decision is to back up the log data to Excel, considering that it may need to be restored later.

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

First, you must download the NPOI.dll assembly,
The class code is 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 Data Columncollection _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>//////</summary>//<p Aram Name= "Sheetrownum" > The maximum number of rows allowed for a single form </param> public Excel (int sheetrownum) {_sheetrownum = Sheetrownum; Instanceworkbook (); }///<summary>//////</summary>//<param name= "Columns" > Header </param> public Excel (Datacol Umncollection 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 shee T, 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 (); sty Le1. 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; The 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), and public void Setrowvalue (DataRow row) {//CRE Ate 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 (Filepa th, FileMode.Create); _workbook.write (file); File. Close (); } } }

The following is a common reference for the small demo:

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

Pros: The 1997-2003 version of the XLS has a maximum of 65536 rows per form, 2010 can support 1048576 rows, considering that the version installed on the client computer is not the same, so the Excel object supports 65536 rows per form, and when the form reaches the maximum number of rows, A new form is automatically created inside an Excel object, and you don't have to think about it when you write data to Excel, which makes it easier to call