python - Python - 从excel文件中获取数字而不是公式

Question

Python的第一步，所以请放纵一下。我搜索了很多，但我不清楚错误可能在哪里。

访问 excel 文件 -> 将总和写入其中 -> 重新读取总和的值。我得到 =SUM(C3:N9) 而不是值，即使使用 data_only=True

请问有什么提示吗？先感谢您

# 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")

标签： pythonexcelpython-2.7sumformula

Answer 1

看看上面 stovfl 链接的答案。Openpyxl 不计算公式的结果，因此您可以

选项 1 使用 data_only=True 保存并重新打开工作簿并尝试读取重新计算的值

选项 2 使用更新的输入值自己计算值。

# 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")

请注意，我假设单元格包含整数值