Home >Backend Development >Python Tutorial >How to use Django to export Excel code issues
This article mainly introduces the detailed examples of Django exporting Excel code. The editor thinks it is quite good, so I will share it with you now and give it as a reference. Let’s follow the editor and take a look.
This technical post talks about how to export Excel under the Django framework. I initially planned to use ajax post data, but found that it didn’t work, so I switched to the get method. If there is only one id (pk), it is very easy to access the corresponding view section using the get method. But if there are multiple ids, I use session. Do you have any other good examples? Hope to discuss together.
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.(Use the xlsxwriter Python library)
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 response3. 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_number4. 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 %}The above is the detailed content of How to use Django to export Excel code issues. For more information, please follow other related articles on the PHP Chinese website!