0

So, I had some code running that opened a series of excel files and converted to dataframes using pd.read_excel, no issues. Just today, the files are now all displaying NaN instead of values. The excel cells have formulas. Searched SO, found several questions on this. None solved it. Figured I would give it another try and see if anyone had a method to get a tab of a multi-tab workbook with formulas into a dataframe displaying the workbook values and not NaN. Thanks.

John Taylor
  • 655
  • 6
  • 19
  • I feel you are better served with openpyxl – sammywemmy Apr 07 '22 at 23:18
  • I think that’s right. Just trying to fashion some solution that will work. – John Taylor Apr 08 '22 at 02:56
  • The first thing that comes to my mind is some kind of auto-calc issue in Excel where the data calculated but came back with NA's. I don't know how it could work, and then suddenly not work, without any changes. The xlwings solution is actually opening Excel, so it could be that the workbook is calculating and getting the values. Try your old method but first make sure the workbook was opened and saved with all of the values. – Eric M Apr 29 '22 at 17:41
  • Pandas uses openpyxl, so switching isn't going to help. `openpyxl` reads the contents of the Excel file, it doesn't execute any macros or formulas. It only offers [simplified formula parsing](https://openpyxl.readthedocs.io/en/stable/formula.html) – Panagiotis Kanavos May 19 '23 at 13:19

1 Answers1

1

Well, after hours of searching, this code, for some reason that is opaque to me, enabled me to use pd.read_excel() and read the values instead of just all NaN values from cells with formulas.

import xlwings as xl

def df_from_excel(path):
    app = xl.App(visible=False)
    book = app.books.open(path)
    book.save()
    app.kill()
    return pd.read_excel(path)
Guillaume Jacquenot
  • 11,217
  • 6
  • 43
  • 49
John Taylor
  • 655
  • 6
  • 19