public void TestExportToRepeaterExcelWithTemplateByDataTable()
{
DataTable dt = GetDataTable(); //获取数据
string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel2.xls"; //获得EXCEL模板路径
SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //实例化一个模板数据格式化容器
//实例化一个可重复表格格式化器，dt.Select()是将DataTable转换成DataRow[]，rpt_begin表示的模板表格开始位置参数名，rpt_end表示的模板表格结束位置参数名
RepeaterFormatterBuilder <DataRow> tableFormatterBuilder = new RepeaterFormatterBuilder <DataRow>(dt.Select(), "rpt_begin", "rpt_end");
tableFormatterBuilder.AddFormatters(new Dictionary <string, Func <DataRow, object> > {
{ "sex", r => r["Col2"] }, //将模板表格中sex对应DataTable中的列Col2
{ "km", r => r["Col3"] }, //将模板表格中km对应DataTable中的列Col3
{ "score", r => r["Col4"] }, //将模板表格中score对应DataTable中的列Col4
{ "result", r => r["Col5"] }//将模板表格中result对应DataTable中的列Co5
});
PartFormatterBuilder <DataRow> partFormatterBuilder2 = new PartFormatterBuilder <DataRow>(); //实例化一个可嵌套的局部元素格式化器
partFormatterBuilder2.AddFormatter("name", r => r["Col1"]); //将模板表格中name对应DataTable中的列Col1
tableFormatterBuilder.AppendFormatterBuilder(partFormatterBuilder2); //添加到可重复表格格式化器中，作为其子格式化器
CellFormatterBuilder <DataRow> cellFormatterBuilder = new CellFormatterBuilder <DataRow>(); //实例化一个可嵌套的单元格格式化器
cellFormatterBuilder.AddFormatter("rptdate", r => DateTime.Today.ToString("yyyy-MM-dd HH:mm")); //将模板表格中rptdate的值设置为当前日期
tableFormatterBuilder.AppendFormatterBuilder(cellFormatterBuilder); //添加到可重复表格格式化器中，作为其子格式化器
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder); //添加到工作薄格式容器中，注意只有添加进去了才会生效
string excelPath = Export.ToExcelWithTemplate(templateFilePath, "multtable", formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}
public void TestExportToExcelWithTemplateByList3()
{
var schoolLevelList = SchoolLevel.GetList();
var classList = ClassInfo.GetListWithLevels();
string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/mb1.xls"; //获得EXCEL模板路径
SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //实例化一个模板数据格式化容器
PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();
partFormatterBuilder.AddFormatter("school", "跨越小学");
formatterContainers.AppendFormatterBuilder(partFormatterBuilder);
TableFormatterBuilder <SchoolLevel> tableFormatterBuilder = new TableFormatterBuilder <SchoolLevel>(schoolLevelList, "lv");//实例化一个表格格式化器
tableFormatterBuilder.AddFormatters(new Dictionary <string, Func <SchoolLevel, object> >
{
{ "lv", r => r.LevelName }, //模板参数与数据源SchoolLevel属性对应关系，下同
{ "clscount", r => r.ClassCount },
{ "lvmaster", r => r.Master }
});
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
RepeaterFormatterBuilder <KeyValuePair <string, List <ClassInfo> > > repeaterFormatterBuilder = new RepeaterFormatterBuilder <KeyValuePair <string, List <ClassInfo> > >(classList, "lv_begin", "lv_end");
repeaterFormatterBuilder.AddFormatter("lvitem", r => r.Key);
TableFormatterBuilder <KeyValuePair <string, List <ClassInfo> >, ClassInfo> tableFormatterBuilder2 = new TableFormatterBuilder <KeyValuePair <string, List <ClassInfo> >, ClassInfo>(r => r.Value, "class");
tableFormatterBuilder2.AddFormatter("class", r => r.ClassName);
tableFormatterBuilder2.AddFormatter("stucount", r => r.StudentCount);
tableFormatterBuilder2.AddFormatter("clsmaster", r => r.Master);
repeaterFormatterBuilder.AppendFormatterBuilder(tableFormatterBuilder2);
formatterContainers.AppendFormatterBuilder(repeaterFormatterBuilder);//添加到工作薄格式容器中，注意只有添加进去了才会生效
string excelPath = Export.ToExcelWithTemplate(templateFilePath, "school", formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}