How to use Django to export Excel code issues-Python Tutorial-php.cn

Home

Backend Development

Python Tutorial

How to use Django to export Excel code issues

巴扎黑

Aug 12, 2017 pm 02:20 PM

djangoexcelExport

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

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&#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;), 
]

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

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

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 = []

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

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

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!

Statement

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

Python and Time: Making the Most of Your Study TimeApr 14, 2025 am 12:02 AM

To maximize the efficiency of learning Python in a limited time, you can use Python's datetime, time, and schedule modules. 1. The datetime module is used to record and plan learning time. 2. The time module helps to set study and rest time. 3. The schedule module automatically arranges weekly learning tasks.

Python: Games, GUIs, and MoreApr 13, 2025 am 12:14 AM

Python excels in gaming and GUI development. 1) Game development uses Pygame, providing drawing, audio and other functions, which are suitable for creating 2D games. 2) GUI development can choose Tkinter or PyQt. Tkinter is simple and easy to use, PyQt has rich functions and is suitable for professional development.

Python vs. C : Applications and Use Cases ComparedApr 12, 2025 am 12:01 AM

Python is suitable for data science, web development and automation tasks, while C is suitable for system programming, game development and embedded systems. Python is known for its simplicity and powerful ecosystem, while C is known for its high performance and underlying control capabilities.

The 2-Hour Python Plan: A Realistic ApproachApr 11, 2025 am 12:04 AM

You can learn basic programming concepts and skills of Python within 2 hours. 1. Learn variables and data types, 2. Master control flow (conditional statements and loops), 3. Understand the definition and use of functions, 4. Quickly get started with Python programming through simple examples and code snippets.

Python: Exploring Its Primary ApplicationsApr 10, 2025 am 09:41 AM

Python is widely used in the fields of web development, data science, machine learning, automation and scripting. 1) In web development, Django and Flask frameworks simplify the development process. 2) In the fields of data science and machine learning, NumPy, Pandas, Scikit-learn and TensorFlow libraries provide strong support. 3) In terms of automation and scripting, Python is suitable for tasks such as automated testing and system management.

How Much Python Can You Learn in 2 Hours?Apr 09, 2025 pm 04:33 PM

You can learn the basics of Python within two hours. 1. Learn variables and data types, 2. Master control structures such as if statements and loops, 3. Understand the definition and use of functions. These will help you start writing simple Python programs.

How to teach computer novice programming basics in project and problem-driven methods within 10 hours?Apr 02, 2025 am 07:18 AM

How to teach computer novice programming basics within 10 hours? If you only have 10 hours to teach computer novice some programming knowledge, what would you choose to teach...

How to avoid being detected by the browser when using Fiddler Everywhere for man-in-the-middle reading?Apr 02, 2025 am 07:15 AM

How to avoid being detected when using FiddlerEverywhere for man-in-the-middle readings When you use FiddlerEverywhere...

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Best Graphic Settings

4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows: Seashell Riddle Solution

2 weeks agoByDDD

R.E.P.O. How to Fix Audio if You Can't Hear Anyone

4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

WWE 2K25: How To Unlock Everything In MyRise

1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

Zend Studio 13.0.1

Powerful PHP integrated development environment

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

Hot Topics

Where is the login entrance for gmail email?

7517

CakePHP Tutorial

1378

What is the format of the account name of steam

win11 activation key permanent

nyt connections hints and answers