Npoi and delegated Excel export npoi generate a native Excel file instance based on the Excel template

First, I learned how to use npoi to export data.ArticleNpoi generates a native Excel file instance based on the Excel template. Thank you for choosing here.

In this article, we have made some minor changes and added the delegation mechanism. Because when exporting, the template is loaded and downloaded as an Excel fileCodeSame, but the values in the Excel template are very different. So I made some minor changes.

The modified main class files are as follows:

Excelhelper:

View code

 Public   Class  Excelhelper {  Private   String  Templatepath;  Private   String  Newfilename; Private   String  Templdatename;  Private   String  Sheetname;  Public   String  Sheetname {  Get { Return  Sheetname ;}  Set {Sheetname = Value ;}}  Public Excelhelper ( String Templdatename, String  Newfilename ){  This . Sheetname = "  Sheet1  "  ; Templatepath = Httpcontext. Current. server. mappath ( "  /  " ) + "  /Config/template/  " ;  This . Templdatename = String . Format ( "  {0} {1}  "  , Templatepath, templdatename );  This . Newfilename = Newfilename ;}  Public   Void Exportdatatoexcel (Action  Actionmethod ){  Using (Memorystream MS =Setdatatoexcel (actionmethod )){  Byte [] DATA = Ms. toarray ();  # Region Response to the client Httpresponse response = System. Web. httpcontext. Current. response; response. Clear (); response. charset = "  UTF-8  "  ; Response. contenttype = " Application/vnd-Excel  " ; //  "Application/vnd. MS-excel "; System. Web. httpcontext. Current. response. addheader ( "  Content-Disposition  " , String . Format ( "  Attachment; filename =  " + Newfilename); system. Web. httpcontext. Current. response. binarywrite (data ); # Endregion  }}  Private Memorystream setdatatoexcel (Action  Actionmethod ){  //  Load Template File Filestream file = New  Filestream (templdatename, filemode. Open, fileaccess. Read); hssfworkbook workbook = New  Hssfworkbook (File); hssfsheet Sheet = Workbook. getsheet (sheetname ); If (Actionmethod! = Null  ) Actionmethod (sheet); sheet. forceformularecalculation = True  ;  Using (Memorystream MS = New  Memorystream () {Workbook. Write (MS); Ms. Flush (); Ms. Position = 0  ; Sheet = Null  ; Workbook = Null  ;  Return  MS ;}}} 

Printmanager:

View code

 Public   Class  Printmanager {  Public   Void  Printpurchase () {excelhelper helper = New Excelhelper ( " Purchaseorder.xls  " , "  Purchaseorder_c000001.xls  "  ); Helper. exportdatatoexcel (setpurchaseorder );}  Private   Void  Setpurchaseorder (hssfsheet sheet) {hssfrow row = Null  ; Hssfcell Cell = Null  ; Row = Sheet. getrow (2  ); Cell = Row. getcell ( 1  ); Cell. setcellvalue (  "  C0000001  "  ); Cell = Row. getcell ( 7  ); Cell. setcellvalue (  "  2013-04-18  "  ); Datatable itemdt =Prepareitemdtfortest (); setdatatablevalue (sheet,  7 , 0  , Itemdt); row = Sheet. getrow ( 14  ); Cell = Row. getcell ( 0  ); Cell. setcellvalue (  "  Nokia  "  ); Cell = Row. getcell ( 6 ); Cell. setcellvalue (  "  CMCC  "  );}  Public   Void Setdatatablevalue (hssfsheet sheet, Int Rowindex, Int  Columnindex, datatable DT) {hssfrow row = Null  ; Hssfcell Cell = Null ;  Foreach (Datarow In  DT. Rows) {row = Sheet. getrow (rowindex); columnindex = 0  ;  Foreach (Datacolumn Column In  DT. columns) {Cell = Row. getcell (columnindex );  String Drvalue =Datarow [column]. tostring ();  Switch  (Column. datatype. tostring ()){  Case   "  System. String  "  : Cell. setcellvalue (drvalue );  Break  ;  Case   "  System. datetime "  : Datetime datev; datetime. tryparse (drvalue,  Out  Datev); cell. setcellvalue (datev );  Break  ;  Case   "  System. Boolean  "  :  Bool Boolv = False ;  Bool . Tryparse (drvalue, Out  Boolv); cell. setcellvalue (boolv );  Break  ;  Case   "  System. int16  "  :  Case   "  System. int32 "  :  Case   "  System. int64  "  :  Case   "  System. byte  "  :  Int Intv = 0  ;  Int . Tryparse (drvalue, Out  Intv); cell. setcellvalue (intv );  Break  ;  Case   "  System. Decimal  "  :  Case   "  System. Double  "  : Double Doubv = 0  ;  Double . Tryparse (drvalue, Out  Doubv); cell. setcellvalue (doubv );  Break  ;  Case   "  System. dbnull  "  : Cell. setcellvalue ( ""  );  Break  ;  Default  : Cell. setcellvalue (  ""  );  Break  ;} Columnindex ++ ;} Rowindex ++ ;}}  Private Datatable prepareitemdtfortest () {datatable itemdt = New  Datatable (); itemdt. Columns. Add (  "  Name  "  ); Itemdt. Columns. Add (  "  Qty  " , Type. GetType ( "  System. Decimal  "  ); Itemdt. Columns. Add ( "  Unitprice  " , Type. GetType ( "  System. Decimal  "  ); Datarow newrow = Itemdt. newrow (); newrow [  0 ] = "  820  "  ; Newrow [  1 ] = " 100  "  ; Newrow [  2 ] = "  3000  "  ; Itemdt. Rows. Add (newrow); datarow newrow2 = Itemdt. newrow (); newrow2 [  0 ] = "  920  "  ; Newrow2 [  1 ] ="  100  "  ; Newrow2 [  2 ] = "  4000  "  ; Itemdt. Rows. Add (newrow2 );  Return  Itemdt ;}} 

Note:

Excelhelper class: opens the template, calls the method to set the value, and outputs the stream.

Printmanager class: Calls excelhelper, obtains data from the business module, and fills the business data in hssfsheet.

 

Effect

Excel template:

Exported Excel:

Download the demo: nopitest.zip