NPOI Core - Formula Issue

0
votes

I am trying to create a simple formula of SUM(E1:E6) but keep getting a Value cannot be null. Parameter name: stream. I have put an = in front and even made it lower case with no success. A formula I was able to get to work was 1 + 3 + 8. I then tried E1 + E2 + E3 + E4 + E5 + E6 and that gave the same error.

Here is my code:

IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Project Time");

... Other numeric and textual cells fill in and work great

row = sheet1.CreateRow(RowIndex++);

var test = row.CreateCell(ColIndex++);
test.SetCellType(CellType.Formula);
test.SetCellFormula($"1 + 2 + 3 + 4 + 5 + 6"); // WORKS
test.SetCellFormula($"SUM(1 + 2 + 3 + 4 + 5 + 6)"); // FAILS
test.SetCellFormula($"SUM(E1:E6)"); // FAILS
test.SetCellFormula($"E1 + E2 + E3"); // FAILS

XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

workbook.Write(TheStream);

The GitHub location is here.

1
c#npoi
see this link stackoverflow.com/questions/25077009/…. Remove $ from SetCellFormula($"SUM(E1:E6)"); – kumar chandraketu
@kumarchandraketu the $ is a prefix for C# string formatting and is not affecting this in any way. I took it out and got the same result. The documentation link from the SO link you mention also does not show what I am doing and really gave no insights to what I am missing or doing wrong. – Grandizer
I'll suggest you to try SetCellFormula( "=SUM(E1:E6)"); or SetCellFormula( $ "=SUM(E1:E6)"); Sometimes syntax from documentation doesn't work in NPOI so you may have to try your workaround by hit or miss :) – kumar chandraketu
The = will throw an error because it assumes that being that it is a formula. I posted the answer myself once I got a hold of the developers. – Grandizer

1 Answers

1
votes

After a post to here I found out that there was an update from 1.0.0 to 1.0.1 and that allowed the formula! Did not check for updates seeing that I downloaded the package yesterday. But that is on me, it is .Net core and thus is moving quite quickly.