Django 导出 Excel 代码的实例详解

作者:chenjinyu_china 时间:2021-04-01 05:56:41 

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

Django 导出 Excel 代码的实例详解

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

3. Script\active <--- activate env environment.

Django 导出 Excel 代码的实例详解

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="{% 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 %}

来源:http://blog.csdn.net/chenjinyu_tang/article/details/52156689

标签:Django,导出,Excel
0
投稿

猜你喜欢

  • Yii2 rbac权限控制之菜单menu实例教程

    2023-11-14 10:41:19
  • 简述python Scrapy框架

    2022-07-13 06:19:30
  • ASP用csDrawGraph组件制作饼图、柱状图

    2008-04-25 22:58:00
  • python 删除列表里所有空格项的方法总结

    2023-01-28 19:37:24
  • python定时复制远程文件夹中所有文件

    2023-08-17 17:55:32
  • javascript实现禁止复制网页内容汇总

    2024-04-16 09:31:48
  • PHP中集成PayPal标准支付的实现方法分享

    2023-09-06 09:11:22
  • asp如何实现人民币的大写转换?

    2010-05-24 18:27:00
  • Python使用matplotlib模块绘制图像并设置标题与坐标轴等信息示例

    2022-11-08 14:50:22
  • python分析apache访问日志脚本分享

    2021-08-12 04:20:09
  • XMLHTTP获取web造访头信息和网页代码

    2010-04-01 14:37:00
  • python变量赋值方法(可变与不可变)

    2021-02-14 08:51:55
  • sql使用cast进行数据类型转换示例

    2024-01-14 07:42:11
  • JavaScript函数参数使用带参数名的方式赋值传入的方法

    2024-04-30 09:51:52
  • JavaScript中的一些实用小技巧总结

    2024-04-10 10:45:15
  • 用户分类浅谈

    2009-09-27 12:14:00
  • 判断所使用的浏览器不仅仅是电脑浏览器代码整理

    2023-12-13 10:13:21
  • 使用Python Fast API发布API服务的过程详解

    2022-05-19 20:03:44
  • js Canvas实现的日历时钟案例分享

    2024-04-22 13:08:26
  • Python双精度浮点数运算并分行显示操作示例

    2024-01-01 21:33:10
  • asp之家 网络编程 m.aspxhome.com