C# Aspose.Cells导出Excel报表

1、html页面

    //导出
    function btn_export() {
        var queryParams = {};
        queryParams = $('.searchPanel').GetWebControls();
        if (queryParams.Year == "") {
            dialogMsg(lang.DialogWarnYear, 1);
            return false;
        }
        location.href = "/PrefManager/Calcuate/ExportExcel/?queryJson=" + JSON.stringify(queryParams);
    }

2、Controller.cs

/// <summary>
/// 工作量报表导出
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
[HttpGet]
public ActionResult ExportExcel(string queryJson)
{
    CalcuateQueryViewModel queryModel = new CalcuateQueryViewModel();
    if (!string.IsNullOrEmpty(queryJson))
    {
        queryModel = queryJson?.ToObject<CalcuateQueryViewModel>();
    }
    string fileName = "员工工作量报表.xls";
    System.IO.MemoryStream ms = new System.IO.MemoryStream();
    fileName = queryModel.Year+"年度员工工作量报表_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
    ms = calcuateBiz.ReportExcel(queryModel);
    return File(ms.ToArray(), "application/ms-excel", fileName);
}

3、业务层

        /// <summary>
        /// 数据集合
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public DataTable GetReportList(CalcuateQueryViewModel queryModel)
        {
            StringBuilder sb = new StringBuilder();
            int year = int.Parse(queryModel.Year);
            sb.AppendFormat(@"
SELECT  tt.LegalEntityName ,
        tt.BUName ,
        tt.DepartmentName ,
        tt.TeamName ,
        tt.PieceType ,
        tt.EmployeeNo ,
        tt.EmployeeName ,
        tt.PositionName ,
        tt.BaseJob ,
        tt.BaseUnit ,
        [{0}01] AS 'Jan' ,
        [{0}02] AS 'Feb' ,
        [{0}03] AS 'Mar' ,
        [{0}04] AS 'Apr' ,
        [{0}05] AS 'May' ,
        [{0}06] AS 'Jun' ,
        [{0}07] AS 'Jul' ,
        [{0}08] AS 'Aug' ,
        [{0}09] AS 'Sep' ,
        [{0}10] AS 'Oct' ,
        [{0}11] AS 'Nov' ,
        [{0}12] AS 'Dec'
FROM    ( SELECT    a.Month ,
                    a.BUID ,
                    b.BUName ,
                    a.DepartmentID ,
                    d.DepartmentName ,
                    a.EmployeeNo ,
                    m.FullNameCN AS EmployeeName ,
                    t.TeamCode ,
                    t.TeamName ,
                    t.PieceType ,
                    lg.LegalEntityID ,
                    lg.LegalEntityNameCN AS LegalEntityName ,
                    p.PositionID ,
                    p.PositionName ,
                    a.BaseJob ,
                    a.BaseUnit ,
                    a.ActJob
          FROM      dbo.HR_PF_Calc a
                    LEFT JOIN dbo.MDT_BU b ON b.BUID = a.BUID
                    LEFT JOIN dbo.MDT_Department d ON d.DepartmentID = a.DepartmentID
                    LEFT JOIN dbo.MDT_Employee m ON m.EmployeeNo = a.EmployeeNo
                    LEFT JOIN dbo.MDT_Position p ON p.PositionID = m.PositionID
                    LEFT JOIN dbo.MDT_LegalEntity lg ON lg.LegalEntityID = m.LegalEntityID
                    LEFT JOIN dbo.HR_PF_Team t ON t.TeamID = a.TeamID
          WHERE     a.IsActive = 1
                    AND lg.IsActive = 1
                    AND t.IsActive = 1
                    AND m.IsActive = 1
                    AND LEFT(a.Month, 4) = '{0}'", year);
            if (!string.IsNullOrEmpty(queryModel.LegalEntityID))
            {
                sb.AppendFormat(" AND lg.LegalEntityID = '{0}'", queryModel.LegalEntityID);
            }
            if (!string.IsNullOrEmpty(queryModel.BUID))
            {
                sb.AppendFormat(" AND a.BUID = '{0}'", queryModel.BUID);
            }
            if (!string.IsNullOrEmpty(queryModel.DepartmentID))
            {
                sb.AppendFormat(" AND a.DepartmentID = '{0}'", queryModel.DepartmentID);
            }
            if (!string.IsNullOrEmpty(queryModel.EmployeeNo))
            {
                sb.AppendFormat(" AND a.EmployeeNo = '{0}'", queryModel.EmployeeNo);
            }
            if (!string.IsNullOrEmpty(queryModel.TeamCode))
            {
                sb.AppendFormat(" AND t.TeamCode = '{0}'", queryModel.TeamCode);
            }
            if (!string.IsNullOrEmpty(queryModel.PieceType))
            {
                sb.AppendFormat(" AND t.PieceType = '{0}'", queryModel.PieceType);
            }
            sb.AppendFormat(@" ) AS bb PIVOT ( SUM(ActJob) FOR Month IN ( 
                                                   [{0}01], [{0}02],
                                                   [{0}03], [{0}04],
                                                   [{0}05], [{0}06],
                                                   [{0}07], [{0}08],
                                                   [{0}09], [{0}10],
                                                   [{0}11], [{0}12] ) ) AS tt", year);

            sb.Append(@" ORDER BY tt.EmployeeNo");
            Framework.Db.DbHelper db = Framework.Db.DbFactory.CreateDbRead();
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sb.ToString());
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return new DataTable();
            }
        }
        /// <summary>
        /// 工作量报表导出
        /// </summary>
        /// <param name="queryModel">条件对象</param>
        /// <returns></returns>
        public MemoryStream ReportExcel(CalcuateQueryViewModel queryModel)
        {
            DataTable dt = GetReportList(queryModel);
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            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;
            Cells cells = sheet.Cells;
            if (dt.Columns.Count != 0)
            {
                cells.Merge(0, 0, 1, dt.Columns.Count);
            }
            cells[0, 0].SetStyle(style);
            cells[0, 0].PutValue($"{queryModel.Year}年度员工实际工作量报表");

            List<string> columnsList = new List<string> { "法人公司", "BU名称", "部门", "组名称", "考核类型", "工号", "姓名", "岗位", "合格工作量", "合格工作量单位", "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月" };
            for (int j = 0; j < columnsList.Count; j++)
            {
                cells[1, j].SetStyle(style);
            }
            int rowIndex = 1;
            SetColumnsName(cells, columnsList, rowIndex);
            rowIndex++;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    style.Font.IsBold = false;
                    cells[rowIndex, j].SetStyle(style);
                    if (j > 9)
                    {
                        style.Custom = "0.00";
                        style.HorizontalAlignment = TextAlignmentType.Right;
                        cells[rowIndex, j].SetStyle(style);
                        cells[rowIndex, j].PutValue(dt.Rows[i][j].ToString(), true);
                    }
                    else
                    {
                        style.HorizontalAlignment = TextAlignmentType.Center;
                        cells[rowIndex, j].SetStyle(style);
                        cells[rowIndex, j].PutValue(dt.Rows[i][j].ToString());
                    }
                }
                rowIndex++;
            }
            int dataRowIndex = 3;
            if (dt.Rows.Count > 0)
            {
                //cells.SetRowHeight(rowIndex, 17);
                for (int i = 0; i < columnsList.Count; i++)
                {
                    cells[rowIndex, i].SetStyle(style);
                }
                cells[rowIndex, 0].PutValue("总计");
                cells[rowIndex, 10].Formula = $"=SUM(K{dataRowIndex}:K{rowIndex})";
                cells[rowIndex, 11].Formula = $"=SUM(L{dataRowIndex}:L{rowIndex})";
                cells[rowIndex, 12].Formula = $"=SUM(M{dataRowIndex}:M{rowIndex})";
                cells[rowIndex, 13].Formula = $"=SUM(N{dataRowIndex}:N{rowIndex})";
                cells[rowIndex, 14].Formula = $"=SUM(O{dataRowIndex}:O{rowIndex})";
                cells[rowIndex, 15].Formula = $"=SUM(P{dataRowIndex}:P{rowIndex})";
                cells[rowIndex, 16].Formula = $"=SUM(Q{dataRowIndex}:Q{rowIndex})";
                cells[rowIndex, 17].Formula = $"=SUM(R{dataRowIndex}:R{rowIndex})";
                cells[rowIndex, 18].Formula = $"=SUM(S{dataRowIndex}:S{rowIndex})";
                cells[rowIndex, 19].Formula = $"=SUM(T{dataRowIndex}:T{rowIndex})";
                cells[rowIndex, 20].Formula = $"=SUM(U{dataRowIndex}:U{rowIndex})";
                cells[rowIndex, 21].Formula = $"=SUM(V{dataRowIndex}:V{rowIndex})";
                rowIndex++;
            }
            return workbook.SaveToStream();
        }
        /// <summary>
        /// 设置Excel表格列名称
        /// </summary>
        /// <param name="cells"></param>
        /// <param name="columnsList">列名集合</param>
        /// <param name="rowIndex">开始行</param>
        /// <param name="startColumnsIndex">开始列，默认：0</param>
        public 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]);
            }
        }