Use the NPOI component in Asp.net to quickly Import and Export Execl data and npoiexecl

Use the NPOI component in Asp.net to quickly Import and Export Execl data and npoiexecl

I believe that many children's shoes have developed the Execl Import and Export function. Recently, whether it is the background data analysis needs or the frontend meets the convenience of user management, both have maintenance requirements for Execl import and export.

Previously, this function was used. If it is web, HttpContext is used. current. response. contentType = "application/ms-excel"; you can export the html data table to execl. The problem with this method is that the encoding format is too compatible, use an office like Mac OS to open it and check it out. You can call the COM component of the office or Execl using a macro script. The main problem with this method is that the client must install the office.

After using the NPOI open source component in the product recently, the appeal method is too Out. First, feel the charm of the Code:

1 /// <summary> 2 /// export Execl 3 /// </summary> 4 /// <returns> </returns> 5 public FileResult DataExportToExecl () 6 {7 // create an Excel file object 9 NPOI. HSSF. userModel. HSSFWorkbook book = new NPOI. HSSF. userModel. HSSFWorkbook (); 10 NPOI. SS. userModel. ISheet sheet1 = book. createSheet ("Sheet1"); // Add a sheet11 12 var _ data = CardHelper. getAllData (); // get list data, or retrieve data by page for more efficient performance 13 14 // Add the header title of the first line to sheet1 15 NPOI. SS. U SerModel. IRow row1 = sheet1.CreateRow (0); 16 row1.CreateCell (0 ). setCellValue ("ranking"); 17 row1.CreateCell (1 ). setCellValue ("CardID"); 18 row1.CreateCell (2 ). setCellValue ("name"); 19 row1.CreateCell (3 ). setCellValue ("cell phone"); 22 row1.CreateCell (4 ). setCellValue ("position"); 23 row1.CreateCell (5 ). setCellValue ("company"); 25 row1.CreateCell (6 ). setCellValue ("Creation Time"); 26 27 // gradually write data to each row of sheet1 28 for (int I = 0; I <_ data. count; I ++) 29 {30 NPOI. SS. userModel. IRow rowtemp = sheet1.CreateRow (I + 1); 31 32 rowtemp. createCell (0 ). setCellValue (I + 1); 33 rowtemp. createCell (1 ). setCellValue (_ data [I]. ID); 34 rowtemp. createCell (2 ). setCellValue (_ data [I]. realName); 35 rowtemp. createCell (3 ). setCellValue (_ data [I]. cellphone); 38 rowtemp. createCell (4 ). setCellValue (string. isNullOrEmpty (_ data [I]. worTitle )? "None": _ data [I]. WorTitle); 39 rowtemp. CreateCell (5). SetCellValue (string. IsNullOrEmpty (_ data [I]. Company )? "None": _ data [I]. company); 41 rowtemp. createCell (6 ). setCellValue (_ data [I]. createDate. toString (); 42} 43 // write to client 44 System. IO. memoryStream MS = new System. IO. memoryStream (); 45 book. write (ms); 46 ms. seek (0, SeekOrigin. begin); 47 return File (MS, "application/vnd. ms-excel ", DateTime. now. toString ("yyyyMMdd") + ". xls "); 48}

At the front end, you only need to use hypertext to link to this Action. As for the plain text or the button method, it is based on your preferences.

1 /// <summary> 2 /// import Execl Information 3 /// </summary> 4 /// <returns> </returns> 5 private DataView LoadExeclFile () 6 {7 // A able 8 dt able dt = new DataTable (); 9 dt. columns. add ("Name", typeof (string); 10 dt. columns. add ("Phone", typeof (string); 11 dt. columns. add ("CID", typeof (string); 12 dt. columns. add ("Status", typeof (string); 13 DataRow newRow = null; 14 15 string filepath = @ "data.xls ";// Read local Execl, the current path is the directory where the program is located 16 HSSFWorkbook wb = new HSSFWorkbook (new FileStream (filepath, FileMode. open); 17 HSSFSheet sheet = wb. getSheet ("data") as HSSFSheet; // get sheet table data named data in execl 18 19 if (sheet = null) 20 {21 MessageBox. show ("check whether the file path and file name are incorrect! "); 22} 23 else24 {25 // import data 26 for (int I = 1; I <= sheet. lastRowNum; I ++) // obtain all Rows 27 {28 IRow row = sheet. getRow (I); // read the data of the current row 29 if (row! = Null) 30 {31 newRow = dt. newRow (); 32 newRow ["Name"] = row. getCell (0 ). toString (); 33 newRow ["Phone"] = row. getCell (1 ). toString (); 34 newRow ["CID"] = row. getCell (2 ). toString (); 35 newRow ["Status"] = row. getCell (3 ). toString (); 36 37 if (IsMobile (row. getCell (1 ). toString () 38 {39 dt. rows. add (newRow); 40} 41 42} 43} 44 45} 46 47 return dt. defaultView; 48}

The Execl import function imports data to the Gridview. If you want to import data to a database or other data storage media, replace the Code:

1 /// <summary> 2 /// import Execl Information 3 /// </summary> 4 /// <returns> </returns> 5 private void LoadExeclFile () 6 {15 string filepath = @ "data.xls"; // read local Execl. The current path is the directory where the program is located. 16 HSSFWorkbook wb = new HSSFWorkbook (new FileStream (filepath, FileMode. open); 17 HSSFSheet sheet = wb. getSheet ("data") as HSSFSheet; // get sheet table data named data in execl 18 19 if (sheet! = Null) 24 {25 // import data 26 for (int I = 1; I <= sheet. lastRowNum; I ++) // obtain all Rows 27 {28 IRow row = sheet. getRow (I); // read the data of the current row 29 if (row! = Null) 30 {31 insertdata1_db (row. getCell (0 ). toString (), row. getCell (1 ). toString (), row. getCell (2 ). toString (), row. getCell (3 ). toString ());42}

43 　　　　　　　　 　} 44　　　　　　　　}45　　　　　　}

After reading the code, I believe you can perceive that NPOI's powerful Execl operations cannot be described in words. Appeal import and export should be the most frequently used Execl operations in normal development, and NPOI has basically encapsulated various methods to the extreme, unless you have special business scenarios, otherwise, you only need to make some modifications to meet your different business needs. Of course, there are still some problems with the appeal scheme. The solution is similar in general, namely, time for space or space for time. For example:

-- If the exported data volume is large, performance problems may occur when data is obtained and processed. In severe cases, the program reports an error. The solution is simple. After code transformation, you can retrieve data by PAGE, export data to multiple sheets, or divide the data into multiple execl for export. I am too lazy to write the code.

To sum up the advantages and disadvantages of NPOI:

Advantage: It is super simple to use and can be understood by Tom. Independent components, reference. No third-party dependency is required. Flexible coding control and excellent compatibility. The performance is very good, and the source code is also very beautiful...

Disadvantage: If yes, please let me know!

How can I use NPOIdll in ASPnet to export EXCEL files? C # development language is preferred.

You don't need to use this. How fast do you write yourself,
I used this file. It was written in CSV format and may be opened in EXCEL. You can save it as xls.

/// <Summary>
/// Convert the DataTable to a string
/// </Summary>
/// <Param name = "dt"> </param>
/// <Returns> </returns>
Public static string DtToString (DataTable dt)
{
String data = "";
Try
{
Foreach (DataColumn column in dt. Columns)
{
Data + = column. ColumnName + ",";
}
Data + = "\ r \ n ";

// Write data
Foreach (DataRow row in dt. Rows)
{
Foreach (DataColumn column in dt. Columns)
{
String t = row [column]. ToString ();
If (! String. IsNullOrEmpty (t ))
{
T = t. Replace (",","");
T = t. Replace ("\ r ","");
T = t. Replace ("\ n ","");
T = HttpContext. Current. Server. HtmlEncode (t );
Data + = t + ",";
}
Else
Data + = ",";
}
Data + = "\ r \ n ";
}
Data + = "\ r \ n ";
}
Catch {}
Return data;
}

/// <Summary>
/// Convert the string to CSV format for output
/// </Summary>
/// <Param name = "content"> string content & lt ...... remaining full text>

How does Aspnet use npoi to read excel content?

There are examples in NPOI
To reference
Using NPOI;
Using NPOI. HPSF;
Using NPOI. HSSF;
Using NPOI. HSSF. UserModel;
Using NPOI. SS. UserModel;
Using NPOI. POIFS;
Using NPOI. Util;
Then, you can read this information based on the examples in NPOI.
PS. It is best to set all words in EXCEL to "text ".
References: I have used my work