你的位置:首页 > ASP.net教程

[ASP.net教程]使用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; }    }  }}