Using Npoi to generate a DataTable for Excel

Heard that the NPOI 2.0 version supports the EXCEL2007 format, expressing the expectation of its performance. But it is still using 1.2.5 to stabilize the focus.

There are too many lists in life that require an export function, and of course the life here refers to the programmer's life. A DataTable is a data structure that is commonly used after reading data from a database, and of course the DataTable here refers to. NET development. Today just did a function to export Excel, and then after the completion of the function to pull out the method to make a class, for later use. The entire class is as follows:

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingNpoi. Ss. Usermodel;usingNpoi. HSSF. Usermodel;usingSystem.Data;usingSystem.IO;namespaceexcel{ Public   classExcelhelper {/// <summary>        ///class version/// </summary>         Public stringVersion {Get{return "0.1"; } }        ReadOnly intExcel03_maxrow =65535; /// <summary>        ///converts a DataTable to the excel2003 format. /// </summary>        /// <param name= "DT" ></param>        /// <returns></returns>         Public byte[] Datatable2excel (DataTable DT,stringsheetname) {Iworkbook Book=NewHssfworkbook (); if(dt. Rows.Count <Excel03_maxrow) Datawrite2sheet (DT,0Dt. Rows.Count-1, book, sheetname); Else            {                intpage = dt. Rows.Count/Excel03_maxrow;  for(inti =0; I < page; i++)                {                    intstart = i *Excel03_maxrow; intEnd = (I * excel03_maxrow) + Excel03_maxrow-1; Datawrite2sheet (DT, start, end, book, SheetName+i.tostring ()); }                intLastpageitemcount = dt. Rows.Count%Excel03_maxrow; Datawrite2sheet (DT, dt. Rows.Count-Lastpageitemcount, Lastpageitemcount, book, SheetName +page.            ToString ()); } MemoryStream Ms=NewMemoryStream (); Book.            Write (MS); returnMs.        ToArray (); }        Private voidDatawrite2sheet (DataTable DT,intStartRow,intEndrow, Iworkbook Book,stringsheetname) {Isheet sheet=Book .            Createsheet (SheetName); IRow Header= Sheet. CreateRow (0);  for(inti =0; i < dt. Columns.count; i++) {Icell cell=header.                Createcell (i); stringval = dt. Columns[i]. Caption??dt. Columns[i].                ColumnName; Cell.            Setcellvalue (Val); }            intRowIndex =1;  for(inti = StartRow; I <= Endrow; i++) {DataRow Dtrow=dt.                Rows[i]; IRow Excelrow= Sheet. CreateRow (rowindex++);  for(intj =0; J < DtRow.ItemArray.Length; J + +) {Excelrow.createcell (j). Setcellvalue (Dtrow[j].                ToString ()); }            }         }    }}

This class implements a binary format that converts a DataTable into excel2003, and if the number of rows in a DataTable exceeds the maximum number of rows for excel2003, it is divided into multiple sheet tables.

The following examples are used:

 Public voidTest () {DataTable dt=NewDataTable (); Datacolumn[] cols=Newdatacolumn[] {NewDataColumn ("name",typeof(string)),                NewDataColumn ("Birthday",typeof(DateTime)),NewDataColumn ("score",typeof(int))            }; Dt.            Columns.addrange (cols); Random Rnd=NewRandom ();  for(inti =0; I <Ten; i++) {DataRow row=dt.                NewRow (); Object[] items =New Object[] {                     "Xiao Ming", DateTime.Now, Rnd. Next ( -)                }; Row. ItemArray=items; Dt.            Rows.Add (row); } myexcellib.excelhelper Myhelper=NewMyexcellib.excelhelper (); byte[] data = Myhelper. Datatable2excel (DT,"Staff Directory"); stringPath ="d:\\temp"+ DateTime.Now.Ticks.ToString () +". xls"; if(!file.exists (path)) {FileStream FS=NewFileStream (path, filemode.createnew); Fs. Write (data,0, data.                Length); Fs.             Close (); }         }

Read the code, and then listen to my explanation:

The entire Excelhelper class provides only a common function datatable2excel, and the parameters are simple enough to pass the DataTable and a sheet table name that will be generated. However, the first row of the Excel table we exported is usually the column header name, for example, the column name in the data is "name", but we need to display in Excel is "name", so here the DataTable also need a little modification, such as dt.columns["name"]. caption= "Name". This will show the first line in the generated Excel with the Name column.

Using Npoi to generate a DataTable for Excel