Opened file as data_only and calculated values are None

[rachel.l...@gmail.com]

Hi everyone,

I'm trying to use openpyxl to do the following:

1. Open an Excel file with pre-entered formulas and paste values into it.

2. Save the file under a unique name.

3. Re-open the unique file as data_only=True so I can...

4. Get the values of the calculated cells, not just the formulas.

Steps 1 and 2 work just fine. Step 3 also seems to work, but when I try to access calculated values from the file, their value appears to be None. Non-calculated values can be accessed without a problem.

Here's the relevant code for Steps 3 and 4.

current_individual_file = openpyxl.load_workbook(individual_filename, data_only=True)
current_sheet = current_individual_file.get_sheet_by_name(unicode('Template 2'))

value_list = []
for row in range(14, 26):
    value_list.append(current_sheet['AB' + str(row)].value)

Although the calculated cells show correct values when I open the Excel document, value_list comes out as a list of Nonetype objects. I have tried using internal_value as well.

I have done quite a bit of searching for a solution but haven't found anything exactly analogous to my situation. Thanks in advance for your help.

Best,

Rachel Klein

[Thomas Nygårds]

Hi Rachel.

The problem is that openpyxl will not evaluate the formulas.
MS Excel stores the formulas and the evaluated value. The evaluated values are what openpyxl is using when you do

data_only=True

If you could insert a step between 2 and 3 where you open the file with MS Excel (using for example win32com) and save it again I think it would work like you expected, as the evaluated values will be saved to the file.

[Charlie Clark]

Am .02.2016, 22:59 Uhr, schrieb <rachel.l...@gmail.com>:

> Hi everyone,

Hiya,

> I'm trying to use openpyxl to do the following:
>
> 1. Open an Excel file with pre-entered formulas and paste values into it.
> 2. Save the file under a unique name.
> 3. Re-open the unique file as data_only=True so I can...
> 4. Get the values of the calculated cells, not just the formulas.
>
> Steps 1 and 2 work just fine. Step 3 also seems to work, but when I try
> to
> access calculated values from the file, their value appears to be
> None. Non-calculated values can be accessed without a problem.

As Thomas said, it's not entirely clear what's being saved by what in Step
2.

> Here's the relevant code for Steps 3 and 4.

Ugh!

> current_individual_file = openpyxl.load_workbook(individual_filename,
> data_only=True)
> current_sheet =
> current_individual_file.get_sheet_by_name(unicode('Template
> 2'))

Why unicode?

current_sheet = current_individual_file['Template 2']

> value_list = []
> for row in range(14, 26):
> value_list.append(current_sheet['AB' + str(row)].value)

In openpyxl you'll never need to manually create 'A1' style coordinates.

values = []
for row in current_sheet['AB14':'AB26']:
for cell in row:
values.append(cell.value)

(can be done in a single list comprehension if you'r feeling adventurous)

> Although the calculated cells show correct values when I open the Excel
> document, value_list comes out as a list of Nonetype objects. I have
> tried
> using internal_value as well.
>
> I have done quite a bit of searching for a solution but haven't found
> anything exactly analogous to my situation. Thanks in advance for your
> help.

Hope this helps you along. If you're still having problems you may need to
supply a file.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[rachel.l...@gmail.com]

Hi Thomas,

Thank you so much, that worked!

I thought the values were being evaluated because when I manually opened the Excel file they were displaying correctly. But I guess openpyxl couldn't get to them until saving them through the Excel app as you suggested.

I so appreciate your help (and patience with my code).

Rachel

[rachel.l...@gmail.com]

Hi Charlie,

Thanks for the stylistic and syntax help, particularly helping me revise those Unicode lines. I put that in because it wasn't finding the worksheet by name without it, but glad to know there's a much better way.

Have a great day!

Rachel

[danghoan...@gmail.com]

Hi Thomas,

I'm sorry to interrupt into the middle, but could you please provide me another solution when I need to run on Linux so the win32com won't work there.

Thanks,

Huy.Dang