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));
}
public void TestExportToExcelWithTemplateByDataSet()
{
var ds = GetDataSet();
string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/mb2.xls"; //获得EXCEL模板路径
Dictionary <string, SheetFormatterContainer> formatterContainerDic = new Dictionary <string, SheetFormatterContainer>(); //实例化一个模板数据格式化容器数组，包含两个SheetFormatterContainer用于格式化两个工作薄
#region 创建第一个工作薄格式化容器，并设置相关参数对应关系
SheetFormatterContainer formatterContainer1 = new SheetFormatterContainer();
PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();
partFormatterBuilder.AddFormatter("school", "跨越小学");
formatterContainer1.AppendFormatterBuilder(partFormatterBuilder);
TableFormatterBuilder <DataRow> tableFormatterBuilder = new TableFormatterBuilder <DataRow>(ds.Tables[0].Select(), "lv");//实例化一个表格格式化器
tableFormatterBuilder.AddFormatters(new Dictionary <string, Func <DataRow, object> >
{
{ "lv", r => r["Col1"] }, //模板参数与数据源DataTable属性对应关系，下同
{ "clscount", r => r["Col2"] },
{ "lvmaster", r => r["Col3"] }
});
formatterContainer1.AppendFormatterBuilder(tableFormatterBuilder); //添加到工作薄格式容器中，注意只有添加进去了才会生效
RepeaterFormatterBuilder <DataRow> repeaterFormatterBuilder = new RepeaterFormatterBuilder <DataRow>(ds.Tables[1].Select(), "lv_begin", "lv_end"); //实例化一个可重复表格格式化器
repeaterFormatterBuilder.AddFormatters(new Dictionary <string, Func <DataRow, object> > {
{ "class", r => r["Col1"] }, //模板参数与数据源ClassInfo属性对应关系，下同
{ "stucount", r => r["Col2"] },
{ "clsmaster", r => r["Col3"] },
{ "lvitem", r => r["Col4"] }
});
formatterContainer1.AppendFormatterBuilder(repeaterFormatterBuilder); //添加到工作薄格式容器中，注意只有添加进去了才会生效
formatterContainerDic.Add("table1", formatterContainer1); //添加到工作薄格式容器数组中，注意此处的Key值为模板上工作薄的名称，此处即为：table1
#endregion
#region 创建第二个工作薄格式化容器，并设置相关参数对应关系
SheetFormatterContainer formatterContainer2 = new SheetFormatterContainer(); //实例化一个模板数据格式化容器
PartFormatterBuilder partFormatterBuilder2 = new PartFormatterBuilder(); //实例化一个局部元素格式化器
partFormatterBuilder2.AddFormatter("Title", "跨越IT学员"); //将模板表格中Title的值设置为跨越IT学员
formatterContainer2.AppendFormatterBuilder(partFormatterBuilder2); //添加到工作薄格式容器中，注意只有添加进去了才会生效
CellFormatterBuilder cellFormatterBuilder2 = new CellFormatterBuilder(); //实例化一个单元格格式化器
cellFormatterBuilder2.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm")); //将模板表格中rptdate的值设置为当前日期
formatterContainer2.AppendFormatterBuilder(cellFormatterBuilder2); //添加到工作薄格式容器中，注意只有添加进去了才会生效
//实例化一个表格格式化器，dt.Select()是将DataTable转换成DataRow[]，name表示的模板表格中第一行第一个单元格要填充的数据参数名
TableFormatterBuilder <DataRow> tableFormatterBuilder2 = new TableFormatterBuilder <DataRow>(ds.Tables[2].Select(), "name");
tableFormatterBuilder2.AddFormatters(new Dictionary <string, Func <DataRow, object> > {
{ "name", r => r["Col1"] }, //将模板表格中name对应DataTable中的列Col1
{ "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
});
formatterContainer2.AppendFormatterBuilder(tableFormatterBuilder2); //添加到工作薄格式容器中，注意只有添加进去了才会生效
formatterContainerDic.Add("table2", formatterContainer2); //添加到工作薄格式容器数组中，注意此处的Key值为模板上工作薄的名称，此处即为：table2
#endregion
string excelPath = Export.ToExcelWithTemplate(templateFilePath, formatterContainerDic);
Assert.IsTrue(File.Exists(excelPath));
}
