5

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)

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!

Léandre Arseneault
  • 53
  • 5
  • You can either have the formulae or the most recently calculated value, never both. – Charlie Clark Feb 22 '17 at 07:56
  • Please explain: "*...the cells containing formulas are reported as empty.*". This can't be the case, as on file open recompute formulas are done. – stovfl Feb 22 '17 at 09:38
  • Right after running the script, I use a javascript script that reads cells. I think this script sees the cells as empty because the sheet is open by a script. As I understand, excel recomputes formulas when opened, but the values are not seen by the script since the sheet needs to be saved in order to really keep those values. – Léandre Arseneault Feb 22 '17 at 15:42

1 Answers1

2

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.

cco
  • 5,873
  • 1
  • 16
  • 21
  • That's what a thought, thanks for the clear answer. Do you know a way to recompute the formulas other that manually opening and saving the sheets? – Léandre Arseneault Feb 22 '17 at 14:18
  • If you're running on Windows and have Excel installed, you can use COM automation to open and save the workbook. A Q&A that has all of the pieces you need is http://stackoverflow.com/questions/24899215/how-to-wait-until-excel-calculates-formulas-before-continuing-with-win32com - be sure and read the answer b/c the question's code is close but not quite right. – cco Feb 22 '17 at 19:45
  • Thanks a lot! This is exactly what I needed. You just saved me a ton of time :) – Léandre Arseneault Feb 22 '17 at 20:58