openpyxl Part 2 – Retrieving Data from a Spreadsheet

In the previous part of the series we created a simple spreadsheet and saved it as wb1.xlsx. Today we’ll retrieve some data from it. We’ll be working in interactive mode today.

Let’s start with retrieving the sheets. There’s only one sheet at the moment and its name is ‘Sheet’. Let’s check it out. We will need the load_work function to load the workbook first:

>>> from openpyxl import load_workbook

Now we have to load the workbook:

>>> workbook = load_workbook(filename="wb1.xlsx")

With the workbook loaded, we can now retrieve data from it. Let’s retrieve the names of the sheets:

>>> workbook.sheetnames
['Sheet']

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

The first available sheet is the active one. In our case there is only one sheet:

>>> active_sheet = workbook.active
>>> active_sheet
<Worksheet "Sheet">

or just the title:

>>> active_sheet.title
'Sheet'

Inside the workbook we put two strings: ‘hey!’ in cell A1 and ‘ho’ in cell C2. Here’s how you can retrieve the cell:

>>> active_sheet['A1']
<Cell 'Sheet'.A1>

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

And here how you can retrieve the value stored in a cell:

>>> active_sheet['A1'].value
'hey!'
>>> active_sheet['C2'].value
'ho!'

You can also use small letters:

>>> active_sheet['c2'].value
'ho!'

An alternative way of accessing the cells and the values stored in them is by means of the cell method. Then we just have to specify the row and column:

>>> active_sheet.cell(row=1, column=1)
<Cell 'Sheet'.A1>
>>> active_sheet.cell(row=1, column=1).value
'hey!'
>>> active_sheet.cell(row=2, column=3).value
'ho!'

As you can see, row and columns are 1-based.

In the next part of the series we’ll see how to iterate over the rows and columns of a spreadsheet.

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.