0

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.

Grandizer
  • 2,819
  • 4
  • 46
  • 75
  • see this link https://stackoverflow.com/questions/25077009/npoi-setcellformula-custom-formula-in-vba. Remove $ from SetCellFormula($"SUM(E1:E6)"); – kumar chandraketu Aug 31 '17 at 13:48
  • @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 Aug 31 '17 at 14:06
  • 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 Aug 31 '17 at 14:19
  • 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 Aug 31 '17 at 18:19

1 Answers1

1

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.

Grandizer
  • 2,819
  • 4
  • 46
  • 75