NPOI copy template export Excel, npoi template excel

NPOI copy template export Excel, npoi template excel

I am a rookie intern, and the company has assigned me an excel export function. Requirements: 1. style requirements for exporting excel files; 2. One or more worksheets for exporting excel files; 3. Functions (my understanding is that the excel Style may be easy to maintain and modify in the future). How can I see the excel template provided by the company?

The excel export function was previously done at school. My first thought was to use Microsoft. office. interop. excel. dll reads the Excel file, but the company's computer does not install the office, there is no corresponding component, I can only find the information in another way, finally decided to use NPOI. What problems are used to solve? What should I do later, at that time, I wanted to write styles by myself, but I stared at the template. After reading the template for a while, I thought that my writing was unrealistic and not easy for later maintenance (in fact, I could write it ). In another way, you can copy the template to produce an intermediate excel file for export. Even if you want to modify the style later, you can modify the template to avoid code tampering. Below is the code

1 /// <summary> 2 /// copy sheet 3 /// </summary> 4 /// <param name = "bjDt"> sheet name set </param> 5 // <param name = "modelfilename"> name of the template attachment </param> 6 // <param name = "tpath"> Generate the file path </param> 7/ // <returns> </returns> 8 public HSSFWorkbook SheetCopy (DataTable bjDt, string modelfilename, out string tpath) 9 {10 string templetfilepath = @ "files \" + modelfilename + ". xls "; // template Excel11 12 tpath = @" files \ download \ "+ modelfilename + DateTime. now. toString ("yyyyMMddHHmmssfff") + ". xls "; // intermediary Excel, which is exported as an intermediary. Avoid changing the module format by using module Excel. 13 FileInfo ff = new FileInfo (tpath); 14 if (ff. exists) 15 {16 ff. delete (); 17} 18 FileStream fs = File. create (tpath); // Create intermediate excel19 HSSFWorkbook x1 = new HSSFWorkbook (); 20 x1.Write (fs); 21 fs. close (); 22 FileStream fileRead = new FileStream (templetfilepath, FileMode. open, FileAccess. read); 23 HSSFWorkbook hssfworkbook = new HSSFWorkbook (fileRead); 24 FileStream fileSave2 = new FileStream (tpath, FileMode. open, FileAccess. read); 25 HSSFWorkbook book2 = new HSSFWorkbook (fileSave2); 26 HSSFWorkbook [] book = new HSSFWorkbook [2] {book2, hssfworkbook}; 27 HSSFSheet CPS = hssfworkbook. getSheet ("Sheet0") as HSSFSheet; // obtain the template sheet28 string rsbh = bjDt. rows [0] ["name"]. toString (); 29 CPS. copyTo (book2, rsbh, true, true); // copy the template sheet to the target sheet30 HSSFSheet sheet = book2.GetSheet (bjDt. rows [0] ["name"]. toString () as HSSFSheet; // obtain the current sheet31 for (int I = 1; I <bjDt. rows. count; I ++) 32 {33 sheet. copySheet (bjDt. rows [I] ["name"]. toString (), true); // copy the sheet to another sheet in the same excel file 34} 35 return book2; 36}

Fill in the data after the mediation excel file is generated

1 /// <summary> 2 /// export the datatable data to excel 3 /// </summary> 4 /// <param name = "bjDt"> sheet name set </param> 5 // <param name = "stuDt"> Fill in data </param> 6 // <param name = "modelfilename"> Template name </param> 7 // <returns> </returns> 8 public string DataTableToExcel (DataTable bjDt, dataTable stuDt, string modelfilename) 9 {10 string path = ""; 11 HSSFWorkbook book2 = SheetCopy (bjDt, modelfilename, out path); 12 for (int j = 0; j <bjDt. rows. count; j ++) 13 {14 HSSFSheet sheets = book2.GetSheet (bjDt. rows [j] ["name"]. toString () as HSSFSheet; 15 sheets. getRow (1 ). getCell (1 ). setCellValue (bjDt. rows [j] ["name"]. toString (); 16 DataRow [] strDt = stuDt. select ("name = '" + bjDt. rows [j] ["name"]. toString () + "'"); // filter data in the corresponding worksheet 17 int rowIndex = 4; 18 for (int I = 0; I <strDt. length; I ++) 19 {20 HSSFRow row0 = sheets. getRow (rowIndex) as HSSFRow; // line 21 HSSFCell cell0 = row0.GetCell (0) as HSSFCell; // column 22 cell0.SetCellValue (strDt [I] ["ID"]. toString (); // data filling 23 rowIndex ++; 24} 25} 26 using (FileStream fileSave = new FileStream (path, FileMode. open, FileAccess. write) 27 {28 book2.Write (fileSave); 29} 30 return path; 31}

Download excel

1 void FileDown (string url) 2 {3 string fileName = "test.xls"; // file name saved by the Client 4 string filePath = url; // Path 5 6 FileInfo fileInfo = new FileInfo (filePath); 7 Response. clear (); 8 Response. clearContent (); 9 Response. clearHeaders (); 10 Response. addHeader ("Content-Disposition", "attachment; filename =" + fileName); 11 Response. addHeader ("Content-Length", fileInfo. length. toString (); 12 Response. addHeader ("Content-Transfer-Encoding", "binary"); 13 Response. contentType = "application/octet-stream"; 14 Response. contentEncoding = System. text. encoding. getEncoding ("gb2312"); 15 Response. writeFile (fileInfo. fullName); 16 Response. flush (); 17 Response. end (); 18}

Excel after export

I am a cainiao. If you have any shortcomings, please forgive me and give me some suggestions.