NPOI cannot access the closed stream solution by using excel export methods or errors. npoiexcel
Some import and export functions are often encountered during the project process. Today, I may leave it empty.
(If there is something wrong, please correct it. If you solve the problem, you can easily record it for your reference)
First, the basic project process diagram is as follows:
1. Basic self-built html
2. jQuery. Upload plug-in
Well, let's get started. Today we mainly record the down-loading capabilities. The NPOI download method is used as an example.
In fact, there are many download methods. Everyone may have their own common methods, but I feel that there are only two download methods,
1. manually generate a document, fill in data, and download
HSSFWorkbook book = new HSSFWorkbook (); ISheet sheet = book. createSheet ("Download example"); sheet. setColumnWidth (256 *); // you can specify the width of sheet in the first column. setColumnWidth (256 *); // set the width of the second column for (int rowId = 0; rowId <= 10; rowId ++) {IRow row = sheet. createRow (rowId); for (int I = 0; I <2; I ++) {ICell cell = row. createCell (I); cell. setCellType (CellType. string); cell. setCellValue ("test" + I) ;}var MS = new MemoryStream (); book. write (ms); ms. flush (); ms. position = 0; string fileName = "test example .xls"; return File (MS, "application/vnd. ms-excel ", fileName );
2. Load a predefined template file, fill in data, and download
// Read the template by loading string filePath = System. Web. HttpContext. Current. Server. MapPath ("~ /Template/BasicInfoTemplete.xlsx "); IWorkbook workbook; FileStream fs = new FileStream (filePath, FileMode. openOrCreate, FileAccess. read); workbook = WorkbookFactory. create (fs); ISheet sheet = workbook. getSheetAt (0); // obtain the first Sheet ICellStyle cellStyle = workbook in Excel. createCellStyle (); // set the cellStyle of the upper and lower sides of the cell. borderTop = NPOI. SS. userModel. borderStyle. thin; cellStyle. borderBottom = NPOI. SS. userModel. borderStyle. thin; cellStyle. borderLeft = NPOI. SS. userModel. borderStyle. thin; cellStyle. borderRight = NPOI. SS. userModel. borderStyle. thin; // horizontal and vertical alignment of text in cellStyle. alignment = NPOI. SS. userModel. horizontalAlignment. center; cellStyle. verticalAlignment = NPOI. SS. userModel. verticalAlignment. center; ICell Cell = null; IRow row = null; for (int I = 2; I <10; I ++) {row = sheet. createRow (I); for (int j = 1; j <= 5; j ++) {Cell = row. createCell (j); Cell. cellStyle = cellStyle; Cell. setCellValue (I + "test example" + j);} var ms = new NpoiMemoryStream (); ms. allowClose = false; workbook. write (fs); workbook. write (ms); ms. flush (); ms. position = 0; ms. allowClose = false; return File (MS, "application/vnd. ms-excel "," test example .xlsx ");
Call in html
<A href = "/UploadCarInfo/DownLoadBasicInfo" target = "ajaxDownload"> <I class = "fa-download"> </I> vehicle model reference </a> <iframe name = "ajaxDownload" style = "display: none "> </iframe>
The above are all very basic, for example, some other verifications or try catch are not all listed yet, for example, what public methods are available, such as TableToExcel, and some public methods are found on the Internet, I will not list the download details one by one;
The second method may encounter the problem of [inaccessible and closed], so the NpoiMemoryStream class, as shown in the code, is inherited from the MemoryStream system class, rewrite the method (Close) for closing the stream in the system class, so that I can manually control it to solve the problem;
My personal conclusion: when a problem is encountered, it is often not the first time to solve the problem, unless you are very familiar with it; instead, you should think about the solution to the problem. Some problems are nothing more than those routines, how to solve the problem? I don't want to ask much details during the interview ......