Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to operate Excel based on NPOI in C#". In daily operation, I believe many people have doubts about how to operate Excel based on NPOI in C#. Xiaobian consulted various materials and sorted out simple and easy operation methods. I hope to help you answer the doubts about "how to operate Excel based on NPOI in C#"! Next, please follow the small series to learn together!

About NPOI

NPOI is a program built on top of POI version 3.x that reads and writes Word or Excel documents without Office installed.

NPOI is an open source C#reading and writing Excel, WORD and other Microsoft OLE2 component documents project.

Advantages (1) Problems encountered in traditional Excel operations:

1. If it is. NET, you need to install Office on the server 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, it may cause the server to crash.

Excel will convert columns containing only numbers to type conversion, originally text type, Excel will convert them to numerical type, such as the number 000123 will become 123.

3. When exporting, if the field content starts with "-" or "=", Excel will treat it as a formula and report an error.

Excel will analyze the data type according to the first 8 rows of Excel file. If it happens that a column in the first 8 rows is only a number, it will think that the column is numerical and automatically convert the column into a format similar to 1.42702E+17.

(ii) Advantages of using NPOI

1. You can use the framework completely free of charge

2. Contains most of EXCEL's features (cell styles, data formats, formulas, etc.)

3, professional technical support services (24*7) (non-free)

File formats supported include xls, xlsx, docx.

5. Adopt an interface-oriented design architecture (you can view the namespace of NPOI.SS)

6. Support file import and export at the same time

7, based on. Net 2.0 also supports xlsx and docx format (of course, also supports. Net 4.0)

A large number of successful and authentic test Cases from around the world

9, a large number of example code

You don't need to install Microsoft Office on your server to avoid copyright issues.

12. It is more convenient and user-friendly to use than Office PIA API.

13, you do not have to spend a lot of effort to maintain NPOI, NPOI Team will constantly update and improve NPOI, absolutely save costs.

14, not only with Excel can be operated, for doc, ppt files can also do the corresponding operation

NPOI is powerful not because it supports exporting Excel, but because it supports importing Excel and "understanding"OLE2 document structure, which is also a weak aspect of some other Excel read-write libraries. Usually, read and understand the structure is far more complex than export, because import you must assume that all circumstances are possible, and generate you as long as you ensure that you can meet your own needs, if the import requirements and generate requirements than two sets, then generate requirements are usually a subset of import requirements, this rule is not only reflected in Excel read-write library, but also reflected in pdf read-write library, most of the pdf libraries currently on the market only support generation, do not support import.

Operating Excel /// ///Operating Excel based on NPOI /// public void ExportExcel() { string filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Excel\\"; string fileName = filePath + "TestExcel_" + DateTime.UtcNow.ToString("yyyyMMddHHmmss") + ".xlsx"; if (! Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { XSSFWorkbook workbook = new XSSFWorkbook(); //Create a cell style ICellStyle cellStyle = workbook.CreateCellStyle(); //Set to text format, or text, i.e. dataFormat.GetFormat("text"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //bottom border line cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //left border line cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //right border line cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //top border line //Settings page signature ISheet sheet = workbook.CreateSheet("Export Number Paragraph"); //Set column width sheet.SetColumnWidth(4, 10 * 500);//fifth column BTMAC sheet.SetColumnWidth(5, 10 * 500);//Sixth Column WifiMAC1 sheet.SetColumnWidth(6, 10 * 500);//Seventh Column WifiMAC2 sheet.SetColumnWidth(7, 10 * 500);//eighth column HARD_CODE sheet.SetColumnWidth(8, 10 * 500);//Column 9 QR_CODE //Excel header field 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 header field IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < excelHeader.Length; i++) { headerRow.CreateCell(i).SetCellValue(excelHeader[i]); } //Number of expansions int count = int.Parse(LoginInfo.QTY); //hexadecimal to decimal Int64 StarthardCode = Int64.Parse(LoginInfo.HardCode, NumberStyles.HexNumber); Int64 StartbtMAC = Int64.Parse(LoginInfo.BTMAC, NumberStyles.HexNumber); Int64 StartWifiMAC1 = Int64.Parse(LoginInfo.WiFiMAC1, NumberStyles.HexNumber); Int64 StartWifiMAC2 = 0; if (LoginInfo.IsEnableWiFiMAC2) { StartWifiMAC2 = Int64.Parse(LoginInfo.WiFiMAC2, NumberStyles.HexNumber); } //Fill Excel for (int i = 0; i < count; i++) { IRow row = sheet.CreateRow(i + 1); //BTMAC Int64 current_btMAC = StartbtMAC + i; //Hex conversion, capital //string strCurrent_btMAC = Convert.ToString(current_btMAC, 16).ToUpper(); string strCurrent_btMAC = current_btMAC.ToString("X").ToUpper(); row.CreateCell(4).SetCellValue(strCurrent_btMAC); //WifiMAC1 Int64 current_WifiMAC1 = StartWifiMAC1 + i; //Hex conversion, capital string strCurrent_WifiMAC1 = current_WifiMAC1.ToString("X").ToUpper(); row.CreateCell(5).SetCellValue(strCurrent_WifiMAC1); //WifiMAC2 if (LoginInfo.IsEnableWiFiMAC2) { Int64 current_WifiMAC2 = StartWifiMAC2 + i; //Hex conversion, upper case string strCurrent_WifiMAC2 = current_WifiMAC2.ToString("X").ToUpper(); row.CreateCell(6).SetCellValue(strCurrent_WifiMAC2); } //HardCode Int64 current_HardCode = StarthardCode + i; //convert to hex, lowercase string strCurrent_HardCode = current_HardCode.ToString("X").ToLower(); row.CreateCell(7).SetCellValue(strCurrent_HardCode); } workbook.Write(fs); //Write to Excel } } At this point, the study of "C#how to operate Excel based on NPOI" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.