Cells with Formulas Display as Blank

[markolbert]

NPOI Version

2.7.0

File Type

• XLSX
• XLS
• DOCX
• XLSM
• OTHER

Upload the Excel File

Sorry, can't do that -- it's got confidential information in it.

Reproduce Steps

Set a cell to contain a formula (e.g., using the SUM function) and the resulting cell, when you open it in Excel, is blank. However, if you edit the cell (F2) and just hit return, the value appears. Something is happening behind the scenes, though, because if you try to close the file, Excel prompts you to save it, which it doesn't if you just open and close the file.

This problem does not appear if you simply assign a value to the cell. In that case, the value appears.

Issue Description

Here's the code fragment I've been using to demonstrate this. This first snippet demonstrates the problem.

  var cell = Creator.Sheet.GetOrCreateCell( startingRow + tableExporter.NumDataRows, sheetCol );

  cell.CellStyle = Creator.StyleSets.ResolveCellStyle(tableExporter.Sheet!.Workbook, aggStyle);
  cell.SetCellFormula($"{aggFuncName}({range})");

GetOrCreateCell() is a method that does what its name suggests: it retrieves a cell from the worksheet, and, if no such cell exists, it creates it.

aggFuncName is the name of an aggregate function. In my test cases I'm using SUM.

range is the range of cells to be evaluated. I've verified it's properly structured. Doing the F2/enter trick I mentioned above does not change the formula in the cell being displayed in Excel.

This next snippet works correctly.

  var cell = Creator.Sheet.GetOrCreateCell( startingRow + tableExporter.NumDataRows, sheetCol );

  cell.CellStyle = Creator.StyleSets.ResolveCellStyle(tableExporter.Sheet!.Workbook, aggStyle);
  cell.SetCellValue(-5.0);

I have tried eliminating setting the CellStyle and the problem still exists.

[markolbert]

I'm going to share anything interesting I find from doing a diff on the text/xml versions of the two files (the one which doesn't display the values in formula cells and one which does).

1. Content_Types.xml - diff reports the files are encoded differently, the original (created by NPOI) being UTF-8 encoded but without a signature (whatever that is). The version saved by Excel has a signature.

2. Same lack of signature on _rels/.rels

3. docProps/app.xml (I had to reformat the Excel-written file because it was not indented): Minor changes, mostly related to where various things show up as nodes and attributes

4. docProps/core.xml (this time I had to reformat the NPOI created file): The corProperties node in the NPOI version appears to be missing this attribute:

xmlns:dcmitype="http://purl.org/dc/dcmitype/"

5. docProps/custom.xml: Certain property nodes have different values:

From the NPOI version:

  <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="2" name="Generator">
    <lpwstr xmlns="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">NPOI</lpwstr>
  </property>
  <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="3" name="Generator Version">
    <lpwstr xmlns="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">2.7.0</lpwstr>
  </property>

    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="2" name="Generator">
        <vt:lpwstr>NPOI</vt:lpwstr>
    </property>
    <property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="3" name="Generator Version">
        <vt:lpwstr>2.7.0</vt:lpwstr>
    </property>

6. xl/worksheets/sheet2.xml (I had to reformat both files -- and I should've started here first, as this contains the actual spreadsheet data).

Some interesting differences in the formula cells. First, here's what NPOI wrote:

<row r="981" spans="1:27" ht="17.5" x14ac:dyDescent="0.35">
    <c r="H981" s="351">
        <f dt2D="0" dtr="0">sum(I973:I977)</f>
        <v/>
    </c>
    <c r="I981" s="353">
        <f dt2D="0" dtr="0">sum(I973:I977)</f>
        <v/>
    </c>

And here's what Excel saved the file as:

<row r="981" spans="1:27" ht="17.5" x14ac:dyDescent="0.45">
    <c r="H981" s="9">
        <f>SUM(I973:I977)</f>
        <v>77.759999999999991</v>
    </c>
    <c r="I981" s="11">
        <f>SUM(I973:I977)</f>
        <v>77.759999999999991</v>
    </c>

The Excel-written file does not include the dt2D="0" dtr="0" attribute in the nodes. More importantly, the v nodes in the NPOI-written file are empty. I presume v stands for value, i.e., the cell's value.

Is maybe the problem that I have to have NPOI do a recalc or something before saving the file? Or do I have to calculate the value myself and store that as well as the formula?

[markolbert]

Apparently you do have to evaluate a workbook before saving it to get those value nodes created. Here's the code I used:

var evaluator = new XSSFFormulaEvaluator( _workbook );
evaluator.EvaluateAll();

worked like a charm.

[tonyqus]

The Excel-written file does not include the dt2D="0" dtr="0" attribute in the nodes. More importantly, the v nodes in the NPOI-written file are empty. I presume v stands for value, i.e., the cell's value.

Please use 2.6.2 version for now. 2.7.0 have a regression bug #1315

Let me know if it works after switching back to 2.6.2