WeihanLi.Npoi 1.11.0/1.12.0 Release Notes

Time:2021-12-1

WeihanLi.Npoi 1.11.0/1.12.0 Release Notes

Intro

Two new versions of npoi extension have been updated recently. Thank youshaka chowThanks for your help and support. These two features are from him. He has helped me test several versions and provided me with a better way to read files. Thank you very much.

The two recently updated functions are excel import. Version 1.11.0 supports formula import processing, and version 1.12.0 supports configuring the allowed column range through attribute or through fluent APICellFilterTo more flexibly filter columns you don’t want to read.

Formula Import Support

In the previous version, there was no formula support. The cell importing a formula would be a string to read. In some scenarios, excel might be very useful. Therefore, we tried to add formula support. It was only import, but there was no formula support during export, On the one hand, when calculating the exported value according to the formula, you may need to fill in all the values first and then calculate the value of the formula. This scenario will lead to low efficiency. On the other hand, I think the method of fluent API can meet the needs of most scenarios, not special needs, so it does not support formula export.

Row / cell filter support

In version 1.10.0, we support aEndRowIndexTo configure an end line to end reading data in advance. In version 1.12.0shakaIt is proposed that it can be increasedStartColumnIndexas well asEndColumnIndexConfigure to specify the range of columns to be read. On this basis, I came up with the idea of adding a new value to the sheet based on the fluent APIRowFilterandCellFilterTo more flexibly configure the data range we want to read. With this, we can easily realize interlaced reading or multiple range reading.

Show the Code

Examples of Excel formula Import Support:

[Theory]
[InlineData(ExcelFormat.Xls)]
[InlineData(ExcelFormat.Xlsx)]
public void ExcelImportWithFormula(ExcelFormat excelFormat)
{
    var setting = FluentSettings.For();
    setting.HasSheetConfiguration(0, "Test", 0);
    setting.Property(x => x.Num1).HasColumnIndex(0);
    setting.Property(x => x.Num2).HasColumnIndex(1);
    setting.Property(x => x.Sum).HasColumnIndex(2);

    var workbook = ExcelHelper.PrepareWorkbook(excelFormat);
    var sheet = workbook.CreateSheet();
    var row = sheet.CreateRow(0);
    row.CreateCell(0, CellType.Numeric).SetCellValue(1);
    row.CreateCell(1, CellType.Numeric).SetCellValue(2);
    row.CreateCell(2, CellType.Formula).SetCellFormula("$A1+$B1");
    var excelBytes = workbook.ToExcelBytes();
    var list = ExcelHelper.ToEntityList(excelBytes, excelFormat);
    Assert.NotNull(list);
    Assert.NotEmpty(list);
    Assert.Equal(1, list[0].Num1);
    Assert.Equal(2, list[0].Num2);
    Assert.Equal(3, list[0].Sum);
}

Formula support does not need to modify any code, and is fully compatible with the original API. You can see that the imported value of the above formula is successfully replaced with the calculated value

Example of cell filter using attribute

[Theory]
[InlineData(ExcelFormat.Xls)]
[InlineData(ExcelFormat.Xlsx)]
public void ExcelImportWithCellFilterAttributeTest(ExcelFormat excelFormat)
{
    IReadOnlyList list = Enumerable.Range(0, 10).Select(i => new CellFilterAttributeTest()
    {
        Id = i + 1,
        Description = $"content_{i}",
        Name = $"title_{i}",
    }).ToArray();
    var excelBytes = list.ToExcelBytes(excelFormat);
    var importedList = ExcelHelper.ToEntityList(excelBytes, excelFormat);
    Assert.NotNull(importedList);
    Assert.Equal(list.Count, importedList.Count);
    for (var i = 0; i < importedList.Count; i++)
    {
        Assert.Equal(list[i].Id, importedList[i].Id);
        Assert.Equal(list[i].Name, importedList[i].Name);
        Assert.Null(importedList[i].Description);
    }
}

[Sheet(SheetName = "test", AutoColumnWidthEnabled = true, StartColumnIndex = 0, EndColumnIndex = 1)]
private class CellFilterAttributeTest
{
    [Column(Index = 0)]
    public int Id { get; set; }

    [Column(Index = 1)]
    public string Name { get; set; }

    [Column(Index = 2)]
    public string Description { get; set; }
}

You can see that the value of the last column is actually ignored, and the value of the last column corresponds toDescriptionProperty is alwaysnull

Example of cell filter using fluent API

[Theory]
[InlineData(ExcelFormat.Xls)]
[InlineData(ExcelFormat.Xlsx)]
public void ExcelImportWithCellFilter(ExcelFormat excelFormat)
{
    IReadOnlyList list = Enumerable.Range(0, 10).Select(i => new Notice()
    {
        Id = i + 1,
        Content = $"content_{i}",
        Title = $"title_{i}",
        PublishedAt = DateTime.UtcNow.AddDays(-i),
        Publisher = $"publisher_{i}"
    }).ToArray();
    var excelBytes = list.ToExcelBytes(excelFormat);

    var settings = FluentSettings.For();
    settings.HasSheetSetting(setting =>
    {
        setting.CellFilter = cell => cell.ColumnIndex == 0;
    });

    var importedList = ExcelHelper.ToEntityList(excelBytes, excelFormat);
    Assert.Equal(list.Count, importedList.Count);
    for (var i = 0; i < list.Count; i++)
    {
        if (list[i] == null)
        {
            Assert.Null(importedList[i]);
        }
        else
        {
            Assert.Equal(list[i].Id, importedList[i].Id);
            Assert.Null(importedList[i].Title);
            Assert.Null(importedList[i].Content);
            Assert.Null(importedList[i].Publisher);
            Assert.Equal(default(DateTime).ToStandardTimeString(), importedList[i].PublishedAt.ToStandardTimeString());
        }
    }

    settings.HasSheetSetting(setting =>
    {
        setting.CellFilter = null;
    });
}

This example is relatively simple. Only the data of the first column is imported, and the properties corresponding to other column data are the default values

More

In addition to these two main features, there are several small updates and refactoringsExcelSettingandSheetSetting, a delegate based configuration method is provided. The original method is used as an extension method. In addition, it optimizes file reading, mainly by specifying one when reading filesFileShareMode (for details, please refer to the link given at the end of the article). It turns out that if another process has opened the file, an exception will be thrown when importing again. After optimization, even if the file is occupied by other processes, the file content can still be read for import operation, and the operation experience may be better.

For more details, please refer to the examples and unit tests in GitHub warehouse

Reference