Cells with Formulas Display as Blank about npoi HOT 3 CLOSED

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.

from npoi.

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?

from npoi.

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

from npoi.