Asp.net use npoi to read the excel template and export and download details

Why use NPOI to export an Excel file?

I. Solve the Problems Encountered in traditional Excel operations:

If yes. NET, you need to install the Office on the server and update it in time to prevent vulnerabilities, you also need to set permissions to allow.. NET Access to COM +. If a problem occurs during the export process, the server may be down.
In Excel, only numeric columns are converted to the type, which is originally a text type. In Excel, numeric columns are converted into numeric columns. For example, the number 000123 is converted to 123.
If the field content starts with "-" or "=" during export, Excel regards it as a formula and reports an error.
Excel analyzes the data type based on the first eight rows of the Excel file. If a column in the first eight rows is only a number, it considers the column as a numeric type, the column is automatically converted to a format similar to 1.42702E + 17, and the date column is changed to contain dates and numbers.

II. I personally think the advantage of using NPOI

The export speed is very fast, which is not an order of magnitude higher than the traditional method.
There is no need to worry about the process, because the traditional export method opens an Excel process every time an Excel file is exported, it will not be closed automatically. If it is manually closed, it is necessary to traverse and kill all the Excel processes, this will cause the issue of concurrent export failure.

Procedure

Step 1: Add references to the solution.

Here we use the. net2.0 version of NPOI 1.2.5. We need to reference two files:

NPOI 1.2.5 is: http://npoi.codeplex.com/releases/view/38113

Step 2: Add references to the CS file.
Copy codeThe Code is as follows:
Using NPOI. HSSF. UserModel;
Using System. IO; // memory stream usage
Using NPOI. SS. UserModel

Step 3: Use the code

Copy codeThe Code is as follows:
# Region load the template file to the workbook object

// Create a workbook object
HSSFWorkbookhssfworkbook;
// Open the template file to the file stream
Using (FileStreamfile = newFileStream (HttpContext. Current. Request. PhysicalApplicationPath + @ "template/book1.xls", FileMode. Open, FileAccess. Read ))
{
// Load the template in the file stream to the workbook object
Hssfworkbook = newHSSFWorkbook (file );
}
# Endregion

# Region set the worksheet content based on the template

// Create a worksheet named Sheet1
ISheetsheet1 = hssfworkbook. GetSheet ("Sheet1 ");
// Add the data to the corresponding cell in the Table. Because the row has been created, you do not need to re-create the row.
Sheet1.GetRow (1). GetCell (1). SetCellValue (200200 );
Sheet1.GetRow (2). GetCell (1). SetCellValue (300 );
Sheet1.GetRow (3). GetCell (1). SetCellValue (500050 );
Sheet1.GetRow (4). GetCell (1). SetCellValue (8000 );
Sheet1.GetRow (5). GetCell (1). SetCellValue (110 );
Sheet1.GetRow (6). GetCell (1). SetCellValue (100 );
Sheet1.GetRow (7). getcells (1). SetCellValue (200 );
Sheet1.GetRow (8). GetCell (1). SetCellValue (210 );
Sheet1.GetRow (9). GetCell (1). SetCellValue (2300 );
Sheet1.GetRow (10). GetCell (1). SetCellValue (240 );
Sheet1.GetRow (11). GetCell (1). SetCellValue (180123 );
Sheet1.GetRow (12). GetCell (1). SetCellValue (150 );

// Force Excel to recalculate all formulas in the table
Sheet1.ForceFormulaRecalculation = true;
# Endregion
# Region set the Response Header (File Name and file format)
// Set the response type to Excel.
Response. ContentType = "application/vnd. ms-excel ";
// Set the downloaded Excel file name
Response. AddHeader ("Content-Disposition", string. Format ("attachment; filename = {0}", "test.xls "));
// Clear method deletes HTML output from all caches. However, this method only deletes the Response display input information, and does not delete the Response header information. To avoid affecting the integrity of exported data.
Response. Clear ();
# Endregion
# Write region to the client
Using (MemoryStreamms = newMemoryStream ())
{
// Put the content of the workbook into the memory stream
Hssfworkbook. Write (MS );
// Change the memory flow to a byte array and send it to the client
Response. BinaryWrite (ms. GetBuffer ());
Response. End ();
}
# Endregion

3. Summary

Load the template file to the workbook object.
Set the content of the worksheet according to the template.
Set the Response Header (File Name and file format ).
Write to the client.