EasyOffice-.NetCore一行程式碼匯入匯出Excel,生成Word

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和推薦，謝謝。