Skip to content

README English

holdengong edited this page May 23, 2019 · 1 revision

中文

Introdcution

EasyOffice provides services for you to easily manipulate excel,word.

  • Excel Import Service:only need to mark attributes on template class,EasyOffice will auto validate data, and convert valid data to specific type. you only need to handle invalid and valid data;

  • Excel Export Service:only need to mark attribute on template class and prepare data, EasyOffice will auto generate excel bytes with proper style;

  • Word Created By Template:only need to define template class, make word template with placeholders, EasyOffice will replace placeholders by text or pictures and generate word bytes for you;

  • Word Created By Master Table:only need to make word template which has a master table with placeholders. EasyOffice will copy master table and fullfill data repeatly. For example, you pass 3 data objects,you will get 3 tables in the word generated.

  • Word Created From Empty :you can create a word from empty, which applies to very simple or complicate word export task;

  • Convert docx to PDF: you can convert docx file to pdf by EasyOffice.Extensions;

EasyOffice depends on NPOI, EasyOffice.Extensions depends on OpenXml,DinkToPDF, which are all open source.

Nuget

//core package
Install-Package EasyOffice

//install extension package if you need convert docx to pdf.
Install-Package EasyOffice.Extensions

==IMPORTANT==

if you install EasyOffice.Extensions via nuget,you need to add libwkhtmltox.dll, libwkhtmltox.so to root path of your project,namely same folder of csproj file, for detail you can refer to DinkToPDF.

DependencyInjection

services.AddEasyOffice(new OfficeOptions());

IExcelImportService

step1 - define a template class

 public class Car
    {
        [ColName("CarCode")]  //excel column name
        [Required] // required
        [Regex(RegexConstant.CAR_CODE_REGEX)] //regex validation
        [Duplication] //validate duplication
        public string CarCode { get; set; }

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

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

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

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

        [ColName("RegisterDate")]
        [DateTime] //validate date
        public DateTime RegisterDate { get; set; }

        [ColName("Age")]
        [Range(0, 150)] //validate number range
        public int Age { get; set; }
    }

Validate Data

    var _rows = _excelImportService.ValidateAsync<ExcelCarTemplateDTO>(new ImportOption()
    {
        FileUrl = fileUrl,
        DataRowStartIndex = 1, 
        HeaderRowIndex = 0, 
        MappingDictionary = null, //you can remap if you want
        SheetIndex = 0, 
        ValidateMode = ValidateModeEnum.Continue 
    }).Result;
    
    //get error data
    var errorDatas = _rows.Where(x => !x.IsValid);
    //handle error data
    
    //convert valid data to specific type
    var validDatas = _rows.Where(x=>x.IsValid).FastConvert<ExcelCarTemplateDTO>();
    //handle valid data

Convert to DataTable

      var dt = _excelImportService.ToTableAsync<ExcelCarTemplateDTO>
                (fileUrl); 

IExcelExportService

define template class

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

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

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

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

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

        [ColName("RegisterDate")]
        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, 
        ExcelType = Bayantu.Extensions.Office.Enums.ExcelTypeEnum.XLS,
        HeaderRowIndex = 0, 
        SheetName = "sheet1"
    });

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

IWordExportService

CreateFromTemplateAsync

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

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

        //use Picture or IEnumerable<Picture> to replace with pictures
        public IEnumerable<Picture> CarPictures { get; set; }

        public Picture CarLicense { get; set; }
    }

//step2 - make word template

//step3 - export word
string templateUrl = @"c:\template.docx";
WordCarTemplateDTO car = new WordCarTemplateDTO()
{
    OwnerName = "SomeOne",
    CarType = "SomeCar",
    CarPictures = new List<Picture>() {
         new Picture()
         {
              PictureUrl = pic1,
              FileName = "pic1",
              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

//step1 - define template class, refer to ex. above

//step2 - define template word with a master table

//step3 - export word
  string templateurl = @"c:\template.docx";
    var user1 = new UserInfoDTO()
    {
        Name = "test",
        Age = 15,
        Gender = "test",
        Remarks = "test"
    };
    var user2 = new UserInfoDTO()
    {
        Name = "test",
        Age = 20,
        Gender = "test",
        Remarks = "test"
    };
    
    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

  [Fact]
        public async Task Test()
        {
            //prepare data
            ...
            
            //create new table
            var table = new Table()
            {
                Rows = new List<TableRow>()
            };

            foreach (var date in dates)
            {
                //new row
                var rowDate = new TableRow()
                {
                    Cells = new List<TableCell>()
                };
                
                //new cell
                rowDate.Cells.Add(new TableCell()
                {
                    Color = "lightblue", //set cell color 
                    Paragraphs = new List<Paragraph>()
                    { 
                    //add paragraph
                    new Paragraph()
                    {
                       //add run
                        Run = new Run()
                        {
                           Text = date.DateTimeStr,
                           Color = "red", 
                           FontFamily = "微软雅黑",
                           FontSize = 12,
                           IsBold = true,
                           Pictures = new List<Picture>()//you can insert pictures 
                        },
                         Alignment = Alignment.CENTER 
                    }
                    }
                });
                table.Rows.Add(rowDate);
            }

            tables.Add(table);

            var word = await _wordExportService.CreateWordAsync(tables);

            File.WriteAllBytes(fileUrl, word.WordBytes);
        }

Extensions

IWordConverter

convert docx to html or pdf,depend on OpenXml and DinkToPDF, which are open source.

step1: Startup DI
serviceCollection.AddEasyOfficeExtensions();

step2:
DI IWordConverter _wordConverter in constructor

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