/// <summary> /// 将报表数据以所见即所得的方式导出为Excel /// </summary> /// <returns></returns> public Workbook WriteXLS() { //执行筛选 this.ExecFilters(); //创建Excel工作薄和工作表 Aspose.Cells.License lic = new License(); lic.SetLicense("Aspose.Total.lic"); Workbook wb = new Workbook(); wb.Worksheets.Clear(); wb.Worksheets.Add(); wb.Worksheets[0].Name = this.ReportDataes.TableName; var sheet = wb.Worksheets[0]; sheet.IsGridlinesVisible = true; //定义一些全局属性 int startRowIndex = 1; //从第二行开始,第一行保留给报表的导航栏 List <JIT.Utility.Web.ComponentModel.ExtJS.Grid.Column.Column> detailColumns; //写入标题 this.WriteHead(wb, sheet, ref startRowIndex, this.Columns, out detailColumns); //写入数据 int summaryDepth = 0; //摘要区域的深度 if (this.ReportDataes != null && detailColumns != null && detailColumns.Count > 0) { List <ISummary> writtenTitleSummaries = new List <ISummary>(); //已经写了摘要标题的摘要列表 //逐列写数据&摘要 for (int i = 0; i < detailColumns.Count; i++) //detailColumns列表是有序的 { var col = detailColumns[i]; //找到表格列所属的分析列,从而获取格式化器 var analysisCol = this.GridColumnToAnalysisColumnMappings.FirstOrDefault(item => item.Key == col).Value; IDataFormatter formatter = analysisCol.DataFormatter != null ? analysisCol.DataFormatter : new CommonExcelDataFormatter(); //一列一列的写数据 for (int j = 0; j < this.ReportDataes.Rows.Count; j++) { var dr = this.ReportDataes.Rows[j]; var data = dr[col.DataIndex]; if (data == DBNull.Value) { data = null; } //格式化并写数据 formatter.WriteData(sheet, data, startRowIndex + j, i, col.ColumnType, col); } //写摘要 var summaries = this.ColumnSummaryValues.FirstOrDefault(item => item.Key == col).Value; if (summaries != null && summaries.Count > 0) { int summaryRowIndex = startRowIndex + this.ReportDataes.Rows.Count; //通过摘要格式化器写摘要 foreach (var summary in summaries) { var summaryFormatter = summary.Key.Formatter != null ? summary.Key.Formatter : new CommonExcelSummaryFormatter(); summaryFormatter.WriteSummary(sheet, summary.Key, summary.Value, summaryRowIndex, i, i == 0, col.ColumnType); if (i == 0) { writtenTitleSummaries.Add(summary.Key); } else { var isWrittenTitle = (writtenTitleSummaries.FirstOrDefault(item => item == summary.Key) != null); if (!isWrittenTitle) { summaryFormatter.WriteSummaryTitle(sheet, summary.Key, summaryRowIndex, 0); } writtenTitleSummaries.Add(summary.Key); } // summaryRowIndex++; } if (summaries.Count > summaryDepth) { summaryDepth = summaries.Count; } } } } //设置数据区域和摘要区域的边框 var dataRange = sheet.Cells.CreateRange(startRowIndex, 0, this.ReportDataes.Rows.Count, detailColumns.Count); this.SetDataRangeStyle(wb, dataRange); if (summaryDepth > 0) { var summaryRange = sheet.Cells.CreateRange(startRowIndex + this.ReportDataes.Rows.Count, 0, summaryDepth, detailColumns.Count); this.SetSummaryRangeStyle(wb, summaryRange); } //写面包屑导航 var navigationRange = sheet.Cells.CreateRange(0, 0, 1, detailColumns.Count); navigationRange.Merge(); navigationRange.Value = this.NavigationText; this.SetNavigationStyle(wb, navigationRange); // return(wb); }