ASP. MVC4 Fast export of Excel documents using NPOI data processing

I. BACKGROUND

In a small project that was previously done, there was a need to count the data for an activity entry and export it as an Excel table, and the format requirement was not high.

Second, the problem analysis

Since the user only requires that the data in the database be exported to Excel, the formatting requirements are not high, so just add a link to the page, the background action to read the data and then through the third-party components to export, and then download, can meet this simple requirement. First Bing found about the data export components, Npoi is the most frequently popped, here also to do winform form development used to the Crystal Report method, in fact, the implementation method is more, because previously did not contact Npoi, it is intended to use this open source components in the project.

Third, solve the idea or process

2. Prepare the data source first. The MySQL database is used to store some data in the data beforehand.
4. Cut straight into the subject. This is done mainly in action. First, introduce the DLL for the Npoi component (npoi the latest download link), and then add the using Npoi to the controller . HSSF. Usermodel;
5. Paste the action code in the controller:

     PublicActionResult Exportdormitorybottlerecycleexcel () {Npoi. HSSF. Usermodel.hssfworkbook Book=NewNpoi. HSSF.            Usermodel.hssfworkbook (); Npoi. Ss. Usermodel.isheet Sheet1= Book. Createsheet ("Sheet1"); List<Model> list =NewList<model>(); List=pbsaccess.derivelist (); //Create ColumnsNpoi. Ss. Usermodel.irow row = Sheet1. CreateRow (0); Row. Createcell (0). Setcellvalue ("numbering"); Row. Createcell (1). Setcellvalue ("Col_1"); Row. Createcell (2). Setcellvalue ("col_2"); Row. Createcell (3). Setcellvalue ("Col_3"); Row. Createcell (4). Setcellvalue ("Col_4"); Row. Createcell (5). Setcellvalue ("col_5");  for(inti =0; I < list. Count; i++) {Npoi. Ss. Usermodel.irow rowtemp= Sheet1. CreateRow (i +1); Rowtemp. Createcell (0). Setcellvalue ((i +1).                ToString ()); Rowtemp. Createcell (1). Setcellvalue (List[i].                Dormitorynumber.tostring ()); Rowtemp. Createcell (2). Setcellvalue (List[i].                Smallbottlenumber.tostring ()); Rowtemp. Createcell (3). Setcellvalue (List[i].                Bigbottlenumber.tostring ()); Rowtemp. Createcell (4). Setcellvalue (List[i].                Totalbottlenumber.tostring ()); Rowtemp. Createcell (5). Setcellvalue (List[i].            Publishtime.tostring ()); }
   

               Write to Client
               //.......  

               return Content ("");         }

7. First, a new book object is first, then Npoi. Ss. Usermodel.isheet Sheet1 = Book. Createsheet ("Sheet1"); Create a Table object named Sheet1, where Sheet1 is not the final output file name. After you create the table, you need to add the column items. Add this section of Npoi here. Ss. Usermodel.irow row = Sheet1. CreateRow (0); One of the irow needs to be aware that when looking at some other blog-written examples, an error is found using npoi. Ss. Usermodel.row, the reference here is 2.1.3.1 version, has changed over, should be used IRow, this is to read the document only to realize the problem here, correct this error. About the npoi operation of the Chinese document, there is a need for friends can leave a message, I post it again, in this thanks to the npoi in the development of the group in the various ways of the great God's help. row. Createcell (i). Setcellvalue ("number"), how many columns there are, and how much I fetch (starting from 0).
9. Start importing data after the table has been built. The list is loaded with data so that each row is traversed once and the data is populated into it. When using foreach, note that the first line is already in use and the column name is created, so you need to start with the starting line +1 that will populate the data:Npoi. Ss. Usermodel.irow rowtemp = Sheet1. CreateRow (i + 1); The data string conversion is also a normal operation.
10. Then the key is to export the downloaded processing

    //Write to clientSystem.IO.MemoryStream ms =NewSystem.IO.MemoryStream (); Book.            Write (MS); Ms. Seek (0, Seekorigin.begin); Response.AddHeader ("content-disposition",string. Format ("attachment; Filename={0}.xls","* * * General table"+ DateTime.Now.ToString ("yyyymmddhhmmssfff"))); Response.BinaryWrite (Ms.            ToArray ()); Book=NULL; Ms.            Close (); Ms.            Dispose (); 

12. System.IO.MemoryStream and response are two critical processes in which file names can be processed in response, string.formate (); In the foreground, you only need to add <a href = "/admin/ Exportdormitorybottlerecycleexcel "></a> label.

14. Actual Build effect

Iv. Summary

Recently also in the use of Npoi do more specific operations, the latter will be some of the use of ideas and everyone to share, today realize the function of simple, if my ideas and methods are wrong, I implore you to correct, humbly ask. Please indicate the source and origin, thank you for your cooperation by the point will not be

ASP. Mvc4 Quick Export of Excel documents using NPOI data processing