Python Excel – How to join forces?

Published on March 2nd, 2018

Many people love spreadsheets. Also many people hate spreadsheets. It doesn’t really matter in which camp you are, you probably have to deal with them anyways because they are everywhere. If you prefer to work with Python, you can use some very powerful packages to deal with Excel files. With these packages, you could almost get to “Python Excel”. In this post we will briefly touch upon two of them and provide useful links for further reading material. You can install these package with ‘pip’:

pip install openpyxl
pip install pandas

openpyxl

This is a very useful Python library for both reading and writing Excel files (xlsx/xlsm/xltx/xltm). Please find below a very basic example on how to create a Excel file:

from openpyxl import Workbook
workbook = Workbook()

# Set the active worksheet
worksheet = workbook.active

# Set the values of cells
worksheet['A1'] = 'Country'
worksheet['B1'] = 'Sales'
worksheet['A2'] = 'JP'
worksheet['B2'] = 200
worksheet['A3'] = 'UK'
worksheet['B3'] = 100

# Append a row
worksheet.append(['US', 150])

# Save the file
workbook.save("sales.xlsx")

And the Excel file ‘sales.xlsx’ will look like this:

Screenshot of 'sales.xlsx'. Python Excel.

In addition, if you would like to open an existing workbook and manipulate it, just replace the second line

workbook = Workbook()

with:

workbook = openpyxl.load_workbook('example.xlsx')

Just make sure you are using the correct path name in case your Excel file is not located your current working directory.

pandas

The ‘Pandas’ package is one of the most used packages in Python for data manipulation and analysis. Pandas works with so called DataFrames, which are comparable to the worksheets in Excel. You can easily manipulate your data with Pandas’ built-in functions.

Let’s import the ‘sales.xlsx’ file we created with ‘openpyxl’ and see how Pandas can help us to easily double our sales:

import pandas

df = pandas.read_excel('sales.xlsx')

df['Sales'] = df['Sales'] * 2

print(df)

And we just doubled our sales:

These are just simple examples. Both packages offer a wide variety of functionalities. There are plenty of high quality (and free of charge) tutorials on the web:

Python Excel

Futhermore, Microsoft is considering adding the option to use Python in Excel. They are asking for feedback on https://excel.uservoice.com/:

If you agree with this, make sure to fill out their survey. This will increase the chances of adoption of getting Python Excel.

Hopefully you enjoyed reading this post. Please let us know your thoughts through the comment form!

Leave a Reply

Your email address will not be published.