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

  This article mainly introduces ASP.net use Npoi to read Excel templates and export downloads of examples, please refer to the use of the bar

Why use Npoi to export Excel?   First, solve the traditional operation of Excel encountered problems:   If it is. NET, you need to install Office on the server, and update it in time to prevent vulnerabilities, you need to set permissions to allow. 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.   II, personally think the advantages of using Npoi   export speed, and the traditional way is not an order of magnitude. 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   Step one: Add references in the solution.   Here is Npoi 1.2.5. net2.0 version, you need to reference two files:   npoi 1.2.5 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; The use of a using system.io;//memory stream is npoi. Ss. Usermodel     Step three: Specific use code     copy code code as follows: #region加载模板文件到工作簿对象中  //Create Workbook object Hssfworkbookhssfworkboo K Open the template file to the file stream using (filestreamfile=newfilestream httpcontext.current.request.physicalapplicationpath+ @ template /book1.xls ", FILEMODE.OPEN,FILEACCess. Read) {//the template in the file stream is loaded into the Workbook object Hssfworkbook=newhssfworkbook (file); #endregion   #region根据模板设置工作表的内容  // Create a worksheet named Sheet1 Isheetsheet1=hssfworkbook. Getsheet ("Sheet1"); Add data to the corresponding cell in the table, because the row is already created and 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). 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 (150);  //force Excel to recalculate all formula Sheet1 in the table. 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 ()) {//Hssfworkbook the contents of the workbook into the memory stream. Write (MS); Converts the memory flow into a byte array to the client Response.BinaryWrite (Ms. GetBuffer ()); Response.End (); #endregion       3, summary   load 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.