EasyOffice-.NetCore line imports and exports Excel to generate Word

brief introduction

Excel and Word operations are often used in the development process. They do not involve core business, but are often indispensable.Previous development methods have introduced NPOI, Aspose or other third-party libraries directly into business code, which is cumbersome, time-consuming and has poor scalability - for example, the base library is modified from NPOI to EPPlus, which means the business code needs to be modified completely.Because of the work requirements, I encapsulated OfficeService on the basis of previous versions in order to maximize the time spent in developing non-core functions like import and export, focus on business implementation, and decouple the business side completely from the underlying underlying components, that is, the business side does not need to know what underlying libraries are used at the bottom, which greatly reduces the refactoring cost.

EasyOffice provides

  • Excel import: By automatically verifying the data with the template class tagging feature (FluentApi is supported in later planning, i.e. parameter determines the check behavior), and converting the valid data to the specified type, the business side only decides what to do after getting the correct and wrong data;
  • Excel export: Automatically render styles for template class tag attributes (FluentApi is supported in later planning, i.e. parameters determine export behavior);
  • Word is generated from templates: supports text and picture substitution, placeholders only need to define template classes, make Word templates, export docx documents in one line of code (post-plan support for conversion to pdf);
  • Word is generated from the Table master: simply define the template class, make the table template, pass in the data, and the service will automatically copy the table master and fill in the data according to the number of data bars;
  • Functions such as Word creation from blanks: Extremely complex Word export tasks that support creation from blanks;

The EasyOffice underlying library currently uses NPOI and is therefore completely free.
Underlying libraries and implementations are decoupled through Provider interfaces such as IExcelImportProvider. If you need to switch the base library such as Excel import to EPPlus later, you only need to provide the EPPlus implementation of IExcelImportProvider interface and modify the dependent injection code.
.

Dependent Injection

Support for.net core built-in dependency injection

// Inject Office Basic Services
services.AddEasyOffice(new OfficeOptions());

IExcelImportService - Excel Universal Import

Define Excel Template Class

 public class Car
    {
        [ColName("License plate number")]  //Corresponding Excel Column Name
        [Required] //Check required
        [Regex(RegexConstant.CAR_CODE_REGEX)] //Regular Expression Check, RegexConstant has some commonly used regular expressions preset or can be customized
        [Duplication] //Check template class for duplicate data in this column
        public string CarCode { get; set; }

        [ColName("Cell-phone number")]
        [Regex(RegexConstant.MOBILE_CHINA_REGEX)]
        public string Mobile { get; set; }

        [ColName("ID number")]
        [Regex(RegexConstant.IDENTITY_NUMBER_REGEX)]
        public string IdentityNumber { get; set; }

        [ColName("Full name")]
        [MaxLength(10)] //Maximum Length Check
        public string Name { get; set; }

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

        [ColName("Date of registration")]
        [DateTime] //Date Check
        public DateTime RegisterDate { get; set; }

        [ColName("Age")]
        [Range(0, 150)] //Value Range Check
        public int Age { get; set; }
    }

Check data


    var _rows = _excelImportService.ValidateAsync<ExcelCarTemplateDTO>(new ImportOption()
    {
        FileUrl = fileUrl, //Absolute address of Excel file
        DataRowStartIndex = 1, //Data start row index, default 1 second row
        HeaderRowIndex = 0,  //Header start row index, default 0 first row
        MappingDictionary = null, //Mapping dictionary, which remaps template classes to Excel columns, default null
        SheetIndex = 0, //Page index, default 0 first page label
        ValidateMode = ValidateModeEnum.Continue //Check mode, stops the check after the default StopOnFirstFailure check error, Continue: Continue after the check error
    }).Result;
    
    //Get the wrong line
    var errorDatas = _rows.Where(x => !x.IsValid);
    //Error Industry Transaction Processing
    
    //Converts a valid data row to a specified type
    var validDatas = _rows.Where(x=>x.IsValid).FastConvert<ExcelCarTemplateDTO>();
    //Correct data business processing

Convert to DataTable

      var dt = _excelImportService.ToTableAsync<ExcelCarTemplateDTO> //Template type
                (
                fileUrl,  //File absolute address
                0,  //Page label index, default 0
                0,  //Header row index, default 0
                1, //Data row index, default 1
                -1); //How many pieces of data to read, default-1 all

IExcelExportService - General Excel Export Service

Define export template class

    [Header(Color = ColorEnum.BRIGHT_GREEN, FontSize = 22, IsBold = true)] //Header Style
    [WrapText] //Automatic line break
    public class ExcelCarTemplateDTO
    {
        [ColName("License plate number")]
        [MergeCols] //Automerge Cells with Same Data
        public string CarCode { get; set; }

        [ColName("Cell-phone number")]
        public string Mobile { get; set; }

        [ColName("ID number")]
        public string IdentityNumber { get; set; }

        [ColName("Full name")]
        public string Name { get; set; }

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

        [ColName("Date of registration")]
        public DateTime RegisterDate { get; set; }

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

Export Excel

    var bytes = await _excelExportService.ExportAsync(new ExportOption<ExcelCarTemplateDTO>()
    {
        Data = list,
        DataRowStartIndex = 1, //Data row start index, default 1
        ExcelType = ExcelTypeEnum.XLS,//Export Excel type, default xls
        HeaderRowIndex = 0, //Header row index, default 0
        SheetName = "sheet1" //Page signature name, default sheet1
    });

    File.WriteAllBytes(@"c:\test.xls", bytes);

IExcelImportSolution Service - Excel Import Solution Service (complete solution with front-end controls)

Define the template class first, refer to the generic Excel import

   //Get Default Import Template
    var templateBytes = await _excelImportSolutionService.GetImportTemplateAsync<DemoTemplateDTO>();

    //Get Import Configuration
    var importConfig = await _excelImportSolutionService.GetImportConfigAsync<DemoTemplateDTO>("uploadUrl","templateUrl");

    //Get preview data
    var previewData = await _excelImportSolutionService.GetFileHeadersAndRowsAsync<DemoTemplateDTO>("fileUrl");

    //Import
    var importOption = new ImportOption()
    {
        FileUrl = "fileUrl",
        ValidateMode = ValidateModeEnum.Continue
    };
    object importSetData = new object(); //Front End Mapping Data
    var importResult = await _excelImportSolutionService.ImportAsync<DemoTemplateDTO>
        (importOption
        , importSetData
        , BusinessAction //Business Method Delegation
        , CustomValidate //Custom Check Delegation
        );

    //Get Import Error Message
    var errorMsg = await _excelImportSolutionService.ExportErrorMsgAsync(importResult.Tag);

IWordExportService - Word Universal Export Service

CreateFromTemplateAsync - Generate Word from Template

//step1 - Define template class
 public class WordCarTemplateDTO
    {
        //The default placeholder is {PropertyName}
        public string OwnerName { get; set; }

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

        //Placeholders can be replaced with pictures using Picture or IEnumerable <Picture>types
        public IEnumerable<Picture> CarPictures { get; set; }

        public Picture CarLicense { get; set; }
    }

//step2 - Make word templates

//step3 - Export word
string templateUrl = @"c:\template.docx";
WordCarTemplateDTO car = new WordCarTemplateDTO()
{
    OwnerName = "Lau Andy",
    CarType = "Luxury Benefit",
    CarPictures = new List<Picture>() {
         new Picture()
         {
              PictureUrl = pic1, //Picture absolute address, this does not work if PictureData is set
              FileName = "Picture 1",//File Name
              Height = 10,//Picture height in centimeters default 8
              Width = 3,//Picture width per centimeter default 14
              PictureData = null,//Picture stream data, preferred here data, not url
              PictureType = PictureTypeEnum.JPEG //Picture type, default 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 - Generate word from template table loop

//step1 - Define the template class, see above

//step2 - Define the word template, make a table, and fill in the placeholders.

//step3 - Call, as shown in the following example, and the resulting word has two user tables
  string templateurl = @"c:\template.docx";
    var user1 = new UserInfoDTO()
    {
        Name = "Zhang San",
        Age = 15,
        Gender = "male",
        Remarks = "Introduction"
    };
    var user2 = new UserInfoDTO()
    {
        Name = "Li Si",
        Age = 20,
        Gender = "female",
        Remarks = "Introduction Introduction"
    };
    
    var datas = new List<UserInfoDTO>() { 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 - Generate word from blank

  [Fact]
        public async Task Export all schedules()
        {
            //Preparing data
            var date1 = new ScheduleDate()
            {
                DateTimeStr = "2019 Friday, May 5, 2000",
                Addresses = new List<Address>()
            };

            var address1 = new Address()
            {
                Name = "Stage One",
                Categories = new List<Category>()
            };

            var cate1 = new Category()
            {
                Name = "Classification 1",
                Schedules = new List<Schedule>()
            };

            var schedule1 = new Schedule()
            {
                Name = "Schedule 1",
                TimeString = "9:00 a.m. - 12:00 a.m.",
                Speakers = new List<Speaker>()
            };
            var schedule2 = new Schedule()
            {
                Name = "Schedule 2",
                TimeString = "13:00 p.m. - 14:00 p.m.",
                Speakers = new List<Speaker>()
            };

            var speaker1 = new Speaker()
            {
                Name = "Zhang San",
                Position = "General manager"
            };
            var speaker2 = new Speaker()
            {
                Name = "Li Si",
                Position = "Vice General Manager"
            };

            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<ScheduleDate>() { date1,date1,date1 };

            var tables = new List<Table>();

            //Create a new table
            var table = new Table()
            {
                Rows = new List<TableRow>()
            };

            foreach (var date in dates)
            {
                //Create a new line
                var rowDate = new TableRow()
                {
                    Cells = new List<TableCell>()
                };
                
                //Add Cells
                rowDate.Cells.Add(new TableCell()
                {
                    Color = "lightblue", //Set cell color
                    Paragraphs = new List<Paragraph>()
                    { 
                    //New Paragraph
                    new Paragraph()
                    {
                       //Add Text Fields to Paragraphs
                        Run = new Run()
                        {
                           Text = date.DateTimeStr,//Text field text, Run can also
                           Color = "red", //Set Text Color
                           FontFamily = "Microsoft YaHei",//Set Text Font
                           FontSize = 12,//Set text font size
                           IsBold = true,//Bold or not
                           Pictures = new List<Picture>()//You can also insert pictures
                        },
                         Alignment = Alignment.CENTER //Centered Paragraph
                    }
                    }
                });
                table.Rows.Add(rowDate);

                //Conference Hall
                foreach (var addr in date.Addresses)
                {
                    //classification
                    foreach (var cate in addr.Categories)
                    {
                        var rowCate = new TableRow()
                        {
                            Cells = new List<TableCell>()
                        };

                        //Venue Name
                        rowCate.Cells.Add(new TableCell()
                        {
                            Paragraphs = new List<Paragraph>{ new Paragraph()
                            {
                                Run = new Run()
                                {
                                    Text = addr.Name,
                                }
                            }
                            }
                        });

                        rowCate.Cells.Add(new TableCell()
                        {
                            Paragraphs = new List<Paragraph>(){ new Paragraph()
                            {
                                Run = new Run()
                                {
                                    Text = cate.Name,
                                }
                            }
                            }
                        });
                        table.Rows.Add(rowCate);

                        //schedule
                        foreach (var sche in cate.Schedules)
                        {
                            var rowSche = new TableRow()
                            {
                                Cells = new List<TableCell>()
                            };

                            var scheCell = new TableCell()
                            {
                                Paragraphs = new List<Paragraph>()
                                {
                                    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);
        }

Extensions

IWordConverter Word Converter

Supports docx file conversion to html or pdf.The underlying libraries use OpenXml and DinkToPdf, which are open source and free of charge.If your company does not have Luisence for Aspose.Word, this is an option to consider.

step1: Startup injection
serviceCollection.AddEasyOfficeExtensions();

step2:
Constructor Injection IWordConverter _wordConverter

step3:Call
var pdfBytes = _wordConverter.ConvertToPDF(docxBytes, "text");

https://github.com/holdengong/EasyOffice

Tags: Excel Mobile github

Posted on Wed, 06 May 2020 16:58:59 -0700 by joix