国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

Home php教程 php手冊(cè) Share my import and export EXCEL class library based on NPOI ExcelReport: ExcelUt

Share my import and export EXCEL class library based on NPOI ExcelReport: ExcelUt

Jul 06, 2016 pm 01:30 PM
share based on accomplish import

自ExcelUtility類(lèi)推出以來(lái),經(jīng)過(guò)項(xiàng)目中的實(shí)際使用與不斷完善,現(xiàn)在又做了許多的優(yōu)化并增加了許多的功能,本篇不再講述原理,直接貼出示例代碼以及相關(guān)的模板、結(jié)果圖,以便大家快速掌握,另外這些示例說(shuō)明我也已同步到GIT中,大家可以下載與學(xué)習(xí),不足之處,

自ExcelUtility類(lèi)推出以來(lái),經(jīng)過(guò)項(xiàng)目中的實(shí)際使用與不斷完善,現(xiàn)在又做了許多的優(yōu)化并增加了許多的功能,本篇不再講述原理,直接貼出示例代碼以及相關(guān)的模板、結(jié)果圖,以便大家快速掌握,另外這些示例說(shuō)明我也已同步到GIT中,大家可以下載與學(xué)習(xí),不足之處,敬請(qǐng)見(jiàn)諒,謝謝!

一、ExcelUtility類(lèi)庫(kù)操作說(shuō)明(模板導(dǎo)出示例)

1.

        /// <summary>
        /// 測(cè)試方法:測(cè)試依據(jù)模板+DataTable來(lái)生成EXCEL
        /// </summary>
        [TestMethod]
        public void TestExportToExcelWithTemplateByDataTable()
        {
            DataTable dt = GetDataTable();//獲取數(shù)據(jù)
            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //獲得EXCEL模板路徑
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器

            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//實(shí)例化一個(gè)局部元素格式化器
            partFormatterBuilder.AddFormatter("Title", "跨越IT學(xué)員");//將模板表格中Title的值設(shè)置為跨越IT學(xué)員
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//實(shí)例化一個(gè)單元格格式化器
            cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//將模板表格中rptdate的值設(shè)置為當(dāng)前日期
            formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            //實(shí)例化一個(gè)表格格式化器,dt.Select()是將DataTable轉(zhuǎn)換成DataRow[],name表示的模板表格中第一行第一個(gè)單元格要填充的數(shù)據(jù)參數(shù)名
            TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(dt.Select(), "name");
            tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{
                {"name",r=>r["Col1"]},//將模板表格中name對(duì)應(yīng)DataTable中的列Col1
                {"sex",r=>r["Col2"]},//將模板表格中sex對(duì)應(yīng)DataTable中的列Col2
                {"km",r=>r["Col3"]},//將模板表格中km對(duì)應(yīng)DataTable中的列Col3
                {"score",r=>r["Col4"]},//將模板表格中score對(duì)應(yīng)DataTable中的列Col4
                {"result",r=>r["Col5"]}//將模板表格中result對(duì)應(yīng)DataTable中的列Co5
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效
            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, "table", formatterContainers);
            Assert.IsTrue(File.Exists(excelPath));
        }

模板如下:

導(dǎo)出結(jié)果如下:

2.

        /// <summary>
        /// 測(cè)試方法:測(cè)試依據(jù)模板+List來(lái)生成EXCEL
        /// </summary>
        [TestMethod]
        public void TestExportToExcelWithTemplateByList()
        {
            List<Student> studentList = GetStudentList();//獲取數(shù)據(jù)
            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xlsx"; //獲得EXCEL模板路徑
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器

            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//實(shí)例化一個(gè)局部元素格式化器
            partFormatterBuilder.AddFormatter("Title", "跨越IT學(xué)員");//將模板表格中Title的值設(shè)置為跨越IT學(xué)員
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//實(shí)例化一個(gè)單元格格式化器
            cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//將模板表格中rptdate的值設(shè)置為當(dāng)前日期
            formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            //實(shí)例化一個(gè)表格格式化器,studentList本身就是可枚舉的無(wú)需轉(zhuǎn)換,name表示的模板表格中第一行第一個(gè)單元格要填充的數(shù)據(jù)參數(shù)名
            TableFormatterBuilder<Student> tableFormatterBuilder = new TableFormatterBuilder<Student>(studentList, "name");
            tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<Student, object>>{
                {"name",r=>r.Name},//將模板表格中name對(duì)應(yīng)Student對(duì)象中的屬性Name
                {"sex",r=>r.Sex},//將模板表格中sex對(duì)應(yīng)Student對(duì)象中的屬性Sex
                {"km",r=>r.KM},//將模板表格中km對(duì)應(yīng)Student對(duì)象中的屬性KM
                {"score",r=>r.Score},//將模板表格中score對(duì)應(yīng)Student對(duì)象中的屬性Score
                {"result",r=>r.Result}//將模板表格中result對(duì)應(yīng)Student對(duì)象中的屬性Result
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);

            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, "table", formatterContainers);
            Assert.IsTrue(File.Exists(excelPath));

        }

模板同上一個(gè)模板

導(dǎo)出結(jié)果如下:

3.

        /// <summary>
        /// 測(cè)試方法:測(cè)試依據(jù)模板+DataTable來(lái)生成多表格EXCEL(注意:由于ExcelReport框架限制,目前僅支持模板文件格式為:xls)
        /// </summary>
        [TestMethod]
        public void TestExportToRepeaterExcelWithTemplateByDataTable()
        {
            DataTable dt = GetDataTable();//獲取數(shù)據(jù)
            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel2.xls"; //獲得EXCEL模板路徑
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器

            //實(shí)例化一個(gè)可重復(fù)表格格式化器,dt.Select()是將DataTable轉(zhuǎn)換成DataRow[],rpt_begin表示的模板表格開(kāi)始位置參數(shù)名,rpt_end表示的模板表格結(jié)束位置參數(shù)名
            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對(duì)應(yīng)DataTable中的列Col2
                {"km",r=>r["Col3"]},//將模板表格中km對(duì)應(yīng)DataTable中的列Col3
                {"score",r=>r["Col4"]},//將模板表格中score對(duì)應(yīng)DataTable中的列Col4
                {"result",r=>r["Col5"]}//將模板表格中result對(duì)應(yīng)DataTable中的列Co5
            });

            PartFormatterBuilder<DataRow> partFormatterBuilder2 = new PartFormatterBuilder<DataRow>();//實(shí)例化一個(gè)可嵌套的局部元素格式化器
            partFormatterBuilder2.AddFormatter("name", r => r["Col1"]);//將模板表格中name對(duì)應(yīng)DataTable中的列Col1
            tableFormatterBuilder.AppendFormatterBuilder(partFormatterBuilder2);//添加到可重復(fù)表格格式化器中,作為其子格式化器


            CellFormatterBuilder<DataRow> cellFormatterBuilder = new CellFormatterBuilder<DataRow>();//實(shí)例化一個(gè)可嵌套的單元格格式化器
            cellFormatterBuilder.AddFormatter("rptdate", r => DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//將模板表格中rptdate的值設(shè)置為當(dāng)前日期
            tableFormatterBuilder.AppendFormatterBuilder(cellFormatterBuilder);//添加到可重復(fù)表格格式化器中,作為其子格式化器

            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, "multtable", formatterContainers);
            Assert.IsTrue(File.Exists(excelPath));
        }

模板如下:

導(dǎo)出結(jié)果如下:

4.

        /// <summary>
        /// 測(cè)試方法:測(cè)試依據(jù)復(fù)雜模板(含固定表格,可重復(fù)表格)+DataTable來(lái)生成EXCEL (注意:由于ExcelReport框架限制,目前僅支持模板文件格式為:xls)
        /// </summary>
        [TestMethod]
        public void TestExportToExcelWithTemplateByList2()
        {
            var schoolLevelList = SchoolLevel.GetList();
            var classList = ClassInfo.GetList();
            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/mb.xls"; //獲得EXCEL模板路徑
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器

            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();
            partFormatterBuilder.AddFormatter("school", "跨越小學(xué)");
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);

            TableFormatterBuilder<SchoolLevel> tableFormatterBuilder = new TableFormatterBuilder<SchoolLevel>(schoolLevelList, "lv");//實(shí)例化一個(gè)表格格式化器
            tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<SchoolLevel, object>>
            {
                {"lv",r=>r.LevelName}, //模板參數(shù)與數(shù)據(jù)源SchoolLevel屬性對(duì)應(yīng)關(guān)系,下同
                {"clscount",r=>r.ClassCount},
                {"lvmaster",r=>r.Master}
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            RepeaterFormatterBuilder<ClassInfo> repeaterFormatterBuilder = new RepeaterFormatterBuilder<ClassInfo>(classList, "lv_begin", "lv_end");//實(shí)例化一個(gè)可重復(fù)表格格式化器
            repeaterFormatterBuilder.AddFormatters(new Dictionary<string, Func<ClassInfo, object>> { 
                {"class",r=>r.ClassName}, //模板參數(shù)與數(shù)據(jù)源ClassInfo屬性對(duì)應(yīng)關(guān)系,下同
                {"stucount",r=>r.StudentCount},
                {"clsmaster",r=>r.Master},
                {"lvitem",r=>r.LevelName}
            });
            formatterContainers.AppendFormatterBuilder(repeaterFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, "school", formatterContainers);
            Assert.IsTrue(File.Exists(excelPath));

        }

模板如下:

導(dǎo)出結(jié)果如下:

5.

        /// <summary>
        /// 測(cè)試方法:測(cè)試依據(jù)復(fù)雜模板(含固定表格,可重復(fù)表格中嵌套表格)+DataTable來(lái)生成EXCEL (注意:由于ExcelReport框架限制,目前僅支持模板文件格式為:xls)
        /// </summary>
        [TestMethod]
        public void TestExportToExcelWithTemplateByList3()
        {
            var schoolLevelList = SchoolLevel.GetList();
            var classList = ClassInfo.GetListWithLevels();

            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/mb1.xls"; //獲得EXCEL模板路徑
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器

            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();
            partFormatterBuilder.AddFormatter("school", "跨越小學(xué)");
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);

            TableFormatterBuilder<SchoolLevel> tableFormatterBuilder = new TableFormatterBuilder<SchoolLevel>(schoolLevelList, "lv");//實(shí)例化一個(gè)表格格式化器
            tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<SchoolLevel, object>>
            {
                {"lv",r=>r.LevelName}, //模板參數(shù)與數(shù)據(jù)源SchoolLevel屬性對(duì)應(yīng)關(guān)系,下同
                {"clscount",r=>r.ClassCount},
                {"lvmaster",r=>r.Master}
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            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);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, "school", formatterContainers);
            Assert.IsTrue(File.Exists(excelPath));

        }

模板如下:

導(dǎo)出結(jié)果如下:

6.

        /// <summary>
        /// 測(cè)試方法:測(cè)試依據(jù)復(fù)雜模板(多工作薄,且含固定表格,可重復(fù)表格)+DataSet來(lái)生成EXCEL,只支持XLS
        /// </summary>
        [TestMethod]
        public void TestExportToExcelWithTemplateByDataSet()
        {
            var ds = GetDataSet();
            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/mb2.xls"; //獲得EXCEL模板路徑
            Dictionary<string, SheetFormatterContainer> formatterContainerDic = new Dictionary<string, SheetFormatterContainer>(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器數(shù)組,包含兩個(gè)SheetFormatterContainer用于格式化兩個(gè)工作薄


            #region 創(chuàng)建第一個(gè)工作薄格式化容器,并設(shè)置相關(guān)參數(shù)對(duì)應(yīng)關(guān)系

            SheetFormatterContainer formatterContainer1 = new SheetFormatterContainer();

            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();
            partFormatterBuilder.AddFormatter("school", "跨越小學(xué)");
            formatterContainer1.AppendFormatterBuilder(partFormatterBuilder);

            TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(ds.Tables[0].Select(), "lv");//實(shí)例化一個(gè)表格格式化器
            tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>
            {
                {"lv",r=>r["Col1"]}, //模板參數(shù)與數(shù)據(jù)源DataTable屬性對(duì)應(yīng)關(guān)系,下同
                {"clscount",r=>r["Col2"]},
                {"lvmaster",r=>r["Col3"]}
            });
            formatterContainer1.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            RepeaterFormatterBuilder<DataRow> repeaterFormatterBuilder = new RepeaterFormatterBuilder<DataRow>(ds.Tables[1].Select(), "lv_begin", "lv_end");//實(shí)例化一個(gè)可重復(fù)表格格式化器
            repeaterFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>> { 
                {"class",r=>r["Col1"]}, //模板參數(shù)與數(shù)據(jù)源ClassInfo屬性對(duì)應(yīng)關(guān)系,下同
                {"stucount",r=>r["Col2"]},
                {"clsmaster",r=>r["Col3"]},
                {"lvitem",r=>r["Col4"]}
            });
            formatterContainer1.AppendFormatterBuilder(repeaterFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            formatterContainerDic.Add("table1", formatterContainer1);//添加到工作薄格式容器數(shù)組中,注意此處的Key值為模板上工作薄的名稱(chēng),此處即為:table1

            #endregion


            #region 創(chuàng)建第二個(gè)工作薄格式化容器,并設(shè)置相關(guān)參數(shù)對(duì)應(yīng)關(guān)系

            SheetFormatterContainer formatterContainer2 = new SheetFormatterContainer(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器

            PartFormatterBuilder partFormatterBuilder2 = new PartFormatterBuilder();//實(shí)例化一個(gè)局部元素格式化器
            partFormatterBuilder2.AddFormatter("Title", "跨越IT學(xué)員");//將模板表格中Title的值設(shè)置為跨越IT學(xué)員
            formatterContainer2.AppendFormatterBuilder(partFormatterBuilder2);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            CellFormatterBuilder cellFormatterBuilder2 = new CellFormatterBuilder();//實(shí)例化一個(gè)單元格格式化器
            cellFormatterBuilder2.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//將模板表格中rptdate的值設(shè)置為當(dāng)前日期
            formatterContainer2.AppendFormatterBuilder(cellFormatterBuilder2);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            //實(shí)例化一個(gè)表格格式化器,dt.Select()是將DataTable轉(zhuǎn)換成DataRow[],name表示的模板表格中第一行第一個(gè)單元格要填充的數(shù)據(jù)參數(shù)名
            TableFormatterBuilder<DataRow> tableFormatterBuilder2 = new TableFormatterBuilder<DataRow>(ds.Tables[2].Select(), "name");
            tableFormatterBuilder2.AddFormatters(new Dictionary<string, Func<DataRow, object>>{
                {"name",r=>r["Col1"]},//將模板表格中name對(duì)應(yīng)DataTable中的列Col1
                {"sex",r=>r["Col2"]},//將模板表格中sex對(duì)應(yīng)DataTable中的列Col2
                {"km",r=>r["Col3"]},//將模板表格中km對(duì)應(yīng)DataTable中的列Col3
                {"score",r=>r["Col4"]},//將模板表格中score對(duì)應(yīng)DataTable中的列Col4
                {"result",r=>r["Col5"]}//將模板表格中result對(duì)應(yīng)DataTable中的列Co5
            });
            formatterContainer2.AppendFormatterBuilder(tableFormatterBuilder2);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            formatterContainerDic.Add("table2", formatterContainer2);//添加到工作薄格式容器數(shù)組中,注意此處的Key值為模板上工作薄的名稱(chēng),此處即為:table2

            #endregion

            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, formatterContainerDic);
            Assert.IsTrue(File.Exists(excelPath));

        }

模板如下:

導(dǎo)出結(jié)果如下:

二、ExcelUtility類(lèi)庫(kù)操作說(shuō)明(嵌入圖片示例)

一、 制作模板(含圖片)
1. 制作模板的文件格式需為兼容格式,即:xls或xlt;
2. 模板變量(或稱(chēng)為占位符)定義與之前相同,即:$[變量名];
3. 圖片變量定義如下:
a) 繪制一個(gè)圖形,圖形形狀盡可能的與要顯示的圖片相同,比如:印章,則可繪制一個(gè)圓形;
b) 圖形必需是透明背景,邊框可要可不要,建議留著,這樣后續(xù)調(diào)整比較方便,如下圖中的藍(lán)色透明背景圓形:

c) 圖形大小盡可能與要顯示的圖片大小相同,如下圖示:

由于EXCEL上大小默認(rèn)采用厘米,而圖片一般采用像素,所以需要自己換算一下像素對(duì)應(yīng)的厘米數(shù)(也可將EXCEL計(jì)算單位設(shè)為像素,方法自行網(wǎng)上查找);也可網(wǎng)上下載單位轉(zhuǎn)換工具
另外圖形屬性建議設(shè)置成如下圖:

溫馨提示:圖形形狀、屬性若未設(shè)置一般不影響導(dǎo)出效果,但不排除其它異常情況,圖形大小是一定要設(shè)置,且盡可能與要顯示圖形大?。ǜ?、寬)相同,否則有可能造成導(dǎo)出變形

代碼示例:

    /// <summary>
        /// 測(cè)試方法:測(cè)試依據(jù)模板+DataTable+圖片來(lái)生成包含圖片的EXCEL,只支持XLS
        /// </summary>
        [TestMethod]
        public void TestInsertPic()
        {
            DataTable dt = GetDataTable();//獲取數(shù)據(jù)
            string templateFilePath = AppDomain.CurrentDomain.BaseDirectory + "/excel.xls"; //獲得EXCEL模板路徑
            SheetFormatterContainer formatterContainers = new SheetFormatterContainer(); //實(shí)例化一個(gè)模板數(shù)據(jù)格式化容器


            PartFormatterBuilder partFormatterBuilder = new PartFormatterBuilder();//實(shí)例化一個(gè)局部元素格式化器
            partFormatterBuilder.AddFormatter("Title", "跨越IT學(xué)員");//將模板表格中Title的值設(shè)置為跨越IT學(xué)員d
            formatterContainers.AppendFormatterBuilder(partFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            CellFormatterBuilder cellFormatterBuilder = new CellFormatterBuilder();//實(shí)例化一個(gè)單元格格式化器
            cellFormatterBuilder.AddFormatter("rptdate", DateTime.Today.ToString("yyyy-MM-dd HH:mm"));//將模板表格中rptdate的值設(shè)置為當(dāng)前日期
            formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效

            //實(shí)例化一個(gè)表格格式化器,dt.Select()是將DataTable轉(zhuǎn)換成DataRow[],name表示的模板表格中第一行第一個(gè)單元格要填充的數(shù)據(jù)參數(shù)名
            TableFormatterBuilder<DataRow> tableFormatterBuilder = new TableFormatterBuilder<DataRow>(dt.Select(), "name");
            tableFormatterBuilder.AddFormatters(new Dictionary<string, Func<DataRow, object>>{
                {"name",r=>r["Col1"]},//將模板表格中name對(duì)應(yīng)DataTable中的列Col1
                {"sex",r=>r["Col2"]},//將模板表格中sex對(duì)應(yīng)DataTable中的列Col2
                {"km",r=>r["Col3"]},//將模板表格中km對(duì)應(yīng)DataTable中的列Col3
                {"score",r=>r["Col4"]},//將模板表格中score對(duì)應(yīng)DataTable中的列Col4
                {"result",r=>r["Col5"]}//將模板表格中result對(duì)應(yīng)DataTable中的列Co5
            });
            formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);//添加到工作薄格式容器中,注意只有添加進(jìn)去了才會(huì)生效


            string picPath = AppDomain.CurrentDomain.BaseDirectory + "\\tz.png";//圖片路徑
            PictureWithShapeFormatterBuilder pictureBuilder = new PictureWithShapeFormatterBuilder();//實(shí)例化一個(gè)圖片關(guān)聯(lián)圖形格式化器
            //pictureBuilder.AddFormatter(picPath);//當(dāng)sheet中只有一個(gè)圖形時(shí),我們可以省略指定區(qū)域,那么默認(rèn)就是把整個(gè)工作薄區(qū)域當(dāng)成一個(gè)尋找圖形區(qū)域,若sheet中包含多個(gè),則應(yīng)指定區(qū)域,替換成如下語(yǔ)句
            pictureBuilder.AddFormatter(picPath,5,60000, 0, 3, false);//第一個(gè)參數(shù)為圖片路徑,中間4個(gè)參數(shù)為數(shù)字型指定圖形尋找的工作薄區(qū)域(行索引,列索引,索引從0開(kāi)始計(jì)),最后一個(gè)為是否自適應(yīng)大小,一般不建議使用,除非壓縮圖片
            formatterContainers.AppendFormatterBuilder(pictureBuilder);

            string excelPath = ExcelUtility.Export.ToExcelWithTemplate(templateFilePath, "table", formatterContainers);
            Assert.IsTrue(File.Exists(excelPath));
        }

模板如下:

注意圖片若需要為透明背景格式,則必需使用PNG格式,NPOI支持的圖片主要格式有:PNG,JPG

導(dǎo)出結(jié)果如下:

溫馨提示:
pictureBuilder.AddFormatter(picPath);//當(dāng)sheet中只有一個(gè)圖形時(shí),我們可以省略指定區(qū)域,那么默認(rèn)就是把整個(gè)工作薄區(qū)域當(dāng)成一個(gè)尋找圖形區(qū)域,若sheet中包含多個(gè),則應(yīng)指定區(qū)域,替換成如下語(yǔ)句
pictureBuilder.AddFormatter(picPath,5,60000, 0, 3, false);//第一個(gè)參數(shù)為圖片路徑,中間4個(gè)參數(shù)為數(shù)字型指定圖形尋找的工作薄區(qū)域(行索引(起止),列索引(起止),索引從0開(kāi)始計(jì)),最后一個(gè)為是否自適應(yīng)大小,一般不建議使用,除非壓縮圖片


如果圖形可能隨單元格進(jìn)行位置調(diào)整,那么在指定圖形區(qū)域時(shí)需注意,如果圖形會(huì)隨單元格下移,那么結(jié)束行索引(MinRow)就需要指定一個(gè)可能的最大值或不指定,如果圖形會(huì)隨單元格右移,那么結(jié)束列索引(MinColumn)就需要指定一個(gè)可能的最大值或不指定,如果存在多個(gè)圖形區(qū)域,則上述情況都必需給定具體值(可能的最大值),以免造成區(qū)域交叉,從而導(dǎo)致圖片顯示不正確,如下示例:

//圖形可能下移,可能右移,那么將結(jié)束行設(shè)為可能最大值:60000,結(jié)束列設(shè)為可能最大值:255
pictureBuilder.AddFormatter(picPath, 5, 60000, 0, 255, false);

//此處只指定開(kāi)始行與開(kāi)始列,與上面差不多,但建議使用上面的用法
pictureBuilder.AddFormatter(new PictureWithShapeInfo(picPath, new SheetRange() {MinRow=5,MinColumn=0 },false));

特別說(shuō)明:

1.本類(lèi)庫(kù)是基于NPOI+ExcelReport,所有功能凡我的類(lèi)庫(kù)能夠?qū)崿F(xiàn)的,NPOI與ExcelReport都可以實(shí)現(xiàn),只是用法及復(fù)雜程度不同而矣,我封裝的目的就是為了降低大家的學(xué)習(xí)難度,提高使用效率,免費(fèi)且開(kāi)源,源代碼同步更新至開(kāi)源社區(qū)的GIT目錄中,具體地址請(qǐng)看我該系列之前的文章有列出,在此就不再說(shuō)明。

2.上述圖片關(guān)聯(lián)圖形顯示功能我是在ExcelReport基礎(chǔ)上增加了一個(gè)PictureWithShapeFormatter類(lèi)及其相關(guān)的類(lèi):PictureWithShapeInfo、SheetRange,因沒(méi)有關(guān)聯(lián)GIT,所以是在本地更新的,這幾個(gè)類(lèi)的代碼如下:

PictureWithShapeFormatter:

using NPOI.Extend;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExcelReport
{
    public class PictureWithShapeFormatter : ElementFormatter
    {
        PRivate PictureWithShapeInfo PictureInfo = null;

        public PictureWithShapeFormatter(PictureWithShapeInfo pictureInfo)
        {
            this.PictureInfo = pictureInfo;
        }


        public override void Format(SheetAdapter sheetAdapter)
        {
            var sheet = sheetAdapter.CurrentSheet;
            var shapes = PictureInfo.GetShapes(sheet);
            bool isCompatible = false;
            if (sheet is HSSFSheet)
            {
                isCompatible = true;
            }

            if (shapes == null || shapes.Count <= 0)
            {
                throw new Exception(string.Format("未能獲取到工作薄[{0}]指定區(qū)域的圖形對(duì)象列表!", sheet.SheetName));
            }

            byte[] bytes = System.IO.File.ReadAllBytes(PictureInfo.FilePath);
            int pictureIdx = -1;
            IDrawing drawing = null;
            IClientAnchor anchor = null;
            if (isCompatible)
            {
                var shape = shapes[0] as HSSFShape;
                anchor = shape.Anchor as IClientAnchor;
                drawing = shape.Patriarch;
                shape.LineStyle = LineStyle.None;
            }
            else
            {
                var shape = shapes[0] as XSSFShape;
                anchor = shape.GetAnchor() as IClientAnchor;
                drawing = shape.GetDrawing();
                shape.LineStyle = LineStyle.None;
            }

            pictureIdx = sheet.Workbook.AddPicture(bytes, PictureInfo.PictureType);
            var picture = drawing.CreatePicture(anchor, pictureIdx);
            if (PictureInfo.AutoSize)
            {
                picture.Resize();
            }
        }


    }
}

PictureWithShapeInfo、SheetRange:

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.Extend;

namespace ExcelReport
{
    /// <summary>
    /// 圖片關(guān)聯(lián)圖形信息
    /// 作者:Zuowenjun
    /// </summary>
    public class PictureWithShapeInfo
    {
        private SheetRange _SheetRange = new SheetRange();

        public string FilePath { get; set; }

        public PictureType PictureType { get; set; }

        public SheetRange ShapeRange
        {
            get { return _SheetRange; }
            set
            {
                if (value != null)
                {
                    _SheetRange = value;
                }
            }
        }

        public bool AutoSize { get; set; }

        public PictureWithShapeInfo()
        { }

        public PictureWithShapeInfo(string filePath, SheetRange shapeRange = null, bool autoSize = false)
        {
            this.FilePath = filePath;
            this.ShapeRange = shapeRange;
            this.AutoSize = autoSize;
            this.PictureType = GetPictureType(filePath);
        }


        public List<object> GetShapes(ISheet sheet)
        {
            List<object> shapeAllList = new List<object>();
            var shapeContainer = sheet.DrawingPatriarch;
            if (sheet is HSSFSheet)
            {
                var shapeContainerHSSF = sheet.DrawingPatriarch as HSSFShapeContainer;
                if (null != shapeContainer)
                {
                    var shapeList = shapeContainerHSSF.Children;
                    foreach (var shape in shapeList)
                    {
                        if (shape is HSSFShape && shape.Anchor is HSSFClientAnchor)
                        {
                            var anchor = shape.Anchor as HSSFClientAnchor;
                            if (IsInternalOrIntersect(ShapeRange.MinRow, ShapeRange.MaxRow, ShapeRange.MinColumn, ShapeRange.MaxColumn, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, true))
                            {
                                shapeAllList.Add(shape);
                            }
                        }
                    }
                }
            }
            else
            {
                var documentPartList = (sheet as XSSFSheet).GetRelations();
                foreach (var documentPart in documentPartList)
                {
                    if (documentPart is XSSFDrawing)
                    {
                        var drawing = (XSSFDrawing)documentPart;
                        var shapeList = drawing.GetShapes();
                        foreach (var shape in shapeList)
                        {
                            var anchorResult = shape.GetAnchor();
                            if (shape is XSSFShape && anchorResult is XSSFClientAnchor)
                            {
                                var anchor = anchorResult as XSSFClientAnchor;
                                if (IsInternalOrIntersect(ShapeRange.MinRow, ShapeRange.MaxRow, ShapeRange.MinColumn, ShapeRange.MaxColumn, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, true))
                                {
                                    shapeAllList.Add(shape);
                                }
                            }
                        }
                    }
                }
            }

            return shapeAllList;
        }


        private PictureType GetPictureType(string filePath)
        {
            string ext = Path.GetExtension(filePath).ToUpper();
            switch (ext)
            {
                case ".JPG": { return PictureType.JPEG; }
                case ".PNG": { return PictureType.PNG; }
                default: { return PictureType.None; }
            }
        }

        private bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
                                        int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
        {
            int _rangeMinRow = rangeMinRow ?? pictureMinRow;
            int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
            int _rangeMinCol = rangeMinCol ?? pictureMinCol;
            int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;

            if (onlyInternal)
            {
                return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
                        _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
            }
            else
            {
                return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
                (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
            }
        }


    }

    /// <summary>
    /// 工作薄區(qū)域
    /// 作者:Zuowenjun
    /// </summary>
    public class SheetRange
    {
        public int? MinRow { get; set; }
        public int? MaxRow { get; set; }
        public int? MinColumn { get; set; }
        public int? MaxColumn { get; set; }

        public SheetRange()
        { }

        public SheetRange(int minRow, int maxRow, int minColumn, int maxColumn)
        {
            this.MinRow = minRow;
            this.MaxRow = maxRow;
            this.MinColumn = minColumn;
            this.MaxColumn = maxColumn;
        }

        public override bool Equals(object obj)
        {
            bool equalResult = false;
            equalResult = base.Equals(obj);
            if (!equalResult)
            {
                var otherSheetRange = obj as SheetRange;
                if (otherSheetRange != null)
                {
                    equalResult = (this.MinRow <= otherSheetRange.MinRow && this.MaxRow >= otherSheetRange.MaxRow
                        && this.MinColumn <= otherSheetRange.MinColumn && this.MaxColumn >= otherSheetRange.MaxColumn);
                }
            }
            return equalResult;
        }

        public override int GetHashCode()
        {
            return this.ToString().GetHashCode();
        }

        public override string ToString()
        {
            return string.Format("MinRow:{0},MaxRow:{1},MinColumn:{2},MaxColumn:{3}", this.MinRow, this.MaxRow, this.MinColumn, this.MaxColumn);
        }

    }

}

分享我基于NPOI+ExcelReport實(shí)現(xiàn)的導(dǎo)入與導(dǎo)出EXCEL類(lèi)庫(kù):ExcelUtility 其它相關(guān)文章鏈接:

分享我基于NPOI+ExcelReport實(shí)現(xiàn)的導(dǎo)入與導(dǎo)出EXCEL類(lèi)庫(kù):ExcelUtility

?

分享我基于NPOI+ExcelReport實(shí)現(xiàn)的導(dǎo)入與導(dǎo)出EXCEL類(lèi)庫(kù):ExcelUtility (續(xù)篇)

?


Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to share Quark Netdisk to Baidu Netdisk? How to share Quark Netdisk to Baidu Netdisk? Mar 14, 2024 pm 04:40 PM

Quark Netdisk and Baidu Netdisk are very convenient storage tools. Many users are asking whether these two softwares are interoperable? How to share Quark Netdisk to Baidu Netdisk? Let this site introduce to users in detail how to save Quark network disk files to Baidu network disk. How to save files from Quark Network Disk to Baidu Network Disk Method 1. If you want to know how to transfer files from Quark Network Disk to Baidu Network Disk, first download the files that need to be saved on Quark Network Disk, and then open the Baidu Network Disk client. , select the folder where the compressed file is to be saved, and double-click to open the folder. 2. After opening the folder, click "Upload" in the upper left corner of the window. 3. Find the compressed file that needs to be uploaded on your computer and click to select it.

How to implement dual WeChat login on Huawei mobile phones? How to implement dual WeChat login on Huawei mobile phones? Mar 24, 2024 am 11:27 AM

How to implement dual WeChat login on Huawei mobile phones? With the rise of social media, WeChat has become one of the indispensable communication tools in people's daily lives. However, many people may encounter a problem: logging into multiple WeChat accounts at the same time on the same mobile phone. For Huawei mobile phone users, it is not difficult to achieve dual WeChat login. This article will introduce how to achieve dual WeChat login on Huawei mobile phones. First of all, the EMUI system that comes with Huawei mobile phones provides a very convenient function - dual application opening. Through the application dual opening function, users can simultaneously

How to import local songs from NetEase Cloud Music How to import local songs How to import local songs from NetEase Cloud Music How to import local songs Mar 13, 2024 am 11:19 AM

When we use this platform to listen to songs, most of them should have some songs that you want to listen to. Of course, some things may not be listened to because there is no copyright. Of course, we can also directly use some songs imported locally. Go up there so you can listen. We can download some songs and directly convert them into mp3 formats, so that they can be scanned on the mobile phone for import and other situations. However, for most users, they don’t know much about importing local song content, so in order to solve these problems well, today the editor will also explain it to you. The content method allows you to make better choices without asking. If you are interested,

How to share NetEase Cloud Music to WeChat Moments_Tutorial on sharing NetEase Cloud Music to WeChat Moments How to share NetEase Cloud Music to WeChat Moments_Tutorial on sharing NetEase Cloud Music to WeChat Moments Mar 25, 2024 am 11:41 AM

1. First, we enter NetEase Cloud Music, and then click on the software homepage interface to enter the song playback interface. 2. Then in the song playback interface, find the sharing function button in the upper right corner, as shown in the red box in the figure below, click to select the sharing channel; in the sharing channel, click the &quot;Share to&quot; option at the bottom, and then select the first &quot;WeChat Moments&quot; allows you to share content to WeChat Moments.

PHP Programming Guide: Methods to Implement Fibonacci Sequence PHP Programming Guide: Methods to Implement Fibonacci Sequence Mar 20, 2024 pm 04:54 PM

The programming language PHP is a powerful tool for web development, capable of supporting a variety of different programming logics and algorithms. Among them, implementing the Fibonacci sequence is a common and classic programming problem. In this article, we will introduce how to use the PHP programming language to implement the Fibonacci sequence, and attach specific code examples. The Fibonacci sequence is a mathematical sequence defined as follows: the first and second elements of the sequence are 1, and starting from the third element, the value of each element is equal to the sum of the previous two elements. The first few elements of the sequence

How to solve the problem of garbled characters when importing Chinese data into Oracle? How to solve the problem of garbled characters when importing Chinese data into Oracle? Mar 10, 2024 am 09:54 AM

Title: Methods and code examples to solve the problem of garbled characters when importing Chinese data into Oracle. When importing Chinese data into Oracle database, garbled characters often appear. This may be due to incorrect database character set settings or encoding conversion problems during the import process. . In order to solve this problem, we can take some methods to ensure that the imported Chinese data can be displayed correctly. The following are some solutions and specific code examples: 1. Check the database character set settings In the Oracle database, the character set settings are

How to share files with friends on Baidu Netdisk How to share files with friends on Baidu Netdisk Mar 25, 2024 pm 06:52 PM

Recently, Baidu Netdisk Android client has ushered in a new version 8.0.0. This version not only brings many changes, but also adds many practical functions. Among them, the most eye-catching is the enhancement of the folder sharing function. Now, users can easily invite friends to join and share important files in work and life, achieving more convenient collaboration and sharing. So how do you share the files you need to share with your friends? Below, the editor of this site will give you a detailed introduction. I hope it can help you! 1) Open Baidu Cloud APP, first click to select the relevant folder on the homepage, and then click the [...] icon in the upper right corner of the interface; (as shown below) 2) Then click [+] in the &quot;Shared Members&quot; column 】, and finally check all

PHP Game Requirements Implementation Guide PHP Game Requirements Implementation Guide Mar 11, 2024 am 08:45 AM

PHP Game Requirements Implementation Guide With the popularity and development of the Internet, the web game market is becoming more and more popular. Many developers hope to use the PHP language to develop their own web games, and implementing game requirements is a key step. This article will introduce how to use PHP language to implement common game requirements and provide specific code examples. 1. Create game characters In web games, game characters are a very important element. We need to define the attributes of the game character, such as name, level, experience value, etc., and provide methods to operate these

See all articles