这里有新鲜出炉的Django教程,程序狗速度看过来!
Django是一个开放源代码的Web应用框架,由Python写成。采用了MVC的软件设计模式,即模型M,视图V和控制器C。它最初是被开发来用于管理劳伦斯出版集团旗下的一些以新闻内容为主的网站的,即是CMS(内容管理系统)软件。并于2005年7月在BSD许可证下发布。这套框架是以比利时的吉普赛爵士吉他手Django Reinhardt来命名的。
本篇文章主要介绍了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'^$', 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://www.phperz.com/article/17/1029/351063.html