Use NPOI to generate the DataTable Excel and npoidatatable

Use NPOI to generate the DataTable Excel and npoidatatable

I heard that npoi 2.0 supports the excel2007 format, and I look forward to its performance. However, we are still using 1.2.5.

There are too many lists in life that require an export function. Of course, life here refers to the life of programmers. DataTable is a commonly used data structure after reading data from the database. Of course, the DataTable here refers to the data structure developed by. Net. Today, I just made an excel export function, so after completing the function, I pulled the method out and made it into a class for later use. The entire class is as follows:

Using System; using System. collections. generic; using System. linq; using System. text; using NPOI. SS. userModel; using NPOI. HSSF. userModel; using System. data; using System. IO; namespace Excel {public class ExcelHelper {// <summary> // class version /// </summary> public string version {get {return "0.1 ";}} readonly int EXCEL03_MaxRow = 65535; // <summary> // convert the data table to the excel2003 format. /// </Summary> /// <param name = "dt"> </param> /// <returns> </returns> public byte [] DataTable2Excel (DataTable dt, string sheetName) {IWorkbook book = new HSSFWorkbook (); if (dt. rows. count <EXCEL03_MaxRow) DataWrite2Sheet (dt, 0, dt. rows. count-1, book, sheetName); else {int page = dt. rows. count/EXCEL03_MaxRow; for (int I = 0; I <page; I ++) {int start = I * EXCEL03_MaxRow; int end = (I * EXCE L03_MaxRow) + EXCEL03_MaxRow-1; DataWrite2Sheet (dt, start, end, book, sheetName + I. toString ();} int lastPageItemCount = dt. rows. count % EXCEL03_MaxRow; DataWrite2Sheet (dt, dt. rows. count-lastPageItemCount, lastPageItemCount, book, sheetName + page. toString ();} MemoryStream MS = new MemoryStream (); book. write (ms); return ms. toArray ();} private void DataWrite2Sheet (DataTable dt, int startRo W, int endRow, IWorkbook book, string sheetName) {ISheet sheet = book. createSheet (sheetName); IRow header = sheet. createRow (0); for (int I = 0; I <dt. columns. count; I ++) {ICell cell = header. createCell (I); string val = dt. columns [I]. caption ?? Dt. columns [I]. columnName; cell. setCellValue (val);} int rowIndex = 1; for (int I = startRow; I <= endRow; I ++) {DataRow dtRow = dt. rows [I]; IRow excelRow = sheet. createRow (rowIndex ++); for (int j = 0; j <dtRow. itemArray. length; j ++) {excelRow. createCell (j ). setCellValue (dtRow [j]. toString ());}}}}}

This class converts DataTable to the binary format of excel2003. If the number of rows of the DataTable exceeds the maximum number of rows of excel2003, it is also divided into multiple sheet tables.

The usage example is as follows:

Public void test () {DataTable dt = new DataTable (); DataColumn [] cols = new DataColumn [] {new DataColumn ("name", typeof (string )), new DataColumn ("birthday", typeof (DateTime), new DataColumn ("score", typeof (int)}; dt. columns. addRange (cols); Random rnd = new Random (); for (int I = 0; I <10; I ++) {DataRow row = dt. newRow (); object [] items = new object [] {"James", DateTime. now, rnd. next (100)}; ro W. itemArray = items; dt. rows. add (row);} myExcelLib. excelHelper myhelper = new myExcelLib. excelHelper (); byte [] data = myhelper. dataTable2Excel (dt, ""); string path = "d: \ temp" + DateTime. now. ticks. toString () + ". xls "; if (! File. exists (path) {FileStream fs = new FileStream (path, FileMode. createNew); fs. write (data, 0, data. length); fs. close ();}}

After reading the code, let me explain the following:

The entire ExcelHelper class only provides a public function ableable2excel. The parameter is also very simple, so you can pass the DataTable and a generated sheet table name. However, the first row of the exported excel table is usually the column header name. For example, the column name in the data is "name", but we need to display it in excel as "name ", so the DataTable here needs to be slightly modified, such as dt. columns ["name"]. caption = "name ". This will display the column header "name" in the first line of the generated excel file.