/// <summary> /// 导出EXCEL并且动态生成多级表头 /// </summary> /// <param name="columns">列</param> /// <param name="group">分组</param> /// <param name="dt">dataTable</param> /// <param name="path">保存路径</param> public static void SaveColumnsHierarchy(List <JqxTableColumns> columns, List <JqxTableColumnsGroup> group, DataTable dt, string path) { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 for (int i = 0; i <= dt.Rows.Count + 1; i++) { sheet.Cells.SetRowHeight(i, 30); } List <AsposeCellInfo> acList = new List <AsposeCellInfo>(); List <string> acColumngroupHistoryList = new List <string>(); int currentX = 0; foreach (var it in columns) { AsposeCellInfo ac = new AsposeCellInfo(); ac.y = 0; if (it.columngroup == null) { ac.text = it.text; ac.x = currentX; ac.xCount = 1; acList.Add(ac); currentX++; ac.yCount = 2; } else if (!acColumngroupHistoryList.Contains(it.columngroup))//防止重复 { var sameCount = columns.Where(itit => itit.columngroup == it.columngroup).Count(); ac.text = group.First(itit => itit.name == it.columngroup).text; ac.x = currentX; ac.xCount = sameCount; acList.Add(ac); currentX = currentX + sameCount; acColumngroupHistoryList.Add(it.columngroup); ac.yCount = 1; ac.groupName = it.columngroup; } else { //暂无逻辑 } } //表头 foreach (var it in acList) { cells.Merge(it.y, it.x, it.yCount, it.xCount); //合并单元格 cells[it.y, it.x].PutValue(it.text); //填写内容 cells[it.y, it.x].SetStyle(_thStyle); if (!string.IsNullOrEmpty(it.groupName)) { var cols = columns.Where(itit => itit.columngroup == it.groupName).ToList(); foreach (var itit in cols) { var colsIndex = cols.IndexOf(itit); cells[it.y + 1, it.x + colsIndex].PutValue(itit.text);//填写内容 cells[it.y + 1, it.x + colsIndex].SetStyle(_thStyle); } } } //表格 if (dt != null && dt.Rows.Count > 0) { var rowList = dt.AsEnumerable().ToList(); foreach (var it in rowList) { int dtIndex = rowList.IndexOf(it); var dtColumns = dt.Columns.Cast <DataColumn>().ToList(); foreach (var itit in dtColumns) { var dtColumnsIndex = dtColumns.IndexOf(itit); cells[2 + dtIndex, dtColumnsIndex].PutValue(it[dtColumnsIndex]); cells[2 + dtIndex, dtColumnsIndex].SetStyle(_tdStyle); } } } workbook.Save(path); }
/// <summary> /// 导出EXCEL并且动态生成多级表头 /// </summary> /// <param name="columns">列</param> /// <param name="group">分组</param> /// <param name="replaceDic">需替换的列名称</param> /// <param name="mergeCols">需合并单元格的规则</param> /// <param name="dt">dataTable</param> public static void SaveColumnsHierarchy(string fileName, List <JqxTableColumns> columns, List <JqxTableColumnsGroup> group, Dictionary <string, string> replaceDic, Dictionary <string, string> mergeCols, DataTable dt) { Workbook workbook = new Workbook(); //工作簿 Worksheet sheet = workbook.Worksheets[0]; //工作表 Cells cells = sheet.Cells; //单元格 for (int i = 0; i <= dt.Rows.Count + 1; i++) { sheet.Cells.SetRowHeight(i, 20); } List <AsposeCellInfo> acList = new List <AsposeCellInfo>(); List <string> acColumngroupHistoryList = new List <string>(); int currentX = 0; foreach (var it in columns) { AsposeCellInfo ac = new AsposeCellInfo(); ac.y = 0; if (it.columngroup == null) { ac.text = it.text; ac.x = currentX; ac.xCount = 1; acList.Add(ac); currentX++; ac.yCount = 2; } else if (!acColumngroupHistoryList.Contains(it.columngroup))//防止重复 { var sameCount = columns.Where(itit => itit.columngroup == it.columngroup).Count(); ac.text = group.First(itit => itit.name == it.columngroup).text; ac.x = currentX; ac.xCount = sameCount; acList.Add(ac); currentX = currentX + sameCount; acColumngroupHistoryList.Add(it.columngroup); ac.yCount = 1; ac.groupName = it.columngroup; } else { //暂无逻辑 } } //表头 foreach (var it in acList) { cells.Merge(it.y, it.x, it.yCount, it.xCount); //合并单元格 cells[it.y, it.x].PutValue(it.text); //填写内容 cells[it.y, it.x].SetStyle(_thStyle); if (!string.IsNullOrEmpty(it.groupName)) { var cols = columns.Where(itit => itit.columngroup == it.groupName).ToList(); foreach (var itit in cols) { var colsIndex = cols.IndexOf(itit); cells[it.y + 1, it.x + colsIndex].PutValue(itit.text);//填写内容 cells[it.y + 1, it.x + colsIndex].SetStyle(_thStyle); } } } //表格 if (dt != null && dt.Rows.Count > 0) { var rowList = dt.AsEnumerable().ToList(); foreach (var it in rowList) { int dtIndex = rowList.IndexOf(it); var dtColumns = dt.Columns.Cast <DataColumn>().ToList(); foreach (var itit in dtColumns) { var dtColumnsIndex = dtColumns.IndexOf(itit); cells[2 + dtIndex, dtColumnsIndex].PutValue(it[dtColumnsIndex]); cells[2 + dtIndex, dtColumnsIndex].SetStyle(_tdStyle); } } } // 合并单元格 if (mergeCols.Any()) { GenerateExcelGridContent(cells, dt, mergeCols); } // 替换列名称 if (replaceDic.Any()) { foreach (var item in replaceDic) { workbook.Replace(item.Key, item.Value); } } // 自动伸缩列 sheet.AutoFitColumns(); // 自动合并行 //sheet.AutoFitRows(); var response = HttpContext.Current.Response; response.Clear(); response.Buffer = true; response.Charset = "utf-8"; response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); response.ContentEncoding = System.Text.Encoding.UTF8; response.ContentType = "application/ms-excel"; response.BinaryWrite(workbook.SaveToStream().ToArray()); response.End(); }