65

I am having real trouble with this, since the cell.value function returns the formula used for the cell, and I need to extract the result Excel provides after operating.

Thank you.

Ok, I think I ahve found a way around it; apparently to access cell.internal value you have to use the iter_rows() in your worksheet previously, which is a list of "RawCell".

for row in ws.iter_rows():

    for cell in row:

        print cell.internal_value
user3455972
  • 651
  • 1
  • 5
  • 4

6 Answers6

113

Like Charlie Clark already suggest you can set data_only on True when you load your workbook:

from openpyxl import load_workbook

wb = load_workbook("file.xlsx", data_only=True)
sh = wb["Sheet_name"]
print(sh["x10"].value)
starball
  • 20,030
  • 7
  • 43
  • 238
Iulian Stana
  • 1,632
  • 1
  • 14
  • 17
  • 1
    'x10' refer to column with letter 'X' and row with number 10. – Iulian Stana Mar 31 '16 at 08:27
  • 2
    Hey, apparently the `sh["x10"]` is a deprecated method. You got anymore of these ... with iter_rows() ? – Imad Jun 01 '18 at 13:13
  • 1
    A problem with this approach is you have the spreadsheet, then the formula's a discarded, essentially requiring the user to save the file as new copy. – gimmegimme Jun 24 '22 at 04:30
34

From the code it looks like you're using the optimised reader: read_only=True. You can switch between extracting the formula and its result by using the data_only=True flag when opening the workbook.

internal_value was a private attribute that used to refer only to the (untyped) value that Excel uses, ie. numbers with an epoch in 1900 for dates as opposed to the Python form. It has been removed from the library since this question was first asked.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
13

You can try following code.Just provide the excel file path and the location of the cell which value you need in terms of row number and column number below in below code.

    from openpyxl import Workbook
    wb = Workbook()
    Dest_filename = 'excel_file_path'
    ws=wb.active
    print(ws.cell(row=row_number, column=column_number).value)
Rohit Gawas
  • 267
  • 3
  • 8
2

Try to use cell.internal_value instead.

Vitaly Isaev
  • 5,392
  • 6
  • 45
  • 64
  • Already tried.. don't know if the feature is not present from where I'm working or the module doesn't currently have it implemented. I get the error message: AttributeError: 'Cell' object has no attribute 'internal_value'. So I was wondering if there is any other way to accomplish this. – user3455972 Mar 24 '14 at 15:22
0

Please use this below in Python, and you can get the real values with openpyxl module:

for row in ws.iter_rows(values_only=True):
    for cell in row:
        print(cell)
freemangifts
  • 144
  • 6
0

I made a value decoder for instances when you need a name off of a name sheet. Hope this helps!

def valueDecode(value="NAME_SHEET!B2",wb):
    #value = "=NAME_SHEET!B2"

    if "=" not in str(value): #checks to see if it is referring to another sheet 
        return value

    decodeSheet = ""

    preDecode1 = value.split("=") #removes equal sign
    decode = preDecode1[1].split("!") #should have the name of the sheet and then the cell we need to use 
    #print(decode) 

    dSheet = (str(decode[0]))
    decodeSheet = wb[dSheet]
    #find B2 Value if it exists 
    #print(decodeSheet[decode[1]].internal_value)
    #save value 
    raw_value = decodeSheet[decode[1]].internal_value
    return raw_value #should return the name that exists on the name sheet
Matthew Wilde
  • 1
  • 2