XSSFWorkbook.SetForceFormulaRecalculation will not force recalc on open Excel book

[kenjiuno]

I want to generate invoice xlsx using NPOI.
The invoice is not fully built by NPOI.
I use a hand made xlsx as a template.
Because it assumes that book is modified by software, there is sheet to hold variables.
The variables are just cells having names.

The issue is that in this way I need to use XSSFWorkbook.SetForceFormulaRecalculation = true;
But this doesn't work as expected.

I'll post simple xlsx files for this repro:

Base.xlsx
SetNameTaro-With-fullCalcOnLoad.xlsx
SetNameTaro-Without-fullCalcOnLoad.xlsx

If fullCalcOnLoad = true is activated around:

npoi/ooxml/XSSF/UserModel/XSSFWorkbook.cs

Line 2303 in b4b94fe

calcPr.calcId = 0;

XSSFWorkbook.SetForceFormulaRecalculation will work as expected.
So I want to ask to add fullCalcOnLoad = value line to the NPOI.

[tonyqus]

But this doesn't work as expected.

What do you exactly mean it's not working as expected? I checked both files (SetNameTaro-With-fullCalcOnLoad.xlsx and SetNameTaro-Without-fullCalcOnLoad.xlsx and what I see in the Microsoft Office are same - the formula is evaluated automatically.

Btw, which version of NPOI are you using?

[kenjiuno]

I don't know why, but the occurrence of this problem is probabilistic. Not 100%. Around 30% ~ 50%?

1148.mp4

[kenjiuno]

Btw, which version of NPOI are you using?

I have found this issue when I use NPOI latest 2.6.1 at nuget.
I have used master for repro of this issue.

[kenjiuno]

What do you exactly mean it's not working as expected?

I'm sorry about missing info.

The actual result is that the third line keeps Your name is UNNAMED!.

The expected result is Your name is Taro!.

[kenjiuno]

And my unit test code (testcases\ooxml\XSSF\UserModel\TestXSSFWorkbook.cs) is like this (sorry for dirty code).

        [Test]
        public void TestSetForceFormulaRecalculation()
        {
            XSSFWorkbook wb = new XSSFWorkbook(new MemoryStream(File.ReadAllBytes(@"C:\A\base.xlsx")));

            wb.SetForceFormulaRecalculation(true);

            var sheet = wb.GetSheet("Sheet1");
            sheet.GetRow(0).GetCell(1).SetCellValue("Taro");

            var stream = new MemoryStream();
            wb.Write(stream);
            File.WriteAllBytes(@"C:\A\SetNameTaro-Without-fullCalcOnLoad.xlsx", stream.ToArray());
        }

[Bykiev]

What is your purpose of calling SetForceFormulaRecalculation instead of using XSSFFormulaEvaluator.EvaluateAll?

POI docs:

Control if Excel should be asked to recalculate all formulas when the workbook is opened, via the "sheetCalcPr fullCalcOnLoad" option. Calculating the formula values with FormulaEvaluator is the recommended solution, but this may be used for certain cases where evaluation in POI is not possible.

[kenjiuno]

What is your purpose of calling SetForceFormulaRecalculation instead of using XSSFFormulaEvaluator.EvaluateAll?

In my case, there was an error when I tried wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

NPOI.SS.Formula.FormulaParseException : Parse error near char 20 '〒' in specified formula '"Your zip code is "&〒'. Expected cell ref or constant literal

Zip.xlsx

〒 mark means post of postal system in Japan. Also used as prefix of zip code.

This didn't behave like Excel did. But I have no intension to offend about this. This will be compatibility problem and should take some while to deal with.

        [Test]
        public void TestEvaluateAll()
        {
            XSSFWorkbook wb = new XSSFWorkbook(new MemoryStream(File.ReadAllBytes(@"C:\A\Zip.xlsx")));

            wb.SetForceFormulaRecalculation(true);

            var name = wb.GetName("〒");
            var cr = new CellReference(name.RefersToFormula);
            wb.GetSheet(name.SheetName).GetRow(cr.Row).GetCell(cr.Col).SetCellValue("123-456");

            wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

            var stream = new MemoryStream();
            wb.Write(stream);
            File.WriteAllBytes(@"C:\A\SetZipTo123456-Without-fullCalcOnLoad.xlsx", stream.ToArray());
        }

 TestEvaluateAll
   Source: TestXSSFWorkbook.cs line 1215
   Duration: 876 ms

  Message: 
NPOI.SS.Formula.FormulaParseException : Parse error near char 20 '〒' in specified formula '"Your zip code is "&〒'. Expected cell ref or constant literal

  Stack Trace: 
FormulaParser.ParseSimpleFactor() line 1886
FormulaParser.PercentFactor() line 1831
FormulaParser.PowerFactor() line 1815
FormulaParser.Term() line 2248
FormulaParser.AdditiveExpression() line 2343
FormulaParser.ConcatExpression() line 2333
FormulaParser.ComparisonExpression() line 2273
FormulaParser.IntersectionExpression() line 2422
FormulaParser.UnionExpression() line 2399
FormulaParser.Parse() line 2388
FormulaParser.Parse(String formula, IFormulaParsingWorkbook workbook, FormulaType formulaType, Int32 sheetIndex, Int32 rowIndex) line 136
XSSFEvaluationWorkbook.GetFormulaTokens(IEvaluationCell evalCell) line 97
WorkbookEvaluator.EvaluateAny(IEvaluationCell srcCell, Int32 sheetIndex, Int32 rowIndex, Int32 columnIndex, EvaluationTracker tracker) line 391
WorkbookEvaluator.Evaluate(IEvaluationCell srcCell) line 260
BaseXSSFFormulaEvaluator.EvaluateFormulaCellValue(ICell cell) line 64
BaseFormulaEvaluator.EvaluateFormulaCellEnum(ICell cell) line 199
BaseFormulaEvaluator.EvaluateFormulaCell(ICell cell) line 170
BaseFormulaEvaluator.EvaluateAllFormulaCells(IWorkbook wb, IFormulaEvaluator evaluator) line 284
XSSFFormulaEvaluator.EvaluateAll() line 102
TestXSSFWorkbook.TestEvaluateAll() line 1225

[tonyqus]

Why can 〒 work without double quote? Is this a special localization of Office Japanese version?

I tested in my Micorosft Office (Chinese simplified version). 〒 is not working.

Unless, I put double quote around 〒.

Normally, the formula parser cannot handle literal text without double quote.

[kenjiuno]

It is likely that the name 〒 isn't defined in Base.xlsx.

Please try this Zip.xlsx I have posted at my prior post.

Zip.xlsx

[tonyqus]

Ok. 〒 is actually a custom name defined in Name manager

[tonyqus]

Can you try a English name for the defined name instead of Japanese character? I'm thinking it can be a Unicode problem in the regular expression used to parse formula.

[tonyqus]

I don't know why, but the occurrence of this problem is probabilistic. Not 100%. Around 30% ~ 50%?

Is it possible it's a bug in the Microsoft Office you used? Because if it's fully not working, perhaps it's NPOI's problem and the problem of the file generated. If partially working, it can be a bug of Microsoft Office.

What version of Microsoft Office are you using? Office 365?

[kenjiuno]

Can you try a English name for the defined name instead of Japanese character? I'm thinking it can be a Unicode problem in the regular expression used to parse formula.

Yes, finally, I had selected to replace the name 〒 with Zip.

Because both obsoleted EPPlus and NPOI rejected this mark as a named range.

System.ArgumentException: Name 〒 contains invalid characters
場所 OfficeOpenXml.ExcelNamedRangeCollection.Add(String Name, ExcelRangeBase Range)
場所 OfficeOpenXml.ExcelWorkbook.GetDefinedNames()
場所 OfficeOpenXml.ExcelPackage.get_Workbook()

[kenjiuno]

What version of Microsoft Office are you using? Office 365?

I have used Excel 2013 with a perpetual license.

[kenjiuno]

I have decided to post a suggest fullCalcOnLoad = value, as I have found the following information at stackoverflow.

openxml - Set xlsx to recalculate formulae on open - Stack Overflow

<calcPr fullCalcOnLoad="1"/>

This works for all Excel and OpenOffice, LibreOffice, Google Docs and Gnumeric versions that I have tested.

The place it won't work is for non-spreadsheet applications that cannot re-calculate the formula value such as file viewers.

However it is your discretion to use it or not.