Django: export object from database to excel

Question

i have this in my template :

<table>
<tr><td>book_id</td><td>book name</td><td>book_author</td></tr>
{% for book in books %}
<tr><td>{{ book.book_id }}</td><td>{{ book.book_name }}</td><td>{{ book.book_author }}</td></tr>
{% endfor %}
</table>
<a href="/export">Export to Excel !</a>

my view seems to be like this:

def export_excel(request):
    books = Book.objects.all()
    response = HttpResponse(books , content_type='application/vnd.ms-excel;charset=utf-8')
    response['Content-Disposition'] = 'attachment; filename="books.xls"'
    return response

And here is mu url in urls.py:

url(r'^export$', 'export_excel', name='export_excel'),

It export books in file that named books.xls and the probleme here is that it export them as "Book objects" in the first square (A1)

what should i do if i want to make every "book_attribute" in separate square and every "book" in separate line ?

user9876 · Accepted Answer · 2013-04-12 14:20:58Z

6

You're sending something called "books.xls", and correctly signalling that it's an Excel spreadsheet... but it isn't. You've completely missed the step of actually creating an Excel spreadsheet containing your data (which is probably 80% of the work here).

Try searching the web for how to create an excel spreadsheet in Python.

answered Apr 12, 2013 at 14:20

user9876

11.1k7 gold badges47 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ryu_hayabusa · Accepted Answer · 2013-08-26 07:41:53Z

3

Working Example using tablib, an excellent tabular dataset library.

    from django.http import HttpResponse
    import tablib

            headers = ('Book', 'Author')
            data = []
            data = tablib.Dataset(*data, headers=headers)
            books = Book.objects.all()
            for book in books:
                data.append((book.book_name, book.author))
            response = HttpResponse(data.xls, content_type='application/vnd.ms-excel;charset=utf-8')
            response['Content-Disposition'] = "attachment; filename=export.xls"

        return response

answered Aug 26, 2013 at 7:41

Ryu_hayabusa

3,7322 gold badges32 silver badges33 bronze badges

Comments

Jerry Meng · Accepted Answer · 2013-04-12 14:16:48Z

0

I think the problem is that you just simply pass a list of Book object to your export file.

books = Book.objects.all()

This line just returns a list of objects.

I think you may want to iterate each property of Book object. Replace each Book Object with a tuple with all field displayed. The hard way is to do something like books_list = map(lambda x: (x.book_id, x.book_name, ....all the field of Book), books)

And you pass the books_list to export file instead of books.

answered Apr 12, 2013 at 14:16

Jerry Meng

1,5063 gold badges21 silver badges40 bronze badges

Comments

catherine · Accepted Answer · 2013-04-12 15:06:56Z

0

def export_excel(request):
    books = Book.objects.all()
    response = HttpResponse(books , content_type='application/vnd.ms-excel;charset=utf-8')
    response['Content-Disposition'] = 'attachment; filename="books.xls"'

    writer = csv.writer(response)
    writer.writerow(['Book', 'Author'])
    for book in books:
        writer.writerow([book.book_name, book.author])

    return response

edited Apr 12, 2013 at 15:06

answered Apr 12, 2013 at 14:37

catherine

22.8k15 gold badges64 silver badges88 bronze badges

Comments

chfw · Accepted Answer · 2015-02-16 10:39:09Z

0

A solution using my plugin: django_excel

import djang_excel as excel
import pyexcel.ext.xls # in order to handle 'xls' format
# import pyexcel.ext.xlsx # in order to handle 'xlsx' format
# import pyexcel.ext.ods # in order to handle 'ods' format


def export_excel(self, request):
    # Book as django model, 'xls' as file format
    response = excel.make_response_from_a_table(Book, 'xls')
    response['Content-Disposition'] = 'attachment; filename="books.xls"' 
    return response

answered Feb 16, 2015 at 10:39

chfw

4,6122 gold badges32 silver badges32 bronze badges

Collectives™ on Stack Overflow

Django: export object from database to excel

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related