Closed markolbert closed 4 months ago
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
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?
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.
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
NPOI Version
2.7.0
File Type
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.
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.
I have tried eliminating setting the CellStyle and the problem still exists.