使用aspose.cell动态导出多表头 EXCEL
效果图：
前台调用：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using ExportCells;
namespace WebApplication1
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
/***********************参数赋值***********************/
//设置列
List<ExportCells.AsposeHelper.JqxTableColumns> columns = new List<ExportCells.AsposeHelper.JqxTableColumns>();
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "id" });
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "name", columngroup = "namesex" });
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "sex", columngroup = "namesex" });
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "id2" });
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "cat", columngroup = "Animal" });
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "dog", columngroup = "Animal" });
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "rabbit", columngroup = "Animal" });
columns.Add(new ExportCells.AsposeHelper.JqxTableColumns() { text = "id3" });
//设置分组
List<ExportCells.AsposeHelper.JqxTableColumnsGroup> group = new List<ExportCells.AsposeHelper.JqxTableColumnsGroup>();
group.Add(new ExportCells.AsposeHelper.JqxTableColumnsGroup() { name = "Animal", text = "动物" });
group.Add(new ExportCells.AsposeHelper.JqxTableColumnsGroup() { name = "namesex", text = "名字性别" });
//设置数据
DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("name");
dt.Columns.Add("sex");
dt.Columns.Add("id2");
dt.Columns.Add("cat");
dt.Columns.Add("dog");
dt.Columns.Add("rabbit");
dt.Columns.Add("id3");
var dr = dt.NewRow();
dr[0] = 0;
dr[1] = 1;
dr[2] = 2;
dr[3] = 3;
dr[4] = 4;
dr[5] = 5;
dr[6] = 6;
dr[7] = 7;
dt.Rows.Add(dr);
var dr2 = dt.NewRow();
dr2[0] = 10;
dr2[1] = 11;
dr2[2] = 12;
dr2[3] = 13;
dr2[4] = 14;
dr2[5] = 15;
dr2[6] = 16;
dr2[7] = 17;
dt.Rows.Add(dr2);
AsposeHelper.SaveColumnsHierarchy("1.xls", columns, group, dt);
}
}
}
ASPOSE封装类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Drawing;
using System.Web;
namespace ExportCells
{
/// <summary>
/// ** 描述：Aspose
/// ** 创始时间：2015-9-10
/// ** 修改时间：-
/// ** 修改人：sunkaixuan
/// ** 使用说明：
/// </summary>
public class AsposeHelper
{
/// <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="dt">dataTable</param>
/// <param name="path">保存路径</param>
public static void SaveColumnsHierarchy(string fileName,List<JqxTableColumns> columns, List<JqxTableColumnsGroup> group, 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, 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);
}
}
}
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();
}
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;
}
}
private static Style _tdStyle
{
get
{
Style s = new Style();
return s;
}
}
public class JqxTableColumns
{
public string field { get; set; }
public string cellsAlign { get; set; }
public string align { get; set; }
public string text { get; set; }
public string columngroup { get; set; }
}
public class JqxTableColumnsGroup
{
public string text { get; set; }
public string align { get; set; }
public string name { get; set; }
}
public class AsposeCellInfo
{
public string text { get; set; }
public int x { get; set; }
public int xCount { get; set; }
public int y { get; set; }
public int yCount { get; set; }
public string groupName { get; set; }
}
}
}
