【测速】C#使用第三方aspose.cell类库动态导出多表头EXCEL实例

更新时间:2020-01-27    来源:excel    手机版     字体:

【www.bbyears.com--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 columns = new List();
            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 group = new List();
            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
{
    /// 
    /// ** 描述:Aspose
    /// ** 创始时间:2015-9-10
    /// ** 修改时间:-
    /// ** 修改人:sunkaixuan
    /// ** 使用说明:
    /// 
    public class AsposeHelper
    {
        /// 
        /// 导出EXCEL并且动态生成多级表头
        /// 
        /// 列
        /// 分组
        /// dataTable
        /// 保存路径
        public static void SaveColumnsHierarchy(List columns, List 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 acList = new List();
            List acColumngroupHistoryList = new List();
            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().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);
        }
 
        /// 
        /// 导出EXCEL并且动态生成多级表头
        /// 
        /// 列
        /// 分组
        /// dataTable
        /// 保存路径
        public static void SaveColumnsHierarchy(string fileName,List columns, List 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 acList = new List();
            List acColumngroupHistoryList = new List();
            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().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; }
        }
    }
}



C#利用Aspose.Cells组件导入导出excel文件

Aspose.Cells导入excel代码:

public static System.Data.DataTable ReadExcel(String strFileName)
{
    Workbook book = new Workbook();
    book.Open(strFileName);
    Worksheet sheet = book.Worksheets[0];
    Cells cells = sheet.Cells;
    
    return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
}

       
Aspose.Cells导出excel代码:

private static void Export(IEnumerable data, HttpResponse response)
{
    Workbook workbook = new Workbook();
    Worksheet sheet = (Worksheet)workbook.Worksheets[0];                        
    PropertyInfo[] ps = typeof(T).GetProperties();
    var colIndex = "A";
    foreach (var p in ps)
    {
        
            sheet.Cells[colIndex + 1].PutValue(p.Name);
            int i = 2;
            foreach (var d in data)
            {
                sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
                i++;
            }
            colIndex = ((char)(colIndex[0] + 1)).ToString();
    }
    response.Clear();
    response.Buffer = true;
    response.Charset = "utf-8";
    response.AppendHeader("Content-Disposition", "attachment;filename=xxx.xls");
    response.ContentEncoding = System.Text.Encoding.UTF8;
    response.ContentType = "application/ms-excel";
    response.BinaryWrite(workbook.SaveToStream().ToArray());
    response.End();
}


本文来源:http://www.bbyears.com/bangongshuma/84903.html

热门标签

更多>>

本类排行