using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace CellHelper.App_Start
{
    /// <summary>
    /// 电子表格帮助类
    /// </summary>
    public class CellHelper
    {
        #region

        /// <summary>
        /// 电子表格格式化转换内存流(datatable转其他文档格式)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dataTableName"></param>
        /// <param name="saveFormatType"></param>
        /// <returns></returns>
        public static MemoryStream GetCellFormatStream(DataTable dt, string dataTableName, SaveFormat saveFormatType)
        {
            MemoryStream m = OutFileToStream(dt, dataTableName);
            MemoryStream formatStrem = new MemoryStream();
            Workbook wb = new Workbook(m);
            wb.Save(formatStrem, saveFormatType);

            m.Dispose();//非托管资源流对象，手动释放
            return formatStrem;
        }

        #endregion

        #region 公用

        /// <summary>
        /// 输出内存流
        /// </summary>
        /// <param name="dt">构建的表格</param>
        /// <param name="tableName">表格名称</param>
        /// <returns>MemoryStream内存流对象</returns>
        public static MemoryStream OutFileToStream(DataTable dt, string tableName)
        {
            Workbook workbook = new Workbook(); //工作簿
            Worksheet sheet = workbook.Worksheets[0]; //工作表
            Cells cells = sheet.Cells;//单元格

            //为标题设置样式    
            Style styleTitle = workbook.Styles[workbook.Styles.Add()];//新增样式
            styleTitle.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            styleTitle.Font.Name = "宋体";//文字字体
            styleTitle.Font.Size = 18;//文字大小
            styleTitle.Font.IsBold = true;//粗体

            //样式2
            Style style2 = workbook.Styles[workbook.Styles.Add()];//新增样式
            style2.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            style2.Font.Name = "宋体";//文字字体
            style2.Font.Size = 14;//文字大小
            style2.Font.IsBold = true;//粗体
            style2.IsTextWrapped = 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;

            //样式3
            Style style3 = workbook.Styles[workbook.Styles.Add()];//新增样式
            style3.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            style3.Font.Name = "宋体";//文字字体
            style3.Font.Size = 12;//文字大小
            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;

            int Colnum = dt.Columns.Count;//表格列数
            int Rownum = dt.Rows.Count;//表格行数

            //生成行1 标题行   
            cells.Merge(0, 0, 1, Colnum);//合并单元格
            cells[0, 0].PutValue(tableName);//填写内容
            cells[0, 0].SetStyle(styleTitle);
            cells.SetRowHeight(0, 38);

            //生成行2 列名行
            for (int i = 0; i < Colnum; i++)
            {
                cells[1, i].PutValue(dt.Columns[i].ColumnName);
                cells[1, i].SetStyle(style2);
                cells.SetRowHeight(1, 25);
            }

            //生成数据行
            for (int i = 0; i < Rownum; i++)
            {
                for (int k = 0; k < Colnum; k++)
                {
                    cells[2 + i, k].PutValue(dt.Rows[i][k].ToString());
                    cells[2 + i, k].SetStyle(style3);
                }
                cells.SetRowHeight(2 + i, 24);
            }
            setColumnWithAuto(sheet);
            MemoryStream ms = workbook.SaveToStream();
            return ms;
        }

        /// <summary>
        /// 设置表页的列宽度自适应
        /// </summary>
        /// <param name="sheet"></param>
        public static void setColumnWithAuto(Worksheet sheet)
        {
            Cells cells = sheet.Cells;
            int columnCount = cells.MaxColumn;  //获取表页的最大列数
            int rowCount = cells.MaxRow;        //获取表页的最大行数

            for (int col = 0; col < columnCount; col++)
            {
                sheet.AutoFitColumn(col, 0, rowCount);
            }
            for (int col = 0; col < columnCount; col++)
            {
                cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
            }
        }
        #endregion

    }


}

                MemoryStream formateStrem = CellHelper.GetCellFormatStream(dt,"文件名称", Aspose.Cells.SaveFormat.Xlsx);
                byte[] stremByte = formateStrem.ToArray();
                formateStrem.Dispose();//非托管资源流对象 手动释放
                return base.File(stremByte, "multipart/form-data", "文件名称" + "." + Aspose.Cells.SaveFormat.Xlsx.ToString());

 protected void Button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Test");
            DataRow dr = dt.NewRow();
            dr["Test"] = "Test";
            dt.Rows.Add(dr);
            MemoryStream formateStrem = CellHelper.App_Start.CellHelper.GetCellFormatStream (dt, "ceshiexcel", Aspose.Cells.SaveFormat.Xlsx);
            byte[] stremByte = formateStrem.ToArray();
            formateStrem.Dispose();//非托管资源流对象 手动释放
            Response.ClearContent();
            Response.AddHeader("Content-Disposition", "attachment;fileName=" + "测试测试222666.xlsx");
            Response.BinaryWrite(stremByte);
        }