openpyxl 2.4.2:保存后由公式生成的单元格值为空 [英] openpyxl 2.4.2: cell value generated by formula empty after saving

问题描述

我使用openpyxl打开文件，编辑一些单元格并保存更改. 这是一个示例:

I use openpyxl to open a file, edit some cells and save the changes. Here's an example:

  import openpyxl
  book = openpyxl.load_workbook(sheet_path)    
  sheet = book.active

  for row in range(sheet.max_row):
      index = row + 1
      sheet.cell(row=index, column=1).value = "something"

  book.save(sheet_path)

问题是，当我保存文件时，其他单元格也被修改了.实际上，我的包含公式的工作表中的单元格已损坏"，文件大小大大减小了，当我使用其他脚本读取工作表时，包含公式的单元格被报告为空.但是，当我打开工作表时，一切看起来都很正常，当我保存时，一切都得到了修复，文件大小又恢复了正常.我认为问题出在保存时，openpyxl没有计算"公式.这将减小文件大小，并且需要手动打开/保存才能获取实际的单元格值.在不完全更改我使用的库的情况下，我找不到解决此问题的任何方法. 任何帮助，将不胜感激，谢谢！

The problem is, when I save the file, other cells are modified. In fact, the cells in my sheet that contains formulas are "corrupted", the file size is greatly reduced and when I use other scripts to read the sheet, the cells containing formulas are reported as empty. But when I open the sheet, everything looks normal and when I save, everything is repaired and the file size is back to normal. I think the problem comes from openpyxl not "calculating" the formulas when saving. This would reduce the file size and require a manual opening/saving in order to get the real cell values. I can't find any way to resolve this issue without completely changing the library I use. Any help would be appreciated, thanks!

推荐答案

openpyxl读写现有公式时，将忽略计算值，并且不会为新公式计算值. openpyxl在工作簿中设置一个标志，告诉excel在加载时重新计算公式值，这就是为什么当您使用excel查看时，一切看起来都很正常.

When openpyxl reads and writes existing formulas, the computed values are omitted, and no values are computed for new formulas. openpyxl sets a flag in the workbook that tells excel to recompute formula values on load, which is why everything looks normal when you look at it with excel.

这篇关于openpyxl 2.4.2:保存后由公式生成的单元格值为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！