openpyxl output formula results, not formula into

问题:

I'm trying to finish a script to convert an Excel file. It iterates through rows in a column, and creates a formula which cleans data in another column. Here is my code so far:

import openpyxl

wb = openpyxl.load_workbook('test-in.xlsx')
ws = wb.worksheets[2]
max_row = sheet.max_row

for row_num in range(2, max_row):
    ws['I{}'.format(row_num)] = '=CLEAN(D{})'.format(row_num)


wb.save('test-out.xlsx')

This works very nicely, however the output file contains cells with the above formula in them. I don't want to send the sheet with formulas in it, I just want the resulting values. Is there a way to do this?

回答1:

Unfortunately, I don't have Excel but this code works fine in LibreOffice Calc. May be executing formulas is somehow forbidden in Excel and you should manually allow it?

Also you need range(2, max_row + 1) otherwise it won't work for the last row.

回答2:

The documentation is quite clear on this http://openpyxl.readthedocs.io/en/latest/usage.html#using-formulae