Aspose.Cells扩展帮助类

1、AsposeCellsHelper帮助类

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;

namespace OA.Util
{
    /// <summary>
    /// Excel导出通用类
    /// </summary>
    public class AsposeCellsHelper
    {
        /// <summary>
        /// 得到第一个sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetName">sheet名称，默认空</param>
        /// <returns></returns>
        public static Worksheet GetWorksheet(Workbook workbook, string sheetName = "")
        {
            Worksheet sheet = workbook.Worksheets[0];
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet.Name = sheetName;
            }
            return sheet;
        }
        /// <summary>
        /// 新增sheet
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static Worksheet AddWorksheet(Workbook workbook, string sheetName)
        {
            Worksheet sheet = workbook.Worksheets.Add(sheetName);
            return sheet;
        }
        /// <summary>
        /// 获取边框单元格样式
        /// </summary>
        /// <param name="style"></param>
        /// <returns></returns>
        public static Style GetStyle(Workbook workbook)
        {
            Style style = workbook.Styles[workbook.Styles.Add()];
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

            return style;
        }

        /// <summary>
        /// 获取边框单元格样式
        /// </summary>
        /// <param name="style"></param>
        /// <returns></returns>
        public static Style GetStyle(Style style)
        {
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

            return style;
        }
        /// <summary>
        /// 设置Excel表格列名称
        /// </summary>
        /// <param name="cells"></param>
        /// <param name="columnsList">列名集合</param>
        /// <param name="rowIndex">开始行</param>
        /// <param name="startColumnsIndex">开始列，默认：0</param>
        public static void SetColumnsName(Cells cells, List<string> columnsList, int rowIndex, int startColumnsIndex = 0)
        {
            for (int i = 0; i < columnsList.Count; i++)
            {
                cells[rowIndex, i + startColumnsIndex].PutValue(columnsList[i]);
            }
        }
        /// <summary>
        /// 设置行的单元格样式
        /// </summary>
        /// <param name="cells">单元格对象</param>
        /// <param name="style">单元格样式</param>
        /// <param name="columnCount">列数量</param>
        /// <param name="rowIndex">指定的行，默认：0</param>
        public static void SetRowColumnsStyle(Cells cells, Style style, int columnCount, int rowIndex = 0)
        {
            for (int i = 0; i < columnCount; i++)
            {
                cells[rowIndex, i].SetStyle(style);
            }
        }
        /// <summary>
        /// 填充DataTable数据到Excel。
        /// </summary>
        /// <param name="fillCellsModel"></param>
        /// <returns></returns>
        public static int FillDataCells(FillCellsModel fillCellsModel)
        {
            for (int i = 0; i < fillCellsModel.dt.Rows.Count; i++)
            {
                for (int j = 0; j < fillCellsModel.dt.Columns.Count; j++)
                {
                    fillCellsModel.cells[fillCellsModel.rowIndex, j].SetStyle(fillCellsModel.style);
                    if (fillCellsModel.formatType == FormatType.None)
                    {
                        if (!fillCellsModel.dt.Rows[i][j].ToString().Contains(",") && !fillCellsModel.dt.Rows[i][j].ToString().Contains(".") && DateTime.TryParse(fillCellsModel.dt.Rows[i][j].ToString(), out DateTime dtDate))
                        {
                            SetDate(fillCellsModel.cells, fillCellsModel.rowIndex, j, dtDate.ToString(), fillCellsModel.CustomDateFormat);
                        }
                        else
                        {
                            fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString());
                        }
                    }
                    else if (fillCellsModel.formatType == FormatType.Contain)
                    {
                        if (fillCellsModel.formatList.Contains(j))
                        {
                            fillCellsModel.style.Number = fillCellsModel.styleNumber;
                            fillCellsModel.cells[fillCellsModel.rowIndex, j].SetStyle(fillCellsModel.style);
                            fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString(), true);
                        }
                        else
                        {
                            if (!fillCellsModel.dt.Rows[i][j].ToString().Contains(",") && !fillCellsModel.dt.Rows[i][j].ToString().Contains(".") && DateTime.TryParse(fillCellsModel.dt.Rows[i][j].ToString(), out DateTime dtDate))
                            {
                                SetDate(fillCellsModel.cells, fillCellsModel.rowIndex, j, dtDate.ToString(), fillCellsModel.CustomDateFormat);
                            }
                            else
                            {
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString());
                            }
                        }
                    }
                    else if (fillCellsModel.formatType == FormatType.Range)
                    {
                        fillCellsModel.cells[fillCellsModel.rowIndex, j].SetStyle(fillCellsModel.style);
                        if (fillCellsModel.offsetFormat <= j)
                        {
                            if (fillCellsModel.endFormat == -1)
                            {
                                fillCellsModel.style.Number = fillCellsModel.styleNumber;
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].SetStyle(fillCellsModel.style);
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString(), true);
                            }
                            else
                            {
                                if (j <= fillCellsModel.endFormat)
                                {
                                    fillCellsModel.style.Number = fillCellsModel.styleNumber;
                                    fillCellsModel.cells[fillCellsModel.rowIndex, j].SetStyle(fillCellsModel.style);
                                    fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString(), true);
                                }
                                else
                                {
                                    fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString());
                                }
                            }
                        }
                        else
                        {
                            if (!fillCellsModel.dt.Rows[i][j].ToString().Contains(",") && !fillCellsModel.dt.Rows[i][j].ToString().Contains(".") && DateTime.TryParse(fillCellsModel.dt.Rows[i][j].ToString(), out DateTime dtDate))
                            {
                                SetDate(fillCellsModel.cells, fillCellsModel.rowIndex, j, dtDate.ToString(), fillCellsModel.CustomDateFormat);
                            }
                            else
                            {
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString());
                            }
                        }
                    }
                    else if (fillCellsModel.formatType == FormatType.ALL)
                    {
                        if (fillCellsModel.formatList.Contains(j))
                        {
                            var result = fillCellsModel.styleAllNumber.TryGetValue(FormatType.Contain, value: out _);
                            if (result)
                            {
                                int numStyle = fillCellsModel.styleAllNumber[FormatType.Contain];
                                fillCellsModel.style.Number = numStyle;
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].SetStyle(fillCellsModel.style);
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString(), true);
                            }
                        }
                        else if ((fillCellsModel.offsetFormat <= j && fillCellsModel.endFormat == -1) || (fillCellsModel.offsetFormat <= j && j <= fillCellsModel.endFormat))
                        {
                            var result = fillCellsModel.styleAllNumber.TryGetValue(FormatType.Range, value: out _);
                            if (result)
                            {
                                int numStyle = fillCellsModel.styleAllNumber[FormatType.Range];
                                fillCellsModel.style.Number = numStyle;
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].SetStyle(fillCellsModel.style);
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString(), true);
                            }
                        }
                        else
                        {
                            if (!fillCellsModel.dt.Rows[i][j].ToString().Contains(",") && !fillCellsModel.dt.Rows[i][j].ToString().Contains(".") && DateTime.TryParse(fillCellsModel.dt.Rows[i][j].ToString(), out DateTime dtDate))
                            {
                                SetDate(fillCellsModel.cells, fillCellsModel.rowIndex, j, dtDate.ToString(), fillCellsModel.CustomDateFormat);
                            }
                            else
                            {
                                fillCellsModel.cells[fillCellsModel.rowIndex, j].PutValue(fillCellsModel.dt.Rows[i][j].ToString());
                            }
                        }
                    }
                }
                fillCellsModel.rowIndex++;
            }
            return fillCellsModel.rowIndex;
        }
        /// <summary>
        /// 日期格式化
        /// </summary>
        /// <param name="cells"></param>
        /// <param name="rowIndex">列</param>
        /// <param name="colIndex">行</param>
        /// <param name="datetime">日期字符串</param>
        /// <param name="format">自定义格式，默认："yyyy-MM-dd"</param>
        public static void SetDate(Cells cells, int rowIndex, int colIndex, string datetime, string format = "yyyy-MM-dd")
        {
            Style dateStyle = cells[rowIndex, colIndex].GetStyle();
            dateStyle.Custom = format == "" ? "yyyy-MM-dd" : format;
            cells[rowIndex, colIndex].SetStyle(dateStyle);
            //cells[rowIndex, colIndex].Value = datetime;
            cells[rowIndex, colIndex].PutValue(datetime,true);
        }

        /// <summary>
        /// 数字值的格式化
        /// </summary>
        /// <param name="cells"></param>
        /// <param name="rowIndex">列</param>
        /// <param name="colIndex">行</param>
        /// <param name="cellsValue">值</param>
        /// <param name="Number">格式，默认：0</param>
        public static void SetNumber(Cells cells, int rowIndex, int colIndex, string cellsValue, int Number = 1)
        {
            Style numStyle = cells[rowIndex, colIndex].GetStyle();
            numStyle.Number = Number;
            cells[rowIndex, colIndex].SetStyle(numStyle);
            cells[rowIndex, colIndex].PutValue(cellsValue, true);
        }
        /// <summary>
        /// 获取Excel数据流
        /// </summary>
        /// <param name="dataTable">表数据</param>
        /// <param name="colList">列名集合</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="fillcellsModel">FillCellsModel</param>
        /// <returns></returns>
        public static MemoryStream GetReportExcel(DataTable dataTable, List<string> colList, string sheetName, FillCellsModel fillcellsModel, List<string> sumColList = null)
        {
            DataTable dt = dataTable;
            Workbook workbook = new Workbook();
            workbook.Worksheets.Clear();
            Style style = GetStyle(workbook);
            Worksheet sheet = workbook.Worksheets.Add(sheetName);
            Cells cells = sheet.Cells;
            int rowIndex = 0;
            cells.Merge(rowIndex, 0, 1, colList.Count);
            cells.SetRowHeight(rowIndex, 21);
            cells[0, 0].SetStyle(style);
            cells[0, 0].PutValue(sheetName);
            rowIndex++;
            cells.SetRowHeight(rowIndex, 17);
            SetRowColumnsStyle(cells, style, colList.Count, rowIndex);
            SetColumnsName(cells, colList, rowIndex);
            rowIndex++;
            Style numstyle = style;
            numstyle.HorizontalAlignment = TextAlignmentType.Center;
            FillCellsModel fillModel = new FillCellsModel();
            fillModel.dt = dt;
            fillModel.cells = cells;
            fillModel.style = style;
            fillModel.rowIndex = rowIndex;
            fillModel.CustomDateFormat = fillcellsModel.CustomDateFormat;
            fillModel.formatType = fillcellsModel.formatType;
            fillModel.formatList = fillcellsModel.formatList;
            fillModel.offsetFormat = fillcellsModel.offsetFormat;
            fillModel.endFormat = fillcellsModel.endFormat;
            fillModel.styleNumber = fillcellsModel.styleNumber;
            fillModel.styleAllNumber = fillcellsModel.styleAllNumber;
            rowIndex = FillDataCells(fillModel);

            if (dt.Rows.Count > 0)
            {
                if (sumColList != null && sumColList.Count > 0)
                {
                    int dataRowIndex = 3;
                    SetRowColumnsStyle(cells, style, dt.Columns.Count, rowIndex);
                    cells.SetRowHeight(rowIndex, 17);
                    cells[rowIndex, 0].PutValue("总计:");
                    foreach (var item in sumColList)
                    {
                        cells[item + (rowIndex + 1).ToString()].Formula = $"=SUM({item}{dataRowIndex}:{item}{rowIndex})";
                    }
                }
            }
            return workbook.SaveToStream();
        }
    }
}

2、参数对象

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

namespace OA.Util
{
    /// <summary>
    /// 参数对象
    /// </summary>
    public class FillCellsModel
    {
        /// <summary>
        /// 数据集合
        /// </summary>
        public DataTable dt { get; set; }
        /// <summary>
        /// cells对象
        /// </summary>
        public Cells cells { get; set; }
        /// <summary>
        /// cells样式
        /// </summary>
        public Style style { get; set; }
        /// <summary>
        /// 行索引
        /// </summary>
        public int rowIndex { get; set; }
        /// <summary>
        /// 自定义日期格式化
        /// </summary>
        public string CustomDateFormat { get; set; } = "yyyy-MM-dd";
        /// <summary>
        /// 系统的数字、日期格式化类型
        /// </summary>
        public int styleNumber { get; set; }
        /// <summary>
        /// 系统的数字、日期格式化类型,FormatType=ALL
        /// </summary>
        public Dictionary<FormatType,int> styleAllNumber { get; set; }
        /// <summary>
        /// 格式化的列索引集合，FormatType=Contain
        /// </summary>
        public List<int> formatList { get; set; }
        /// <summary>
        /// 格式方式
        /// </summary>
        public FormatType formatType { get; set; }
        /// <summary>
        /// 格式化起始索引，FormatType=Range
        /// </summary>
        public int offsetFormat { get; set; }
        /// <summary>
        /// 格式化结束索引，FormatType=Range
        /// </summary>
        public int endFormat { get; set; } = -1;
    }
    /// <summary>
    /// 格式类型枚举
    /// </summary>
    public enum FormatType
    {
        /// <summary>
        /// 默认
        /// </summary>
        None=0,
        /// <summary>
        /// 包含
        /// </summary>
        Contain=1,
        /// <summary>
        /// 范围
        /// </summary>
        Range=2,
        /// <summary>
        /// 包含和范围,包含的索引必须不在范围的索引之内
        /// </summary>
        ALL=3,
    }
}