asp.net use Npoi to read Excel templates and export download details _ Practical Tips

Why use Npoi to export Excel?

First, solve the traditional operation of Excel encountered problems:

If you are. NET, you need to install Office on the server and update it in time to prevent vulnerabilities, and you need to set permissions. NET access to COM + if problems with the export process can cause server downtime.
Excel converts a column that contains only numbers to a type, which is text-typed, and Excel converts it to a numeric value, such as number 000123, which becomes 123.
When exporting, if the field content starts with "-" or "=", Excel takes it as a formula and complains.
Excel analyzes the data type based on the first 8 lines of the Excel file, and if your first 8 lines are just numbers, it thinks that the column is numeric, automatically converting it to a similar 1.42702E+17 format, and date columns to include dates and numbers.

Second, the individual thinks the advantage that uses Npoi

The speed of the export is very fast and is not an order of magnitude with the traditional way.
Do not worry about process issues, because the traditional export method for each export Excel will open an Excel process, will not automatically shut down, if manually closed, you must traverse all the Excel process to kill, which will cause the problem can not be exported concurrently.

Use steps

First step: Add a reference in the solution.

Here you use the. net2.0 version of Npoi 1.2.5, which requires two files to be referenced:

Npoi 1.2.5 's download address is: http://npoi.codeplex.com/releases/view/38113

Step Two: Add a reference to the CS file.

Copy Code code as follows:

Using Npoi. HSSF. Usermodel;
Use of a using system.io;//memory stream
Using Npoi. Ss. Usermodel

Step Three: Use code specifically

Copy Code code as follows:

#region加载模板文件到工作簿对象中

Create a Workbook object
Hssfworkbookhssfworkbook;
Open template file to file stream
Using (Filestreamfile=newfilestream (httpcontext.current.request.physicalapplicationpath+@ "Template/book1.xls"), FileMode.Open,FileAccess.Read))
{
To load a template from a file stream into a workbook object
Hssfworkbook=newhssfworkbook (file);
}
#endregion

#region根据模板设置工作表的内容

//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 and no rows need to be recreated
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). Getcell (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);

Force Excel to recalculate all formulas in a table
Sheet1. Forceformularecalculation=true;
#endregion
#region设置响应头 (file name and file format)
Set the type of response as Excel
Response.contenttype= "Application/vnd.ms-excel";
Set the downloaded Excel file name
Response.AddHeader ("Content-disposition", String. Format ("attachment;filename={0}", "Test.xls"));
The Clear method deletes the HTML output from all caches. However, this method deletes only response display input information and does not delete response header information. To avoid affecting the integrity of the exported data.
Response.Clear ();
#endregion
#region写入到客户端
using (Memorystreamms=newmemorystream ())
{
To place the contents of a workbook in a memory stream
Hssfworkbook. Write (MS);
Send memory flow to byte array to client
Response.BinaryWrite (Ms. GetBuffer ());
Response.End ();
}
#endregion

3, Summary

Loads the template file into the Workbook object.
Sets the contents of the worksheet according to the template.
Sets the response header (file name and file format).
Write to the client.