Aspoce.cells 导出Excel

aspoce.cells 要用破解版本的，不然导出的Excel 有版权信息

 

有格式的导出：【超大量数据相对没格式的会有点慢】

/// <summary>
        /// 利用aspoce.cells 导出 DataTable
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        /// <param name="page"></param>
        public static void AsposeCellToExcel(DataTable dt, string fileName, System.Web.UI.Page page, string[] colFields)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = (Worksheet)workbook.Worksheets[0];
            Cells cells = sheet.Cells;//单元格 

            Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式 
            style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中 
            style2.Font.Name = "宋体";//文字字体 
            style2.Font.Size = 12;//文字大小 
            style2.Font.IsBold = true;//粗体             
            style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

            Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()];//新增样式              
            style3.IsTextWrapped = true;//单元格内容自动换行 
            style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            //生成行 列名行 
            for (int i = 0; i < colFields.Length; i++)
            {
                if (colFields[i] == "")
                    continue;
                cells[0, i].PutValue(colFields[i]);
                cells[0, i].SetStyle(style2);
                cells.SetRowHeight(1, 24);
            }
            //生成数据行 
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int k = 0; k < colFields.Length; k++)
                {
                    cells[1 + i, k].PutValue(dt.Rows[i][k].ToString());
                    cells[1 + i, k].SetStyle(style3);
                }
                cells.SetRowHeight(1 + i, 22);
            }
            sheet.AutoFitColumns();

            MemoryStream stream2 = new MemoryStream();
            workbook.Save(stream2, Aspose.Cells.SaveFormat.Xlsx);
            byte[] byte2 = stream2.ToArray();

            page.Response.Clear();
            page.Response.Buffer = true;
            page.Response.Charset = "utf-8";
            page.Response.AppendHeader("Content-Disposition", "attachment;filename=List.xlsx");
            page.Response.AppendHeader("Content-Length", byte2.Length.ToString());
            page.Response.ContentEncoding = System.Text.Encoding.UTF8;
            page.Response.ContentType = "application/ms-excel";
            page.Response.BinaryWrite(byte2);
            page.Response.End();

        }

无格式导出：速度快

public static void AsposeCellToExcel(DataTable dt, string fileName, System.Web.UI.Page page)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = (Worksheet)workbook.Worksheets[0];
            sheet.Cells.ImportDataTable(dt, true, 0, 0);//导入表格

            MemoryStream stream2 = new MemoryStream();
            workbook.Save(stream2, Aspose.Cells.SaveFormat.Xlsx);
            byte[] byte2 = stream2.ToArray();

            page.Response.Clear();
            page.Response.Buffer = true;
            page.Response.Charset = "utf-8";
            page.Response.AppendHeader("Content-Disposition", "attachment;filename=List.xlsx");
            page.Response.AppendHeader("Content-Length", byte2.Length.ToString());
            page.Response.ContentEncoding = System.Text.Encoding.UTF8;
            page.Response.ContentType = "application/ms-excel";
            page.Response.BinaryWrite(byte2);
            page.Response.End();
        }

封装的类，返回字符串

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using Aspose.Cells;

namespace ABC.Common.FileUtility
{
    public class AsposeCellsHelper
    {
        /// <summary>
        /// 导出excel  //设置表头名称  要求表头为中文所以不用 p.name 为字段名称 可在list第一条数据为表头名称
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data">Ilist集合</param>
        public string ExportWithColumnName<T>(IList<T> data)
        {
            string strData = string.Empty;

            try
            {
                Workbook workbook = new Workbook();
                Worksheet sheet = workbook.Worksheets[0];

                PropertyInfo[] ps = typeof(T).GetProperties();
                var colIndex = "A";
                foreach (var p in ps)
                {
                    //    sheet.Cells[colIndex + 1].PutValue(p.Name);//设置表头名称  要求表头为中文所以不用 p.name 为字段名称 可在list第一条数据为表头名称
                    int i = 1;
                    foreach (var d in data)
                    {
                        sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
                        i++;
                    }
                    colIndex = getxls_top(colIndex); //((char)(colIndex[0] + 1)).ToString();//表头  A1/A2/
                }

                

                //workbook.Shared = true;
                var ms = new MemoryStream();
                workbook.Save(ms, SaveFormat.Xlsx);
                byte[] dataStream = ms.ToArray();
                strData = Convert.ToBase64String(dataStream);
                ms.Close();
                GC.Collect();
            }
            catch
            {
                // ignored
            }


            return strData;

        }

        /// <summary>
        /// 生成新的对应的列  A-Z  AA-ZZ
        /// </summary>
        /// <param name="top">当前列</param>
        /// <returns></returns>
        private string getxls_top(string top)
        {
            char[] toplist = top.ToArray();
            var itemtop = top.Last();
            string topstr = string.Empty;
            if ((char)itemtop == 90)//最后一个是Z
            {
                if (toplist.Count() == 1)
                {
                    topstr = "AA";
                }
                else
                {
                    toplist[0] = (char)(toplist[0] + 1);
                    toplist[toplist.Count() - 1] = 'A';
                    foreach (var item in toplist)
                    {
                        topstr += item.ToString();
                    }
                }
            }
            else//最后一个不是Z  包括top为两个字符
            {
                itemtop = (char)(itemtop + 1);
                toplist[toplist.Count() - 1] = itemtop;

                foreach (var item in toplist)
                {
                    topstr += item.ToString();
                }
            }
            return topstr;
        }
    }
}