/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> /// <param name="tb"></param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable tb) { tb = tb ?? throw new Exception($"无法把空{nameof(DataTable)}转成Excel"); using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet"); var style = GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index); for (int i = 0; i < tb.Rows.Count; ++i) { var row = sheet.CreateRow(i); for (int j = 0; j < tb.Columns.Count; ++j) { var cell = row.CreateCell(j); var data = tb.Rows[i][j]; cell.SetCellValue(ConvertHelper.GetString(data)); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> public byte[] DataTableToExcel(DataTable tb, bool show_header = true) { tb = tb ?? throw new ArgumentNullException($"无法把空{nameof(DataTable)}转成Excel"); using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsNotEmpty(tb.TableName) ? tb.TableName : "sheet"); var columns = tb.Columns.AsEnumerable_ <DataColumn>(); var row_index = 0; IRow NewRow() => sheet.CreateRow(row_index++); if (show_header) { //头部 var header_style = this.GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.Black.Index, NPOI.HSSF.Util.HSSFColor.White.Index); var header = NewRow(); var cell_index = 0; foreach (var col in columns) { var cell = header.CreateCell(cell_index++, CellType.String); var data = col.ColumnName; cell.SetCellValue(data); cell.CellStyle = header_style; } } var style = this.GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.HSSF.Util.HSSFColor.Black.Index); foreach (var tb_row in tb.Rows.AsEnumerable_ <DataRow>()) { var row = NewRow(); var cell_index = 0; foreach (var tb_col in columns) { var cell = row.CreateCell(cell_index++, CellType.String); var data = tb_row[tb_col.ColumnName]; cell.SetCellValue(ConvertHelper.GetString(data)); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> /// <param name="tb"></param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable tb) { if (tb == null) { return(null); } using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet"); var style = GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.Red.Index, NPOI.HSSF.Util.HSSFColor.White.Index); NPOI.SS.UserModel.IRow row = null; NPOI.SS.UserModel.ICell cell = null; for (int i = 0; i < tb.Rows.Count; ++i) { row = sheet.CreateRow(i); for (int j = 0; j < tb.Columns.Count; ++j) { cell = row.CreateCell(j); cell.SetCellValue(ConvertHelper.GetString(tb.Rows[i][j])); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }