Пример #1
0
        public void TestExportToExcelWithTemplateByDataTable2()
        {
            DataTable dt = GetDataTable();
            string    templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xls"; //获得EXCEL模板路径


            string excelPath = Export.ToExcelWithTemplate <DataRow>(templateFilePath, "table", dt.Select(), (data) =>
            {
                SheetFormatterContainer formatterContainers = new SheetFormatterContainer();               //实例化一个模板数据格式化容器

                PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();                    //实例化一个局部元素格式化器
                partFormatterBuilder.AddFormatter("Title", "跨越IT学员");                                      //将模板表格中Title的值设置为跨越IT学员
                formatterContainers.AppendFormatterBuilder(partFormatterBuilder);                          //添加到工作薄格式容器中,注意只有添加进去了才会生效

                CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();                    //实例化一个单元格格式化器
                cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm")); //将模板表格中rptdate的值设置为当前日期
                formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);                          //添加到工作薄格式容器中,注意只有添加进去了才会生效

                //实例化一个表格格式化器,data是已拆分后的数据源(这里是10条记录),name表示的模板表格中第一行第一个单元格要填充的数据参数名
                TableFormatterBuilder <DataRow> tableFormatterBuilder = new TableFormatterBuilder <DataRow>(data, "name"); //这里的数据源设置:data是重点
                tableFormatterBuilder.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
                });
                formatterContainers.AppendFormatterBuilder(tableFormatterBuilder); //添加到工作薄格式容器中,注意只有添加进去了才会生效
                return(formatterContainers);                                       //返回一个模板数据格式化容器
            }, 10);                                                                //注意这里的10表示动态生成新的工作薄,且每个工作薄显示10条记录


            Assert.IsTrue(File.Exists(excelPath));
        }
Пример #2
0
        public void TestExportToExcelWithTemplateByList()
        {
            List <Student>          studentList         = GetStudentList();                                      //获取数据
            string                  templateFilePath    = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer();                         //实例化一个模板数据格式化容器

            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();                              //实例化一个局部元素格式化器

            partFormatterBuilder.AddFormatter("Title", "跨越IT学员");                                                //将模板表格中Title的值设置为跨越IT学员
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);                                    //添加到工作薄格式容器中,注意只有添加进去了才会生效

            CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();                              //实例化一个单元格格式化器

            cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));           //将模板表格中rptdate的值设置为当前日期
            formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);                                    //添加到工作薄格式容器中,注意只有添加进去了才会生效

            //实例化一个表格格式化器,studentList本身就是可枚举的无需转换,name表示的模板表格中第一行第一个单元格要填充的数据参数名
            TableFormatterBuilder <Student> tableFormatterBuilder = new TableFormatterBuilder <Student>(studentList, "name");

            tableFormatterBuilder.AddFormatters(new Dictionary <string, Func <Student, object> > {
                { "name", r => r.Name },   //将模板表格中name对应Student对象中的属性Name
                { "sex", r => r.Sex },     //将模板表格中sex对应Student对象中的属性Sex
                { "km", r => r.KM },       //将模板表格中km对应Student对象中的属性KM
                { "score", r => r.Score }, //将模板表格中score对应Student对象中的属性Score
                { "result", r => r.Result }//将模板表格中result对应Student对象中的属性Result
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);

            string excelPath = Export.ToExcelWithTemplate(templateFilePath, "table", formatterContainers);

            Assert.IsTrue(File.Exists(excelPath));
        }
Пример #3
0
        public void ExportToExcelWithTemplateByList()
        {
            List <Student>          studentList         = GetStudentList();                                   //获取数据
            string                  templateFilePath    = AppDomain.CurrentDomain.BaseDirectory + "/cc2.xlt"; //获得EXCEL模板路径
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer();                      //实例化一个模板数据格式化容器

            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();                           //实例化一个局部元素格式化器

            partFormatterBuilder.AddFormatter("Title", "股票数据");                                               //将模板表格中Title的值设置为股票数据
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);                                 //添加到工作薄格式容器中,注意只有添加进去了才会生效

            CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();                           //实例化一个单元格格式化器

            cellFormatterBuilder.AddFormatter("rptdate", "GUDCUB");                                           //将模板表格中rptdate的值设置为GUDCUB
            formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);                                 //添加到工作薄格式容器中,注意只有添加进去了才会生效

            //实例化一个表格格式化器,studentList本身就是可枚举的无需转换,name表示的模板表格中第一行第一个单元格要填充的数据参数名
            TableFormatterBuilder <Student> tableFormatterBuilder = new TableFormatterBuilder <Student>(studentList, "date");

            tableFormatterBuilder.AddFormatters(new Dictionary <string, Func <Student, object> > {
                { "date", r => r.Date.ToString() }, //将模板表格中date对应Student对象中的属性Name
                { "open", r => r.Open },            //将模板表格中open对应Student对象中的属性Sex
                { "high", r => r.High },            //将模板表格中high对应Student对象中的属性KM
                { "low", r => r.Low },              //将模板表格中low对应Student对象中的属性Score
                { "close", r => r.Close }//将模板表格中close对应Student对象中的属性Result
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);

            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, "table", formatterContainers);

            Assert.IsTrue(File.Exists(excelPath));
        }
Пример #4
0
        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));
        }
Пример #5
0
        public void TestInsertPic()
        {
            DataTable dt = GetDataTable();                                                      //获取数据
            string    templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //获得EXCEL模板路径
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer();        //实例化一个模板数据格式化容器


            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();                    //实例化一个局部元素格式化器

            partFormatterBuilder.AddFormatter("Title", "跨越IT学员");                                      //将模板表格中Title的值设置为跨越IT学员d
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);                          //添加到工作薄格式容器中,注意只有添加进去了才会生效

            CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();                    //实例化一个单元格格式化器

            cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm")); //将模板表格中rptdate的值设置为当前日期
            formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);                          //添加到工作薄格式容器中,注意只有添加进去了才会生效

            //实例化一个表格格式化器,dt.Select()是将DataTable转换成DataRow[],name表示的模板表格中第一行第一个单元格要填充的数据参数名
            TableFormatterBuilder <DataRow> tableFormatterBuilder = new TableFormatterBuilder <DataRow>(dt.Select(), "name");

            tableFormatterBuilder.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
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加进去了才会生效


            string picPath = AppDomain.CurrentDomain.BaseDirectory + "\\tz.png";                      //图片路径

            PictureWithShapeFormatterBuilder pictureBuilder = new PictureWithShapeFormatterBuilder(); //实例化一个图片关联图形格式化器

            pictureBuilder.AddFormatter(picPath);                                                     //当sheet中只有一个图形时,我们可以省略指定区域,那么默认就是把整个工作薄区域当成一个寻找图形区域,若sheet中包含多个,则应指定区域,如下语句
            //pictureBuilder.AddFormatter(picPath,5,60000, 0, 3, false);//第一个参数为图片路径,中间4个参数为数字型指定图形寻找的工作薄区域(行索引,列索引,索引从0开始计),最后一个为是否自适应大小,一般不建议使用,除非压缩图片

            //pictureBuilder.AddFormatter(picPath, 5, 60000, 0, 255, false);//图形可能下移,可能右移,那么将结束行设为可能最大值:60000,结束列设为可能最大值:255

            //pictureBuilder.AddFormatter(new PictureWithShapeInfo(picPath, new SheetRange() {MinRow=5,MinColumn=0 },false));//此处只指定开始行与开始列,与上面差不多,但建议使用上面的用法

            formatterContainers.AppendFormatterBuilder(pictureBuilder);

            string excelPath = Export.ToExcelWithTemplate(templateFilePath, "table", formatterContainers);

            Assert.IsTrue(File.Exists(excelPath));
        }
Пример #6
0
        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));
        }
Пример #7
0
        public void TestInsertPic2()
        {
            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/pic.xls";             //获得EXCEL模板路径
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer();              //实例化一个模板数据格式化容器

            string picPath = AppDomain.CurrentDomain.BaseDirectory + "\\tz.png";                      //图片路径

            PictureWithShapeFormatterBuilder pictureBuilder = new PictureWithShapeFormatterBuilder(); //实例化一个图片关联图形格式化器

            pictureBuilder.AddFormatter(picPath);
            formatterContainers.AppendFormatterBuilder(pictureBuilder);

            string excelPath = Export.ToExcelWithTemplate(templateFilePath, "pic", formatterContainers);

            Assert.IsTrue(File.Exists(excelPath));
        }
Пример #8
0
        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));
        }