0

First steps with Python so please be indulgent. I searched a lot, but it is not so clear to me where the error may be.

Accessing an excel file -> writing the sum into it -> re-read the value of the sum. I get =SUM(C3:N9) instead of the value, even with data_only=True

Any hint, please? Thank you in advance

# Load the sources of interest
import openpyxl
from openpyxl import load_workbook

# Open the document
excel_document = openpyxl.load_workbook('1.xlsx', data_only=True)

# Open the sheet of interest
sheet = excel_document.get_sheet_by_name('Foglio1')

# Read a cell of interest and print its content
cell_of_interest_1 = sheet['C4'].value
print cell_of_interest_1

#Ask a value to the user to be inserted for updating the cell of interest
valore1 = input ("Insert the new value for the cell :")

#The cell is updated with the user input
sheet['C4'] = valore1

#Insert the total sum of the values of the Sheet in cell P9
sheet["P9"] = "=SUM(C3:N9)"

# The value of cell P9
grand_sum = sheet['P9'].value

# Read and print the value of cell P9
print "Total sum of the Sheet values:"
print grand_sum

d = sheet.cell(row=9, column=16).value
print d

# Save the updated file
excel_document.save("1.xlsx")
Carlos Gonzalez
  • 858
  • 13
  • 23
InflatableGull
  • 9
  • 3
  • Possible duplicate of [Openpyxl 1.8.5: Reading the result of a formula typed in a cell using openpyxl](https://stackoverflow.com/questions/23350581/openpyxl-1-8-5-reading-the-result-of-a-formula-typed-in-a-cell-using-openpyxl) – stovfl Jan 25 '19 at 15:06

1 Answers1

0

Take a look at the answer linked by stovfl above. Openpyxl does not calculate the result of a formula therefore you can either

option 1 save and reopen the workbook with data_only=True and try to read the recalculated value

option 2 calculate the value yourself using the updated input value.

# Load the sources of interest
import openpyxl
from openpyxl import load_workbook

# Open the document
excel_document = openpyxl.load_workbook('1.xlsx', data_only=True)

# Open the sheet of interest
sheet = excel_document.get_sheet_by_name('Foglio1')

# Read a cell of interest and print its content
cell_of_interest_1 = sheet['C4'].value
print (cell_of_interest_1)

#Ask a value to the user to be inserted for updating the cell of interest
valore1 = input ("Insert the new value for the cell :")

#The cell is updated with the user input
sheet['C4'] = int(valore1)

#Insert the total sum of the values of the Sheet in cell P9
sheet["P9"] = "=SUM(C3:N9)"

# The value of cell P9
grand_sum = sheet['P9'].value


old_c4_value = int(sheet['C4'].value)
# Read and print the value of cell P9
print ("Total sum of the Sheet values:")
old_sum = 0
for row in sheet['C3:N9']:
  for cell in row:
      old_sum+= int(cell.value)

#subtracted the prev value of cell C4 then added the current value to the sum of other cells
real_sum = old_sum+int(valore1)-old_c4_value

print(real_sum)

d = sheet.cell(row=9, column=16).value
print(d)




# Save the updated file
excel_document.save("1.xlsx")

note that i assumed the cells are holding integer values

sunny chidi
  • 84
  • 2
  • 5