Comparison between NPOI export Excel and office-based export Excel

First, let's introduce NPOI.

NPOI is the. NET version of the POI project. POI is an open-source Java project for reading and writing Microsoft OLE2 component documents such as Excel and WORD. With NPOI, you can read and write WORD/EXCEL documents on a machine without an Office or corresponding environment. NPOIIs built on POI 3.xVersion above, it can be installed OfficeIn the case Word/ExcelDocumentation for read/write operations. NPOI advantages: (1) traditional operations ExcelProblems: 1. If yes . NET, Must be installed on the server OfficeAnd update it in time to prevent vulnerabilities. You also need to set permissions to allow . NETAccess COM +If a problem occurs during the export process, the server may be down. 2, ExcelConverts the types of columns containing only numbers. They are originally text-type columns, ExcelIt will be converted to numeric type, for example, the number 000123 will be changed to 123. 3. During export, if the field content starts with "-" or "=", Excel regards it as a formula and reports an error. 4, ExcelAccording ExcelAnalysis Data Type of the first eight rows of the file. If a column in the first eight rows is only a number, it will consider the column as a numeric type and automatically convert the column to a type similar to 1.42702 E+ In the 17 format, the date column is changed to include date and number. (2) Use NPOI1. You do not need to install Microsoft's OfficeTo avoid copyright issues. 2. usage ratio Office PIAOf APIMore convenient and user-friendly. 3. You don't have to make great effort to maintain NPOI, NPOI TeamWill be updated and improved continuously NPOI, Absolutely saving costs. Export data from office to Excel:

Public static void ExportExcel (string fileName, DataGridView myDGV, bool isShowDialog) {string saveFileName = ""; if (isShowDialog) {// bool fileSaved = false; saveFileDialog saveDialog = new SaveFileDialog (); saveDialog. defaultExt = "xls"; saveDialog. filter = "Excel file | *. xls "; saveDialog. fileName = fileName; saveDialog. showDialog (); saveFileName = saveDialog. fileName; if (saveFileName. indexOf (":") <0) return; // canceled} else {// saveFileName = Application. startupPath + @ "\ export record \" + fileName + ". xls "; saveFileName = fileName;} Microsoft. office. interop. excel. application xlApp = new Microsoft. office. interop. excel. application (); if (xlApp = null) {MessageBox. show ("an Excel object cannot be created, or Excel is not installed on your computer"); return;} Microsoft. office. interop. excel. workbooks workbooks = xlApp. workbooks; Microsoft. office. in Terop. excel. workbook workbook = workbooks. add (Microsoft. office. interop. excel. xlWBATemplate. xlWBATWorksheet); Microsoft. office. interop. excel. worksheet worksheet = (Microsoft. office. interop. excel. worksheet) workbook. worksheets [1]; // get sheet1 // write the title for (int I = 0; I <myDGV. columnCount; I ++) {worksheet. cells [1, I + 1] = myDGV. columns [I]. headerText;} // write value for (int r = 0; r <myDGV. rows. count; r ++) {For (int I = 0; I <myDGV. columnCount; I ++) {if (myDGV [I, r]. valueType = typeof (string) | myDGV [I, r]. valueType = typeof (DateTime) // verify the type in the DataGridView cell. If it is of the string or DataTime type, add "" Before the content when saving the data ""; {worksheet. cells [r + 2, I + 1] = "'" + myDGV. rows [r]. cells [I]. value;} else {worksheet. cells [r + 2, I + 1] = myDGV. rows [r]. cells [I]. value ;}} System. windows. forms. application. doEvents ();} w Orksheet. Columns. EntireColumn. AutoFit (); // adaptive column width // if (Microsoft. Office. Interop. cmbxType. Text! = "Notification") // {// Excel. range rg = worksheet. get_Range (worksheet. cells [2, 2], worksheet. cells [ds. tables [0]. rows. count + 1, 2]); // rg. numberFormat = "00000000"; //} if (saveFileName! = "") {Try {workbook. saved = true; workbook. saveCopyAs (saveFileName); // fileSaved = true;} catch (Exception ex) {// fileSaved = false; MessageBox. show ("An error occurred while exporting the file. The file may be opened! \ N "+ ex. message) ;}// else // {// fileSaved = false; //} xlApp. quit (); GC. collect (); // forcibly destroy // if (fileSaved & System. IO. file. exists (saveFileName) System. diagnostics. process. start (saveFileName); // open the EXCEL MessageBox. show (fileName + "saved successfully", "prompt", MessageBoxButtons. OK );}

How to export NPOI to an Excel table:

/// <Summary> /// NPOI: Export Excel. It does not depend on whether Excel is installed locally, fast export speed // </summary> /// <param name = "dataGridView1"> control of the dataGridView to be exported </param> /// <param name = "sheetName"> sheet table name </param> public static void ExportToExcel, string sheetName) {SaveFileDialog fileDialog = new SaveFileDialog (); fileDialog. filter = "Excel | *. xls "; if (fileDialog. showDialog () = System. windows. forms. dialogResult. C Ancel) {return;} // The dataGridView is not allowed to display the Add row. During export, the last row is reported with an error not instantiated. dataGridView1.AllowUserToAddRows = false; HSSFWorkbook workbook = new HSSFWorkbook (); ISheet sheet = workbook. createSheet (sheetName); IRow rowHead = sheet. createRow (0); // enter the header for (int I = 0; I <dataGridView1.Columns. count; I ++) {rowHead. createCell (I, CellType. STRING ). setCellValue (maid [I]. headerText. toString ();} // enter For (int I = 0; I <dataGridView1.Rows. count; I ++) {IRow row = sheet. createRow (I + 1); for (int j = 0; j <dataGridView1.Columns. count; j ++) {row. createCell (j, CellType. STRING ). setCellValue (maid [I]. cells [j]. value. toString () ;}} using (FileStream stream = File. openWrite (fileDialog. fileName) {workbook. write (stream); stream. close ();} MessageBox. show ("data exported successfully! "," Prompt ", MessageBoxButtons. OK, MessageBoxIcon. Information); GC. Collect ();}

After comparison: If you export 50000 int-type data, it takes about 10 minutes to export data based on office, and about 3 seconds to export NPOI data.

 

The NPOI export time is as follows:

 

 

The export time based on office is as follows:

 

Haha, we can see the speed of both, and NPOI is quite flexible to use. For the first time I wrote a blog, I hope to share with you, learn together, and make progress together!