Introduction to NPOI
NPOI refers to a program built on top of the POI version. NPOI can read and write Word or Excel documents without Office installed.
NPOI is an open source C# project that reads and writes Microsoft OLE2 component documents such as Excel and WORD.
Advantages
(I) Problems encountered in traditional operation of Excel:
1. If it is .NET, you need to install Office on the server side and update it in time to prevent vulnerabilities. You also need to set permissions to allow .NET to access COM+. If there is a problem during the export process, the server may be down.
2. Excel will type columns containing only numbers. They are originally text-type, but Excel will convert them into numerical types, such as the number 000123 will become 123.
3. When exporting, if the field content starts with "-" or "=", Excel will perform it as a formula and an error will be reported.
4. Excel will analyze the data type based on the first 8 lines of the Excel file. If a column in the first 8 lines is just a number, it will consider the column to be numerical and automatically convert the column into a format similar to 1.42702E+17, and the date column becomes a date and number.
(II) Advantages of using NPOI
1. You can use this framework completely free
2. Contains most of the EXCEL features (cell style, data format, formula, etc.)
3. Professional technical support services (24*7 all-weather) (not free)
4. The file formats that support processing include xls, xlsx, and docx.
5. Adopt an interface-oriented design architecture (namespace that can be viewed)
6. Supports file import and export at the same time
7. Based on .net 2.0, it also supports xlsx and docx formats (of course, it also supports .net 4.0)
8. A large number of successful and real tests from around the world
9. A large number of instance codes
11. You don’t need to install Microsoft Office on the server, which can avoid copyright issues.
12. It is more convenient and user-friendly than the Office PIA API.
13. You don’t have to spend a lot of effort to maintain NPOI. NPOI Team will continue to update and improve NPOI, which will definitely save costs.
14. Not only can operations with Excel, but also can operate corresponding operations for doc and ppt files.
The reason why NPOI is powerful is not because it supports exporting Excel, but because it supports importing Excel and can "understand" the OLE2 document structure, which is also a weaker aspect of some other Excel read and write libraries. Generally speaking, reading and understanding the structure is much more complicated than exporting, because you must assume that everything is possible in importing, and generating it is enough to ensure that it meets your own needs. If you compare the import requirements and the generation requirements into two sets, then the generation requirements are usually a subset of the import requirements. This rule is not only reflected in the Excel read and write library, but also in the pdf read and write library. Currently, most pdf libraries on the market only support generation and do not support import.
Operation Excel
/// <summary>
///Operate Excel based on NPOI /// </summary>
public void ExportExcel()
{
string filePath = () + "\\Excel\\";
string fileName = filePath + "TestExcel_" + ("yyyyMMddHHmmss") + ".xlsx";
if (!(filePath))
{
(filePath);
}
using (FileStream fs = new FileStream(fileName, , ))
{
XSSFWorkbook workbook = new XSSFWorkbook();
//Create cell style ICellStyle cellStyle = ();
//Set it to text format, or it can be text, that is ("text"); = ; //Lower border line = ; //Left border line = ; //Right border line = ; //The upper border line
//Set page signature ISheet sheet = ("Export Segment");
//Set the column width (4, 10 * 500);//Column 5 BTMAC (5, 10 * 500);//Column 6 WifiMAC1 (6, 10 * 500);//Column 7 WifiMAC2 (7, 10 * 500);//Column 8 HARD_CODE (8, 10 * 500);//Column 9 QR_CODE
//Excel table header column string[] excelHeader = new string[] { "IMEI1", "IMEI2", "MEID", "MSN number", "Bluetooth MAC Address", "Wireless MAC Address 1", "Wireless MAC Address 2", "HARD_CODE", "QR_CODE", "TOKEN", "KEYMASTER" };
//Set the header field IRow headerRow = (0);
for (int i = 0; i < ; i++)
{
(i).SetCellValue(excelHeader[i]);
}
// Expanded number int count = ();
//Convert hexadecimal to decimal Int64 StarthardCode = (, );
Int64 StartbtMAC = (, );
Int64 StartWifiMAC1 = (LoginInfo.WiFiMAC1, );
Int64 StartWifiMAC2 = 0;
if (LoginInfo.IsEnableWiFiMAC2)
{
StartWifiMAC2 = (LoginInfo.WiFiMAC2, );
}
//Fill Excel for (int i = 0; i < count; i++)
{
IRow row = (i + 1);
//BTMAC
Int64 current_btMAC = StartbtMAC + i;
//Return to hexadecimal, capital //string strCurrent_btMAC = (current_btMAC, 16).ToUpper();
string strCurrent_btMAC = current_btMAC.ToString("X").ToUpper();
(4).SetCellValue(strCurrent_btMAC);
//WifiMAC1
Int64 current_WifiMAC1 = StartWifiMAC1 + i;
//Return to hexadecimal, capital string strCurrent_WifiMAC1 = current_WifiMAC1.ToString("X").ToUpper();
(5).SetCellValue(strCurrent_WifiMAC1);
//WifiMAC2
if (LoginInfo.IsEnableWiFiMAC2)
{
Int64 current_WifiMAC2 = StartWifiMAC2 + i;
//Return to hexadecimal, capital string strCurrent_WifiMAC2 = current_WifiMAC2.ToString("X").ToUpper();
(6).SetCellValue(strCurrent_WifiMAC2);
}
//HardCode
Int64 current_HardCode = StarthardCode + i;
//Return to hexadecimal, lowercase string strCurrent_HardCode = current_HardCode.ToString("X").ToLower();
(7).SetCellValue(strCurrent_HardCode);
}
(fs); //Write to Excel }
}This is all about this article about C#’s NPOI-based operation of Excel. I hope it will be helpful to everyone's learning and I hope everyone will support me more.