What’s New in NPOI 2.6.0

With the community help, we got a few nice PRs and enhanced features for 2.6.0. I’d like to thank a few contributors here:

• Yuriy from Russia
• Collin Alpert from Germany
• Artem Koloskov from Russia
• Andreas Wenger from Germany
• Brian Yule from Ireland
• Houlongchao from China
• Pascal Brunot from Italy
• EmilienDup

Although it has been 14 years since I started working on NPOI, the good news is that NPOI package is still in good maintainence.

It’s not like what NCC mentioned in the readme of the repo of Dotnetcore.NPOI. NPOI package has been supporting .NET core since late 2018 and there is no reason we don’t support .NET core as .NET core is getting more and more popular. They are lying all the time. The purpose is obvious — they just wanna destroy NPOI and persuade the NPOI users to use Dotnetcore.NPOI instead. Fortunately, they didn’t succeed.

New features in 2.6.0

1. You can leave Workbook outputstream open while calling Workbook.Write to allow further operation. This is useful for web response stream scenario.

using (FileStream sw = File.Create("test.xlsx"))
{
    workbook.Write(sw,false);
}

NOTE: This is a breaking change if you migrate from NPOI 2.5.x and below version to 2.6.0. You have to provide leaveOpen parameter a value.

2. IWorkbook and XWPFDocument are disposable now

IWorkbook has a Close method, which can be used to release unused IO resources. However, some users complains that this is a Java style interface design.

To fill in the gap, 2.6.0 implements IDisposable interface and calls the Close method internally. Here is the sample code:

using (IWorkbook workbook = new XSSFWorkbook())
{
}

3. Support DateOnly type (introduced by .NET 6) parameter for SetCellValue method.

cell.SetCellValue(new DateOnly(2004, 5, 6)); //This is only availabe in .NET 6

4. Open encrypted xlsx file (with password) with NPOI

To open a xlsx with password, use the following code

IWorkbook workbook = WorkbookFactory.Create("encryped.xlsx", "password");

5. Get rid of System.Drawing.Common

Since Microsoft stops supporting System.Drawing.Common on Linux since .NET 6, we have no choice but change to a library supports text rendering and font objects.

According to the discussion in one Github thread, I finally choose ImageSharp package as the alternative package.

6. Autosize row height feature

There is a new method called AutoSizeRow in ISheet instance.

Here is the sample code:

ICell cell4 = sheet1.CreateRow(6).CreateCell(1);
ICellStyle style4 = workbook.CreateCellStyle();
style4.WrapText=true;
cell4.SetCellValue("test"+Environment.NewLine+"test"+ Environment.NewLine + "test");
cell4.CellStyle = style4;
sheet1.AutoSizeRow(6);

Here is the result:

7. Apply RecyclableMemoryStream to all memory related scenario in NPOI

There are a few places using MemoryStream in NPOI code. However, MemoryStream is well-known for its late memory release issue because it’s .NET GC feature (only objects in Gen 2 will be finally released).

Microsoft bing team created a library called RecyclableMemoryStream, which can skip GC pipeline and release the used memory immediately.

For detail, you can read this post.

8. A few new Excel functions can be evaluated by NPOI

• AREAS
• DOLLAR/DOLLARDE/DOLLARFR
• NUMBERVALUE
• WORKDAY.INTL
• PERCENTRANK
• XLOOKUP
• XMATCH