EasyExcel生成word java

Excel和Word操作在开发过程中经常需要使用，这类工作不涉及到核心业务，但又往往不可缺少。以往的开发方式在业务代码中直接引入NPOI、Aspose或者其他第三方库，工作繁琐，耗时多，扩展性差——比如基础库由NPOI修改为EPPlus，意味着业务代码需要全部修改。由于工作需要，我在之前版本的基础上，封装了OfficeService，目的是最大化节省导入导出这种非核心功能开发时间，专注于业务实现，并且业务端与底层基础组件完全解耦，即业务端完全不需要知道底层使用的是什么基础库，使得重构代价大大降低。

EasyOffice提供了

• Excel导入：通过对模板类标记特性自动校验数据(后期计划支持FluentApi，即传参决定校验行为)，并将有效数据转换为指定类型，业务端只在拿到正确和错误数据后决定如何处理；
• Excel导出：通过对模板类标记特性自动渲染样式(后期计划支持FluentApi，即传参决定导出行为)；
• Word根据模板生成：支持使用文本和图片替换，占位符只需定义模板类，制作Word模板，一行代码导出docx文档(后期计划支持转换为pdf)；
• Word根据Table母版生成：只需定义模板类，制作表格模板，传入数据，服务会根据数据条数自动复制表格母版，并填充数据；
• Word从空白创建等功能：特别复杂的Word导出任务，支持从空白创建；

EasyOffice底层库目前使用NPOI,因此是完全免费的。
通过IExcelImportProvider等Provider接口实现了底层库与实现的解耦，后期如果需要切换比如Excel导入的基础库为EPPlus，只需要提供IExcelImportProvider接口的EPPlus实现，并且修改依赖注入代码即可。

提供了.net core自带ServiceCollection注入和Autofac注入

builder.AddOffice(new OfficeOptions());


services.AddOffice(new OfficeOptions());

---

定义Excel模板类

public class Car
    {
        [ColName("车牌号")]  
        [Required] 
        [Regex(RegexConstant.CAR_CODE_REGEX)] 
        [Duplication] 
public string CarCode { get; set; }

        [ColName("手机号")]
        [Regex(RegexConstant.MOBILE_CHINA_REGEX)]
public string Mobile { get; set; }

        [ColName("身份证号")]
        [Regex(RegexConstant.IDENTITY_NUMBER_REGEX)]
public string IdentityNumber { get; set; }

        [ColName("姓名")]
        [MaxLength(10)] 
public string Name { get; set; }

        [ColName("性别")] 
        [Regex(RegexConstant.GENDER_REGEX)]
public GenderEnum Gender { get; set; }

        [ColName("注册日期")]
        [DateTime] 
public DateTime RegisterDate { get; set; }

        [ColName("年龄")]
        [Range(0, 150)] 
public int Age { get; set; }
    }

校验数据

var _rows = _excelImportService.ValidateAsync(new ImportOption()
    {
        FileUrl = fileUrl, 
        DataRowStartIndex = 1, 
        HeaderRowIndex = 0,  
        MappingDictionary = null, 
        SheetIndex = 0, 
        ValidateMode = ValidateModeEnum.Continue 
    }).Result;
    var errorDatas = _rows.Where(x => !x.IsValid);
    var validDatas = _rows.Where(x=>x.IsValid).FastConvert();

转换为DataTable

var dt = _excelImportService.ToTableAsync
                (
                fileUrl,  0,  0,  1, -1);

---

定义导出模板类

[Header(Color = ColorEnum.BRIGHT_GREEN, FontSize = 22, IsBold = true)] 
    [WrapText] 
public class ExcelCarTemplateDTO
    {
        [ColName("车牌号")]
        [MergeCols] 
public string CarCode { get; set; }

        [ColName("手机号")]
public string Mobile { get; set; }

        [ColName("身份证号")]
public string IdentityNumber { get; set; }

        [ColName("姓名")]
public string Name { get; set; }

        [ColName("性别")]
public GenderEnum Gender { get; set; }

        [ColName("注册日期")]
public DateTime RegisterDate { get; set; }

        [ColName("年龄")]
public int Age { get; set; }

导出Excel

var bytes = await _excelExportService.ExportAsync(new ExportOption()
    {
        Data = list,
        DataRowStartIndex = 1, 
        ExcelType = Bayantu.Extensions.Office.Enums.ExcelTypeEnum.XLS,
        HeaderRowIndex = 0, 
        SheetName = "sheet1" 
    });
    File.WriteAllBytes(@"c:\test.xls", bytes);

---

首先定义模板类，参考通用Excel导入

var templateBytes = await _excelImportSolutionService.GetImportTemplateAsync();var importConfig = await _excelImportSolutionService.GetImportConfigAsync("uploadUrl","templateUrl");var previewData = await _excelImportSolutionService.GetFileHeadersAndRowsAsync("fileUrl");var importOption = new ImportOption()
    {
        FileUrl = "fileUrl",
        ValidateMode = ValidateModeEnum.Continue
    };object importSetData = new object(); var importResult = await _excelImportSolutionService.ImportAsync
        (importOption
        , importSetData
        , BusinessAction 
        , CustomValidate 
        );var errorMsg = await _excelImportSolutionService.ExportErrorMsgAsync(importResult.Tag);

---

CreateFromTemplateAsync - 根据模板生成Word

public class WordCarTemplateDTO
    {

public string OwnerName { get; set; }

        [Placeholder("{Car_Type Car Type}")] 
public string CarType { get; set; }


public IEnumerable CarPictures { get; set; }public Picture CarLicense { get; set; }
    }string templateUrl = @"c:\template.docx";
WordCarTemplateDTO car = new WordCarTemplateDTO()
{
    OwnerName = "刘德华",
    CarType = "豪华型宾利",
    CarPictures = new List() {new Picture()
         {
              PictureUrl = pic1, 
              FileName = "图片1",
              Height = 10,
              Width = 3,
              PictureData = null,
              PictureType = PictureTypeEnum.JPEG 
         },new Picture(){
              PictureUrl = pic2
         }
    },
    CarLicense = new Picture { PictureUrl = pic3 }
};var word = await _wordExportService.CreateFromTemplateAsync(templateUrl, car);
File.WriteAllBytes(@"c:\file.docx", word.WordBytes);

CreateWordFromMasterTable-根据模板表格循环生成word

string templateurl = @"c:\template.docx";
var user1 = new UserInfoDTO()
    {
        Name = "张三",
        Age = 15,
        Gender = "男",
        Remarks = "简介简介"
    };
var user2 = new UserInfoDTO()
    {
        Name = "李四",
        Age = 20,
        Gender = "女",
        Remarks = "简介简介简介"
    };

var datas = new List() { user1, user2 };for (int i = 0; i < 10; i++)
    {
        datas.Add(user1);
        datas.Add(user2);
    }var word = await _wordExportService.CreateFromMasterTableAsync(templateurl, datas);
    File.WriteAllBytes(@"c:\file.docx", word.WordBytes);

CreateWordAsync - 从空白生成word

[Fact]
        public async Task 导出所有日程()
        {

var date1 = new ScheduleDate()
            {
                DateTimeStr = "2019年5月5日 星期八",
                Addresses = new List

()
 };var address1 = new Address()
 {
 Name = "会场一",
 Categories = new List()
 };var cate1 = new Category()
 {
 Name = "分类1",
 Schedules = new List()
 };var schedule1 = new Schedule()
 {
 Name = "日程1",
 TimeString = "上午9：00 - 上午12：00",
 Speakers = new List()
 };var schedule2 = new Schedule()
 {
 Name = "日程2",
 TimeString = "下午13：00 - 下午14：00",
 Speakers = new List()
 };var speaker1 = new Speaker()
 {
 Name = "张三",
 Position = "总经理"
 };var speaker2 = new Speaker()
 {
 Name = "李四",
 Position = "副总经理"
 };
 schedule1.Speakers.Add(speaker1);
 schedule1.Speakers.Add(speaker2);
 cate1.Schedules.Add(schedule1);
 cate1.Schedules.Add(schedule2);
 address1.Categories.Add(cate1);
 date1.Addresses.Add(address1);var dates = new List() { date1,date1,date1 };var tables = new List 
  ();var table = new Table()
 {
 Rows = new List()
 };
 foreach (var date in dates)
 {var rowDate = new TableRow()
 {
 Cells = new List()
 };
 rowDate.Cells.Add(new TableCell()
 {
 Color = "lightblue", 
 Paragraphs = new List()
 { new Paragraph()
 {
 Run = new Run()
 {
 Text = date.DateTimeStr,
 Color = "red", 
 FontFamily = "微软雅黑",
 FontSize = 12,
 IsBold = true,
 Pictures = new List()
 },
 Alignment = Alignment.CENTER 
 }
 }
 });
 table.Rows.Add(rowDate);
 foreach (var addr in date.Addresses)
 {
 foreach (var cate in addr.Categories)
 {var rowCate = new TableRow()
 {
 Cells = new List()
 };
 rowCate.Cells.Add(new TableCell()
 {
 Paragraphs = new List{ new Paragraph()
 {
 Run = new Run()
 {
 Text = addr.Name,
 }
 }
 }
 });
 rowCate.Cells.Add(new TableCell()
 {
 Paragraphs = new List(){ new Paragraph()
 {
 Run = new Run()
 {
 Text = cate.Name,
 }
 }
 }
 });
 table.Rows.Add(rowCate);
 foreach (var sche in cate.Schedules)
 {var rowSche = new TableRow()
 {
 Cells = new List()
 };var scheCell = new TableCell()
 {
 Paragraphs = new List()
 {new Paragraph()
 {
 Run = new Run()
 {
 Text = sche.Name
 }
 },
 {new Paragraph()
 {
 Run = new Run()
 {
 Text = sche.TimeString
 }
 }
 }
 }
 };
 foreach (var speaker in sche.Speakers)
 {
 scheCell.Paragraphs.Add(new Paragraph()
 {
 Run = new Run()
 {
 Text = $"{speaker.Position}:{speaker.Name}"
 }
 });
 }
 rowSche.Cells.Add(scheCell);
 table.Rows.Add(rowSche);
 }
 }
 }
 }
 tables.Add(table);var word = await _wordExportService.CreateWordAsync(tables);
 File.WriteAllBytes(fileUrl, word.WordBytes);
 }

github地址：https://github.com/holdengong/EasyOffice

水平有限，如果有bug欢迎提issue；如果本项目对您略有帮助，请帮忙Start，谢谢。