Django: openpyxl saving workbook as attachment

Question

Hi I have a quick question. I didn't find answer in internet maybe someone of you can help me.

So i want to save workbook as attachment but I don't know how lets see an example :

    from openpyxl import Workbook
    from openpyxl.cell import get_column_letter
    wb = Workbook(encoding='utf-8')
    dest_filename = 'file.xlsx'
    ws = wb.worksheets[0]
    ws.title = "range names"
    for col_idx in xrange(1, 40):
        col = get_column_letter(col_idx)
        for row in xrange(1, 600):
            ws.cell('%s%s'%(col, row)).value = '%s%s' % (col, row)
    ws = wb.create_sheet()
    ws.title = 'Pi'
    ws.cell('F5').value = 3.14

Then I tried :

response = HttpResponse(wb, content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename="foo.xls"'
return response

It's returning xlsx file indeed but in file there is only object adres not the content of file:

<openpyxl.workbook.Workbook object at 0x00000000042806D8>

Can someone help ?

Have you tried to instantiate `output = StringIO.StringIO()`, then write to it via `openpyxl.writer.excel.save_workbook` method and pass `output.getvalue()` to `HttpResponse` constructor? Let me know if it helps - I'll add it as an answer. — alecxe, Apr 15 '13 at 13:23
One more option is to use `openpyxl.writer.excel.save_virtual_workbook` method. — alecxe, Apr 15 '13 at 13:26
`response = HttpResponse(save_virtual_workbook(wb), content_type='application/vnd.ms-excel')` Working Thanks mate :)) make this comment as answer I will mark it as corrected — Silwest, Apr 15 '13 at 13:47

score 37 · Accepted Answer · answered Apr 15 '13 at 13:50

37

Give it a try:

from openpyxl.writer.excel import save_virtual_workbook
...
response = HttpResponse(save_virtual_workbook(wb), content_type='application/vnd.ms-excel')

save_virtual_workbook was specially designed for your use case. Here's a docstring:

"""Return an in-memory workbook, suitable for a Django response."""

answered Apr 15 '13 at 13:50

alecxe

462,703
120
1,088
1,195

1

Thx! That wasn't immediately obvious in the documentation as it's only mentioned in the generated docs – shangxiao Oct 20 '14 at 18:24
It's working , But when I downloaded it on Front End side(Created of Angular Js) , It's giving me some unicode things (Dirty data). Do I need to add something in Angular code? – Piyush S. Wanare Sep 21 '16 at 13:22
3

Hi! This has been deprecated from openpyxl by this commit: https://bitbucket.org/openpyxl/openpyxl/commits/2c8045bc3abf6a637f9f93400d8e0f603506a133. Looking for snippets to use NamedTemporaryFile instead :) – David Guillot Feb 19 '20 at 08:53

score 5 · Answer 2 · answered Jul 27 '16 at 19:13

On at least some versions of django/python/openpyxl, the given solution does not work. See https://bitbucket.org/openpyxl/openpyxl/issues/657/save_virtual_workbook-generates-junk-data

Simple working solution:

wb = Workbook(write_only=True, encoding='utf-8')
ws = wb.create_sheet()
for row in data:
    ws.append([str(cell) for cell in row])
response = HttpResponse(content_type='application/vnd.ms-excel')
wb.save(response)

What's happening here is that Django's HttpResponse is a file-like object. Workbook.save() can take a file-like object. (Internally, it uses zipfile, which takes either a filename or a file-like object.)

If you're manipulating the file in memory, this is the simplest and probably most efficient solution. A streaming response doesn't really make sense since the data is not being created with a generator. Even if save_virtual_workbook works, the data it writes is generated as a block before it's readable.

The other option would be to create a NamedTemporaryFile (from tempfile or Django's wrapper), pass that into Workbook.save(), then use FileResponse to stream that from the filesystem instead of from memory.

emigue · Answer 3 · 2013-04-15T13:41:45.350

4

I usually use

ws = wb.add_sheet("Pi")

instead of

ws = wb.create_sheet()
ws.title = "Pi"

Moreover, you can try to do: (see documentation)

wb.save(stream)

and then use stream in HttpResponse.

edited Apr 15 '13 at 13:41

answered Apr 15 '13 at 13:31

emigue

492
3
13

What is the type of this stream value ? – Silwest Apr 15 '13 at 13:42
You can use StringIO, e.g. – emigue Apr 15 '13 at 16:13
in Python 3 it must be `ByteIO` instead of `StringIO` – WBAR Mar 29 '18 at 16:32

score 0 · Answer 4 · answered Dec 08 '22 at 21:18

0

You can try also the following code lines.
You can also set your file name as you want.

wb = Workbook()
..............
..............
response = HttpResponse(save_virtual_workbook(wb),content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename='+str(yourFileName)+'_report'+'.xlsx'
return response

answered Dec 08 '22 at 21:18

arost2022

1
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 12 '22 at 16:00

Django: openpyxl saving workbook as attachment

4 Answers4

Linked

Related