0

I am reading from a file like this in openpyxl:

import openpyxl
from pathlib import WindowsPath

p = WindowsPath(r'.\data\product_version.xlsx')

if p.exists():
    wb = openpyxl.load_workbook(p)
    ws = wb.active
    for row in ws.iter_rows(values_only=True):
        print(row)

This should return values only and indeed it does for some columns , while others that are basically excel functions return the function as a value.

('WTX84', 'TX', '=MID(A338,1,3)', 'Yes', 'Yes', 258979, 255980)
('WTX90', 'TX', '=MID(A339,1,3)', 'Yes', 'Yes', 258979, 11572174)

I want the value =MID(A338,1,3) from this.

moth
  • 1,833
  • 12
  • 29
  • 2
    You may need to [add an option when loading the workbook](https://stackoverflow.com/questions/23350581/openpyxl-1-8-5-reading-the-result-of-a-formula-typed-in-a-cell-using-openpyxl) – Shorn Feb 21 '23 at 08:56
  • maybe you need something like `value = ws['C1']` – JRudransh Feb 21 '23 at 09:02
  • 1
    Openpyxl [doesn't evaluate formulas](https://openpyxl.readthedocs.io/en/stable/simple_formulae.html?highlight=formula#using-formulae). The reason you see values at all is because Excel, the application, saves the calculated results when saving a file. If you want to evaluate the formulas you need to use another library – Panagiotis Kanavos Feb 21 '23 at 09:03
  • hum ok, so it might be easier to read with pandas – moth Feb 21 '23 at 09:10
  • As Panagiotis Kanavos states Openpyxl doesn't evaluate formulas neither does Pandas. The Openpyxl doc shows as you can either open a workbook to read formulas as the formula or the last calculated value (when it was last opened in Excel and updated) [Loading from a file](https://openpyxl.readthedocs.io/en/stable/tutorial.html?highlight=data_only%3D#loading-from-a-file) If you want to get up to date calulated values you need Xlwings (which uses Excel) or PyCel or Koala. – moken Feb 22 '23 at 02:39
  • @moken but if i open a file with pandas that does the trick . why you saying pandas does not ? – moth Feb 22 '23 at 03:21

0 Answers0