NPOI export Excel (ASP. net mvc), npoimvc

NPOI export Excel (ASP. net mvc), npoimvc

NPOI is very convenient to export Excel. You can customize the exported Excel Style. Next I will analyze the code of a task I have done in detail.

2. The related code is as follows:

• View page call method:

1 <div class="col-md-1 col-sm-1 clearfix " style="text-align: center; overflow: hidden">2 　　<input type="submit" value="Excel" class="btn bg-purple" formaction="@Url.Action("ListToExcel")"/>3  </div>

 

• Controller code

1 /// <summary> 2 /// export the total printed data to the Excel table 3 // Zheng Xin added 4 in May 12, 2016 /// </summary> 5 /// <param name = "printer"> condition </param> 6 public void ListToExcel (MonitorPrinterViewModel printer) 7 {8 // obtain the var able table 9 var printList = _ monitorPrinterServices. getListToExcel (printer, (int) AuditEnum. sortTypeEnum. sceneryName); 10 // if no data exists, the prompt 11 if (printList = null | printList. rows. count = 0) 12 {13 Response. write ("<s Parent> parent. messenger. options = {extraClasses: 'messenger-fixed messenger-theme-future messenger-on-bottom messenger-on-Right'}; "+ 14" parent. messenger (). post ({message: 'No data, cannot export! ', Type: 'error', showCloseButton: true}); "+ 15" location =' "+ Url. Action (" Index ") +" '+ '? SceneryName = "+ printer. sceneryName + "'+' & printerName =" + printer. printerName + "'+' & startTime =" + printer. startTime + "'+' & endTime =" + printer. endTime + "'+' & PageIndex = 1'; </script>"); 16 return; 17} 18 19 # region new table 20 // obtain the scenic spot name, if it is null, return "All scenic spots" 21 string printName = string. empty; 22 if (! String. isNullOrEmpty (printer. sceneryName) 23 {24 printName = "" + printer. sceneryName + ""; 25} 26 if (string. isNullOrEmpty (printer. sceneryName) 27 {28 printName = "All scenic spots"; 29} 30 HSSFWorkbook book = new NPOI. HSSF. userModel. HSSFWorkbook (); 31 ISheet sheet = book. createSheet ("" + printName + ""); 32 // The first line 33 NPOI. SS. userModel. IRow row = sheet. createRow (0); 34 row. createCell (0 ). setCellValue ("" + pri NtName + ""); 35 // empty column to merge 36 rows of cells. createCell (1 ). setCellValue (""); 37 row. createCell (2 ). setCellValue (""); 38 row. createCell (3 ). setCellValue (""); 39 // The second row. Obtain the time range of 40 DateTime startDate = printList. rows [0] ["StartTime"]. packDateTime (); 41 DateTime endDate = printList. rows [0] ["EndTime"]. packDateTime (); 42 for (int I = 0; I <printList. rows. count; I ++) 43 {44 DateTime dtStart = printList. rows [I] ["St ArtTime "]. packDateTime (); 45 DateTime dtEnd = printList. rows [I] ["EndTime"]. packDateTime (); 46 if (startDate> dtStart) 47 {48 startDate = dtStart; 49} 50 if (endDate <dtEnd) 51 {52 endDate = dtEnd; 53} 54} 55 NPOI. SS. userModel. IRow row2 = sheet. createRow (1); 56 row2.CreateCell (0 ). setCellValue ("" + startDate. toString ("yyyy-MM-dd") + "" + "~ "+" "+ EndDate. toString ("yyyy-MM-dd") + "" + "Print details"); 57 row2.CreateCell (1 ). setCellValue (""); 58 row2.CreateCell (2 ). setCellValue (""); 59 row2.CreateCell (3 ). setCellValue (""); 60 61 // data column name 62 NPOI. SS. userModel. IRow row3 = sheet. createRow (2); 63 row3.CreateCell (0 ). setCellValue ("scenic spot"); 64 row3.CreateCell (1 ). setCellValue ("Bluetooth printer name"); 65 row3.CreateCell (2 ). setCellValue ("time used"); 66 row3.CreateCell (3 ). se TCellValue ("Print quantity"); 67 int count = 0; 68 // Add the Datatable data to table 69 for (int I = 0; I <printList. rows. count; I ++) 70 {71 count + = printList. rows [I] ["TotalTimes"]. packInt (); 72 NPOI. SS. userModel. IRow rows = sheet. createRow (I + 3); 73 rows. createCell (0 ). setCellValue ("" + printList. rows [I] ["MPSceneryName"]. packString () + ""); 74 rows. createCell (1 ). setCellValue ("" + printList. rows [I] ["MPPrinterName"]. pac KString () + ""); 75 rows. createCell (2 ). setCellValue ("" + printList. rows [I] ["StartTime"]. packDateTime (). toString ("yyyy-MM-dd") + "" + "~ "+" "+ PrintList. rows [I] ["EndTime"]. packDateTime (). toString ("yyyy-MM-dd") + ""); 76 rows. createCell (3 ). setCellValue ("" + printList. rows [I] ["TotalTimes"]. packInt () + ""); 77 // Add the border 78 rows. cells [0]. cellStyle = loopStyle (book); 79 rows. cells [1]. cellStyle = loopStyle (book); 80 rows. cells [2]. cellStyle = loopStyle (book); 81 rows. cells [3]. cellStyle = loopStyle (book); 82 // merge 83 if (I> 0) 84 {85 if (printList. rows [I] ["MPSceneryName"]. packString () = printList. rows [I-1] ["MPSceneryName"]. packString () 86 {87 sheet. addMergedRegion (new CellRangeAddress (I + 2, I + 3, 0, 0); 88} 89} 90} 91 // the last row counts the total number of NPOI prints 92. SS. userModel. IRow rowLast = sheet. createRow (printList. rows. count + 3); 93 rowLast. createCell (0 ). setCellValue ("Total number of prints"); 94 rowLast. createCell (1 ). setCellValue (""); 95 rowLast. createCell (2 ). setCellValue (""); 96 rowLast. createCell (3 ). setCellValue ("" + count + ""); 97 // set the style 98 for (int I = 0; I <4; I ++) 99 {100 row. cells [I]. cellStyle = titleStyle (book); 101 row2.Cells [I]. cellStyle = titleStyle (book); 102 row3.Cells [I]. cellStyle = titleStyle (book); 103 row3.Cells [I]. cellStyle. fillForegroundColor = HSSFColor. lightTurquoise. index; 104 rowLast. cells [I]. cellStyle = titleStyle (book); 105 sheet. setColumnWidth (I, 35*256); 106} 107 108 // merge cell 109 sheet. addMergedRegion (new CellRangeAddress (0, 0, 0, 3); 110 sheet. addMergedRegion (new CellRangeAddress (1, 1, 0, 3); 111 sheet. addMergedRegion (new CellRangeAddress (printList. rows. count + 3, printList. rows. count + 3, 0, 2); 112 # endregion113 114 // write to client 115 System. IO. memoryStream MS = new System. IO. memoryStream (); 116 book. write (MS); 117 Response. addHeader ("Content-Disposition", string. format ("attachment; filename=%0%-%1% .xls", printName, DateTime. now. toString ("yyyy-MM-dd"); 118 Response. binaryWrite (ms. toArray (); 119 books = null; 120 ms. close (); 121 ms. dispose (); 122}

 

• Referenced Excel Style code

1 // <summary> 2 // Excel title style, center, bold, background color, border 3 /// </summary> 4 /// <param name = "book"> </param> 5 /// <returns> </returns> 6 public ICellStyle titleStyle (HSSFWorkbook) 7 {8 ICellStyle style = book. createCellStyle (); 9 style. alignment = HorizontalAlignment. center; 10 IFont font = book. createFont (); 11 font. boldweight = short. maxValue; 12 style. setFont (font); 13 style. borderBottom = BorderStyle. thin; 14 style. borderLeft = BorderStyle. thin; 15 style. borderRight = BorderStyle. thin; 16 style. borderTop = BorderStyle. thin; 17 style. fillForegroundColor = 42; 18 style. fillPattern = FillPattern. solidForeground; 19 return style; 20} 21 22 /// <summary> 23 // Excel table data content, border 24 /// </summary> 25 /// <param name = "book"> </param> 26 /// <returns> </returns> 27 public ICellStyle loopStyle (HSSFWorkbook) 28 {29 ICellStyle style = book. createCellStyle (); 30 style. borderBottom = BorderStyle. thin; 31 style. borderLeft = BorderStyle. thin; 32 style. borderRight = BorderStyle. thin; 33 style. borderTop = BorderStyle. thin; 34 style. alignment = HorizontalAlignment. center; 35 style. alignment = HorizontalAlignment. center; 36 return style; 37}

 

3. generated Excel

4. Conclusion

NPOI is a very convenient tool. You only need to clarify the code logic, and you can export a wide range of Excel tables.