/// <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();
}
