本篇文章主要介绍了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 %}
<p 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>
</p>
<p 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>
</p>
<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 %}
以上就是如何使用django导出excel代码问题的详细内容。