Django 导出 Excel 代码的实例详解

这篇技术贴讲怎样在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

1. cd export_excel <--- Go into the export_excel folder

2. Scriptactive <--- activate env environment.

once activate the environment, in the windows would be liked as above.

1. pip install django.

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

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

4. 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'), 
    ] 
    
    

1. 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 
    

1. 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  
    
    

1. 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 = [] 
    
    

1. 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 
    
    

1. html

​
    {% extends "base.html" %} 
     
    {% block content %} 
     
    <div id="form_body" style="margin:20px;"> 
      <table class="table"> 
        <tr> 
          <td style="float: left"><a href="{% url 'home' %}" rel="external 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> 
      </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="javascript:void(0)" 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="{% 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 %} 
    

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

最后

以上就是彪壮小刺猬为你收集整理的Django 导出 Excel 代码的实例详解的全部内容，希望文章能够帮你解决Django 导出 Excel 代码的实例详解所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。