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. Scriptactive <--- 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,'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,'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,null=True) demo_desc = models.CharField(max_length = 500,null=True) demo_cost = models.DecimalField(max_digits=15,decimal_places=2,null=True) demo_ex_cost = models.DecimalField(max_digits=15,null=True) demo_msrp = models.DecimalField(max_digits=15,null=True) demo_ex_msrp = models.DecimalField(max_digits=15,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','valign': 'top','border': 1 }) bold_header = workbook.add_format({ 'bold': True,'bg_color': '#F7F7F7','border': 1 }) cell = workbook.add_format({ 'align': 'left','text_wrap': True,'border': 1 }) bold_cell = workbook.add_format({ 'bold': True,'align': 'left','border': 1 }) cell_center = workbook.add_format({ 'align': 'center','border': 1 }) # write header,this is row 1 in excel worksheet_s.write(0,_(HEADER_ITEM_TXT),header) worksheet_s.write(0,1,_(QTY_TXT),2,_(PART_NUM_TXT),3,_(NONFIO_SKU),4,_(DESC_TXT),5,_(COST_TXT),6,_(EX_COST_TXT),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,_(SMART_BUY_TXT),cell) if not sb.demo_qty: sb.demo_qty = '' worksheet_s.write(data_row,sb.demo_qty,cell) if not sb.demo_part_number: sb.demo_part_number = '' worksheet_s.write_string(data_row,sb.demo_part_number,bold_cell) worksheet_b.write_string(second_sheet_data_row,cell) second_sheet_data_row += 1 if not sb.demo_nonfio_sku: sb.demo_nonfio_sku = '' worksheet_s.write_string(data_row,sb.demo_nonfio_sku,cell) if not sb.demo_desc: sb.demo_desc = '' worksheet_s.write_string(data_row,sb.demo_desc,cell) if not sb.demo_cost: sb.demo_cost = '' worksheet_s.write(data_row,sb.demo_cost,cell) if not sb.demo_ex_cost: sb.demo_ex_cost = '' worksheet_s.write(data_row,sb.demo_ex_cost,cell) if not sb.demo_msrp: sb.demo_msrp = '' worksheet_s.write(data_row,sb.demo_msrp,bold_cell) if not sb.demo_ex_msrp: sb.demo_ex_msrp = '' worksheet_s.write(data_row,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="{% 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 %} 以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持编程小技巧。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |