Django 导出 Excel 代码的实例详解

框架(架构)  /  管理员 发布于 7年前   197

这篇技术贴讲怎样在Django的框架下导出Excel， 最开始打算用ajax post data 过去，但是发现不行，所以改用了get的方式。如果只有一个id(pk)那用get的方式很简单就可以访问对应的view section，但是如果是多个ids，我用的是session，大家还有其他好的例子吗？希望一起讨论。

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 be liked as above.

4. pip install django.

5. pip install django-bootstrap3. <-- bootstrap3 for django.

6. pip install xlsxwriter.  <-- this uses for excel export.(用xlsxwriter 这个Python 库)

1. urls.py

"""export_excel URL Configuration   The `urlpatterns` list routes URLs to views. For more information please 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 import 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')[:100], 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 reverse 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_message(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 request.method == 'GET':     if 'store_modi_id' in request.session:        messages.add_message(request, messages.ERROR, 'The Part Number have been lost, please re-search them.')        return HttpResponseRedirect(reverse('home'))demo_list = []     pn_id_list = request.session['searched_sb_list'] #<--- the session will be created when a list page was 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')     response['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 = 20, blank = True, null=True) # smart buy part number cannot be empty.   demo_nonfio_sku = models.CharField(max_length = 200, blank = True, null=True)   demo_desc = models.CharField(max_length = 500, 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 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': _(DEMO_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.translation 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 List')      # excel styles   title = workbook.add_format({     'bold': True,     'font_size': 14,     '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_color': '#F7F7F7',     'color': 'black',     'align': 'center',     'valign': 'top',     'border': 1   })      cell = workbook.add_format({     'align': 'left',     'valign': 'top',     'text_wrap': True,     'border': 1   })      bold_cell = workbook.add_format({     'bold': True,     'align': 'left',     'valign': 'top',     'text_wrap': True,     'border': 1   })      cell_center = workbook.add_format({     'align': 'center',     'valign': 'top',     'border': 1   })      # write header, this is row 1 in excel   worksheet_s.write(0, 0, _(HEADER_ITEM_TXT), header)   worksheet_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_MSRP_TXT), header)      # column widths    item_name_col_width = 20   qty_col_width = 10   part_num_col_width = 20   nonfio_sku_col_width = 30   desc_col_width = 80   cost_col_width = 10   ex_cost_col_width= 10   msrp_col_width = 10   ex_msrp_col_width = 10      # add data into the table   data_row = 1   second_sheet_data_row = 0   for sb in demo_list:          if data_row is not 1:       for index in range(9):         worksheet_s.write(data_row, index, '', cell)       data_row += 1          # this is for smartbuy 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(second_sheet_data_row, 0, sb.demo_part_number, cell)     second_sheet_data_row += 1     if not sb.demo_nonfio_sku:        sb.demo_nonfio_sku = ''     worksheet_s.write_string(data_row, 3, sb.demo_nonfio_sku, cell)     if not sb.demo_desc:       sb.demo_desc = ''      worksheet_s.write_string(data_row, 4, sb.demo_desc, cell)     if not sb.demo_cost:        sb.demo_cost = ''     worksheet_s.write(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_msrp, 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: worksheet_s.set_column('A:A', item_name_col_width)     if sb.demo_qty: worksheet_s.set_column('B:B', qty_col_width)     if sb.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_column('F:F', cost_col_width)     if sb.demo_qty: worksheet_s.set_column('G:G', ex_cost_col_width)     if sb.demo_qty: worksheet_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 <<<------          # 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.close()   xlsx_data = output.getvalue()   return xlsx_data 

6. html

{% extends "base.html" %}  {% block content %}  <div id="form_body" style="margin:20px;">   <table class="table">     <tr>       <td style="float: left"><a href="https:/article/{% url 'home' %}" rel="external nofollow" class="btn btn-primary" role="button">Back</a></td>       <td style="float: right">         {% if export_all %}       <a href="" 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>   </div>   <div 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>Description</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 }}{% endif %} </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-align:right">           <a href="" rel="external nofollow" rel="external nofollow" class="btn btn-primary 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>         </td>         <td style="text-align:right">           <a href="https:/article/{% url 'demo_edit_id' pk=s.pk%}" rel="external nofollow" class="btn btn-primary" role="button" value="{{ s.pk }}">Edit</a>         </td>       </tr>       {% endfor %}     </tbody>        <p></p>   </table>    </div>  <script type="text/javascript"> 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 %} 

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。