Community Learn Tools Library Leisure
search
English
Home > Backend Development > Python Tutorial > How to use Django to export Excel code issues

How to use Django to export Excel code issues

巴扎黑
Release： 2017-08-12 14:20:25
Original
1913 people have browsed it

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&#39;^$&#39;, views.home, name=&#39;home&#39;) 
Class-based views 
  1. Add an import: from other_app.views import Home 
  2. Add a URL to urlpatterns: url(r&#39;^$&#39;, Home.as_view(), name=&#39;home&#39;) 
Including another URLconf 
  1. Import the include() function: from django.conf.urls import url, include 
  2. Add a URL to urlpatterns: url(r&#39;^blog/&#39;, include(&#39;blog.urls&#39;)) 
""" 
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&#39;^admin/&#39;, admin.site.urls), 
  url(r&#39;^$&#39;, ListView.as_view(queryset = ExcelDemoData.objects.all().defer(&#39;id&#39;, &#39;part_number&#39;).order_by(&#39;id&#39;)[:100], template_name="part_num_list.html"), name = &#39;home&#39;), 
  url(r&#39;^demo/exportall/$&#39;, views.export_all_to_excel, name = &#39;export_all_to_excel&#39;), 
]
Copy after login

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 == &#39;GET&#39;: 
    demo_list = [] 
    try: 
      demo_row = ExcelDemoData.objects.get(pk = pk) 
    except ExcelDemoData.DoesNotExist: 
      messages.add_message(request, messages.ERROR, &#39;the Part Number: [%s] does not exist in database.&#39; % str(pk)) 
    else:   
      demo_list.append(demo_row) 
        
    response = HttpResponse(content_type=&#39;application/ms-excel&#39;) 
    response[&#39;Content-Disposition&#39;] = &#39;attachment; filename=ExcelDemoData_%s.xlsx&#39; % 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 == &#39;GET&#39;: 
    if &#39;store_modi_id&#39; in request.session: 
       messages.add_message(request, messages.ERROR, &#39;The Part Number have been lost, please re-search them.&#39;) 
       return HttpResponseRedirect(reverse(&#39;home&#39;)) 
       
    demo_list = [] 
    pn_id_list = request.session[&#39;searched_sb_list&#39;] #<--- 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, &#39;the Part Number does not exist in database.&#39; ) 
      else:   
        demo_list.append(demo_row) 
        
    response = HttpResponse(content_type=&#39;application/ms-excel&#39;) 
    response[&#39;Content-Disposition&#39;] = &#39;attachment; filename=ExcelDemoData_%s.xlsx&#39; % attr.get_current_timestamp()  
    xlsx_data = WriteToExcel(demo_list) 
    response.write(xlsx_data) 
    return response
Copy after login

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) + &#39; Part Number: &#39; + self.demo_part_number
Copy after login

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 ------ 
      &#39;demo_qty&#39;: forms.NumberInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_part_number&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_nonfio_sku&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_desc&#39;: forms.TextInput(attrs = {&#39;class&#39;: SELECT_CSS}), 
      &#39;demo_cost&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_ex_cost&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_msrp&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
      &#39;demo_ex_msrp&#39;: forms.TextInput(attrs = {&#39;class&#39;: INPUT_CSS}), 
    } 
     
    labels = { 
      # ----- Smart ------ 
      &#39;demo_qty&#39;: _(DEMO_TXT + &#39; &#39; + QTY_TXT), 
      &#39;demo_part_number&#39;: _(DEMO_TXT + &#39; &#39; + PART_NUM_TXT), 
      &#39;demo_nonfio_sku&#39;: _(DEMO_TXT + &#39; &#39; + NONFIO_SKU), 
      &#39;demo_desc&#39;: _(DEMO_TXT + &#39; &#39; + DESC_TXT), 
      &#39;demo_cost&#39;: _(DEMO_TXT + &#39; &#39; + COST_TXT), 
      &#39;demo_ex_cost&#39;: _(DEMO_TXT + &#39; &#39; + EX_COST_TXT), 
      &#39;demo_msrp&#39;: _(DEMO_TXT + &#39; &#39; + MSRP_TXT), 
      &#39;demo_ex_msrp&#39;: _(DEMO_TXT + &#39; &#39; + EX_MSRP_TXT), 
       
 
    } 
     
    exclude = []
Copy after login

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(&#39;Smart Buy&#39;) 
  worksheet_b = workbook.add_worksheet(&#39;Part Number List&#39;) 
   
  # excel styles 
  title = workbook.add_format({ 
    &#39;bold&#39;: True, 
    &#39;font_size&#39;: 14, 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;vcenter&#39; 
  }) 
  header = workbook.add_format({ 
    &#39;bg_color&#39;: &#39;#F7F7F7&#39;, 
    &#39;color&#39;: &#39;black&#39;, 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;border&#39;: 1 
  }) 
   
  bold_header = workbook.add_format({ 
    &#39;bold&#39;: True, 
    &#39;bg_color&#39;: &#39;#F7F7F7&#39;, 
    &#39;color&#39;: &#39;black&#39;, 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;border&#39;: 1 
  }) 
   
  cell = workbook.add_format({ 
    &#39;align&#39;: &#39;left&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;text_wrap&#39;: True, 
    &#39;border&#39;: 1 
  }) 
   
  bold_cell = workbook.add_format({ 
    &#39;bold&#39;: True, 
    &#39;align&#39;: &#39;left&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;text_wrap&#39;: True, 
    &#39;border&#39;: 1 
  }) 
   
  cell_center = workbook.add_format({ 
    &#39;align&#39;: &#39;center&#39;, 
    &#39;valign&#39;: &#39;top&#39;, 
    &#39;border&#39;: 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, &#39;&#39;, 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 = &#39;&#39; 
    worksheet_s.write(data_row, 1, sb.demo_qty, cell) 
    if not sb.demo_part_number: 
      sb.demo_part_number = &#39;&#39;  
    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 = &#39;&#39; 
    worksheet_s.write_string(data_row, 3, sb.demo_nonfio_sku, cell) 
    if not sb.demo_desc: 
      sb.demo_desc = &#39;&#39;  
    worksheet_s.write_string(data_row, 4, sb.demo_desc, cell) 
    if not sb.demo_cost:  
      sb.demo_cost = &#39;&#39; 
    worksheet_s.write(data_row, 5, sb.demo_cost, cell) 
    if not sb.demo_ex_cost:  
      sb.demo_ex_cost = &#39;&#39; 
    worksheet_s.write(data_row, 6, sb.demo_ex_cost, cell) 
    if not sb.demo_msrp:  
      sb.demo_msrp = &#39;&#39; 
    worksheet_s.write(data_row, 7, sb.demo_msrp, bold_cell) 
    if not sb.demo_ex_msrp: 
       sb.demo_ex_msrp = &#39;&#39; 
    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(&#39;A:A&#39;, item_name_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;B:B&#39;, qty_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;C:C&#39;, part_num_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;D:D&#39;, nonfio_sku_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;E:E&#39;, desc_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;F:F&#39;, cost_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;G:G&#39;, ex_cost_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;H:H&#39;, msrp_col_width) 
    if sb.demo_qty: worksheet_s.set_column(&#39;I:I&#39;, ex_msrp_col_width) 
     
    # for each smart buy data end <<<------ 
     
    # change column widths 
    worksheet_s.set_column(&#39;A:A&#39;, item_name_col_width) 
    worksheet_s.set_column(&#39;B:B&#39;, qty_col_width) 
    worksheet_s.set_column(&#39;C:C&#39;, part_num_col_width) 
    worksheet_b.set_column(&#39;A:A&#39;, part_num_col_width) 
    worksheet_s.set_column(&#39;D:D&#39;, nonfio_sku_col_width) 
    worksheet_s.set_column(&#39;E:E&#39;, desc_col_width) 
    worksheet_s.set_column(&#39;F:F&#39;, cost_col_width) 
    worksheet_s.set_column(&#39;G:G&#39;, ex_cost_col_width) 
    worksheet_s.set_column(&#39;H:H&#39;, msrp_col_width) 
    worksheet_s.set_column(&#39;I:I&#39;, ex_msrp_col_width) 
     
  # close workbook 
  workbook.close() 
  xlsx_data = output.getvalue() 
  return xlsx_data
Copy after login

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 &#39;home&#39; %}" 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=&#39;fa fa-circle-o-notch fa-spin&#39;></i> Processing Export..." id="export_all_excel_a" onclick="javascript: getExportExcels(&#39;all&#39;);">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(&#39;{{ s.pk }}&#39;);" data-loading-text="<i class=&#39;fa fa-circle-o-notch fa-spin&#39;></i> Processing Export..." id="sgl_export_{{ s.pk }}" >Export to Excel</a> 
        </td> 
        <td style="text-align:right"> 
          <a href="{% url &#39;demo_edit_id&#39; 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 == &#39;all&#39;){ 
    var post_url = &#39;/demo/exportall/&#39;; 
    location.replace(post_url); 
  } 
  else{ 
    var post_url = &#39;/demo/export/&#39;; 
    location.replace(post_url + pn_id); 
  } 
}  
 
</script> 
{% endblock content %}
Copy after login

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!

Related labels：
django excel Export
source：php.cn
Previous article：Briefly describe how Python calls the system's underlying API to play wav files Next article：Use the library fractions module to let Python support fraction types
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
How to pass multiple lists from ajax to Django I have a problem, I want to get all the data from the list, so I want to loop through each...
From 2024-04-03 23:20:06
0
1
362
How to effectively apply custom "css" and "js" files to all admins in an application? I have custom css and js files, admin.py and overridden base.html located in templates/adm...
From 2024-04-03 20:54:06
0
1
400
Highcharts in Django: Display JSON data instead of charts on a web page So I'm trying to make a candlestick chart using Highcharts in Django, but I can't display ...
From 2024-04-02 10:06:09
0
1
291
Solve the problem of operation error when pythonanywhere connects to MySQL database I have finished my django project and I want to test it on PythonAnywhere, everything was ...
From 2024-03-31 22:43:19
0
1
418
Deploy Django using mysql database on a personal server I'm trying to deploy my Django project on Linode. The MySQL database I use during developm...
From 2024-03-31 14:44:26
0
1
371
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template