NPOI has been famous for a long time, but it has always been thought that a third party cannot have a high efficiency of Microsoft, so it has always used COM to process EXCEL. One day, I processed a large amount of data in EXCEL and found that the data generated by NPOI was completed in almost one second, hundreds of times faster than the traditional method. I was pleasantly surprised to learn how to use NPOI and hope to leave something for others.
NPOI has been famous for a long time, but it has always been thought that a third party cannot have a high efficiency of Microsoft, so it has always used COM to process EXCEL. One day, I processed a large amount of data in EXCEL and found that the data generated by NPOI was completed in almost one second, hundreds of times faster than the traditional method. I was pleasantly surprised to learn how to use NPOI and hope to leave something for others.
NPOI has been famous for a long time, but it has always been thought that a third party cannot have a high efficiency of Microsoft, so it has always used COM to process EXCEL.
One day, I processed a large amount of data in EXCEL and found that the data generated by NPOI was completed in almost one second, hundreds of times faster than the traditional method.
I was pleasantly surprised to learn how to use NPOI and hoped to leave something for others to learn.
This is the first article. Create a New EXCEL file with NPOI.
I will not go into details.
For simple processing, you only need to reference
NPOI. dll and ICSharpCode. SharpZipLib. dll
Only two files.
Okay, start.
Step 1: reference a space
Step 2: Create a workbook and a sheet
HSSFWorkbook wk = new HSSFWorkbook (); // create a SheetISheet sheet = wk. CreateSheet ("example ");
Step 3: Create rows and cells
// Create row IRow row = sheet. CreateRow (0) in the first row; // create cell ICell cell = row. CreateCell (0) in the first column of the First row );
In NPOI, IRow and ICell are basic elements in the table structure. They must be created using the Create FUNCTION before they can be used. Otherwise, an error is reported.
- Knowledge Point:
- In excel, there are no cells at a certain position, and there are no values in the cells. The two are saved separately. "Whether a cell exists" is also one of the information to be saved, which also occupies the file size. A cell is an invisible container. You must first have a container to store values in it.
- I have seen an incorrect example. The exported excel file contains only one row of data, but the file size exceeds 10 MB. It is because when exporting, the programmer did not figure out the difference between cells and values, and thought that empty cells do not occupy the size, and all the grids in the 65535 rows were created, resulting in a large number of blank lines.
- Pay attention to this in actual programming. Only the rows that need to be used can be created, and only the cells that need to be used can be created.
Next we can use this function to assign values to cells.
Cell. SetCellValue ("test ");
It is worth noting that the SetCellValue overload supports
- Bool
- DateTime
- Double
- String
- IRichTextString
Parameters in these formats are not supported as Excel COM does. Therefore, type conversion must be completed before assigning values.
Finally, save the prepared EXCEL file to the hard disk.
// Open an xlsfile. If no File exists, create it on your own. If the myxls.xls File exists, do not open the File using (FileStream fs = File. openWrite ("d :\\ excel.xls") {wk. write (fs); // Write and save to the opened xls file .}
This completes the most basic creation, writing, and saving.
No need to release, no need to install EXCEL. Extremely fast and convenient.