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;

}

}