Django 导出 Excel 代码的实例详解

时间：2022-04-28 19:36:54

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

?
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

标签：

相关文章