Django Create And Export Excel Files With XLWT Example

This article introduces how to use the Python XLWT library to generate excel spreadsheet tables and export the excel file to HTTP client in Django.

1. Install Python XLWT Library.

XLWT is a Python library that can be used to generate excel files, before using it, we should make sure it is installed in your Python environment.

1. Run the command pip show xlwt in a terminal to see whether the python XLWT library has been installed or not.

   $ pip show xlwt
   WARNING: Package(s) not found: xlwt

2. If the python XLWT library does not been installed, you can run the command pip install xlwt to install it.

   $ pip install xlwt
   Collecting xlwt
     Downloading xlwt-1.3.0-py2.py3-none-any.whl (99 kB)
        |████████████████████████████████| 99 kB 269 kB/s

3. Show the Python XLWT library again to verify it has been installed succesfully.

   $ pip show xlwt
   Name: xlwt
   Version: 1.3.0
   Summary: Library to create spreadsheet files compatible with MS Excel 97/2000/XP/2003 XLS files, on any platform, with Python 2.6, 2.7, 3.3+
   Home-page: http://www.python-excel.org/
   Author: John Machin
   Author-email: [email protected]
   License: BSD
   Location: /Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages
   Requires: 
   Required-by: 
   

2. Use Python XLWT Library To Create Excel File & Export It To Client In Django Example.

This is just a demonstration of the export method, and it doesn’t do any REST and Exception handling. It also maintains a unified style to make the exported data more beautiful.

# Import the python xlwt module. 
import xlwt

# This function will export an excel workbook file from Django to the requesting client.
def export_excel(request): 
  
  # Create a HttpResponse object and set its content_type header value to Microsoft excel.
  response = HttpResponse(content_type='application/vnd.ms-excel') 
  
  # Set HTTP response Content-Disposition header value. Tell web server client the attached file name is students.xls.
  response['Content-Disposition'] = 'attachment;filename=students.xls' 

  # Create a new Workbook file.
  work_book = xlwt.Workbook(encoding = 'utf-8') 

  # Create a new worksheet in the above workbook.
  work_sheet = work_book.add_sheet(u'Students Info')

  # Maintain some worksheet styles，style_head_row, style_data_row, style_green, style_red
　　
    # This style will be applied to worksheet head row.
    style_head_row = xlwt.easyxf("""    
    align:
      wrap off,
      vert center,
      horiz center;
    borders:
      left THIN,
      right THIN,
      top THIN,
      bottom THIN;
    font:
      name Arial,
      colour_index white,
      bold on,
      height 0xA0;
    pattern:
      pattern solid,
      fore-colour 0x19;
    """
  )

  # Define worksheet data row style. 
  style_data_row = xlwt.easyxf("""
    align:
      wrap on,
      vert center,
      horiz left;
    font:
      name Arial,
      bold off,
      height 0XA0;
    borders:
      left THIN,
      right THIN,
      top THIN,
      bottom THIN;
    """
  )
  
  # Set data row date string format.
  style_data_row.num_format_str = 'M/D/YY'

  # Define a green color style.
  style_green = xlwt.easyxf(" pattern: fore-colour 0x11, pattern solid;")

  # Define a red color style.
  style_red = xlwt.easyxf(" pattern: fore-colour 0x0A, pattern solid;")


  # Generate worksheet head row data.
  work_sheet.write(0,0, 'ID', style_head_row) 
  work_sheet.write(0,1, 'Name', style_head_row) 
  work_sheet.write(0,2, 'ClassRoom', style_head_row) 
  work_sheet.write(0,3, 'Sex', style_head_row) 
  work_sheet.write(0,4, 'Email', style_head_row) 
  work_sheet.write(0,5, 'Score', style_head_row) 
   
  # Generate worksheet data row data.
  row = 1 
  for student in studentsInfo.objects.all():
    work_sheet.write(row,0, student.id, style_data_row)
    work_sheet.write(row,1, student.name, style_data_row)
    work_sheet.write(row,2, student.classroom, style_data_row)
    work_sheet.write(row,3, student.sex, style_data_row)
    work_sheet.write(row,4, student.email, style_data_row)
    work_sheet.write(row,4, student.score, style_data_row)

    row=row + 1 
   
  # Create a StringIO object.
  output = StringIO.StringIO()

  # Save the workbook data to the above StringIO object.
  work_book.save(output)

  # Reposition to the beginning of the StringIO object.
  output.seek(0)

  # Write the StringIO object's value to HTTP response to send the excel file to the web server client.
  response.write(output.getvalue()) 

  return response