Use NPOI from DataBase to Excel '2, npoidatabaseexcel
Npoi c # Helper Code 2
1 public static MemoryStream ExportXls (DataTable dt) 2 {3 HSSFWorkbook wk = new HSSFWorkbook (); 4 ISheet sheet = null; 5 6 string sheetName = "Sheet1"; 7 if (! String. isNullOrEmpty (dt. tableName) 8 {9 sheetName = dt. tableName; 10} 11 sheet = wk. createSheet (sheetName); 12 // column header and style 13 IRow headerRow = sheet. createRow (0); 14 ICellStyle headStyle = wk. createCellStyle (); 15 headStyle. alignment = HorizontalAlignment. center; 16 17 IFont font = wk. createFont (); 18 font. fontHeightInPoints = 10; 19 font. boldweight = 700; 20 font. fontName = ""; 21 headStyle. setFont (font); 22 23 foreach (DataColumn column in dt. columns) // column total attribute 24 {25 headerRow. createCell (column. ordinal ). setCellValue (column. caption); // clomun. columnName26 headerRow. getCell (column. ordinal ). cellStyle = headStyle; // experience 27} 28 29 int rowIndex = 1; 30 foreach (DataRow row in dt. rows) 31 {32 // HSSFRow dataRow = (HSSFRow) sheet. createRow (rowIndex); // You can also write 33 IRow dataRow = sheet. createRow (rowIndex); 34 foreach (DataColumn column in dt. columns) 35 {36 dataRow. createCell (column. ordinal ). setCellValue (row [column]. toString (); 37} 38 rowIndex ++; 39} 40 MemoryStream MS = new MemoryStream (); 41 wk. write (ms); 42 ms. flush (); 43 return MS; 44} 45 46 // output 47 public static void SaveToFile (MemoryStream MS) 48 {49 using (FileStream fs = File. openWrite (@ "c: \ 1.xls") 50 {51 byte [] data = ms. toArray (); 52 fs. write (data, 0, data. length); 53 fs. flush (); 54 data = null; 55} 56} 57 public static void SaveToFile (MemoryStream MS, string filePath) 58 {59 using (FileStream fs = new FileStream (filePath, FileMode. create, FileAccess. write) 60 {61 byte [] data = ms. toArray (); 62 fs. write (data, 0, data. length); 63 fs. flush (); 64 data = null; 65} 66}
View Code