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.
- 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
- 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
- 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
where does the xlsx get saved?