5

I have a excel 2010 file which has cells (See C2 below in formula bar) with formulae. (Screenshot 1 below)

enter image description here

I am reading the value using python and it's printing the formula instead of actual value of the cell (Screenshot below of eclipse console.)

enter image description here

I want the results to print 10.188.11.184 which is the value populated by the formula of the excel cell. How can this be achieved? Thanks in advance.

santoshaa
  • 173
  • 2
  • 4
  • 10
  • possible duplicate of [Read Excel cell value and not the formula computing it -openpyxl](http://stackoverflow.com/questions/28517508/read-excel-cell-value-and-not-the-formula-computing-it-openpyxl) – Nagaraj Tantri Jun 09 '15 at 14:05

2 Answers2

11

From: https://openpyxl.readthedocs.org/en/latest/usage.html#read-an-existing-workbook

data_only controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet.

wb = load_workbook('file.xlsx', data_only=True)
Eric Levieil
  • 3,554
  • 2
  • 13
  • 18
  • 1
    Please provide an explanation to your answer, and format code explicitly. – AncientSwordRage Jun 09 '15 at 14:03
  • But when it saves the file, my formulas are gone from the excel sheet !! is there a way to leave the cells with formulas alone? i am not updating those cells anyways. – santoshaa Jun 09 '15 at 14:09
  • "it saves the files"? what is "it"? Please provide a complete minimal example.(code) – Eric Levieil Jun 09 '15 at 14:12
  • def update_driver_excel(fullFilePathOfDriverExcel): wb = load_workbook(fullFilePathOfDriverExcel, data_only=True) first_sheet = wb.get_active_sheet() for x in range(2,12): ipAddressOfMc = first_sheet.cell(row=x,column=3).value print(ipAddressOfMc) if(isUp(ipAddressOfMc)): first_sheet.cell(row=x,column=4).value = 1 wb.save(fullFilePathOfDriverExcel) else: first_sheet.cell(row=x,column=4).value = 0 wb.save(fullFilePathOfDriverExcel) print("Done updating the driver.") – santoshaa Jun 09 '15 at 14:13
  • Sorry no formatting possible above, so if you see i implemented date_inly=True above, and finally i am doing wb.save. after that when i opened the excel file, my formulae were gone. – santoshaa Jun 09 '15 at 14:16
  • 1
    Have you tried opening two Workbooks (one with data_only for reading values, one without for editing and saving)? – Eric Levieil Jun 09 '15 at 15:03
  • 2
    In openpyxl you can **either** have the formulae **or** the results of evaluating them. This is because the formulae themselves are never evaluated so their stability cannot be guaranteed. If you need both then you may be best scripting an application that can evaluate them. xlwings, http://xlwings.org/, might be useful here. – Charlie Clark Jun 09 '15 at 17:25
  • Charlie, you are right, python removes the formulas from the excel file on workbook.save(). I am going to stick to openpyxl for now, i will use a backup of the main excel every time i start the execution. may be shift to xlwings later. Thanks for all the help. – santoshaa Jun 10 '15 at 07:53
0

You can use xlrd module and do the reading easily.

read_fullreport = xlrd.open_workbook(report_to_read)
read_fullreport_sheet = read_fullreport.sheet_by_index(0)
ipAddressofMC = read_fullreport_sheet.cell_value(row_nr, col_nr)
jepaljey
  • 15
  • 9