这篇技术贴讲怎样在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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
"""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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
{% 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
