cari

Rumah >pembangunan bahagian belakang >Tutorial Python >如何使用Django导出Excel代码问题

如何使用Django导出Excel代码问题

巴扎黑
巴扎黑asal
2017-08-12 14:20:251911semak imbas

本篇文章主要介绍了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

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&#39;^$&#39;, views.home, name=&#39;home&#39;) 
Class-based views 
  1. Add an import: from other_app.views import Home 
  2. Add a URL to urlpatterns: url(r&#39;^$&#39;, Home.as_view(), name=&#39;home&#39;) 
Including another URLconf 
  1. Import the include() function: from django.conf.urls import url, include 
  2. Add a URL to urlpatterns: url(r&#39;^blog/&#39;, include(&#39;blog.urls&#39;)) 
""" 
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&#39;^admin/&#39;, admin.site.urls), 
  url(r&#39;^$&#39;, ListView.as_view(queryset = ExcelDemoData.objects.all().defer(&#39;id&#39;, &#39;part_number&#39;).order_by(&#39;id&#39;)[:100], template_name="part_num_list.html"), name = &#39;home&#39;), 
  url(r&#39;^demo/exportall/$&#39;, views.export_all_to_excel, name = &#39;export_all_to_excel&#39;), 
]

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 == &#39;GET&#39;: 
    demo_list = [] 
    try: 
      demo_row = ExcelDemoData.objects.get(pk = pk) 
    except ExcelDemoData.DoesNotExist: 
      messages.add_message(request, messages.ERROR, &#39;the Part Number: [%s] does not exist in database.&#39; % str(pk)) 
    else:   
      demo_list.append(demo_row) 
        
    response = HttpResponse(content_type=&#39;application/ms-excel&#39;) 
    response[&#39;Content-Disposition&#39;] = &#39;attachment; filename=ExcelDemoData_%s.xlsx&#39; % 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 == &#39;GET&#39;: 
    if &#39;store_modi_id&#39; in request.session: 
       messages.add_message(request, messages.ERROR, &#39;The Part Number have been lost, please re-search them.&#39;) 
       return HttpResponseRedirect(reverse(&#39;home&#39;)) 
       
    demo_list = [] 
    pn_id_list = request.session[&#39;searched_sb_list&#39;] #<--- 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, &#39;the Part Number does not exist in database.&#39; ) 
      else:   
        demo_list.append(demo_row) 
        
    response = HttpResponse(content_type=&#39;application/ms-excel&#39;) 
    response[&#39;Content-Disposition&#39;] = &#39;attachment; filename=ExcelDemoData_%s.xlsx&#39; % 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) + &#39; Part Number: &#39; + 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 ------ 
      &#39;demo_qty&#39;: forms.NumberInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_part_number&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_nonfio_sku&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_desc&#39;: forms.TextInput(attrs = {&#39;class&#39;: SELECT_CSS}), 
      &#39;demo_cost&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_ex_cost&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_msrp&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_ex_msrp&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
    } 
     
    labels = { 
      # ----- Smart ------ 
      &#39;demo_qty&#39;: _(DEMO_TXT + &#39; &#39; + QTY_TXT), 
      &#39;demo_part_number&#39;: _(DEMO_TXT + &#39; &#39; + PART_NUM_TXT), 
      &#39;demo_nonfio_sku&#39;: _(DEMO_TXT + &#39; &#39; + NONFIO_SKU), 
      &#39;demo_desc&#39;: _(DEMO_TXT + &#39; &#39; + DESC_TXT), 
      &#39;demo_cost&#39;: _(DEMO_TXT + &#39; &#39; + COST_TXT), 
      &#39;demo_ex_cost&#39;: _(DEMO_TXT + &#39; &#39; + EX_COST_TXT), 
      &#39;demo_msrp&#39;: _(DEMO_TXT + &#39; &#39; + MSRP_TXT), 
      &#39;demo_ex_msrp&#39;: _(DEMO_TXT + &#39; &#39; + 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(&#39;Smart Buy&#39;) 
  worksheet_b = workbook.add_worksheet(&#39;Part Number List&#39;) 
   
  # excel styles 
  title = workbook.add_format({ 
    &#39;bold&#39;: True, 
    &#39;font_size&#39;: 14, 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;vcenter&#39; 
  }) 
  header = workbook.add_format({ 
    &#39;bg_color&#39;: &#39;#F7F7F7&#39;, 
    &#39;color&#39;: &#39;black&#39;, 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;border&#39;: 1 
  }) 
   
  bold_header = workbook.add_format({ 
    &#39;bold&#39;: True, 
    &#39;bg_color&#39;: &#39;#F7F7F7&#39;, 
    &#39;color&#39;: &#39;black&#39;, 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;border&#39;: 1 
  }) 
   
  cell = workbook.add_format({ 
    &#39;align&#39;: &#39;left&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;text_wrap&#39;: True, 
    &#39;border&#39;: 1 
  }) 
   
  bold_cell = workbook.add_format({ 
    &#39;bold&#39;: True, 
    &#39;align&#39;: &#39;left&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;text_wrap&#39;: True, 
    &#39;border&#39;: 1 
  }) 
   
  cell_center = workbook.add_format({ 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;border&#39;: 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, &#39;&#39;, 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 = &#39;&#39; 
    worksheet_s.write(data_row, 1, sb.demo_qty, cell) 
    if not sb.demo_part_number: 
      sb.demo_part_number = &#39;&#39;  
    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 = &#39;&#39; 
    worksheet_s.write_string(data_row, 3, sb.demo_nonfio_sku, cell) 
    if not sb.demo_desc: 
      sb.demo_desc = &#39;&#39;  
    worksheet_s.write_string(data_row, 4, sb.demo_desc, cell) 
    if not sb.demo_cost:  
      sb.demo_cost = &#39;&#39; 
    worksheet_s.write(data_row, 5, sb.demo_cost, cell) 
    if not sb.demo_ex_cost:  
      sb.demo_ex_cost = &#39;&#39; 
    worksheet_s.write(data_row, 6, sb.demo_ex_cost, cell) 
    if not sb.demo_msrp:  
      sb.demo_msrp = &#39;&#39; 
    worksheet_s.write(data_row, 7, sb.demo_msrp, bold_cell) 
    if not sb.demo_ex_msrp: 
       sb.demo_ex_msrp = &#39;&#39; 
    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(&#39;A:A&#39;, item_name_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;B:B&#39;, qty_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;C:C&#39;, part_num_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;D:D&#39;, nonfio_sku_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;E:E&#39;, desc_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;F:F&#39;, cost_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;G:G&#39;, ex_cost_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;H:H&#39;, msrp_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;I:I&#39;, ex_msrp_col_width) 
     
    # for each smart buy data end <<<------ 
     
    # change column widths 
    worksheet_s.set_column(&#39;A:A&#39;, item_name_col_width) 
    worksheet_s.set_column(&#39;B:B&#39;, qty_col_width) 
    worksheet_s.set_column(&#39;C:C&#39;, part_num_col_width) 
    worksheet_b.set_column(&#39;A:A&#39;, part_num_col_width) 
    worksheet_s.set_column(&#39;D:D&#39;, nonfio_sku_col_width) 
    worksheet_s.set_column(&#39;E:E&#39;, desc_col_width) 
    worksheet_s.set_column(&#39;F:F&#39;, cost_col_width) 
    worksheet_s.set_column(&#39;G:G&#39;, ex_cost_col_width) 
    worksheet_s.set_column(&#39;H:H&#39;, msrp_col_width) 
    worksheet_s.set_column(&#39;I:I&#39;, ex_msrp_col_width) 
     
  # close workbook 
  workbook.close() 
  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 &#39;home&#39; %}" 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=&#39;fa fa-circle-o-notch fa-spin&#39;></i> Processing Export..." id="export_all_excel_a" onclick="javascript: getExportExcels(&#39;all&#39;);">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>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(&#39;{{ s.pk }}&#39;);" data-loading-text="<i class=&#39;fa fa-circle-o-notch fa-spin&#39;></i> Processing Export..." id="sgl_export_{{ s.pk }}" >Export to Excel</a> 
        </td> 
        <td style="text-align:right"> 
          <a href="{% url &#39;demo_edit_id&#39; pk=s.pk%}" rel="external nofollow" class="btn btn-primary" role="button" value="{{ s.pk }}">Edit</a> 
        </td> 
      </tr> 
      {% endfor %} 
    </tbody> 
     
  <p></p> 
  </table> 
   
</p> 
 
<script type="text/javascript"> 
function getExportExcels(pn_id){ 
  if(pn_id == &#39;all&#39;){ 
    var post_url = &#39;/demo/exportall/&#39;; 
    location.replace(post_url); 
  } 
  else{ 
    var post_url = &#39;/demo/export/&#39;; 
    location.replace(post_url + pn_id); 
  } 
}  
 
</script> 
{% endblock content %}

Atas ialah kandungan terperinci 如何使用Django导出Excel代码问题. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

Kenyataan：
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Artikel sebelumnya：简述Python如何调用系统底层API播放wav文件Artikel seterusnya：利用库fractions模块让Python支持分数类型

Artikel berkaitan

Lihat lagi