C# Aspose.Cells导出Excel报表
1、html页面2、Controller.cs3、业务层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.ActJobFROMdbo.HR_PF_Calc aLEFT JOIN dbo.MDT_BU b ON b.BUID = a.BUIDLEFT JOIN dbo.MDT_Department d ON d.DepartmentID = a.DepartmentIDLEFT JOIN dbo.MDT_Employee m ON m.EmployeeNo = a.EmployeeNoLEFT JOIN dbo.MDT_Position p ON p.PositionID = m.PositionIDLEFT JOIN dbo.MDT_LegalEntity lg ON lg.LegalEntityID = m.LegalEntityIDLEFT JOIN dbo.HR_PF_Team t ON t.TeamID = a.TeamIDWHEREa.IsActive = 1AND lg.IsActive = 1AND t.IsActive = 1AND m.IsActive = 1AND 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]);}}
