Npoi export excel, npoiexcel

Npoi export excel, npoiexcel

First, use the Nuget Package Manager that comes with Vs to download and install npoi 2.0. If the NuGet package option is not available, search for NuGet in the menu --> tool --> Extension Manager.

Then write a method to read the content in the DataTable and output it to MemoryStream.

This is my NPOIHelper

1 using System; 2 using System. collections. generic; 3 using System. linq; 4 using System. web; 5 using System. data; 6 using System. IO; 7 using NPOI. HSSF. userModel; 8 using NPOI. HPSF; 9 using System. text; 10 using NPOI. SS. util; 11 using NPOI. SS. userModel; 12 13 /// <summary> 14 /// summary of NPOIHelper 15 /// </summary> 16 public static class NPOIHelper 17 {18 public static HSSFWorkbook; 19 publi C static void CloseWorkBook () 20 {21 workbook = null; 22} 23 public static void OpenWorkBook () {24 workbook = new HSSFWorkbook (); 25} 26 /// <summary> 27 // DataTable export data to the MemoryStream 28 in Excel /// </summary> 29 /// <param name = "dtSource"> Source dataTable </param> 30 // <param name = "strHeaderText"> header text </param> 31 public static void Export (DataTable dtSource, string strHeaderText) {32 33 HSSFSheet she Et = (HSSFSheet) workbook. createSheet (); 34 35 # region right-click File Attribute Information 36 {37 DocumentSummaryInformation dsi = PropertySetFactory. createDocumentSummaryInformation (); 38 dsi. company = "Beijing Communication Technology Co., Ltd."; 39 workbook. documentSummaryInformation = dsi; 40 41 SummaryInformation si = PropertySetFactory. createSummaryInformation (); 42 // si. author = "file Author information"; // Add the xls file Author information 43 // si. applicationName = "create program information"; // Add the xls file Create a program 44 // si. lastAuthor = "Last warranty information"; // Add the last warranty information of the xls file. 45 // si. comments = "author information"; // Add the author information of the xls file. 46/si. title = "Title information"; // Add the xls file Title information 47 // si. subject = "topic information"; // enter the file topic information 48 si. createDateTime = DateTime. now; 49 workbook. summaryInformation = si; 50} 51 # endregion 52 53 HSSFCellStyle dateStyle = (HSSFCellStyle) workbook. createCellStyle (); 54 HSSFDataFormat format = (HSSFDataFormat) workbook. createDat AFormat (); 55 dateStyle. dataFormat = format. getFormat ("yyyy-mm-dd"); 56 57 HSSFCellStyle tdStyle = (HSSFCellStyle) workbook. createCellStyle (); 58 // set the cell border 59 tdStyle. verticalAlignment = verticalignment. CENTER; // vertical CENTER 60 tdStyle. borderBottom = BorderStyle. THIN; 61 tdStyle. borderLeft = BorderStyle. THIN; 62 tdStyle. borderRight = BorderStyle. THIN; 63 tdStyle. borderTop = BorderStyle. THIN; 64 // get the column width 65 Int [] arrColWidth = new int [dtSource. columns. count]; 66 foreach (DataColumn item in dtSource. columns) {67 arrColWidth [item. ordinal] = Encoding. getEncoding (1, 936 ). getBytes (item. columnName. toString ()). length; 68} 69 for (int I = 0; I <dtSource. rows. count; I ++) {70 for (int j = 0; j <dtSource. columns. count; j ++) {71 int intTemp = Encoding. getEncoding (1, 936 ). getBytes (dtSource. rows [I] [j]. toString ()). Length; 72 if (intTemp> arrColWidth [j]) {73 arrColWidth [j] = intTemp; 74} 75} 76} 77 int rowIndex = 0; 78 foreach (DataRow row in dtSource. rows) {79 # region create a table, fill in the table header, fill in the column header, style 80 if (rowIndex = 65535 | rowIndex = 0) {81 if (rowIndex! = 0) {82 sheet = (HSSFSheet) workbook. createSheet (); 83} 84 85 # region header and style 86 {87 HSSFRow headerRow = (HSSFRow) sheet. createRow (0); 88 headerRow. heightInPoints = 25; 89 headerRow. createCell (0 ). setCellValue (strHeaderText); 90 91 HSSFCellStyle headStyle = (HSSFCellStyle) workbook. createCellStyle (); 92 headStyle. alignment = HorizontalAlignment. CENTER; 93 HSSFFont font = (HSSFFont) workbook. createFont (); 94 95 font. fontHeightInPoints = 20; 96 font. boldweight = 700; 97 headStyle. setFont (font); 98 headerRow. getCell (0 ). cellStyle = headStyle; 99 sheet. addMergedRegion (new CellRangeAddress (0, 0, 0, dtSource. columns. count-1); 100 // headerRow. dispose (); 101} 102 # endregion103 104 105 # region column header and style 106 {107 HSSFRow headerRow = (HSSFRow) sheet. createRow (1); 108 HSSFCellStyle headStyle = (HSSFCellStyle) wor Kbook. createCellStyle (); 109 headStyle. alignment = HorizontalAlignment. CENTER; 110 HSSFFont font = (HSSFFont) workbook. createFont (); 111 // set the 112 headStyle cell border. borderBottom = BorderStyle. THIN; 113 headStyle. borderLeft = BorderStyle. THIN; 114 headStyle. borderRight = BorderStyle. THIN; 115 headStyle. borderTop = BorderStyle. THIN; 116 117 font. fontHeightInPoints = 10; 118 font. boldweight = 700; 119 headStyle. se TFont (font); 120 foreach (DataColumn column in dtSource. columns) {121 headerRow. createCell (column. ordinal ). setCellValue (column. columnName); 122 headerRow. getCell (column. ordinal ). cellStyle = headStyle; 123 124 // set the column width to 125 sheet. setColumnWidth (column. ordinal, (arrColWidth [column. ordinal] + 1) * 256); 126} 127 // headerRow. dispose (); 128} 129 # endregion130 131 rowIndex = 2; 132} 133 # endregion134 135 136 # reg Ion filling content 137 HSSFRow dataRow = (HSSFRow) sheet. createRow (rowIndex); 138 foreach (DataColumn column in dtSource. columns) {139 HSSFCell newCell = (HSSFCell) dataRow. createCell (column. ordinal); 140 newCell. cellStyle = tdStyle; 141 142 string drValue = row [column]. toString (); 143 switch (column. dataType. toString () {144 case "System. string ": // String type 145 newCell. setCellValue (drValue); 146 break; 147 case "System. DateTime ": // Date type 148 DateTime dateV; 149 DateTime. tryParse (drValue, out dateV); 150 newCell. setCellValue (dateV); 151 152 newCell. cellStyle = dateStyle; // formatted display 153 break; 154 case "System. boolean ": // Boolean 155 bool boolV = false; 156 bool. tryParse (drValue, out boolV); 157 newCell. setCellValue (boolV); 158 break; 159 case "System. int16 ": // an integer of 160 case" System. int32 ": 161 case" System. int64 & quot;: 162 case & quot; System. byte ": 163 Int intV = 0; 164 int. tryParse (drValue, out intV); 165 newCell. setCellValue (intV); 166 break; 167 case "System. decimal ": // floating point 168 case" System. double ": 169 double doubV = 0; 170 double. tryParse (drValue, out doubV); 171 newCell. setCellValue (doubV); 172 break; 173 case "System. DBNull ": // null value processing 174 newCell. setCellValue (""); 175 break; 176 default: 177 newCell. setCellValue (""); 178 break; 179} 180 181} 182 # endregion1 83 184 rowIndex ++; 185} 186 using (MemoryStream MS = new MemoryStream () {187 workbook. write (MS); 188} 189 190 191 public static MemoryStream Export2 (DataTable dt, string p) {192 Export (dt, p); 193 MemoryStream MS = new MemoryStream (); 194 ISheet sheet = workbook. getSheet ("Sheet1"); 195 int FirstRow = 2; 196 int LastRow = sheet. lastRowNum; 197 int Start = 0; 198 int End = 0; 199 string temp = ""; 200 HSSFCellStyle da TeStyle = (HSSFCellStyle) workbook. createCellStyle (); 201 dateStyle. alignment = HorizontalAlignment. RIGHT; 202 dateStyle. verticalAlignment = verticalignment. CENTER; 203 for (int I = FirstRow; I <LastRow; I ++) {204 for (int j = 1; j <5; j ++) {205 if (j = 1) 206 {207 IRow row = sheet. getRow (I); 208 if (row = null) continue; // The row without data is null209 by default if (row. getCell (j) = null) {continue;} // Similarly, all cells without data are nul by default. L210 ICell cell = row. getCell (j); 211 string cellText = cell. stringCellValue; 212 if (cellText = temp) // the upper and lower rows are equal. The last row of the record to be merged ranges from 213 {214 End = I; 215} 216 else // the upper and lower rows, record 217 {218 if (Start! = End) {219 for (int n = Start; n <End; n ++) {220 ICell tempcell = sheet. getRow (n ). getCell (2); 221 tempcell. setCellValue ("+ (End-Start + 1); 222 tempcell. cellStyle = dateStyle; 223} 224 for (int m = 1; m <5; m ++) {225 CellRangeAddress region = new CellRangeAddress (Start, End, m, m ); 226 sheet. addMergedRegion (region); 227} 228 229} 230 Start = I; 231 End = I; 232 temp = cellText; 233} 234} 235 236} 237 workbook. write (MS); 239 return MS; 240} 241}NPOIHelper

The usage is as follows:

NPOIHelper. OpenWorkBook ();
DataTable dt = economical mirper4.getsumprojectmanager ();
NPOIHelper. Export (dt, "manage"); // Export1 is used to generate a able
DataTable dt2 = economical mirper4.getextensionprojectmanager ();
MemoryStream MS = NPOIHelper. Export2 (dt2, "data"); // Export2 is used to merge Cells
Response. AddHeader ("Content-Disposition", string. Format ("attachment; filename%0%.xls", DateTime. Now. ToString ("yyyyMMddHHmmssfff ")));
Response. BinaryWrite (ms. ToArray ());
Ms. Close ();
Ms. Dispose ();
NPOIHelper. CloseWorkBook ();