search

nissl-lab / npoi

a .NET library that can read/write Office formats without Microsoft Office installed. No COM+, no interop.
Apache License 2.0
5.69k stars 1.43k forks source link

Exception System.ArgumentNullException "HResult=0x80004003 Message=Value cannot be null. (Parameter 'stream')..." thrown in .NET Standard 2.1 #473

Closed yafeiguo888 closed 3 years ago

yafeiguo888 commented 3 years ago

I tested the new version 2.5.2 in my Windows 10, .NET core3.1 Visual Studio 2019 and unfortunately, I got errors in running the following command in C#

Cell.SetCellFormula(string.Format("SUM(C{0}:C{1})", HeaderRowOffset + 1, totalRow.RowNum - 2 + 1)); Cell.SetCellFormula("NOW()");

The following is the stack trace:

System.ArgumentNullException HResult=0x80004003 Message=Value cannot be null. (Parameter 'stream') Source=System.Private.CoreLib StackTrace: at System.IO.StreamReader..ctor(Stream stream, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean leaveOpen) at System.IO.StreamReader..ctor(Stream stream) at NPOI.SS.Formula.Function.FunctionMetadataReader.CreateRegistry() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetInstance() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.LookupIndexByName(String name) at NPOI.SS.Formula.PTG.AbstractFunctionPtg.IsBuiltInFunctionName(String name) at NPOI.SS.Formula.FormulaParser.Function(String name) at NPOI.SS.Formula.FormulaParser.ParseNonRange(Int32 savePointer) at NPOI.SS.Formula.FormulaParser.ParseRangeable() at NPOI.SS.Formula.FormulaParser.ParseRangeExpression() at NPOI.SS.Formula.FormulaParser.ParseSimpleFactor() at NPOI.SS.Formula.FormulaParser.PercentFactor() at NPOI.SS.Formula.FormulaParser.PowerFactor() at NPOI.SS.Formula.FormulaParser.Term() at NPOI.SS.Formula.FormulaParser.AdditiveExpression() at NPOI.SS.Formula.FormulaParser.ConcatExpression() at NPOI.SS.Formula.FormulaParser.ComparisonExpression() at NPOI.SS.Formula.FormulaParser.IntersectionExpression() at NPOI.SS.Formula.FormulaParser.UnionExpression() at NPOI.SS.Formula.FormulaParser.Parse() at NPOI.SS.Formula.FormulaParser.Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, Int32 sheetIndex, Int32 rowIndex) at NPOI.XSSF.UserModel.XSSFCell.SetFormula(String formula, FormulaType formulaType) at NPOI.XSSF.UserModel.XSSFCell.SetCellFormula(String formula)

The issue wasn't in prevoius version 2.5.1 and 2.4.1.

Thanks, Daniel Guo

mganss commented 3 years ago

I'm seeing this when calling the getter for CellFormula, too. Can't repro when testing with locally compiled NPOI, though. Also, I can get the stream for the embedded resource "NPOI.Resources.functionMetadata.txt" from user code without a problem. Somehow this line seems to be failing: https://github.com/nissl-lab/npoi/blob/4de0a694f21344da52c697899fcf60d566c81c46/main/SS/Formula/Function/FunctionMetadataReader.cs#L58

tonyqus commented 3 years ago

I'll check the issue soon.

yafeiguo888 commented 3 years ago

More findings about this issue:

This issue is only appearing in Windows 10 Visual Studio 2019 .NET 5.0/Core 3.1

It disappears when in Windows 10 Visual Studio 2019 .NET Framework 4.5.1

yafeiguo888 commented 3 years ago

It seems compiling error disappears, but the Formula doesn't fully work and the value is not showing up.

tonyqus commented 3 years ago 
    class FunctionMetadataReader
    {
#if NETSTANDARD2_0
        private const String METADATA_FILE_NAME = "NPOI.Resources.functionMetadata.txt";
#else
        private const String METADATA_FILE_NAME = "functionMetadata.txt";

Changes to

    class FunctionMetadataReader
    {
#if NETSTANDARD2_1 || NETSTANDARD2_0
        private const String METADATA_FILE_NAME = "NPOI.Resources.functionMetadata.txt";
#else
        private const String METADATA_FILE_NAME = "functionMetadata.txt";
mganss commented 3 years ago

What's the reasoning behind targeting both netstandard2.0 and netstandard2.1? AFAICT there is no code that uses a netstandard2.1 API that is not in netstandard2.0. Same goes for multi-targeting both net461 and net472.

.NET open source library guidance says:

❌ AVOID multi-targeting as well as targeting .NET Standard, if your source code is the same for all targets.

The .NET Standard assembly will automatically be used by NuGet. Targeting individual .NET implementations increases the *.nupkg size for no benefit.

tonyqus commented 3 years ago

@mganss your concern makes sense. I'll change the release strategy

leykov commented 3 years ago

cell.SetCellFormula(formula); doesn't work for me too. And stackTrace: at System.IO.StreamReader..ctor(Stream stream, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean leaveOpen) at System.IO.StreamReader..ctor(Stream stream) at NPOI.SS.Formula.Function.FunctionMetadataReader.CreateRegistry() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetInstance() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.LookupIndexByName(String name) at NPOI.SS.Formula.PTG.AbstractFunctionPtg.IsBuiltInFunctionName(String name) at NPOI.SS.Formula.FormulaParser.Function(String name) at NPOI.SS.Formula.FormulaParser.ParseNonRange(Int32 savePointer) at NPOI.SS.Formula.FormulaParser.ParseRangeable() at NPOI.SS.Formula.FormulaParser.ParseRangeExpression() at NPOI.SS.Formula.FormulaParser.ParseSimpleFactor() at NPOI.SS.Formula.FormulaParser.PercentFactor() at NPOI.SS.Formula.FormulaParser.PowerFactor() at NPOI.SS.Formula.FormulaParser.Term() at NPOI.SS.Formula.FormulaParser.AdditiveExpression() at NPOI.SS.Formula.FormulaParser.ConcatExpression() at NPOI.SS.Formula.FormulaParser.ComparisonExpression() at NPOI.SS.Formula.FormulaParser.IntersectionExpression() at NPOI.SS.Formula.FormulaParser.UnionExpression() at NPOI.SS.Formula.FormulaParser.Parse() at NPOI.SS.Formula.FormulaParser.Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, Int32 sheetIndex, Int32 rowIndex) at NPOI.XSSF.UserModel.XSSFCell.SetFormula(String formula, FormulaType formulaType) at NPOI.XSSF.UserModel.XSSFCell.SetCellFormula(String formula)

MatiSanchez commented 3 years ago

Hi guys! Any news about this error?

We are getting the same error in .Net 5 using NPOI 2.5.2 when use SetAutoFilter

ArgumentNullException: Value cannot be null. (Parameter 'stream')
System.IO.StreamReader..ctor(Stream stream, Encoding encoding, bool detectEncodingFromByteOrderMarks, int bufferSize, bool leaveOpen)
System.IO.StreamReader..ctor(Stream stream)
NPOI.SS.Formula.Function.FunctionMetadataReader.CreateRegistry()
NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetInstance()
NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetFunctionByName(string name)
NPOI.SS.Formula.FormulaParser.IsValidCellReference(string str)
NPOI.SS.Formula.FormulaParser.ParseSimpleRangePart()
NPOI.SS.Formula.FormulaParser.ParseRangeable()
NPOI.SS.Formula.FormulaParser.ParseRangeExpression()
NPOI.SS.Formula.FormulaParser.ParseSimpleFactor()
NPOI.SS.Formula.FormulaParser.PercentFactor()
NPOI.SS.Formula.FormulaParser.PowerFactor()
NPOI.SS.Formula.FormulaParser.Term()
NPOI.SS.Formula.FormulaParser.AdditiveExpression()
NPOI.SS.Formula.FormulaParser.ConcatExpression()
NPOI.SS.Formula.FormulaParser.ComparisonExpression()
NPOI.SS.Formula.FormulaParser.IntersectionExpression()
NPOI.SS.Formula.FormulaParser.UnionExpression()
NPOI.SS.Formula.FormulaParser.Parse()
NPOI.SS.Formula.FormulaParser.Parse(string formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, int sheetIndex, int rowIndex)
NPOI.XSSF.UserModel.XSSFName.set_RefersToFormula(string value)
NPOI.XSSF.UserModel.XSSFSheet.SetAutoFilter(CellRangeAddress range)
tonyqus commented 3 years ago 
    class FunctionMetadataReader
    {
#if NETSTANDARD2_0
        private const String METADATA_FILE_NAME = "NPOI.Resources.functionMetadata.txt";
#else
        private const String METADATA_FILE_NAME = "functionMetadata.txt";

Changes to

    class FunctionMetadataReader
    {
#if NETSTANDARD2_1 || NETSTANDARD2_0
        private const String METADATA_FILE_NAME = "NPOI.Resources.functionMetadata.txt";
#else
        private const String METADATA_FILE_NAME = "functionMetadata.txt";

This issue is caused by this bug. But the fix will only be involved in next release. The bad news is that the release time of next release is to be determined.

dpiessens commented 3 years ago

This bug is a showstopper for me, and I can't downgrade because I'm running net core 3.1 so I need the 2.1 standard. Any way you can push 2.5.3 with this as a patch?

marekpetak commented 3 years ago

Same here. .NET Core 5, NPOI version 2.5.2, trying to set sheet.SetAutoFilter(new CellRangeAddress(7, Data.Count, 0, 6));

and I get

` 2021-01-22 11:07:36.1046 | ERROR | debug | System.ArgumentNullException: Value cannot be null. (Parameter 'stream') at System.IO.StreamReader..ctor(Stream stream, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean leaveOpen) at System.IO.StreamReader..ctor(Stream stream) at NPOI.SS.Formula.Function.FunctionMetadataReader.CreateRegistry() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetInstance() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetFunctionByName(String name) at NPOI.SS.Formula.FormulaParser.IsValidCellReference(String str) at NPOI.SS.Formula.FormulaParser.ParseSimpleRangePart() at NPOI.SS.Formula.FormulaParser.ParseRangeable() at NPOI.SS.Formula.FormulaParser.ParseRangeExpression() at NPOI.SS.Formula.FormulaParser.ParseSimpleFactor() at NPOI.SS.Formula.FormulaParser.PercentFactor() at NPOI.SS.Formula.FormulaParser.PowerFactor() at NPOI.SS.Formula.FormulaParser.Term() at NPOI.SS.Formula.FormulaParser.AdditiveExpression() at NPOI.SS.Formula.FormulaParser.ConcatExpression() at NPOI.SS.Formula.FormulaParser.ComparisonExpression() at NPOI.SS.Formula.FormulaParser.IntersectionExpression() at NPOI.SS.Formula.FormulaParser.UnionExpression() at NPOI.SS.Formula.FormulaParser.Parse() at NPOI.SS.Formula.FormulaParser.Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, Int32 sheetIndex, Int32 rowIndex) at NPOI.XSSF.UserModel.XSSFName.set_RefersToFormula(String value) at NPOI.XSSF.UserModel.XSSFSheet.SetAutoFilter(CellRangeAddress range)

 ***
 ***
 at System.IO.StreamReader..ctor(Stream stream, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean leaveOpen)
 at System.IO.StreamReader..ctor(Stream stream)
 at NPOI.SS.Formula.Function.FunctionMetadataReader.CreateRegistry()
 at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetInstance()
 at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetFunctionByName(String name)
 at NPOI.SS.Formula.FormulaParser.IsValidCellReference(String str)
 at NPOI.SS.Formula.FormulaParser.ParseSimpleRangePart()
 at NPOI.SS.Formula.FormulaParser.ParseRangeable()
 at NPOI.SS.Formula.FormulaParser.ParseRangeExpression()
 at NPOI.SS.Formula.FormulaParser.ParseSimpleFactor()
 at NPOI.SS.Formula.FormulaParser.PercentFactor()
 at NPOI.SS.Formula.FormulaParser.PowerFactor()
 at NPOI.SS.Formula.FormulaParser.Term()
 at NPOI.SS.Formula.FormulaParser.AdditiveExpression()
 at NPOI.SS.Formula.FormulaParser.ConcatExpression()
 at NPOI.SS.Formula.FormulaParser.ComparisonExpression()
 at NPOI.SS.Formula.FormulaParser.IntersectionExpression()
 at NPOI.SS.Formula.FormulaParser.UnionExpression()
 at NPOI.SS.Formula.FormulaParser.Parse()
 at NPOI.SS.Formula.FormulaParser.Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, Int32 sheetIndex, Int32 rowIndex)
 at NPOI.XSSF.UserModel.XSSFName.set_RefersToFormula(String value)
 at NPOI.XSSF.UserModel.XSSFSheet.SetAutoFilter(CellRangeAddress range)

`

corentinaltepe commented 3 years ago

For those having this issue, we are currently using v2.5.1 fine. It doesn't seem to have the problem.

gecapo commented 3 years ago

For those having this issue, we are currently using v2.5.1 fine. It doesn't seem to have the problem.

Clarification: v.2.5.1 works with .NET Core 3.1 v2.5.1/v2.5.2 doesn't work with .NET 5

qContinuum commented 3 years ago

v.2.5.1 works with .NET Core 3.1 and .NET 5 for me. Only 2.5.2 blows chunks.

My Use: ICell cell = row.CreateCell(1); cell.SetCellType(CellType.Formula); cell.SetCellFormula("COUNTA(OFFSET(Sheet2!A:A,1,0,COUNTA(Sheet2!A:A)-1))");

imranshams commented 3 years ago

I have the same problem with .Net 5.0.3 and NPOI 2.5.2.

Code: XSSFName definedNamed = (XSSFName)wb.CreateName(); definedNamed.NameName = name; definedNamed.RefersToFormula = $"Sheet2!$A${startIndex}:$A${endIndex}";

And Error: Value cannot be null. (Parameter 'stream') at System.IO.StreamReader..ctor(Stream stream, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean leaveOpen) at System.IO.StreamReader..ctor(Stream stream) at NPOI.SS.Formula.Function.FunctionMetadataReader.CreateRegistry() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetInstance() at NPOI.SS.Formula.Function.FunctionMetadataRegistry.GetFunctionByName(String name) at NPOI.SS.Formula.FormulaParser.IsValidCellReference(String str) at NPOI.SS.Formula.FormulaParser.ParseSimpleRangePart() at NPOI.SS.Formula.FormulaParser.ParseRangeable() at NPOI.SS.Formula.FormulaParser.ParseRangeExpression() at NPOI.SS.Formula.FormulaParser.ParseSimpleFactor() at NPOI.SS.Formula.FormulaParser.PercentFactor() at NPOI.SS.Formula.FormulaParser.PowerFactor() at NPOI.SS.Formula.FormulaParser.Term() at NPOI.SS.Formula.FormulaParser.AdditiveExpression() at NPOI.SS.Formula.FormulaParser.ConcatExpression() at NPOI.SS.Formula.FormulaParser.ComparisonExpression() at NPOI.SS.Formula.FormulaParser.IntersectionExpression() at NPOI.SS.Formula.FormulaParser.UnionExpression() at NPOI.SS.Formula.FormulaParser.Parse() at NPOI.SS.Formula.FormulaParser.Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, Int32 sheetIndex, Int32 rowIndex) at NPOI.XSSF.UserModel.XSSFName.set_RefersToFormula(String value)

Any solution?

wdmeest commented 3 years ago

Same problem here (core 3.1 => NPO 2.5.2).

My use:

IName namedCell = this._workbook.CreateName();
namedCell.NameName = "hidden_name";
namedCell.RefersToFormula = "Hidden!$A$1$U$1;

Works fine with 2.5.1

andrewbusch7 commented 3 years ago

Likewise having an issue with this, .NET Core 3.1 & NPOI 2.5.2 throws this exception for me. I've rolled back to NPOI 2.5.1 and it's working ok.

plcly commented 3 years ago

cell.SetCellType(CellType.Formula); cell.SetCellFormula("B3&A3"); .Net 5,2.51 works.

tonyqus commented 3 years ago

https://www.nuget.org/packages/NPOI/2.5.3 is released. Can someone verify if this version can solve this issue?

jianfeixu95 commented 3 years ago

https://www.nuget.org/packages/NPOI/2.5.3 is released. Can someone verify if this version can solve this issue?

this issue was sloved. @tonyqus

NPOI 2.5.3 DotNetCore 3.1 Windows 10

corentinaltepe commented 3 years ago

I confirm the issue is resolved with 2.5.3. Thanks a bunch!

NPOI 2.5.3 DotNetCore 5.0 Windows 10 and Ubuntu on Docker

PredragCrnogorac commented 3 years ago

SetAutoFilters works also. Thanks

malhotrar commented 3 years ago

So 2.5.3 requires SharpZipLib = 1.2.0; but 2.5.2 requires > 1.2.0. I am running a later version of SharpZipLib because of a security vulnerability identified by our security team. Is there a reason 2.5.3 is more restrictive?

Summary SharpZipLib Tar/TarArchive.cs TarArchive::ExtractEntry() Function Tar Archive Handling Path Traversal Arbitrary File Write Type Security Provider JFrog Severity High Update Jun 16, 2021 11:58:41 AM References https://github.com/icsharpcode/SharpZipLib/commit/0cbdef20f1d5654ab5b93a6ce1ff8a917d3b905b https://github.com/icsharpcode/SharpZipLib/pull/519 https://github.com/icsharpcode/SharpZipLib/releases/tag/v1.3.0 Infected Component SharpZipLib