C # Use NPOI to quickly export data to excel,

C # Use NPOI to quickly export data to excel,

In the previous blog article "C # Quick export to excel", this article cannot be imported into the .xlsx format. To solve this problem, we will use NPOI in this article.

Reference: https://www.cnblogs.com/lazyneal/p/6148912.html

1. Add a package.

Right-click the project name.

Select Manage NuGet packages, search for NPOI in the browser, and install it.

2. code reference.

using System.IO;using System.Data.SqlClient;using System.Diagnostics;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;

3. Excel export method:

Public void ExportDataToExcel (DataTable TableName, string FileName) {SaveFileDialog saveFileDialog = new SaveFileDialog (); // set the file title saveFileDialog. title = "Export an Excel file"; // set the file type saveFileDialog. filter = "Excel Workbook (*. xlsx) | *. xlsx | workbook in Excel 97-2003 (*. xls) | *. xls "; // set the display sequence of the default file type saveFileDialog. filterIndex = 1; // whether to automatically add the saveFileDialog extension to the file name. addExtension = true; // whether to remember the last opened directory saveFileDialog. restoreDire Ctory = true; // set the default file name saveFileDialog. fileName = FileName; // press the OK button if (saveFileDialog. showDialog () = DialogResult. OK) {// obtain the file path string localFilePath = saveFileDialog. fileName. toString (); // data initialization int TotalCount; // total number of rows int RowRead = 0; // read number of rows int Percent = 0; // percentage TotalCount = TableName. rows. count; lblStatus. text = "Total" + TotalCount + "data entries"; lblStatus. visible = true; barStatus. visible = true; // NPOI IWorkbook workbook; string FileExt = Path. getExtension (localFilePath ). toLower (); if (FileExt = ". xlsx ") {workbook = new XSSFWorkbook ();} else if (FileExt = ". xls ") {workbook = new HSSFWorkbook ();} else {workbook = null;} if (workbook = null) {return;} ISheet sheet = string. isNullOrEmpty (FileName )? Workbook. createSheet ("Sheet1"): workbook. createSheet (FileName); // seconds Stopwatch timer = new Stopwatch (); timer. start (); try {// read the title IRow rowHeader = sheet. createRow (0); for (int I = 0; I <TableName. columns. count; I ++) {ICell cell = rowHeader. createCell (I); cell. setCellValue (TableName. columns [I]. columnName);} // read data for (int I = 0; I <TableName. rows. count; I ++) {IRow rowData = sheet. createRow (I + 1); for (int j = 0; j <TableName. columns. count; j ++) {ICell cell = rowData. createCell (j); cell. setCellValue (TableName. rows [I] [j]. toString ();} // the status bar displays RowRead ++; Percent = (int) (100 * RowRead/TotalCount); barStatus. maximum = TotalCount; barStatus. value = RowRead; lblStatus. text = "Total" + TotalCount + "data records, read" + Percent. data of ToString () + "%. "; Application. doEvents ();} // change lblStatus in the status bar. text = "generating Excel... "; Application. doEvents (); // convert to the byte array MemoryStream = new MemoryStream (); workbook. write (stream); var buf = stream. toArray (); // save as an Excel file using (FileStream fs = new FileStream (localFilePath, FileMode. create, FileAccess. write) {fs. write (buf, 0, buf. length); fs. flush (); fs. close ();} // change lblStatus in the status bar. text = "Excel generated successfully, time consumed" + t Milliseconds. ElapsedMilliseconds + "milliseconds. "; Application. doEvents (); // close timer in seconds. reset (); timer. stop (); // if (MessageBox. show ("exported successfully. open now? "," Prompt ", MessageBoxButtons. yesNo, MessageBoxIcon. information) = DialogResult. yes) {System. diagnostics. process. start (localFilePath);} // assign the initial value lblStatus. visible = false; barStatus. visible = false;} catch (Exception ex) {MessageBox. show (ex. message, "prompt", MessageBoxButtons. OK, MessageBoxIcon. information);} finally {// close timer in seconds. reset (); timer. stop (); // assign the initial value lblStatus. visible = false; barStatus. visible = false ;}}}

4. Result demonstration: