0

I've been working w/ OpenpyXL to organize data in Excel workbooks. I have a dataset where on the first sheet 'Data' is about 70 columns & 400 rows of numerical data. A custom function I have goes into another sheet 'Average' and averages across the columns such that theres 1 col & 400 rows of data.

Then, this information is copied to another workbook.

but there's a weird bug. My custom function that copies data reports theres no data in the 'average' sheet. Inspecting the workbook with the "preview" pane in Windows explorer, I see there is indeed no data in column 'A' where data should be averaged.

But when I open the spreadsheet, the averages are all there. If I close the spreadsheet and look at the preview, there's nothing there. If I open the spreadsheet again and edit it in any way close it, and I check the preview, there's data there.

Edit: And when I say that there's "no" data picked up by pthon: What I mean is, is that if I set the load_workbook flag "data_only" to "true", such that I should get only call values and not the formula, python prints out "none" line by line. But if I remove that "data_only" argument completly, I get the formula line by line, which is the "=average(A1:BR1)" or whatever. And then this copies successfully to the new workbook.

So I feel like the preview is telling. I tried adding the workbook.close() function after saving, so that the workbook is saved, closed properly, then re-opened. Didn't work.

I also tried brute forcing the "Editing" I was doing by hand by opening the workbook, entering some arbitrary value in a random cell, then saving & closing it. Didn't work.

Edit2: Seems to be related to this stackoverflow: Python openpyxl data_only=True returning None

SugarCereal191126
  • 15
  • 6

1 Answers1

0

What it boils down to is openpyxl can't handle the values computed by formulas. It's built into the library.

Got around this by computing the average via the Python script (appending a list, then summing the list and dividing it by the length of the list) then putting the average into the cell I want.

There's some answers in Python openpyxl data_only=True returning None

and elsewhere related to using python libraries to simply open excel the application and immediately close it, but I got errors with them.

SugarCereal191126
  • 15
  • 6
  • Your first sentence seems a bit off. From the thread you linked to, [this answer by an openpyxl author](https://stackoverflow.com/a/36117824) is more authoritative. Openpyxl can handle the values computed by a formula, but it [can't actually compute a formula](https://stackoverflow.com/a/23362361) - you need Excel's calculation engine for that. – BigBen May 12 '23 at 20:58
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 13 '23 at 04:49