41

I'm building OpenPyXL into an application that expects a string containing the content of the excel file, for it to write via file stream.

From my investigation into the OpenPyXL source code, it doesn't look like it supports this kind of output. Does anyone have any experience with modifying openpyxl to support this?

Or any general advice/workarounds?

Thanks.

Nelson Shaw
  • 1,103
  • 2
  • 10
  • 10

5 Answers5

51

In openpyxl 2.6 calling the save_virtual_workbook method issues the following warning:

DeprecationWarning: Call to deprecated function save_virtual_workbook (Use a NamedTemporaryFile).

At some point save_virtual_workbook will be removed from openpyxl.

In Python 3 typical usage to save an openpyxl workbook to a filestream becomes:

from io import BytesIO
from tempfile import NamedTemporaryFile
from openpyxl import Workbook

wb = Workbook()
with NamedTemporaryFile() as tmp:
    wb.save(tmp.name)
    output = BytesIO(tmp.read())

After looking at the implementation of the WorkBook save method, the 'filename' is sent straight to ZipFile which accepts a path or file-like object so there is no need for a NamedTemporaryFile and simply use in-memory BytesIO:

from io import BytesIO
from openpyxl import Workbook

wb = Workbook()
virtual_workbook = BytesIO()
wb.save(virtual_workbook)

# now use virtual_workbook to send to a stream; email attachment, etc
adejones
  • 960
  • 10
  • 11
  • Could you please care to answer as to how we are supposed to send virtual_workbook to send to a stream or an email or via an API response. – Anutosh Chaudhuri Dec 14 '21 at 07:33
  • Saving directly to a `BytesIO` does not work here for some reason. But anyway, it is more of a hack based on private code so I wouldn't recommend doing that on production code as there is no guarantee that private code will not change. The suggested code with `NamedTemporaryFile` works fine though, and is also suggested by the deprecation message for `save_virtual_workbook`! – Jerther Jul 20 '23 at 14:57
50

jcollado's answer is actually valid, but there is also a function (sadly not documented yet) called "save_virtual_workbook" in openpyxl.writer.excel that will take your workbook and return the workbook as a string:

from openpyxl.workbook import Workbook
from openpyxl.writer.excel import save_virtual_workbook

wb = Workbook()
print save_virtual_workbook(wb)

What you're looking for is the string returned by save_virtual_workbook()

Eric Gazoni
  • 846
  • 7
  • 7
  • 9
    This was exactly what I needed to save via filestream. You can also save it like: `io.BytesIO(save_virtual_workbook(wb))` – Esteban Jul 06 '15 at 19:50
  • 5
    We should not use this function anymore. `Deprecated: Use a NamedTemporaryFile`. See [the documentation](https://openpyxl.readthedocs.io/en/stable/tutorial.html#saving-as-a-stream) – LMB Oct 29 '21 at 09:53
19

What about using a StringIO object to save the contents of the file:

from openpyxl.workbook import Workbook
from StringIO import StringIO

output = StringIO()
wb = Workbook()
wb.save(output)
print output.getvalue()

The string you're looking for is what is being printed in the last line of this example.

jcollado
  • 39,419
  • 8
  • 102
  • 133
  • 4
    that doesn't seem to work.. instead of a IO-Byte-object thing, wb.save() is just expecting a filename (a str-object) – nuts Mar 03 '15 at 12:28
  • 3
    Confirming that this does not work. Whatever you provide to the save method of the wb is trying to be used as the filename. – Esteban Jul 06 '15 at 19:49
  • 1
    My openpyxl 2.3.3. works with this, but requires `io.BytesIO` – velis Mar 04 '16 at 05:40
  • Just to support this - checking the [openpxyl code on this](https://bitbucket.org/openpyxl/openpyxl/src/93604327bce7aac5e8270674579af76d390e09c0/openpyxl/writer/excel.py?at=default&fileviewer=file-view-default#excel.py-221), it just passes the `filename` to Python's [ZipFile library](https://docs.python.org/2/library/zipfile.html#zipfile.ZipFile) which accepts _either_ a filename _or_ a file-like object (such as `BytesIO`). Unfortunately, I haven't been able to find out since what version this has been the case. – Jonas Jun 03 '16 at 09:05
10

A compatible implementation with save_virtual_workbook deprecated since version 2.6:

from io import BytesIO
from tempfile import NamedTemporaryFile


def save_virtual_workbook(workbook):
    with NamedTemporaryFile() as tf:
        workbook.save(tf.name)
        in_memory = BytesIO(tf.read())
        return in_memory.getvalue()
Thanh Nguyen
  • 5,174
  • 11
  • 43
  • 74
  • I have tried that method but I'm getting `Object of type bytes is not JSON serializable`. More here: https://stackoverflow.com/questions/62519571/output-json-file-as-excel-formated-filestream – JackTheKnife Jun 22 '20 at 19:34
  • @JackTheKnife Are you working with Django Rest framework? It would be helpful if you can provide your minimal code. – Thanh Nguyen Jun 23 '20 at 02:13
0
from openpyxl import Workbook
from io import BytesIO

rows = [[1,2], [3,4]]

book = Workbook()
sheet = book.active

for row in rows:
    sheet.append(row)

io = BytesIO
book.save(io)

content = io.getValue()

return Response(
    content,
    mimetype=magic.from_buffer(content, mime=True),
    headers={
    'Content-Disposition': 'attachment;filename=' + 'test.xlsx'}
)
Vlad Bezden
  • 83,883
  • 25
  • 248
  • 179
Marlon Bernal
  • 577
  • 5
  • 5