Using EXCEL with python and XlsxWriter
Using EXCEL with python and XlsxWriter
...
CHAPTER ONE INTRODUCTION
XlsxWriter is a Python module for writing files in Excel 2007+ XLSX format.
It can be used to write text, numbers, and formulas in multiple worksheets. It supports features such as formatting, images, graphics, layout, automatic filters, conditional formatting, and more.
XlsxWriter has some advantages and disadvantages over alternative Python modules for writing.
Excel files.
- advantages:
- It supports more Excel features than any of the alternative modules.
- It is very faithful to the files produced by Excel. In most cases, the files produced are 100% equivalent to the files produced by Excel.
- It contains complete documentation, sample files and tests.
- It is fast and can be configured to use very little memory, even for very large output files.
- Disadvantages:
- It can not read or modify existing Excel XLSX files.
XlsxWriter is under BSD license and the source code is available on GitHub.
To try the module, refer to the following section Getting Started with XlsxWriter.
...
CHAPTER TWO GETTING STARTED WITH XLSXWRITER
Here are some simple instructions to become familiar with the XlsxWriter module.
2.1 Installing XlsxWriter
The first step is to install the XlsxWriter module. There are many ways to do it.
2.1.1 Using the PIP
The pip install program is the recommended method for installing Python modules from PyPI, the Python software.
Package index:
$ sudo pip install XlsxWriter
Note: Windows users may omit sudo at the beginning of the command.
2.1.2 Using Easy_Install
If pip does not work, try easy_install:
$ sudo easy_install XlsxWriter
2.1.3 Installing from an archive
If you download an archive of the latest version of XlsxWriter, you can install it as follows (change the version number accordingly):
$ tar -zxvf XlsxWriter-1.2.3.tar.gz
$ cd XlsxWriter-1.2.3
$ sudo python setup.py install
An archive of the latest code can be downloaded from GitHub as follows:
$ curl -O -L
$ tar zxvf master.tar.gz
$ cd XlsxWriter-master /
$ sudo python setup.py install
2.1.4 Cloning from GitHub
Source code and XlsxWriter bug tracking is in the XlsxWriter repository on GitHub. You can clone the repository and install it as follows:
$ git clone //github.com/jmcnamara/XlsxWriter.git
$ cd XlsxWriter
$ sudo python setup.py install
2.2 Execution of a sample program
If the installation was successful, you can create a small sample program similar to the following to verify that the module is working properly:
import xlsxwriter
workbook = xlsxwriter.Workbook ('hello.xlsx')
worksheet = workbook.add_worksheet ()
worksheet.write ('A1', 'Hello everyone')
workbook.close ()
Save this to a file named hello.py and run it as follows:
$ python hello.py
This will produce a file called hello.xlsx that should look like this:
If you have downloaded a tarball or cloned the repository, as noted above, you must also have a directory called examples with sample applications illustrating different features of XlsxWriter.
2.3 Documentation
The latest version of this document is hosted on Read The Docs. It is also available in PDF format.
Once you are satisfied with the installation and operation of the module, you can view the rest of the XlsxWriter documentation. Tutorial 1: Creating a simple XLSX file is a good starting point.
CHAPTER THREE 1: CREATING A SIMPLE XLSX FILE
Let's start by creating a simple worksheet using Python and the XlsxWriter module.
Suppose we have data on monthly expenses that we want to convert to Excel XLSX.
file:
expenditure = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
To do this, we can start with a small program like this:
import xlsxwriter
# Create a workbook and add a spreadsheet.
workbook = xlsxwriter.Workbook ('Expenses01.xlsx')
worksheet = workbook.add_worksheet ()
# Some data we want to write in the spreadsheet.
expenditure = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
# Start from the first cell. Rows and columns are indexed to zero.
row = 0
col = 0
# Browse the data and write them line by line.
for article, cost in (expenses):
worksheet.write (row, col, item)
worksheet.write (row, col + 1, cost)
row + = 1
# Write a total using a formula.
worksheet.write (line, 0, 'Total')
worksheet.write (line, 1, '= SUM (B1: B4)')
workbook.close ()
If we run this program, we should get a spreadsheet that looks like this:
This is a simple example, but the steps to follow are representative of all the programs that use XlsxWriter, which is why we are going to split it into several parts.
The first step is to import the module:
import xlsxwriter
The next step is to create a new workbook object using the Workbook () constructor.
Workbook () takes a non-optional argument, which is the file name we want to create:
workbook = xlsxwriter.Workbook ('Expenses01.xlsx')
Note: XlsxWriter can only create new files. It can not read or modify existing files.
The workbook object is then used to add a new worksheet using the add_worksheet () method: worksheet = workbook.add_worksheet ()
By default, the worksheet names in the worksheet will be Sheet1, Sheet2, and so on, but we can also specify a name:
worksheet1 = workbook.add_worksheet () # The default value is Sheet1.
worksheet2 = workbook.add_worksheet ('Data') # Data.
worksheet3 = workbook.add_worksheet () # By default, Sheet3.
We can then use the worksheet object to write data via the write () method:
worksheet.write (row, col, some_data)
Note: In XlsxWriter, rows and columns are indexed to zero. The first cell of a spreadsheet,
A1, is (0, 0).
So, in our example, we go through our data and write it as follows:
# Browse the data and write them line by line.
for article, cost in (expenses):
worksheet.write (row, col, item)
worksheet.write (row, col + 1, cost)
row + = 1
We then add a formula to calculate the total of the elements in the second column:
worksheet.write (line, 1, '= SUM (B1: B4)')
Finally, we close the Excel file via the close () method:
workbook.close ()
Like most file objects in Python, an XlsxWriter file is closed implicitly when it is out of scope or is no longer referenced in the program. As such, this line is usually optional unless you need to explicitly close the file.
And that's all. We now have a file that can be read by Excel and other spreadsheet applications.
In the following sections, we'll see how to use the XlsxWriter module to add formatting and more
Excel features.
CHAPTER FOUR TUTORIAL 2: ADD FORMATTING TO THE XLSX FILE
In the previous section, we created a simple worksheet using Python and the XlsxWriter module. This converted the required data into an Excel file, but it seemed a little stripped. In order to make the information clearer, we would like to add a simple formatting, like this:
...
The differences here are that we've added the Item and Cost column headers in a bold font, that we've formatted the currency in the second column, and that we have set the Total string to bold. To do this, we can expand our program as follows:
import xlsxwriter
# Create a workbook and add a spreadsheet.
workbook = xlsxwriter.Workbook ('Expenses02.xlsx')
worksheet = workbook.add_worksheet ()
# Add a bold format to use to highlight cells.
bold = workbook.add_format ({'bold': True})
# Add a numeric format for cells with money.
money = workbook.add_format ({'num_format': '$ #, ## 0'})
# Write data headers.
worksheet.write ('A1', 'Item', bold)
worksheet.write ('B1', 'Cost', bold)
# Some data we want to write in the spreadsheet.
expenditure = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
# Start with the first cell under the headers.
row = 1
col = 0
# Browse the data and write them line by line.
for article, cost in (expenses):
worksheet.write (row, col, item)
worksheet.write (line, col + 1, cost, money)
row + = 1
# Write a total using a formula.
worksheet.write (line, 0, 'Total', bold)
worksheet.write (line, 1, '= SUM (B2: B5)', silver)
workbook.close ()
The main difference between this program and the previous program is that we have added two Format objects that we can use to format the cells in the spreadsheet.
Formatting objects represent all of the formatting properties that can be applied to a cell in Excel, such as fonts, number formatting, colors, and borders. This is explained in more detail in The Format
Class section.
For the moment, we will avoid going into the details and will use a limited number of formatting features to add simple formatting:
# Add a bold format to use to highlight cells.
bold = workbook.add_format ({'bold': True})
# Add a numeric format for cells with money.
money = workbook.add_format ({'num_format': '$ #, ## 0'})
We can then pass these formats as a third optional parameter to the worksheet.write () method to format the data in the cell:
write (row, column, token, [format])
Like that:
worksheet.write (line, 0, 'Total', bold)
Which brings us to another new feature of this program. To add the headers in the first row of the
spreadsheet we used write () like this:
worksheet.write ('A1', 'Item', bold)
worksheet.write ('B1', 'Cost', bold)
So, instead of (row, col), we used the Excel style notation 'A1'. See Using cell notation for more details, but do not worry too much yet. This is just a little syntactic sugar to help in the preparation of spreadsheets.
In the next section, we will see how to handle more types of data.