自定义EXCEL导出格式
在项目中经常会用到这种情况:前台通过easyui自定义页表格展示,然后再通过excel导出到本地。
项目中用的最多的就是多级表头的情况,在这里markI一下。
1、自定义列对象
1 public class EasyUIDataColumn 2 { 3 4 public string field { get; set; } 5 6 private int _colspan = 1; 7 private int _rowspan = 1; 8 9 10 public string align = "left"; 11 public int colspan 12 { 13 get { return _colspan; } 14 set { _colspan = value; } 15 } 16 public int rowspan 17 { 18 get { return _rowspan; } 19 set { _rowspan = value; } 20 } 21 22 public string title; 23 private int _width; 24 25 public int width 26 { 27 get 28 { 29 if (_width == 0) 30 { 31 if (DicEasyUIColumnWidth.ContainsKey(title)) { return DicEasyUIColumnWidth[title]; } 32 return 20 * _colspan * (Encoding.Default.GetBytes(title).Length / 2); 33 } 34 else 35 { 36 return _width; 37 } 38 } 39 set 40 { 41 _width = value; 42 } 43 44 } 45 46 47 public static readonly char EasyUIColumnSplit = '_'; 48 49 } 50 }
2.通过从数据库中得到的DataTable数据生成对应的List<List<EasyUIDataColumn>>对象
1 public List<List<EasyUIDataColumn>> GetEasyUIDataColumnList(string testID, DataTable dt, int frozenColumns = -1, bool isGetFrozenColumns = false) 2 { 3 List<List<EasyUIDataColumn>> arrEasyDataColumnList = new List<List<EasyUIDataColumn>>(); 4 List<EasyUIDataColumn> EasyDataColumnList = new List<EasyUIDataColumn>(); 5 List<EasyUIDataColumn> TitleColumnList = new List<EasyUIDataColumn>(); 6 for (int i = 0; i < dt.Columns.Count; i++) 7 { 8 EasyUIDataColumn easyDataColumn = new EasyUIDataColumn(); 9 easyDataColumn.width = 100; 10 if (isGetFrozenColumns) 11 { 12 if (i <= frozenColumns) 13 { 14 EasyUIDataColumn titleColumn = new EasyUIDataColumn(); 15 titleColumn.rowspan = 2; 16 titleColumn.title = dt.Columns[i].ColumnName; 17 titleColumn.field = dt.Columns[i].ColumnName; 18 TitleColumnList.Add(titleColumn); 19 continue; 20 } 21 } 22 else 23 { 24 if (i <= frozenColumns) 25 { 26 continue; 27 } 28 } 29 30 if (i == 2||i==6) 31 { 32 EasyUIDataColumn titleColumn = new EasyUIDataColumn(); 33 string[] columnName = dt.Columns[i].ColumnName.Split(EasyUIDataColumn.EasyUIColumnSplit); 34 titleColumn.colspan = 4; 35 titleColumn.title = columnName[0]; 36 titleColumn.field = ""; 37 TitleColumnList.Add(titleColumn); 38 } 39 40 41 if (dt.Columns[i].ColumnName.IndexOf(EasyUIDataColumn.EasyUIColumnSplit) > -1) 42 { 43 string[] columnName = dt.Columns[i].ColumnName.Split(EasyUIDataColumn.EasyUIColumnSplit); 44 easyDataColumn.title = columnName[1]; 45 easyDataColumn.field = dt.Columns[i].ColumnName; 46 easyDataColumn.align = "center"; 47 } 48 else 49 { 50 easyDataColumn.title = dt.Columns[i].ColumnName; 51 easyDataColumn.field = dt.Columns[i].ColumnName; 52 easyDataColumn.align = "center"; 53 } 54 EasyDataColumnList.Add(easyDataColumn); 55 } 56 arrEasyDataColumnList.Add(TitleColumnList); 57 arrEasyDataColumnList.Add(EasyDataColumnList); 58 return arrEasyDataColumnList; 59 }
3.根据自定义的表格式生成excel
1 /// <summary> 2 /// 将DataTable数据导出到Excel文件中(xlsx),自定义列头 3 /// </summary> 4 /// <param name="dt"></param> 5 /// <param name="file"></param> 6 /// <param name="columnList">列集合</param> 7 /// <param name="frozenClounms">冻结列 或者 列表开始rowspan=2的列数 </param> 8 public void ExportDataTableToExcelForXLSXByCustomColumn(DataTable dt, string file, 9 List<List<EasyUIDataColumn>> columnList, int frozenClounms) 10 { 11 var xssfworkbook = new XSSFWorkbook(); 12 ICellStyle styleColName = xssfworkbook.CreateCellStyle(); 13 styleColName.Alignment = HorizontalAlignment.Center; 14 styleColName.VerticalAlignment = VerticalAlignment.Center; 15 var sheet = xssfworkbook.CreateSheet(dt.TableName); //sheet名 16 var firstrow = 0; 17 var lastrow = 0; 18 var firstcol = 0; 19 var lastcol = 0; 20 //表头 21 for (var i = 0; i < columnList.Count; i++) 22 { 23 var sumColSpan = 0; 24 25 for (var j = 0; j < columnList[i].Count; j++) 26 { 27 int tempRowSpan = columnList[i][j].rowspan == 0 ? 1 : columnList[i][j].rowspan; 28 firstrow = i; 29 lastrow = i + tempRowSpan - 1; 30 31 int tempColSpan = columnList[i][j].colspan == 0 ? 1 : columnList[i][j].colspan; 32 firstcol = j + sumColSpan; 33 lastcol = j + sumColSpan + tempColSpan - 1; 34 sumColSpan += (tempColSpan - 1); 35 36 MergeColumn(sheet, firstrow, lastrow, firstcol, lastcol, columnList[i][j].title, styleColName, frozenClounms); 37 } 38 } 39 40 sheet.SetColumnWidth(0, 15 * 256); 41 //写入数据 42 for (var i = 0; i < dt.Rows.Count; i++) 43 { 44 var row1 = sheet.CreateRow(i + 2); 45 for (var j = 0; j < dt.Columns.Count; j++) 46 { 47 var cell = row1.CreateCell(j); 48 if (IsNumeric(dt.Rows[i][j].ToString())) 49 { 50 cell.SetCellType(CellType.Numeric); 51 cell.SetCellValue(Convert.ToDouble(dt.Rows[i][j])); 52 } 53 else 54 { 55 cell.SetCellValue(dt.Rows[i][j].ToString()); 56 } 57 } 58 } 59 60 //转为字节数组 61 var stream = new MemoryStream(); 62 xssfworkbook.Write(stream); 63 var buf = stream.ToArray(); 64 65 //保存为Excel文件 66 using (var fs = new FileStream(file, FileMode.Create, FileAccess.Write)) 67 { 68 fs.Write(buf, 0, buf.Length); 69 fs.Close(); 70 } 71 } 72 73 private void MergeColumn(ISheet sheet, int firstrow, int lastrow, int firstcol, int lastcol, string value, ICellStyle cellStyle, int frozenClounms) 74 { 75 //第二行,全部往前进frozenClounms个列 76 if (firstrow == 1) 77 { 78 firstcol += frozenClounms; 79 lastcol += frozenClounms; 80 } 81 for (var i = firstrow; i <= lastrow; i++) 82 { 83 for (var j = firstcol; j <= lastcol; j++) 84 { 85 if (j == firstcol) 86 { 87 88 LoadCell(sheet, i, j, cellStyle).SetCellValue(value); 89 } 90 else 91 { 92 LoadCell(sheet, i, j).SetCellValue(""); 93 } 94 } 95 } 96 sheet.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, firstcol, lastcol)); //合并单元格 97 } 98 private ICell LoadCell(ISheet sheet1, int rowIndex, int cellIndex, ICellStyle cellStyle = null) 99 { 100 var row = sheet1.GetRow(rowIndex) ?? sheet1.CreateRow(rowIndex); 101 102 var cell = row.GetCell(cellIndex); 103 if (cell == null) 104 { 105 cell = sheet1.GetRow(rowIndex).CreateCell(cellIndex); 106 } 107 cell.CellStyle = cellStyle; 108 return cell; 109 } 110