EPPlus can't get cell value unless document was opened before

Question

I'm trying to parse an excel worksheet using EPPlus, and having issues getting values from formula cells. The document I'm using is downloaded from internet as byte array on runtime, and is working fine if saved and opened using Excel. File format is .xlsm (macros enabled).

The issue is the following: Some cells contain formulas, that just reference cells in different sheets, like for example: ='diffSheet'!A25. I cannot get values from such cells: cell.Value is null, and cell.Text is just " -".

I tried calling Calculate() method on those cells, but all it makes is just loads for a really long time, and then returns the "#VALUE" error every time.

The weird thing, is that the same document is processed by the same code just fine, if the document was opened by Excel previously. I tried downloading the document manually, opening it in Excel, then uploading it back; and the code had no issues getting values from same cells after that. It may be worth pointing out that Excel does not calculate formula values unless you press the Enable Editing button, but there is no such thing is EPPlus as far as I know. I tried to save & reopen the document programmatically, but it did not work.

I hope somebody could explain what's the issue here, and if there is the way to process the document without user having to open it each time.

Thanks in advance!

Wolfgang Jacques Wolfgang Jacques · Accepted Answer · 2020-08-13T16:01:27

Excel files can contain values for cells which have formulas but the values might be missing as well. Once you open and save the file in Excel, Excel calculates and writes all the values.

EPPlus can do some of the calculation but it does not use Excel (or require it to be installed) and it is not so advanced in calculations as Excel itself.

As far as I know, doing the calculations one important piece of work for the EPPlus developers who are going commercial now with their version 5.

Excel (xlsx) files are in fact ZIP files. So if you extract them, you can view the contents as xml. For example:

reads like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{3DBD100E-5F85-4729-996E-130F34C637C8}">
    <sheetPr codeName="Sheet1"/>
    <dimension ref="A1:A3"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="A3" sqref="A3"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData>
        <row r="1" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A1">
                <v>1</v>
            </c>
        </row>
        <row r="2" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A2">
                <v>2</v>
            </c>
        </row>
        <row r="3" spans="1:1" x14ac:dyDescent="0.25">
            <c r="A3">
                <f>A1+A2</f>
                <v>3</v>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

In Cell A3 you can notice the formula and the value. Go ahead and inspect your excel files before opening and you will see.

(This does not apply to old .xls format.)

EPPlus can't get cell value unless document was opened before

1 Answers