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 TestExportToExcelWithTemplateByList2() { var schoolLevelList = SchoolLevel.GetList(); var classList = ClassInfo.GetList(); string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/mb.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 <ClassInfo> repeaterFormatterBuilder = new RepeaterFormatterBuilder <ClassInfo>(classList, "lv_begin", "lv_end"); //实例化一个可重复表格格式化器 repeaterFormatterBuilder.AddFormatters(new Dictionary <string, Func <ClassInfo, object> > { { "class", r => r.ClassName }, //模板参数与数据源ClassInfo属性对应关系,下同 { "stucount", r => r.StudentCount }, { "clsmaster", r => r.Master }, { "lvitem", r => r.LevelName } }); 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)); }