C # NPOI export EXCEL,

C # NPOI export EXCEL,

Dll files need to be introduced

 

 

 

 

 

Using System; using System. collections. generic; using System. linq; using System. text; namespace CSR_Web.Common {public class NPOIExport {public static NPOI. HSSF. userModel. HSSFWorkbook DoExport (System. data. dataTable dt, string notile) {// create a workbook NPOI. HSSF. userModel. HSSFWorkbook book = new NPOI. HSSF. userModel. HSSFWorkbook (); // create table npoi. SS. userModel. ISheet sheet = book. createSheet (notile); // adaptive column width // shee T. autoSizeColumn (1, true); // the header row merges the cells sheet. addMergedRegion (new NPOI. SS. util. cellRangeAddress (0, 0, 0, dt. columns. count-1); NPOI. SS. userModel. IRow firstrow = sheet. createRow (0); NPOI. SS. userModel. ICell firstcell = firstrow. createCell (0); // Table Name Style NPOI. SS. userModel. ICellStyle styleHeader = book. createCellStyle (); NPOI. SS. userModel. IFont fontHeader = book. createFont (); styleHeader. alignment = NPOI. SS. UserModel. horizontalAlignment. center; styleHeader. verticalAlignment = NPOI. SS. userModel. verticalAlignment. center; fontHeader. fontHeightInPoints = 20; styleHeader. setFont (fontHeader); firstcell. cellStyle = styleHeader; firstcell. setCellValue (notile); try {// column name style NPOI. SS. userModel. ICellStyle styleColName = book. createCellStyle (); NPOI. SS. userModel. IFont fontColName = book. createFont (); styleColName. Alignment = NPOI. SS. userModel. horizontalAlignment. center; styleColName. verticalAlignment = NPOI. SS. userModel. verticalAlignment. center; fontColName. fontHeightInPoints = 14; styleColName. setFont (fontColName); // data style and font size NPOI. SS. userModel. ICellStyle styleBody = book. createCellStyle (); NPOI. SS. userModel. IFont fontBody = book. createFont (); styleBody. alignment = NPOI. SS. userModel. horizontalAlignment. C Enter; styleBody. verticalAlignment = NPOI. SS. userModel. verticalAlignment. center; fontBody. fontHeightInPoints = 12; styleBody. setFont (fontBody); // create a specific cell data int rowCount = dt. rows. count; int colCount = dt. columns. count; NPOI. SS. userModel. IRow colNameRow = sheet. createRow (1); for (int x = 0; x <colCount; x ++) {// write the column name to the cell NPOI. SS. userModel. ICell colNameCell = colNameRow. createCell (x); colNameCell. S EtCellValue (dt. columns [x]. columnName); colNameCell. cellStyle = styleColName;} for (int I = 0; I <rowCount; I ++) {NPOI. SS. userModel. IRow row = sheet. createRow (I + 2); // the name of the second row of the table in the first row starting from the third row for (int j = 0; j <colCount; j ++) {// fill in the Data NPOI. SS. userModel. ICell cell = row. createCell (j); if (dt. rows [I] [j]! = Null) {cell. setCellValue (dt. rows [I] [j]. toString ();} else {cell. setCellValue ("");} cell. cellStyle = styleBody; }}// adaptive column width for (int x = 0; x <colCount; x ++) {sheet. autoSizeColumn (x, true);} // The code here is to send the xls file to the page and download it directly to the local place through the browser, which can be placed in the place called on the interface. // System. IO. memoryStream MS = new System. IO. memoryStream (); // book. write (MS); // Response. addHeader ("Content-Disposition", string. format ("attachment; filename .xls"); // Response. binaryWrite (ms. toArray (); // book = null; // ms. close (); // ms. dispose (); return book;} catch {throw new Exception () ;}finally {book = null ;}}}}

/// <Summary> /// export /// </summary> /// <param name = "sender"> </param> /// <param name =" e "> </param> protected void btnExport_Click (object sender, eventArgs e) {DataTable dt = cmbll. getdt (); NPOI. HSSF. userModel. HSSFWorkbook book = NPOIExport. doExport (dt, "xxx Report"); // write the client try {WriteClient (book);} catch {} finally {book = null;} public void WriteClient (NPOI. HSSF. userModel. HSSFWorkbook) {System. IO. memoryStream MS = new System. IO. memoryStream (); book. write (MS); Response. addHeader ("Content-Disposition", string. format ("attachment; filename = Customer Profile" + DateTime. now. toString ("yyyyMMddHHmmss") + ". xls "); Response. binaryWrite (ms. toArray (); book = null; ms. close (); ms. dispose ();}