Error when trying to load_workbook using openpyxl

Anvil Q&A
1

What I’m trying to do:
I am using openpyxl to load a .xlsm workbook however I am getting the the below error. The file is fine opens with excel outside of the code. Is it a python version problem. Using python 3.10 with the openpyxl package 3.2.0b1. Thank you all for the help.

InvalidFileException: openpyxl does not support file format, please check you can open it with Excel first. Supported formats are: .xlsx,.xlsm,.xltx,.xltm

What I’ve tried and what’s not working:

Code Sample:

@anvil.server.callable
def Reporttoreview(reporttoreview, previousreport):
  with anvil.media.TempFile(reporttoreview) as f:
      print(f)
      'Open the Current Report'

      wb = load_workbook(f, data_only=True)
2

I use openpyxl every day, it is not up to normal python standards for handling files. (Not fully pythonic)

This error is usually caused by the name of the file you are writing to or opening not ending in one of the file format extensions specified in the error.

What does the variable reporttoreview contain when passed to this function?
Specifically what type of object is it. (string, media object? etc.)

3

yeah I know but it usually does the job. lol

so when your print(f)
result - /tmp/k2ez2pn2eqkerxz6d52p0pzolwmkyvz4. The .xlsm is using a file uploader. pd.read_excel() works on the file so i think its weird how load_workbook does work.

4

Just to test if its breaking because of this weird behavior of openpyxl try this and see what happens:

from pathlib import Path

@anvil.server.callable
def Reporttoreview(reporttoreview, previousreport):
 ̶ ̶ ̶r̶e̶p̶o̶r̶t̶t̶o̶r̶e̶v̶i̶e̶w̶ ̶=̶ ̶P̶a̶t̶h̶(̶r̶e̶p̶o̶r̶t̶t̶o̶r̶e̶v̶i̶e̶w̶)̶
̶ ̶ ̶n̶e̶w̶_̶p̶a̶t̶h̶ ̶=̶ ̶r̶e̶p̶o̶r̶t̶t̶o̶r̶e̶v̶i̶e̶w̶.̶w̶i̶t̶h̶_̶s̶u̶f̶f̶i̶x̶(̶'̶.̶x̶l̶s̶m̶'̶)̶
̶ ̶ ̶r̶e̶p̶o̶r̶t̶t̶o̶r̶e̶v̶i̶e̶w̶.̶r̶e̶n̶a̶m̶e̶(̶n̶e̶w̶_̶p̶a̶t̶h̶)̶
  # EDIT: Don't bother with this, I read alexpetrone's response incorrectly
  # read further below

  with anvil.media.TempFile(reporttoreview) as f:
      print(f)
      'Open the Current Report'

      wb = load_workbook(f, data_only=True)
5

Broke on reporttoreview = Path(reporttoreview)

TypeError: expected str, bytes or os.PathLike object, not StreamingMedia

  • at /usr/local/lib/python3.10/pathlib.py:578
  • called from /usr/local/lib/python3.10/pathlib.py:594
  • called from /usr/local/lib/python3.10/pathlib.py:960
  • called from ServerModule1, line 35
  • called from Main, line 35
6

Ah, I just realized you said you did print(f), not reporttoreview.

If its a media file, then you could create a file handler directly from the bytes of the media object, instead of using the anvil.media.TempFile() context manager.

7

Can you give me the code snippet example? Thank you very much for your help.

8 
from io import BytesIO
import anvil.media

@anvil.server.callable
def Reporttoreview(reporttoreview, previousreport):
  
  f = BytesIO( reporttoreview.get_bytes() )
  wb = load_workbook(f, data_only=True)
1 Like
9

Worked like a charm!

1 Like
10

Reviewing this again today, I still don’t know why openpyxl will not work with anvil.media.TempFile().

My one last guess would be that when loading the file with the extension of .xlsm using the anvil file loader the MIME type of the anvil.Media object was not correctly set to application/vnd.ms-excel

You can easily directly change the MIME type of a media object by directly changing the content_type attribute, like:

anvil_media_object.content_type = "application/vnd.ms-excel"