使用Aspose.Cells制作三层表头
今天,需要实现一个功能,就是使用Aspose.Cells制作三层表头,我当时百度了一下,大部分都是制作两层表头,于是我在这个基础上,花了点时间制作了一个可以做两层表头也可以制作三层表头的方法,参考的地址是http://www.mamicode.com/info-detail-1033038.html
具体代码如下:
//列的分组 public class ColumnGroup { //分组的文本 public string text { get; set; } //分组的对齐方式 public string align { get; set; } //分组的名称 public string name { get; set; } } //列的管理 public class ColumnsManage { //列的ID public string fieid { get; set; } //单元格的对齐方式 public string cellsAlign { get; set; } //列的对齐方式 public string align { get; set; } //列的文本 public string text { get; set; } //列的所属分组(关联ColumnGroup中的name) public string columngroup { get; set; } } //单元格管理 public class CellManage { //单元格的文本 public string text { get; set; } //单元格所属x轴 public int x { get; set; } //单元格在x轴的数量 public int xCount { get; set; } //单元格所属y轴 public int y { get; set; } //单元格在y轴的数量 public int yCount { get; set; } //分组名称 public string GroupName { get; set; } }
首先呢先要创建好上面三个类,等下需要用到的,思路我可以给大家大致理一下:
大致上就是你的表头占据的几行几列,现将其合并单元格,然后再往合并单元格之后的单元格填充数据。
public class AsposeHelper { #region 成功例子 //判断分组的名称出现了几次,方便后面合并单元格 private static int AppearCount(List<string> columns, string text) { int i = 0; foreach (var item in columns) { if (item.Contains(text)) { i++; } } return i; } //th的样式设置 private static Style _thStyle { get { Style s = new Style(); s.Font.IsBold = true; s.Font.Name = "宋体"; s.Font.Color = Color.Black; s.HorizontalAlignment = TextAlignmentType.Center; //标题居中对齐 return s; } } //td的样式设置 private static Style _tdStyle { get { Style s = new Style(); return s; } } /// <summary> /// 将所给数据导出成多层表头(该方法目前只适用于两层表头以及三层表头,后期还需要完善该方法。) /// </summary> /// <param name="columns">列的类</param> /// <param name="group">分组的类</param> /// <param name="dt">所给的表格数据</param> /// <param name="path">所给路径</param> /// <param name="tableheadcount">需要导出的表头数</param> public static void SelectTableCountToExportExcel(List<ColumnsManage> columns, List<ColumnGroup> group, DataTable dt, string path, int tableheadcount) { #region 设置好基本参数 //工作簿 Workbook workbook = new Workbook(); //工作表 Worksheet sheet = workbook.Worksheets[0]; //单元格 Cells cell = sheet.Cells; //首先设置单元格的行高 for (int i = 0; i < dt.Rows.Count + 1; i++) { cell.SetRowHeight(i, 30); } //操作单元格的类 List<CellManage> cellsList = new List<CellManage>(); //将所有存在的分组存储在泛型中,方便好判断 List<string> ColumnGroupList = new List<string>(); //将存在的分组再次存储在泛型中,但是要去除重复的值 List<string> WipeRepeatGroup = new List<string>(); #endregion #region 将列当中的数据遍历至单元格数组中 #region 操控最下面的表头 int CurrentX = 0; foreach (var item in columns) { //用于操作单元格的类,以下所有操作都是往该类追加数据,方便后面合并以及填充单元格。 CellManage cellds = new CellManage(); //判断是否存在分组,如果不存在,则说明该列的数据是可以直接填充至单元格中 if (item.columngroup == null) { cellds.text = item.text; cellds.x = CurrentX; cellds.y = 0; cellds.xCount = 1; cellds.yCount = tableheadcount; cellsList.Add(cellds); CurrentX++; } //如果存在分组,就需要判断该分组是属于第几层表头 else if (item.columngroup != null) { //将分组用,分割,所取出来的数组长度就等于第几层表头 string[] belonggroup = item.columngroup.Split(','); //如果数组的长度等同于所给表头参数-1,即tableheadcount-1,则说明该表头就是第tableheadcount表头。 if (belonggroup.Length == tableheadcount) { cellds.text = item.text; cellds.x = CurrentX; cellds.y = belonggroup.Length - 1; cellds.xCount = 1; cellds.yCount = 1; cellds.GroupName = belonggroup[belonggroup.Length - 1]; cellsList.Add(cellds); CurrentX = CurrentX + 1; for (int i = belonggroup.Length - 1; i <= belonggroup.Length; i--) { if (i == 0) { break; } ColumnGroupList.Add(belonggroup[i - 1]); } } } else { //暂无逻辑 } } #endregion #region 操控其他表头 for (int i = 2; i <= tableheadcount; i++) { int CurrentXX = 1; //用于操作单元格的类,以下所有操作都是往该类追加数据,方便后面合并以及填充单元格。 foreach (var other in columns) { CellManage cellds = new CellManage(); if (other.columngroup != null) { string[] belonggroup = other.columngroup.Split(','); if (!WipeRepeatGroup.Contains(belonggroup[belonggroup.Length - i])) { int samecount = AppearCount(ColumnGroupList, belonggroup[belonggroup.Length - i]); cellds.text = group.First(it => it.name == belonggroup[belonggroup.Length - i]).text; cellds.x = CurrentXX; cellds.y = tableheadcount - i; cellds.xCount = samecount; cellds.yCount = 1; cellsList.Add(cellds); CurrentXX = CurrentXX + samecount; WipeRepeatGroup.Add(belonggroup[belonggroup.Length - i]); } } } CurrentXX = 0; } #endregion #region 最后将所有已经设置好的单元格,按照已有参数合并以及填充单元格 foreach (var item in cellsList) { cell.Merge(item.y, item.x, item.yCount, item.xCount); cell[item.y, item.x].PutValue(item.text); cell[item.y, item.x].SetStyle(_thStyle); } #endregion #region 表格 //根据现有数据生成表格 if (dt != null && dt.Rows.Count > 0) { var rowList = dt.AsEnumerable().ToList(); foreach (var item in rowList) { int dtIndex = rowList.IndexOf(item); //将查询到列的集合的数据类型转换为DataColumn的类型 var dtColumns = dt.Columns.Cast<DataColumn>().ToList(); foreach (var i in dtColumns) { var dtColumnsIndex = dtColumns.IndexOf(i); cell[tableheadcount + dtIndex, dtColumnsIndex].PutValue(item[dtColumnsIndex]); cell[tableheadcount + dtIndex, dtColumnsIndex].SetStyle(_tdStyle); } } } #endregion #region 导出Excel表格 workbook.Save(path); #endregion #endregion } #endregion }
private void BtnExcel_Click(object sender, EventArgs e) { //1.先设置好需要显示的列 List<ColumnsManage> columns = new List<ColumnsManage>(); columns.Add(new ColumnsManage() { text = "乔丹时代最佳阵容" }); columns.Add(new ColumnsManage() { text = "埃尔文·约翰逊", columngroup = "PG,First," }); columns.Add(new ColumnsManage() { text = "约翰·斯托克顿", columngroup = "PG,First," }); columns.Add(new ColumnsManage() { text = "伊塞亚·托马斯", columngroup = "PG,Second," }); columns.Add(new ColumnsManage() { text = "加里·佩顿", columngroup = "PG,Second," }); columns.Add(new ColumnsManage() { text = "迈克尔·乔丹", columngroup = "SG,Third," }); columns.Add(new ColumnsManage() { text = "克莱德·德雷克斯勒", columngroup = "SG,Fourth," }); columns.Add(new ColumnsManage() { text = "拉里·伯德", columngroup = "SF,Fifth," }); columns.Add(new ColumnsManage() { text = "多米尼克·威尔金斯", columngroup = "SF,Sixth," }); columns.Add(new ColumnsManage() { text = "丹尼斯·罗德曼", columngroup = "PF,Seventh," }); columns.Add(new ColumnsManage() { text = "肖恩·坎普", columngroup = "PF,Eighth," }); columns.Add(new ColumnsManage() { text = "哈基姆·奥拉朱旺", columngroup = "C,Ninth," }); columns.Add(new ColumnsManage() { text = "沙奎尔·奥尼尔", columngroup = "C,Tenth," }); columns.Add(new ColumnsManage() { text = "后乔丹时代最佳阵容" }); //2.再设置好需要分的组 List<ColumnGroup> group = new List<ColumnGroup>(); group.Add(new ColumnGroup() { name = "PG", text = "控球后卫" }); group.Add(new ColumnGroup() { name = "SG", text = "得分后卫" }); group.Add(new ColumnGroup() { name = "SF", text = "小前锋" }); group.Add(new ColumnGroup() { name = "PF", text = "大前锋" }); group.Add(new ColumnGroup() { name = "C", text = "中锋" }); group.Add(new ColumnGroup() { name = "First", text = "第一阵容"}); group.Add(new ColumnGroup() { name = "Second", text = "第二阵容"}); group.Add(new ColumnGroup() { name = "Third", text = "第一阵容"}); group.Add(new ColumnGroup() { name = "Fourth", text = "第二阵容"}); group.Add(new ColumnGroup() { name = "Fifth", text = "第一阵容"}); group.Add(new ColumnGroup() { name = "Sixth", text = "第二阵容"}); group.Add(new ColumnGroup() { name = "Seventh", text = "第一阵容"}); group.Add(new ColumnGroup() { name = "Eighth", text = "第二阵容"}); group.Add(new ColumnGroup() { name = "Ninth", text = "第一阵容"}); group.Add(new ColumnGroup() { name = "Tenth", text = "第二阵容"}); //3.再将数据填充 DataTable dt = new DataTable(); dt.Columns.Add("乔丹时代最佳阵容"); dt.Columns.Add("埃尔文·约翰逊"); dt.Columns.Add("约翰·斯托克顿"); dt.Columns.Add("伊塞亚·托马斯"); dt.Columns.Add("加里·佩顿"); dt.Columns.Add("迈克尔·乔丹"); dt.Columns.Add("克莱德·德雷克斯勒"); dt.Columns.Add("拉里·伯德"); dt.Columns.Add("多米尼克·威尔金斯"); dt.Columns.Add("丹尼斯·罗德曼"); dt.Columns.Add("肖恩·坎普"); dt.Columns.Add("哈基姆·奥拉朱旺"); dt.Columns.Add("沙奎尔·奥尼尔"); dt.Columns.Add("后乔丹时代最佳阵容"); var dr = dt.NewRow(); dr[0] = 1; dr[1] = 2; dr[2] = 3; dr[3] = 4; dr[4] = 5; dr[5] = 6; dr[6] = 7; dr[7] = 8; dr[8] = 9; dr[9] = 10; dr[10] = 11; dr[11] = 12; dr[12] = 13; dr[13] = 14; dt.Rows.Add(dr); var dr2 = dt.NewRow(); dr2[0] = 15; dr2[1] = 16; dr2[2] = 17; dr2[3] = 18; dr2[4] = 19; dr2[5] = 20; dr2[6] = 21; dr2[7] = 22; dr2[8] = 23; dr2[9] = 24; dr2[10] = 25; dr2[11] = 26; dr2[12] = 27; dr2[13] = 28; dt.Rows.Add(dr2); AsposeHelper.SelectTableCountToExportExcel(columns, group, dt, "测试数据.xls", 3); }
当时,是想做成多层表头的,但是无奈思路有限,没有太好的方法改进,如果有大神能完善一下,也是可以的,开发这一行就是要互相学习互相进步。