Calculated cells in excel are not showing the right value in linux

Question

i have a project which deployed in linux environment using docker

this project takes as input a xlsm file that has a lot of sheets within these sheets there are cell contain some formulas when these formulas are calculated, they give values.

the problem that i am facing now is when i open the the content of the file in the project these cells are showing #NAME? instead of the right value, when i open the file in windows it open normally with the right data

since the project is deployed in linux environment i want to retrieve the right data in order to process rightly

thanks for your help !

Answer 1

The second bullet point and comment in the code sample in @Nariman Jafari answer is not correct in relation to Openpyxl.

Basically an Excel cell can have two values, the 'value' of the cell, int, float, date, time or evaluated from a formula and when entered or recognised as such, the cell can also have a formula. In some Python modules you can obtain either or both usually as the .value and .formula attribute to the cell.

For Example;
Cell C1 has a formula, the Sum of the values in cells A1 and B1 (values 1 and 2). When the formula has been evaluated there are two values that can be obtained from this cell.
Formula: SUM(A1:B1)
Value: 3

Sheet XML data (use link if the XML is not displayed below)

<c r="C1">
<f>SUM(A1:B1)</f>
<v>3</v>
</c>

With Openpyxl however you can get only one or the other depending on how you open the workbook.
The data_only= switch determines whether the formula field or the value field is read from the Sheet.
If data_only=False (default) you will get the Formula from the cell if one exists otherwise you get the value
If data_only=True you will get the value from the cell if one exists. If a cell has a formula and, if it has been evaluated and saved since entered to the cell, you will get the calculated value.
If the cell is a formula that has not been evaluated and saved you will get a Python None.
If the cell is empty, i.e. an unused cell you get a Python None regardless.

Example;
If a new Sheet is created and these values added to it A1 = 1 B1 = 2 C1 = SUM(A1:B1)

Openpyxl determines the 'value' for C1 to be a formula and so is stored as a formula, the rest are values.
As noted Openpyxl is effectively just working with this XML data in the Sheet, reading and writing an XML;

Sheet XML data (use link if the XML is not displayed below)

<c r="A1" t="n">
    <v>1</v>
</c>
<c r="B1" t="n">
    <v>2</v>
</c>
<c r="C1">
    <f>SUM(A1:B1)</f>
    <v></v>
</c>

After saving the workbook in Openpyxl you then;
Open using Openpyxl with data_only=True the three cells will return:- 1, 2, None
Open using Openpyxl with data_only=False the three cells will return:- 1, 2, =SUM(A1:B1)

Openpyxl itself does not evaluate the formula ever under any circumstance

If you want the formula evaluated so the <v></v> field for cell C1 is updated you must open and save the file with the Excel app or use another module that can evaluate and update this field.

You can calculate the value of a formula yourself. For example you can read Cells A1, B1 and C1 and calculate in Python the value that cell C1 would have.
However if using Openpyxl you then update C1 with that calculated value you will be overwriting the formula with a value and the formula will be lost.
For this reason be careful opening a workbook with Sheets that contain formulas. If you open the workbook as 'data_only=True` then all cells are the values and re-saving using the same name as the original workbook will overwrite the existing formula, .i.e. all formulas will now be values and the formulas are lost.

Answer 2

I've actually dealt with this exact headache before when working with Excel files in Linux containers. It's a pretty common issue that happens because Linux doesn't have Excel installed to actually calculate those formulas. Here's what's going on: When you open the file in Windows, Excel does all the heavy lifting of calculating those formulas for you. But in your Linux environment, you're probably just reading the raw file content without any formula processing. I'd suggest a couple of ways to tackle this:

1. The quick fix: Before uploading the file to your Linux system, open it in Excel (Windows), save it with all formulas calculated (you can do this by copying the whole sheet and pasting as values). This way, your Linux system will see the actual values instead of formulas.

2. The proper programmatic way: Use a library that can handle Excel formula calculations. I've had good results with openpyxl or Apache POI (if you're using Java/Kotlin). You'll need to configure these to evaluate formulas when reading the file.

Here's a quick example using Python and openpyxl:

pythonCopyfrom openpyxl import load_workbook

wb = load_workbook('your_file.xlsm', data_only=True)
# data_only=True tells openpyxl to calculate formulas