How to get the formula of Excel file as a character string in Python.
After checking, it seems that you can use a library called openpyxl for XLSX files. However, the weak point is that the result of the formula cannot be obtained.
In the case of XLS file, it can be read by xlrd, but it seems that the formula itself cannot be obtained (Reference / questions / 4690423 / get-formula-from-excel-cell-with-python-xlrd)). This time, on the contrary, only the value is available.
The following is an operation experiment. Use openpyxl to create an XSLX file with formulas and save it as "test.xlsx". Furthermore, this file is read and the formula part is acquired as a character string.
import openpyxl
# 1. Create a XLSX file #
wb = openpyxl.Workbook()
ws = wb.active
# can access a cell by:
# - cell(row = 1, column = 1)
# - cell["A1"]
for i in range(1, 11):
# note: cells are one-based
ws.cell(row = i, column = 1).value = i
ws["C1"].value = "=SUM(A1:A10)"
wb.save("test.xlsx")
# 2. Read and get formula
x = openpyxl.load_workbook("test.xlsx", data_only = False)
s = x.active
print("formula at C1 is...", s["C1"].value)
# Note. openpyxl does not read the computed value of cells
Recommended Posts