ASP. NET uses the open-source component NPOI to quickly Import and Export Execl data, npoiexecl

ASP. NET uses the open-source component NPOI to quickly Import and Export Execl data, 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 html data tables to execl. The problem with this method is that the compatibility of the encoding format is too poor, 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:

Copy codeThe Code is as follows:
/// <Summary>
/// Export Execl
/// </Summary>
/// <Returns> </returns>
Public FileResult DataExportToExecl ()
{
// Create an Excel file object
NPOI. HSSF. UserModel. HSSFWorkbook book = new NPOI. HSSF. UserModel. HSSFWorkbook ();
NPOI. SS. UserModel. ISheet sheet1 = book. CreateSheet ("Sheet1"); // Add a sheet

Var _ data = CardHelper. GetAllData (); // obtain the list data. You can also obtain the data by page for more efficient performance.
// Add the header title of the first line to sheet1
NPOI. SS. UserModel. IRow row1 = sheet1.CreateRow (0 );
Row1.CreateCell (0). SetCellValue ("ranking ");
Row1.CreateCell (1). SetCellValue ("CardID ");
Row1.CreateCell (2). SetCellValue ("name ");
Row1.CreateCell (3). SetCellValue ("cell phone ");
Row1.CreateCell (4). SetCellValue ("position ");
Row1.CreateCell (5). SetCellValue ("company ");
Row1.CreateCell (6). SetCellValue ("Creation Time ");

// Gradually write data into each row of sheet1
For (int I = 0; I <_ data. Count; I ++)
{
NPOI. SS. UserModel. IRow rowtemp = sheet1.CreateRow (I + 1 );
Rowtemp. CreateCell (0). SetCellValue (I + 1 );
Rowtemp. CreateCell (1). SetCellValue (_ data [I]. ID );
Rowtemp. CreateCell (2). SetCellValue (_ data [I]. RealName );
Rowtemp. CreateCell (3). SetCellValue (_ data [I]. Cellphone );
Rowtemp. CreateCell (4). SetCellValue (string. IsNullOrEmpty (_ data [I]. WorTitle )? "None": _ data [I]. WorTitle );
Rowtemp. CreateCell (5). SetCellValue (string. IsNullOrEmpty (_ data [I]. Company )? "None": _ data [I]. Company );
Rowtemp. CreateCell (6). SetCellValue (_ data [I]. CreateDate. ToString ());
}
// Write data to the client
System. IO. MemoryStream MS = new System. IO. MemoryStream ();
Book. Write (MS );
Ms. Seek (0, SeekOrigin. Begin );
Return File (ms, "application/vnd. ms-excel", DateTime. Now. ToString ("yyyyMMdd") + ". xls ");
}

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.

Copy codeThe Code is as follows:
/// <Summary>
/// Import Execl Information
/// </Summary>
/// <Returns> </returns>
Private DataView LoadExeclFile ()
{
// Generate a DataTable
DataTable dt = new DataTable ();
Dt. Columns. Add ("Name", typeof (string ));
Dt. Columns. Add ("Phone", typeof (string ));
Dt. Columns. Add ("CID", typeof (string ));
Dt. Columns. Add ("Status", typeof (string ));
DataRow newRow = null;

String filepath = @ "data.xls"; // read local Execl. The current path is the directory of the program.
HSSFWorkbook wb = new HSSFWorkbook (new FileStream (filepath, FileMode. Open ));
HSSFSheet sheet = wb. GetSheet ("data") as HSSFSheet; // obtain sheet table data named data in execl

If (sheet = null)
{
MessageBox. Show ("check whether the file path and file name are incorrect! ");
}
Else
{
// Import data
For (int I = 1; I <= sheet. LastRowNum; I ++) // obtain all rows
{
IRow row = sheet. GetRow (I); // read data from the current row
If (row! = Null)
{
NewRow = dt. NewRow ();
NewRow ["Name"] = row. GetCell (0). ToString ();
NewRow ["Phone"] = row. GetCell (1). ToString ();
NewRow ["CID"] = row. GetCell (2). ToString ();
NewRow ["Status"] = row. GetCell (3). ToString ();

If (IsMobile (row. GetCell (1). ToString ()))
{
Dt. Rows. Add (newRow );
}

}
}

}

Return dt. DefaultView;
}

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:

Copy codeThe Code is as follows:
/// <Summary>
/// Import Execl Information
/// </Summary>
/// <Returns> </returns>
Private void LoadExeclFile ()
{
String filepath = @ "data.xls"; // read local Execl. The current path is the directory of the program.
HSSFWorkbook wb = new HSSFWorkbook (new FileStream (filepath, FileMode. Open ));
HSSFSheet sheet = wb. GetSheet ("data") as HSSFSheet; // obtain sheet table data named data in execl

If (sheet! = Null)
{
// Import data
For (int I = 1; I <= sheet. LastRowNum; I ++) // obtain all rows
{
IRow row = sheet. GetRow (I); // read data from the current row
If (row! = Null)
{
Insertdataanalytic dB (row. getCell (0 ). toString (), row. getCell (1 ). toString (), row. getCell (2 ). toString (), row. getCell (3 ). toString ());
}
}
}
}

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