How to use Django to export Excel code problems

This article mainly introduces the Django export Excel code example of the detailed, small series feel very good, and now share to everyone, but also for everyone to do a reference. Let's take a look at it with a little knitting.

This technique describes how to export excel in a Django framework, with the intention of using AJAX post data in the past, but not finding it, so we used the Get method instead. If there is only one ID (PK) that is easy to get access to the corresponding view section, but if it is a number of IDs, I use the session, do you have any other good examples? Hope to discuss it together.

Python3.5 Django 1.10, sqlite3, Windows 10

1. VIRTUALENV Export_excel <---Create a virtualenv for Django

2. CD Export_excel <---Go into the Export_excel folder

3. script\active <---Activate env environment.

Once activate the environment, in the windows would is liked as above.

4. Pip install Django.

5. Pip install DJANGO-BOOTSTRAP3. <--bootstrap3 for Django.

6. Pip install Xlsxwriter. <--this uses for Excel export. (with Xlsxwriter this Python library)

1. urls.py

"" "Export_excel URL Configuration the ' urlpatterns ' list routes URLs to views. For more information see:https://docs.djangoproject.com/en/1.10/topics/http/urls/examples:function views 1. ADD an import:from My_app import views 2. Add a URL to Urlpatterns:url (R ' ^$ ', views.home, Name= ' home ") class-based views 1. ADD an import:from other_app.views import Home 2. Add a URL to Urlpatterns:url (R ' ^$ ', Home.as_view (), name= ' Home ') including another URLconf 1. Import the Include () function:from django.conf.urls import URL, include 2. Add a URL to Urlpatterns:url (R ' ^blog/', include (' Blog.urls ')) "" "from Django.conf.urls import URL from Django.contrib im Port admin from django.views.generic import ListView from. Models import Exceldemodata from. Import views urlpatterns = [url (r ' ^admin/', admin.site.urls), url (r ' ^$ ', listview.as_view (Queryset = Exceldemodata. Objects.all (). Defer (' id ', ' part_number '). order_by (' id ') [: +], template_name= "part_num_list.html"), Name = ' home '), url (r ' ^demo/exportall/$ ', views.export_all_to_excel, name = ' Export_all_to_excel '),] 

2. views.py

From django.http import Jsonresponse, Http404 from django.shortcuts import Render, get_object_or_404, Render_to_response From django.http import httpresponseredirect from django.contrib import messages from django.core.urlresolvers import Rev Erse from django.http import HttpResponse from. Models import exceldemodata from. Forms import Exceldemoform from. Excel_ Utils import Writetoexcel from. Import Attrs_override as attr def export_sig_to_excel (Request, PK): if Request.method = = ' GET ': demo_list = [ ] Try:demo_row = ExcelDemoData.objects.get (pk = pk) except ExcelDemoData.DoesNotExist:messages.add_m Essage (Request, messages.             ERROR, ' The part number: [%s] does not exist in database. '% str (PK)) Else:demo_list.append (Demo_row) Response = HttpResponse (content_type= ' application/ms-excel ') response[' content-disposition '] = ' attachment; Filename=exceldemodata_%s.xlsx '% demo_row.demo_row_part_number xlsx_data = Writetoexcel(demo_list) Response.Write (Xlsx_data) return response def Export_all_to_excel (Request): if req Uest.method = = ' GET ': If ' store_modi_id ' in request.session:messages.add_message (request, messages.        ERROR, ' The part number has been lost, please re-search them. ') Return Httpresponseredirect (Reverse (' home)) demo_list = [] pn_id_list = request.session[' searched_sb_list '     ] #<---The session is created when a list page is created.          For ID in pn_id_list:try:demo_row = ExcelDemoData.objects.get (pk = ID) except smartbuy.doesnotexist: Messages.add_message (Request, messages.       ERROR, ' The part number does not exist in database. ') Else:demo_list.append (demo_row) response = HttpResponse (content_type= ' Application/ms-excel ') re sponse[' content-disposition '] = ' attachment; Filename=exceldemodata_%s.xlsx '% attr.get_current_timestamp () Xlsx_data = Writetoexcel (demo_list) Response.Write (Xlsx_data) return response 

3. models.py

from django.db Import models # Create your models here. Class Exceldemodata (models. Model): #----This is exceldemodata scope----Demo_qty = models. Positiveintegerfield (blank = True, null=true) Demo_part_number = models.   Charfield (max_length = $, blank = True, null=true) # Smart Buy Part number cannot is empty. Demo_nonfio_sku = models. Charfield (max_length = $, blank = True, null=true) Demo_desc = models. Charfield (max_length = $, blank = True, null=true) Demo_cost = models. Decimalfield (max_digits=15, decimal_places=2, blank = True, null=true) Demo_ex_cost = models. Decimalfield (max_digits=15, decimal_places=2, blank = True, null=true) DEMO_MSRP = models. Decimalfield (max_digits=15, decimal_places=2, blank = True, null=true) DEMO_EX_MSRP = models. Decimalfield (max_digits=15, decimal_places=2, blank = True, null=true) def __str__ (self): return str (SELF.PK) + ' Part number: ' + self.demo_part_number 

4. forms.py

From the Django Import forms from Django.utils.translation import Ugettext_lazy as _ from. Models, import Exceldemodata from. Attrs_override Import * Class Exceldemoform (forms. Modelform): Class Meta:model = <span style= "font-family:arial, Helvetica, Sans-serif;" >ExcelDemoData</span> widgets = {#-----Smart------' demo_qty ': forms. Numberinput (attrs = {' class ': Input_css}), ' Demo_part_number ': forms. TextInput (attrs = {' class ': Input_css}), ' Demo_nonfio_sku ': forms. TextInput (attrs = {' class ': Input_css}), ' Demo_desc ': forms. TextInput (attrs = {' class ': Select_css}), ' Demo_cost ': forms. TextInput (attrs = {' class ': Input_css}), ' Demo_ex_cost ': forms. TextInput (attrs = {' class ': Input_css}), ' DEMO_MSRP ': forms. TextInput (attrs = {' class ': Input_css}), ' DEMO_EX_MSRP ': forms.  TextInput (attrs = {' class ': Input_css}),} labels = {#-----Smart------' Demo_qty ': _ (Demo_txt + ' + qty_txT), ' Demo_part_number ': _ (demo_txt + ' + part_num_txt), ' Demo_nonfio_sku ': _ (demo_txt + ' + nonfio_sku), ' Demo_desc ': _ (demo_txt + ' + desc_txt), ' Demo_cost ': _ (demo_txt + ' + cost_txt), ' Demo_ex_cost ': _ (D Emo_txt + "+ ex_cost_txt), ' DEMO_MSRP ': _ (demo_txt + ' + msrp_txt), ' DEMO_EX_MSRP ': _ (demo_txt + ' + EX _msrp_txt),} exclude = []

5. excel_utils.py

Try:from Bytesio import Bytesio except importerror:from io import bytesio import xlsxwriter from Django.utils.tra Nslation Import Ugettext as _ from. Models import exceldemodata from. Attrs_override Import * def writetoexcel (demo_ List): output = Bytesio () workbook = Xlsxwriter. Workbook (output) worksheet_s = Workbook.add_worksheet (' Smart Buy ') Worksheet_b = Workbook.add_worksheet (' Part number L     ist ') # Excel Styles title = Workbook.add_format ({' bold ': True, ' font_size ': +, ' align ': ' Center ', ' valign ': ' vcenter '} Header = Workbook.add_format ({' Bg_color ': ' #F7F7F7 ', ' Color ': ' Black ', ' align ': ' Center ', ' valign ': ' Top ', ' Border ': 1}) Bold_header = Workbook.add_format ({' bold ': True, ' Bg_col or ': ' #F7F7F7 ', ' Color ': ' Black ', ' Align ': ' Center ', ' valign ': ' Top ', ' Border ': 1}) cell = Workboo K.add_format ({' Align ': ' Left ', ' valign ': ' Top ', ' text_wrap ': True, ' border': 1}) Bold_cell = Workbook.add_format ({' bold ': True, ' align ': ' Left ', ' valign ': ' Top ', ' Text_wra P ': True, ' Border ': 1}) Cell_center = Workbook.add_format ({' Align ': ' Center ', ' valign ': ' Top ', ' B Order ': 1}) # Write header, this was row 1 in Excel worksheet_s.write (0, 0, _ (header_item_txt), header) Workshe Et_s.write (0, 1, _ (qty_txt), header) Worksheet_s.write (0, 2, _ (Part_num_txt), header) Worksheet_s.write (0, 3, _ (Nonfio _sku), header) Worksheet_s.write (0, 4, _ (Desc_txt), header) Worksheet_s.write (0, 5, _ (Cost_txt), header) worksheet_s . Write (0, 6, _ (Ex_cost_txt), header) Worksheet_s.write (0, 7, _ (msrp_txt), Bold_header) worksheet_s.write (0, 8, _ (Ex_ms Rp_txt), header) # column widths item_name_col_width = Qty_col_width = Ten Part_num_col_width = Nonfio_ Sku_col_width = Desc_col_width = Cost_col_width = Ten ex_cost_col_width= msrp_col_width = ten Ex_msrp_col _width = ten # Add DATA into the table Data_row = 1 Second_sheet_data_row = 0 for SB in Demo_list:if Data_row are not 1: For index in range (9): Worksheet_s.write (Data_row, index, ', cell) Data_row + = 1 # This is for SMA        Rtbuy row, row 2 in Excel worksheet_s.write_string (Data_row, 0, _ (smart_buy_txt), cell) if not sb.demo_qty: Sb.demo_qty = ' Worksheet_s.write (data_row, 1, sb.demo_qty, cell) if not Sb.demo_part_number:sb.demo_part _number = ' Worksheet_s.write_string (data_row, 2, Sb.demo_part_number, Bold_cell) worksheet_b.write_string (secon D_sheet_data_row, 0, Sb.demo_part_number, cell) Second_sheet_data_row + = 1 if not sb.demo_nonfio_sku:sb.de Mo_nonfio_sku = ' Worksheet_s.write_string (Data_row, 3, Sb.demo_nonfio_sku, cell) if not SB.DEMO_DESC:SB.D Emo_desc = ' worksheet_s.write_string (Data_row, 4, Sb.demo_desc, cell) if not sb.demo_cost:sb.demo_cost = "Worksheet_s.wri"Te (Data_row, 5, sb.demo_cost, cell) if not sb.demo_ex_cost:sb.demo_ex_cost = ' Worksheet_s.write (data_row , 6, Sb.demo_ex_cost, cell) if not SB.DEMO_MSRP:SB.DEMO_MSRP = ' Worksheet_s.write (data_row, 7, Sb.demo_m SRP, Bold_cell) if not SB.DEMO_EX_MSRP:SB.DEMO_EX_MSRP = ' Worksheet_s.write (data_row, 8, SB.DEMO_EX_MSRP , cell) # for each smart buy data end <<<------# Change column widths if Sb.demo_qty:wo Rksheet_s.set_column (' a:a ', item_name_col_width) if Sb.demo_qty:worksheet_s.set_column (' b:b ', qty_col_width) if s B.demo_qty:worksheet_s.set_column (' C:c ', part_num_col_width) if Sb.demo_qty:worksheet_s.set_column (' D:d ', Nonfio_ Sku_col_width) if Sb.demo_qty:worksheet_s.set_column (' E:e ', desc_col_width) if Sb.demo_qty:worksheet_s.set_colum N (' f:f ', cost_col_width) if Sb.demo_qty:worksheet_s.set_column (' G:g ', ex_cost_col_width) if Sb.demo_qty:workshee T_s.set_column (' H:h ', msrp_Col_width) if Sb.demo_qty:worksheet_s.set_column (' i:i ', ex_msrp_col_width) # for each smart buy data end &lt ;<<------# Change column widths Worksheet_s.set_column (' a:a ', item_name_col_width) worksheet_s.set _column (' b:b ', qty_col_width) worksheet_s.set_column (' C:c ', part_num_col_width) worksheet_b.set_column (' A:A ', part     _num_col_width) worksheet_s.set_column (' D:d ', nonfio_sku_col_width) worksheet_s.set_column (' E:E ', desc_col_width) Worksheet_s.set_column (' F:f ', cost_col_width) worksheet_s.set_column (' G:g ', ex_cost_col_width) worksheet_s.set _column (' h:h ', msrp_col_width) worksheet_s.set_column (' i:i ', ex_msrp_col_width) # Close Workbook Workbook.clo SE () xlsx_data = Output.getvalue () return Xlsx_data

6. html

{% extends "base.html"%} {% block content%} <p id= "Form_body" style= "margin:20px;" > <table class= "table" > <tr> <td style= "Float:left" ><a href= "{% url ' home '%}" rel= "E          xternal nofollow "class=" btn btn-primary "role=" button ">Back</a></td> <td style=" Float:right "> {% if Export_all%} <a href= "javascript:void (0)" rel= "external nofollow" rel= "external nofollow" class= " BTN btn-primary "role=" button "data-loading-text=" <i class= ' fa fa-circle-o-notch fa-spin ' ></i> processing Export ... "id=" export_all_excel_a "onclick=" javascript:getexportexcels (' all '); >export all to excel</a> {% endif%}</td> </tr> </table> </p> <p class       = "Table-responsive" > <table class= "Table table-bordered usr_def_tbl" > <thead class= "Thead-inverse" > <tr style= "FONT-SIZE:14PX; Text-align:center; " > <th> </th> <th>Qty</th> <th>part number</th> <th>nonfio sku</th> <th>descrip        tion</th> <th>Cost</th> <th>Ex.Cost</th> <th>MSRP</th> <th>ex.  Msrp</th> </tr> </thead> <tbody> {% for s in demo_list%} <!-- This is demo list sections-<tr> <td>Demo</td> <td>{% if S.demo_qty% }{{s.demo_qty}}{% endif%} </td> <td>{% if S.demo_part_number%}{{s.demo_part_number}}{% endif%} </td> <td>{% if S.demo_nonfio_sku%}{{s.demo_nonfio_sku}}{% endif%} </td> <td>{ % if S.demo_desc%}{{s.demo_desc}}{% endif%} </td> <td>{% if S.demo_cost%}{{s.demo_cost}}{% Endi  F%} </td> <td>{% if S.demo_ex_cost%}{{s.demo_ex_cost}}{% endif%} </td> <td>{% If s.deMO_MSRP%}{{s.demo_msrp}}{% endif%} </td> <td>{% if S.DEMO_EX_MSRP%}{{s.demo_ex_msrp}}{% endif% }</td> </tr> <tr> <td colspan= "7" ></td> <td style= "Text-alig N:right "> <a href=" javascript:void (0) "rel=" external nofollow "rel=" external nofollow "class=" btn Btn-prim ary Part_num_flag "role=" button "onclick=" Javascript:getexportexcels (' {{s.pk}} '); "data-loading-text=" <i class= ' FA fa-circle-o-notch fa-spin ' ></i> processing Export ... "id=" sgl_export_{{s.pk}} ">export to excel</a&         Gt </td> <td style= "Text-align:right" > <a href= "{% url ' demo_edit_id ' pk=s.pk%}" rel= "extern Al nofollow "class=" btn btn-primary "role=" button "value=" {{s.pk}} ">Edit</a> </td> </tr&       Gt {% endfor%} </tbody> <p></p> </table> </p> <script type= "Text/javascrip T "> FuNction Getexportexcels (pn_id) {if (pn_id = = ' all ') {var post_url = '/demo/exportall/';   Location.replace (Post_url);     } else{var post_url = '/demo/export/';   Location.replace (Post_url + pn_id); }} </script> {% Endblock content%}