Alizhou-Blog Alizhou-Blog

周黎的个人博客

目录
Alizhou.Office-.NetCore 导入导出execl,根据word模板生成word
/      

Alizhou.Office-.NetCore 导入导出execl,根据word模板生成word

1.简介

工作中,遇到需要导入导出execl,导出word需求,还有各种各样的样式,于是有了word模板导出。这类工作不涉及到核心业务,但又往往不可缺少
查询了大量资料,有以下框架/库可以用:

名称是否收费说明
NPOI开源免费不需要服务器安装Office,功能强大
Docx开源免费不需要服务器安装Office,功能强大
Aspose收费不需要服务器安装Office,功能很强大
OpenXml开源免费不需要服务器安装Office,微软提供的组件
Spire收费不需要服务器安装Office,官方有提供免费版本,不过有些限制

最后决定采用 Docx操作word,EPPlus操作execl

由于工作需要,我封装了 Alizhou.Office,目的是最大化节省导入导出这种非核心功能开发时间,专注于业务实现,并且业务端与底层基础组件完全解耦,即业务端完全不需要知道底层使用的是什么基础库,使得重构代价大大降低。
Alizhou.Office底层库目前使用Docx,因此是完全免费的
Alizhou.Office目前提供了

  • Word根据模板生成:支持使用文本/图片/表格替换,占位符只需定义模板类,制作Word模板,一行代码导出docx文档;
  • 根据实体对execl导入导出;

2.如何使用

2.1安装Alizhou.Office

nuget 搜索并安装 Alizhou.Office

image.png

2.2依赖注入

// 注入Office基础服务
services.AddAlizhouOffice();

2.3 IWordExportService - Word通用导出服务

2.3.1定义模板类,模板类需实现IWordExportTemplate空接口

public class WordUserTemplate : IWordExportTemplate
    {
        /// <summary>
        ///  默认占位符为{PropertyName}
        /// </summary>
        public string UserName { get; set; }
        /// <summary>
        /// 默认
        /// </summary>
        [Placeholder("{电话}")]
        public string Phone { set; get; }
        /// <summary>
        /// 表格
        /// </summary>
        public AlizhouTable Table { get; set; }
        /// <summary>
        /// 图片
        /// </summary>
        public IEnumerable<AlizhouPicture> Pictures { get; set; }
    }

2.3.2制作模板

image.png

2.3.3根据模板生成word

string basePath = Environment.CurrentDirectory;
            string templateUrl = @$"{basePath}/template/word/TemplateWrod.docx";
            IWordExportService wordExportService = new WordExportService(new WordExportProvider());
            WordUserTemplate userTemplate = new WordUserTemplate
            {
                UserName = "小周黎",
                Phone = "175626565656",
                Pictures = new List<AlizhouPicture>() {
                new Office.Model.AlizhouPicture
                {
                    PictureUrl = "D://图片1.png",
                    Width=540,
                    Height=405,
                },
                 new Office.Model.AlizhouPicture
                {
                    PictureUrl = "D://图片2.png",
                    Width=540,
                    Height=405,
                }
                },
                Table = new Office.Model.AlizhouTable(3, 2)
                {
                    Rows = new System.Collections.Generic.List<Office.Model.AlizhouTableRow>() {
                        new Office.Model.AlizhouTableRow{
                            Height=200,
                            Cells=new System.Collections.Generic.List<Office.Model.AlizhouTableCell>(){
                                new Office.Model.AlizhouTableCell{
                                    Width=100,
                                    Paragraphs=new System.Collections.Generic.List<Office.Model.AlizhouParagraph>(){
                                        new Office.Model.AlizhouParagraph{
                                            Run=new Office.Model.AlizhouRun{
                                                Text="姓名",
                                                 IsBold=true
                                            }
                                        }
                                    }

                                },
                                new Office.Model.AlizhouTableCell{
                                    Width=100,
                                    Paragraphs=new System.Collections.Generic.List<Office.Model.AlizhouParagraph>(){
                                        new Office.Model.AlizhouParagraph{
                                            Run=new Office.Model.AlizhouRun{
                                                Text="年龄"
                                            }
                                        }
                                    }

                                }
                            },
                        },
                         new Office.Model.AlizhouTableRow{
                            Height=200,
                            Cells=new System.Collections.Generic.List<Office.Model.AlizhouTableCell>(){
                                new Office.Model.AlizhouTableCell{
                                    Width=100,
                                    Paragraphs=new System.Collections.Generic.List<Office.Model.AlizhouParagraph>(){
                                        new Office.Model.AlizhouParagraph{
                                            Run=new Office.Model.AlizhouRun{
                                                Text="周黎"
                                            }
                                        }
                                    }

                                },
                                new Office.Model.AlizhouTableCell{
                                    Width=100,
                                    Paragraphs=new System.Collections.Generic.List<Office.Model.AlizhouParagraph>(){
                                        new Office.Model.AlizhouParagraph{
                                            Run=new Office.Model.AlizhouRun{
                                                Text="18"
                                            }
                                        }
                                    }

                                }
                            },
                        },
                           new Office.Model.AlizhouTableRow{
                            Height=200,
                            Cells=new System.Collections.Generic.List<Office.Model.AlizhouTableCell>(){
                                new Office.Model.AlizhouTableCell{
                                    Width=100,
                                    Paragraphs=new System.Collections.Generic.List<Office.Model.AlizhouParagraph>(){
                                        new Office.Model.AlizhouParagraph{
                                            Run=new Office.Model.AlizhouRun{
                                                Text="张三",
                                                IsBold=true,
                                                Pictures=new System.Collections.Generic.List<Office.Model.AlizhouPicture>()                     {
                                                    new Office.Model.AlizhouPicture{ PictureUrl="D://191cb437-2bc8-4fe9-9c5c-b7536eae1883.jpg",Width=30,Height=30},
                                                    new Office.Model.AlizhouPicture{ PictureUrl="D://renwu-mayun1.jpg",Width=30,Height=30}
                                                }
                                            }
                                        }
                                    }

                                },
                                new Office.Model.AlizhouTableCell{
                                    Width=100,
                                    Paragraphs=new System.Collections.Generic.List<Office.Model.AlizhouParagraph>(){
                                        new Office.Model.AlizhouParagraph{
                                            Run=new Office.Model.AlizhouRun{
                                                Text="19",
                                                Color=Color.Red,
                                                FontFamily="微软雅黑",
                                                FontSize=12,
                                                IsBold=true,
                                            }
                                        }
                                    }

                                }
                            },
                        }
                    }
                }
            };
            var word = wordExportService.TemplateCreateWord(templateUrl, userTemplate);
            File.WriteAllBytes($"{basePath}/{DateTime.Now.ToString("yyyyMMddHHmmss")}测试生成word.docx", word.WordBytes);
            Console.ReadKey();

2.3.3生成效果

image.png

2.4 IExeclImportExportService- execl通用导入导出服务

2.4.1定义execl模板类

public class PersonDto
    {
        [ExcelTableColumn("姓名")]
        [Required(ErrorMessage = "姓名必填")]

        public string Name { get; set; }
        [ExcelTableColumn("年龄")]
        [Range(1, 25, ErrorMessage = "年龄超过25岁的MM不要")]//范围判断
        public int Age { get; set; }

        [ExcelTableColumn(3)]//根据索引取
        public string Adress { get; set; }
        [ExcelTableColumn("电话")]
        [MaxLength(11, ErrorMessage = "手机号 长度不能超过11")]
        public string Phone { set; get; }
        public override string ToString()
        {
            return $"姓名:{Name};年龄:{Age};地址:{Adress};电话:{Phone}";
        }
    }

2.4.2导入导出execl

string basePath = Environment.CurrentDirectory;
            var list = new List<PersonDto>();
            for (int i = 0; i < 100; i++)
            {
                list.Add(new PersonDto
                {
                    Name = $"张三{i}",
                    Age = 18,
                    Adress = $"地址{i}",
                    Phone = $"17783042962"
                });
            }
            IExeclImportExportService execlImportExport = new ExeclImportExportService(new ExeclImportExportProvider());
            var alizhouExecl = execlImportExport.Export(list);
            string path = $@"{basePath}..\..\..\..\OutPut\execl\ExeclImportExportTest.xlsx";
            File.WriteAllBytes(path, alizhouExecl.WordBytes);
            var data = execlImportExport.Import<PersonDto>(File.OpenRead(path));
            foreach (var item in data)
            {
                Console.WriteLine(item.ToString());
            }

2.4.3导出效果

image.png

2.4.3导入效果

image.png

最后附上源码地址:https://github.com/lizhounet/Alizhou.Office


标题:Alizhou.Office-.NetCore 导入导出execl,根据word模板生成word
作者:小周黎
地址:https://zhouli.info/articles/2020/08/19/1597850920589.html