Django 导出 Excel 代码的实例详解

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

 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 

 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 

 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 = ExcelDemoData 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 = [] 

 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 

 {% extends "base.html" %} {% block content %} 
 
 
 
Back
 {% if export_all %} Export All to Excel {% endif %}
  {% for s in demo_list %}  {% endfor %} 
 
 
 
 
Qty
Part Number
NonFIO SKU
Description
Cost
Ex.Cost
MSRP
ex.MSRP
 
Demo
{% if s.demo_qty %}{{ s.demo_qty }}{% endif %} 
{% if s.demo_part_number %}{{ s.demo_part_number }}{% endif %} 
{% if s.demo_nonfio_sku %}{{ s.demo_nonfio_sku }}{% endif %} 
{% if s.demo_desc %}{{ s.demo_desc }}{% endif %} 
{% if s.demo_cost %}{{ s.demo_cost }}{% endif %} 
{% if s.demo_ex_cost %}{{ s.demo_ex_cost }}{% endif %} 
{% if s.demo_msrp %}{{ s.demo_msrp }}{% endif %} 
{% if s.demo_ex_msrp %}{{ s.demo_ex_msrp }}{% endif %}
 
 Export to Excel
 Edit
 {% endblock content %}