asp. net USES npoi to read the excel template and export the download details

Why export Excel using NPOI?

1. Solve the problems encountered by traditional Excel operation:

If it is.NET, you need to install Office on the server side and update it in time to prevent vulnerabilities. You also need to set permissions to allow.NET to access COM+. If something goes wrong during the export process, the server may go down.
Excel will type columns that contain only Numbers. Excel will type columns that are text and convert them to numeric ones. For example, the number 000123 will change to 123.
When exporting, if the field content begins with "-" or "=", Excel will treat it as a formula and report an error.
Excel analyzes the data type according to the first eight lines of the Excel file. If you happen to have a column in the first eight lines of the Excel file that is just a number, it assumes that the column is numeric and automatically converts the column to a format similar to 1.42702E+17. The date column becomes a date and number column.

2. Personally, I think the advantages of using NPOI

The speed of the export is very fast, not an order of magnitude from the traditional way.
Don't worry about the process problem, because the traditional export method will open 1 Excel process for every export of Excel, and will not close automatically. If it is closed manually, all Excel processes must be killed by traversal, which will cause the problem of not exporting concurrently.

Using the step

Step 1: Add a reference to the solution.

NPOI 1.2.5. net2.0 is used here and two files need to be referenced:

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

Step 2: Add a reference to the CS file.

using NPOI.HSSF.UserModel;
using System.IO;// Memory flow usage 
using NPOI.SS.UserModel

Step 3: Use the code

#region Load the template file into 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))
{
// Loads the template from the file stream into the workbook object 
hssfworkbook=newHSSFWorkbook(file);
}
#endregion
#region Set the contents of the worksheet according to the template 
// To establish 1 called Sheet1 The work table 
ISheetsheet1=hssfworkbook.GetSheet("Sheet1");
// Add the data to the corresponding cell in the table, because the row has already been created and there is no need to recreate 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);
// mandatory Excel Recalculate all the formulas in the table 
sheet1.ForceFormulaRecalculation=true;
#endregion
#region Set the response header (file name and file format) 
// Set the type of response to be Excel
Response.ContentType="application/vnd.ms-excel";
// Set the downloaded Excel The file name 
Response.AddHeader("Content-Disposition",string.Format("attachment;filename={0}","test.xls"));
//Clear Method to delete all caches HTML The output. But this method only deletes Response Display input information without deleting Response Header information. To avoid affecting the integrity of the exported data. 
Response.Clear();
#endregion
#region Write to the client 
using(MemoryStreamms=newMemoryStream())
{
// Puts the contents of the workbook into the memory stream 
hssfworkbook.Write(ms);
// Convert memory flow into byte array to send to client 
Response.BinaryWrite(ms.GetBuffer());
Response.End();
}
#endregion

3, summarize

Load the template file into the workbook object.
Set the contents of the worksheet according to the template.
Set the response header (file name and file format).
Write to the client.