openpyxl输出公式结果,而不是将公式放入单元格 [英] openpyxl output formula results, not formula into cells

查看:521
本文介绍了openpyxl输出公式结果,而不是将公式放入单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试完成一个脚本来转换Excel文件.它遍历一列中的行,并创建一个公式来清除另一列中的数据.到目前为止,这是我的代码:

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?

推荐答案

不幸的是,我没有Excel,但是此代码在LibreOffice Calc中可以正常工作.在Excel中可能会禁止执行公式,您应该手动允许它?

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?

此外,您还需要range(2,max_row +1 ),否则它将不适用于最后一行.

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

这篇关于openpyxl输出公式结果,而不是将公式放入单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆