Creating an Excel file or a spreadsheet using Python's xlutils (xlwt) module

A spreadsheet is used to simulates a paper accounting worksheet. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. We have so many reporting software s that will simply create spreadsheets. But the problem is, these reporting software s may not support on all systems and also take lot of CPU time. Then arise an idea to use some method to create spreadsheet from my python code. Then I found about the module xlutils in python.

XLUTILS module contain packages for reading from(xlrd) and writing to(xlwt) excel files. Since I only need the package to write to excel file I’m installing the xlwt package.

 

Installation

We can install any python package to our repository using pip, pip is an easy way to install python packages.

To install pip

omal@zbeanz:~$sudo apt-get install python-pip

After installing pip you can install python packages by

omal@zbeanz:~$sudo pip install xlwt

This will install xlwt package to python modules

 Now in our python code,to import the package functionality to the our program.

We can define the title, header, number, date, and normal style using xlwt.easyxf(). For example title style is given below

We can define as many styles we want. We can also define how the border should appear. For example,

After defining the styles we can now define the workbook. Workbook is actually what we view in our spreadsheet. To define workbook,

A spreadsheet may contain more than one sheet, so to add sheets,

Now we can start adding data to the sheet we have created.  To write data to the ‘sheet1’ use the reference ws and ws.write(row, column, ‘Value or text’,style). For example:

To change the width and height of a cell,

 We can add complete data to the spreadsheet by writing and changing the height and width according to our need. After completing this we can save the workbook to a buffer. So First define the buffer and then save the workbook to the  buffer. This doesn’t complete the process.

Now we have to encode the data in the buffer and write it to a file say ‘test.xls’. To encode the data in the buffer import base64 module. Now open the file test.xls to view our excel file we have created.

I have not mentioned the last few steps (from encoding) so that you can try it out and create the excel file.