基于Excel自定义模板通用报表系统的设计与实现
Excel自定义模板报表具有良好的直观性，由它直接设计报表模板十分简便易行。自定义模板文件主要包括静态和动态两部分内容,静态部分主要封装了报表的样式信息，动态部分包括动态属性及动态扩展行/列等内容。报表引擎解析模板文件加载业务数据，使用开源的NPOI组件动态生成Excel报表。
NPOI介绍
NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。
Excel模板设计
我们通常所使用的报表有以下几类：
1）固定行的报表：如下图，这类报表最简单只需要获取业务数据进行单元格替换即可生产报表。
2）扩展行的单表，此类报表用的最多，可以分为几个区域。红色部分为报表顶部标题部分和底部页脚。页头和页脚都属于固定行，也不需要依据数据进行扩展。
绿色部分也相应的分header，body，footer。header和fotter部分相对固定，从body部分根据数据进行动态扩展行。
3）主从表报表
顾名思义，主从表的报表有多个数据集合，这种报表比较复杂，格式也多样。根据主表的每一条数据，都对应有个子表数据集合，每个子表都可以进行行扩展，还存在行列都允许进行扩展的交叉表报表。
我们使用Excel中的“名称管理器”来定义模板中的各个区域（子表header，子表body等）。
名称管理器中的名称相应定义对应了不同的excel区域（行列单元格），这里名称必须唯一，如果excel文档中存在多个sheet页，每个页的名称前面需要加上sheet页名称。
模板文件中扩展数据行字段使用“[]”,静态替换自动使用“$$”做标识。
报表模型代码：
//报表对象
public class Report
{
public Dictionary<string, string> GlobalProperties { get; set; }
public Master Master { get; set; }
public bool IsRowThenColumn { get; set; }
public ISheet SourceSheet { get; set; }
public ISheet TargetSheet { get; set; }
public IWorkbook Workbook { get; set; }
public Report()
{
Master = new Master();
}
public DataTable DataSources { get; set; }
public string FileDescription
{
get;
set;
}
}
报表内容对象
public class Master
{
public TemplateSection Header { get; set; }
public Detail Detail { get; set; }
public TemplateSection Footer { get; set; }
public Master()
{
Header = new TemplateSection();
Footer = new TemplateSection();
Detail = new Detail();
}
}
模板区域
public class TemplateSection
{
public Report ParentReport { get; set; }
public TemplateSection Parent { get; set; }
public IList<int> Rows { get; set; }
public int StartRowIndex { get; set; }
public DataRow DataSource { get; set; }
public TemplateSection()
{
Rows = new List<int>();
}
}
模板动态行扩展区域：
public class DynamicTemplateSections : TemplateSection
{
private TemplateSection templateSection;
public DynamicTemplateSections()
{
// TODO: Complete member initialization
}
public int TotalRows { get; set; }
public DataTable DataSources { get; set; }
}
报表数据子表对象：
public class Detail
{
public TemplateSection Header { get; set; }
public DynamicTemplateSections Body { get; set; }
public TemplateSection Footer { get; set; }
public Detail()
{
Header = new TemplateSection();
Body = new DynamicTemplateSections();
Footer = new TemplateSection();
}
}
具体代码示例如下：
//获取业务数据
var data = this.reportService.LoadData(strUrl, taskID);
//获取替换的属性
var globalProperties = this.reportService.LoadGlobalProperties();
//分析模板文件得到report模型
var reports = FileAnalyse.AnalyseExcelContent((fileName));
//循环生成多页excel文档
foreach(var report in reports.Values)
{
FileAnalyse.GeneralReport(report, datas[i], reportType, globalProperties);
}
删除模板中定义的sheet页
//delete the sheets of template defined
foreach (var keyValuePairs in reports)
{
anyReport = keyValuePairs.Value;
var sheetName = keyValuePairs.Key;
;
anyReport.Workbook.SetSheetHidden(anyReport.Workbook.GetSheetIndex(sheetName), SheetState.Hidden);
anyReport.Workbook.RemoveSheetAt(anyReport.Workbook.GetSheetIndex(sheetName));
}
根据模板中定义的sheet页名称，恢复新生产的sheet页名称
//resotre the sheet name for the sheet created with data
foreach (var keyValuePairs in reports)
{
anyReport = keyValuePairs.Value;
var sheetName = keyValuePairs.Key;
var index = anyReport.Workbook.GetSheetIndex(FileAnalyse.GetTempSheetName(sheetName));
anyReport.Workbook.SetSheetName(index, sheetName);
}
using (FileStream fsSave = File.OpenWrite(fileName))
{
anyReport.Workbook.Write(fsSave);
}
分析模板文件代码：
public static Dictionary<String,Report> AnalyseExcelContent(string excelFile)
{
Dictionary<String, Report> reports = new Dictionary<string, Report>();
IWorkbook workbook = null;
var ext = Path.GetExtension(excelFile);
var ver = OfficeVer.xls;
if (ext.ToLower() == ".xlsx")
{
ver = OfficeVer.xlsx;
}
using (FileStream fs = new FileStream(excelFile, FileMode.Open, FileAccess.Read))
{
if (ver == OfficeVer.xlsx) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (ver == OfficeVer.xls) // 2003版本
workbook = new HSSFWorkbook(fs);
}
for(int i=0;i< workbook.NumberOfSheets;i++)
{
var sheetName = workbook.GetSheetName(i);
if(SheetNameFilter(sheetName))
{
var report = Analyse(workbook, sheetName);
reports.Add(sheetName, report);
}
}
///创建新的sheet页
foreach(var item in reports)
{
workbook.CreateSheet(GetTempSheetName(item.Key));
item.Value.Workbook = workbook;
item.Value.TargetSheet = workbook.GetSheet(GetTempSheetName(item.Key));
}
return reports;
}
public static Report Analyse(IWorkbook workbook,string sheetName)
{
Report report = ReportInit();
if (workbook != null )
{
var sheet = workbook.GetSheet(sheetName);
report.SourceSheet = sheet;
IFormulaParsingWorkbook book = null;
if (workbook is XSSFWorkbook)
{
book = XSSFEvaluationWorkbook.Create(workbook);
}
else
{
book = HSSFEvaluationWorkbook.Create(workbook);
}
IList<string> sheetNames = GetSheetNames(workbook,sheetName);
//for (int i = 0; i < workbook.NumberOfNames; i++)
foreach(var name in sheetNames)
{
var nameRange = workbook.GetName(name);// workbook.GetNameAt(i);
if (!nameRange.IsFunctionName)
{
var refersToFormula = nameRange.RefersToFormula;
var ptgs = FormulaParser.Parse(refersToFormula, book, FormulaType.NamedRange, nameRange.SheetIndex);
Ptg ptg0 = ptgs[0];
if (!(ptg0 is AreaPtgBase || ptg0 is Ref3DPxg || ptg0 is Ref3DPtg))
{
continue;
}
if(nameRange.NameName.Equals(sheetName + "_Master_Header", StringComparison.CurrentCulture))
{
AddRows(report.Master.Header.Rows, ptg0);
continue; ;
}
if (nameRange.NameName.Equals(sheetName + "_Master_Detail_Header", StringComparison.CurrentCulture))
{
AddRows(report.Master.Detail.Header.Rows, ptg0);
continue; ;
}
if (nameRange.NameName.Equals(sheetName + "_Master_Detail_Body", StringComparison.CurrentCulture))
{
AddRows(report.Master.Detail.Body.Rows, ptg0);
continue; ;
}
if (nameRange.NameName.Equals(sheetName + "_Master_Detail_Footer", StringComparison.CurrentCulture))
{
AddRows(report.Master.Detail.Footer.Rows, ptg0);
continue; ;
}
if (nameRange.NameName.Equals(sheetName + "_Master_Footer", StringComparison.CurrentCulture))
{
AddRows(report.Master.Footer.Rows, ptg0);
continue; ;
}
}
}
}
return report;
}
生成excel代码：
public static ReportOperator GeneralReport(Report report, DataTable data, ReportType reportType,Dictionary<string, string> globalProperty)
{
report.DataSources = data;
report.GlobalProperties = globalProperty;
var opertor = new ReportOperator(report, report.TargetSheet);
if (reportType == ReportType.SingleReport)
{
opertor.CreateSingleReport();
}
else if (reportType == ReportType.FixRowMasterDetailReport)
{
opertor.CreateFixRowMasterDetailReport();
}
else if (reportType == ReportType.MasterDetailReport)
{
opertor.CreateMasterDetailReport();
}
else
{
opertor.CreateSingleReport();
}
ExcelHelp.CopyWidth(report.SourceSheet, report.TargetSheet);
return opertor;
}
/// <summary>
/// 生成单表报表
/// </summary>
public void CreateSingleReport()
{
var position = 0;
var dataSources = _report.DataSources;
if (dataSources == null)
{
dataSources = new DataTable();
}
if (dataSources.Rows.Count == 0)
{
dataSources.Rows.Add(dataSources.NewRow());
}
var firstRowData = dataSources.Rows[0];
var detailProperties = dataSources.Columns;
_report.Master.Header.DataSource = firstRowData;
_report.Master.Detail.Body.DataSources = dataSources;
if (dataSources.Rows.Count > 0)
{
_report.Master.Detail.Header.DataSource = firstRowData;
}
_report.Master.Footer.DataSource = firstRowData;
GenerateReportSection(_sheet, _report.Master.Header, ref position, detailProperties);
GenerateReportSection(_sheet, _report.Master.Detail.Header, ref position, detailProperties);
GenerateReportSection(_sheet, _report.Master.Detail.Body, ref position, detailProperties);
GenerateReportSection(_sheet, _report.Master.Footer, ref position, detailProperties);
}
生成模板区域数据：
private void GenerateReportSection(ISheet sheet, TemplateSection section, ref int position, DataColumnCollection properties)
{
if (section == null)
{
return;
}
if (section.Rows == null || section.Rows.Count == 0)
{
return;
}
section.StartRowIndex = position;
if (!(section is DynamicTemplateSections))
{
int off = 0;
foreach (var rowIndex in section.Rows)
{
var row = ExcelHelp.CopyRow(_report.SourceSheet, rowIndex, _report.TargetSheet, position + off);
if (row.Cells != null)
{
foreach (var cellItem in row.Cells)
{
if (GetCellContext(cellItem) == null)
cellItem.SetCellValue("");
var propertyList = GetPropertyByCell(cellItem, properties);
var cellText = GetCellContext(cellItem).SafeToString();
if (propertyList.Count > 0)
{
foreach (var propertyItem in propertyList)
{
var context = GetCellContext(section.DataSource, propertyItem);
if (context == null)
{
context = "";
}
var oldString = "[" + propertyItem.ColumnName + "]";
cellText = cellText.Replace(oldString, context.ToString());
}
cellItem.SetCellValue(cellText);
}
else
{
cellItem.SetCellValue(cellText);
}
//replace the property of global
foreach(var item in this._report.GlobalProperties.Keys)
{
if(cellText.IndexOf("$" + item + "$",StringComparison.CurrentCultureIgnoreCase)>=0)
{
cellText = cellText.Replace("$" + item + "$", this._report.GlobalProperties[item].ToString());
cellItem.SetCellValue(cellText);
}
}
}
}
off++;
}
position = position + section.Rows.Count;
}
else
{
var dynamicSection = section as DynamicTemplateSections;
var sequenceNo = 1;
foreach (DataRow item in dynamicSection.DataSources.Rows)
{
int off = 0;
foreach (var rowIndex in section.Rows)
{
var row = ExcelHelp.CopyRow(_report.SourceSheet, rowIndex, _report.TargetSheet, position + off);
if (row.Cells != null)
{
foreach (var cellItem in row.Cells)
{
var propInfo = GetPropertyByCell(cellItem, properties);
if (propInfo.Count > 0)
{
var newString = GetCellContext(cellItem).SafeToString();
foreach (var propertyItem in propInfo)
{
var context = GetCellContext(item, propertyItem);
var oldString = "[" + propertyItem.ColumnName + "]";
if (context == null)
{
context = "";
}
newString = newString.Replace(oldString, context.ToString());
}
cellItem.SetCellValue(newString);
}
else
{
if (GetCellContext(cellItem).SafeToString() != null && GetCellContext(cellItem).SafeToString().Equals("&No", StringComparison.CurrentCultureIgnoreCase))
{
cellItem.SetCellValue(sequenceNo.ToString());
}
}
}
}
off++;
}
position = position + section.Rows.Count;
dynamicSection.TotalRows += section.Rows.Count;
sequenceNo++;
}
dynamicSection.TotalRows = dynamicSection.DataSources.Rows.Count * section.Rows.Count;
}
}