python导出excel代码_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 

2. cd export_excel  

3. Script\active

once activate the environment, in the windows would be liked as above.

4. pip install django.

5. pip install django-bootstrap3.

6. pip install xlsxwriter. 

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'] #

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 = ExcelDemoData

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 %}

Back	{% if export_all %} Export All to Excel {% endif %}

QtyPart NumberNonFIO SKUDescriptionCostEx.CostMSRPex.MSRP

{% for s in demo_list %}

Demo{% if s.demo_qty %}{{ s.demo_qty }}{% endif %} {% if s.demo_part_number %}{{ s.demo_part_number }}{% endif %} {% if s.demo_nonfio_sku %}{{ s.demo_nonfio_sku }}{% endif %} {% if s.demo_desc %}{{ s.demo_desc }}{% endif %} {% if s.demo_cost %}{{ s.demo_cost }}{% endif %} {% if s.demo_ex_cost %}{{ s.demo_ex_cost }}{% endif %} {% if s.demo_msrp %}{{ s.demo_msrp }}{% endif %} {% if s.demo_ex_msrp %}{{ s.demo_ex_msrp }}{% endif %}

Export to Excel

Edit

{% endfor %}

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);

}

}

{% endblock content %}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。