1.支持导出多个sheet文件
1 /// <summary> 2 /// 导出到Excel并下载（html） 3 /// </summary> 4 /// <param name="tablels">需要导出的Excel表集合</param> 5 public static void ToExcel(List<DataTable> tablels, string fileName = "") 6 { 7 8 if (fileName == "") 9 { 10 fileName = "数据导出汇总-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称 11 } 12 string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址，供给前台下载 13 14 string filePath = HttpContext.Current.Server.MapPath("\" + urlPath); // 文件路径 15 16 string directoryName = Path.GetDirectoryName(filePath); 17 18 if (!Directory.Exists(directoryName)) 19 { 20 Directory.CreateDirectory(directoryName); 21 } 22 IWorkbook workBook = new HSSFWorkbook(); 23 24 foreach (DataTable table in tablels) 25 { 26 27 string sheetName = table.TableName; 28 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); 29 sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName; 30 ISheet sheet = workBook.CreateSheet(sheetName); 31 32 //处理表格标题 33 IRow row = sheet.CreateRow(0); 34 row.CreateCell(0).SetCellValue(sheetName); 35 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1)); 36 row.Height = 500; 37 38 ICellStyle cellStyle = workBook.CreateCellStyle(); 39 IFont font = workBook.CreateFont(); 40 font.FontName = "微软雅黑"; 41 font.FontHeightInPoints = 17; 42 cellStyle.SetFont(font); 43 cellStyle.VerticalAlignment = VerticalAlignment.Center; 44 cellStyle.Alignment = HorizontalAlignment.Center; 45 row.Cells[0].CellStyle = cellStyle; 46 47 //处理表格列头 48 row = sheet.CreateRow(1); 49 for (int i = 0; i < table.Columns.Count; i++) 50 { 51 row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName); 52 row.Height = 350; 53 sheet.AutoSizeColumn(i); 54 } 55 56 //处理数据内容 57 for (int i = 0; i < table.Rows.Count; i++) 58 { 59 row = sheet.CreateRow(2 + i); 60 row.Height = 250; 61 for (int j = 0; j < table.Columns.Count; j++) 62 { 63 row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString()); 64 sheet.SetColumnWidth(j, 256 * 15); 65 } 66 } 67 //写入数据流 68 workBook.Write(fs); 69 fs.Flush(); 70 fs.Close(); 71 } 72 // 3.进行Excel转换操作，并返回转换的文件下载链接 73 System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer(); 74 HttpContext.Current.Response.ContentType = "text/plain"; 75 HttpContext.Current.Response.Write(js.Serialize(urlPath)); // 返回Json格式的内容 76 }
2.只支持一个sheet文件
1 /// <summary> 2 /// 导出到Excel并下载（html） 3 /// </summary> 4 /// <param name="table">数据源</param> 5 /// <param name="title">标题</param> 6 /// <param name="sheetName">sheetName名称</param> 7 /// <param name="fileName">路径</param> 8 public static void ToExcel(DataTable table, string title, string sheetName, string fileName = "") 9 { 10 if (fileName == "") 11 { 12 fileName = sheetName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称 13 } 14 string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址，供给前台下载 15 16 string filePath = HttpContext.Current.Server.MapPath("\" + urlPath); // 文件路径 17 18 string directoryName = Path.GetDirectoryName(filePath); 19 20 if (!Directory.Exists(directoryName)) 21 { 22 Directory.CreateDirectory(directoryName); 23 } 24 25 FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); 26 IWorkbook workBook = new HSSFWorkbook(); 27 sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName; 28 ISheet sheet = workBook.CreateSheet(sheetName); 29 30 //处理表格标题 31 IRow row = sheet.CreateRow(0); 32 row.CreateCell(0).SetCellValue(title); 33 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1)); 34 row.Height = 500; 35 36 ICellStyle cellStyle = workBook.CreateCellStyle(); 37 IFont font = workBook.CreateFont(); 38 font.FontName = "微软雅黑"; 39 font.FontHeightInPoints = 17; 40 cellStyle.SetFont(font); 41 cellStyle.VerticalAlignment = VerticalAlignment.Center; 42 cellStyle.Alignment = HorizontalAlignment.Center; 43 row.Cells[0].CellStyle = cellStyle; 44 45 //处理表格列头 46 row = sheet.CreateRow(1); 47 for (int i = 0; i < table.Columns.Count; i++) 48 { 49 row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName); 50 row.Height = 350; 51 sheet.AutoSizeColumn(i); 52 } 53 54 //处理数据内容 55 for (int i = 0; i < table.Rows.Count; i++) 56 { 57 row = sheet.CreateRow(2 + i); 58 row.Height = 250; 59 for (int j = 0; j < table.Columns.Count; j++) 60 { 61 row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString()); 62 sheet.SetColumnWidth(j, 256 * 15); 63 } 64 } 65 66 //写入数据流 67 workBook.Write(fs); 68 fs.Flush(); 69 fs.Close(); 70 71 // 3.进行Excel转换操作，并返回转换的文件下载链接 72 System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer(); 73 HttpContext.Current.Response.ContentType = "text/plain"; 74 HttpContext.Current.Response.Write(js.Serialize(urlPath)); // 返回Json格式的内容 75 }