how to load workbook using tempfile using openpyxl

Question

In my flask web app, I am writing data from excel to a temporary file which I then parse in memory. This method works fine with xlrd but it does not with openpyxl.

Here is how I am writing to a temporary file which I then parse with xlrd.

    xls_str = request.json.get('file')

    try:
        xls_str = xls_str.split('base64,')[1]
        xls_data = b64decode(xls_str)
    except IndexError:
        return 'Invalid form data', 406

    save_path = os.path.join(tempfile.gettempdir(), random_alphanum(10))
    with open(save_path, 'wb') as f:
        f.write(xls_data)
        f.close()
    try:
        bundle = parse(save_path, current_user)
    except UnsupportedFileException:
        return 'Unsupported file format', 406
    except IncompatibleExcelException as ex:
        return str(ex), 406
    finally:
        os.remove(save_path)]

When I use openpyxl with the code above it complains about an unsupported type but that is because I'm using a temporary file to parse the data hence it doesn't have an ".xlsx" extension and even if I added it, it would not work because its not a excel file after all.

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

What should I do?

You're already generating the filename yourself, easy to append '.xlsx'. — Charlie Clark, Nov 26 '19 at 10:14
Adding a ".xlsx" extension does not work because the data written into the temp file is binary. — Boosted_d16, Nov 26 '19 at 10:22
XLSX files are binary, if you still can't open the files like this then there is something wrong with how you're handling things and the exception is a friendly hint. — Charlie Clark, Nov 26 '19 at 13:47

score 3 · Accepted Answer · answered Nov 26 '19 at 09:42

Why not create a temp excel file with openpyxl instead. Give this example a try. I did something similar in the past.

from io import BytesIO
from openpyxl.writer.excel import save_virtual_workbook
from openpyxl import Workbook


def create_xlsx():
    wb = Workbook()
    ws = wb.active

    row = ('Hello', 'Boosted_d16')
    ws.append(row)

    return wb

@app.route('/',  methods=['GET'])
def main():
    xlsx = create_xlsx()

    filename = BytesIO(save_virtual_workbook(xlsx))

    return send_file(
        filename,
        attachment_filename='test.xlsx',
        as_attachment=True
        )

how to load workbook using tempfile using openpyxl

1 Answers1