Writing Excel files

In this chapter we will explore pyexcel a Python module that is installed with pip. Use the pyexcel documentation to find additional functionality on top of what is already provided in these notes.

Note: pyexcel can render the value from a formula, openpyxl can determine if cells are highlighted.

Installing pyexcel

On the windows machine we can install additional Python modules with the following command.

pip install pyexcel --user
pip install pyexcel-xlsx --user

This will allow the modules to be installed in a local directory - bypassing the issue of installing applications in program files.

Writing an excel spreadsheet - 1

Writing data to an XLSX file is surprisingly very easy. You just write your data as a 2 dimensional array as shown below.

import pyexcel

#-- create a data array.
myArray = [
    ['Col A', 'Col B', 'Col C'], 
    [4, 5, 6], 
    [7, 8, 9]
]

#-- write the output to an XLSX file
#-- "output.xls" "output.xlsx" "output.ods" "output.xlsm"
sheet = pyexcel.Sheet(myArray)
sheet.save_as("data/output1.xlsx")

Writing an excel spreadsheet - 2

The previous section showed how easy it was to write data to an XLSX file. Lets look at another example, this time using a dictionary

import pyexcel

#-- create a data array.
myArray = [
    {
        'name': 'python',
        'rating': 7
    },
    {
        'name': 'php',
        'rating': 8
    },
    {
        'name': 'JavaScript',
        'rating': 6
    },
    {
        'name': 'C++',
        'rating': 8
    }
]

#-- write the output to an XLSX file
pyexcel.save_as(records=myArray, dest_file_name="data/output2.xlsx")

Writing to a specific cell

There are 2 ways to write to a specific cell on a spreadsheet.

• sheet[row, column]
• sheet[‘A1’]

import pyexcel

# sheet[row, column]
# sheet['A1']

sheet = pyexcel.Sheet(None)
sheet['B1'] = 'method 1'
sheet[1, 4] = 'method 2'
sheet.save_as("data/output3.xlsx")

Writing to an existing spreadsheet - specific cells

Imagine that there is an XLSX template that we need to write data to. We do that by opening the spreadsheet and then writing data to it exactly as we have already been doing.

import pyexcel

# sheet[row, column]
# sheet['A1']

sheet = pyexcel.get_sheet(file_name='data/output3.xlsx')
sheet['B2'] = 'I added this to an existing spreadsheet'
sheet['B3'] = 'I saved it as a new file!'

sheet.save_as("data/output4.xlsx")

Writing to an existing spreadsheet - entire row

We can write an entire row to an existing spreadsheet. This might be useful if we are wanting to add log entries and transactions to an already existing files.

import pyexcel

# sheet.row
# sheet.column

sheet = pyexcel.get_sheet(file_name='data/output1.xlsx')
sheet.row += ['new cell data', 'new cell data', 'new cell data']

sheet.save_as("data/output5.xlsx")

Writing to an existing spreadsheet - entire column

We can also add an entire new column to the speadsheet.

import pyexcel

# sheet.row
# sheet.column

sheet = pyexcel.get_sheet(file_name='data/output2.xlsx')
sheet.column += ['Awesomeness', 'pretty cool', 'really cool', 'a bit tricky', 'quite complicated']

sheet.save_as("data/output6.xlsx")

Extended Exercise

I’ve got some python code that contains a couple of lists.

colA = [10, 15, 20, 25]
colB = [2, 4, 6, 8, 10, 12]
colC = [1, 2, 3]

Write some code so that I can have an excel spreadsheet that looks like

Make sure that the code is flexible enough so that even as the length of the lists change, the excel spreadsheet is still able to be created without modification to the code. For example:

colA = [10, 15, 20]
colB = [2, 4, 6]
colC = [1, 2, 3]