/// <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);
}
