Python, working with Excel spreadsheets.

Python has a lot of powerful libraries. In this post we’ll use openpyxl, which allows you to work easily with Excel spreadsheets.

We will refer to the Python2 version of the library. It is also available for Python3 and should also be compatible with this one.

The full project documentation is available here.

The Excel formats currently supported by this module are:

• .xlsx
• .xlsm
• .xltx
• .xltm

Installing the library

First of all, we need to install the openpyxl module, so make sure you have installed a Python package management system.

In this post we will use pip. It comes already installed if your’re using Python 2 >= 2.7.9 or Python 3 >= 3.4. If it’s not installed yet, you can do it in your Linux system running:

sudo apt-get install pip

to install pip for Python2, or

sudo apt-get install pip3

for Python3.

After that, install the openpyxl library using pip:

pip install openpyxl

If you want to be able to include images into the Excel file, you have to install also the pillow module:

pip install pillow

Using the library

Shebang & importing

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from openpyxl import load_workbook

The second line is used to declare the UTF-8 encoding of the Python source file.

Opening the file

wb = load_workbook("excelFileName.xlsx" [, data_only=True])

Setting the data_only attribute to True, you will access to the values of the cells, otherwise you will access to the formula of the cell.

Selecting a sheet

To select the current sheet, use

sheet = wb.active

If you wnat to access to a specific sheet knowing its name, do

sheet = wb["D1_densa_WGS84_quota"]

Reading & writing a cell value

To read a cell value:

val = sheet["D2"].value

ws['D2'] = val

Saving the file

After the modifications apported to the file, remember to save the file:

wb.save('aFileName.xlsm')

Note that the file name used in save() function can be different to the file name used in open() function. In that case, it will create a new file, otherwise if the name is the same it will overwrite the old file.

Threating column letters as numbers

Working with letters to index a column can be difficult, expecially when you have to do with a lot of column. A simply solution is to call two functions which gives you the column index of a given letter or the column letter by a given index.

To use these functions, first you have to import them, and then you can invoke.

from openpyxl.cell import get_column_letter, column_index_from_string

colDIndex = column_index_from_string('D')
colLetter = get_column_letter(colIndex)

Getting the max col and row used

It’s useful sometimes to know the last row or column used in the current sheet.

To know these informations, you can use the functions:

maxRow= sheet.max_row
maxCol= sheet.max_column

Full example

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from openpyxl import load_workbook
from openpyxl.cell import get_column_letter, column_index_from_string

wb = load_workbook("name.xlsx")    #, data_only=True)

sheet = wb.active
# or sheet = wb["Sheet1"]

excelNorthCol = "A"
excelEastCol = "B"
rowIndex = 1
valueToWrite = 10


north = sheet["{0}{1}".format(excelNorthCol, str(rowIndex))].value # (sheet["A1"].value)
east = sheet["{0}{1}".format(excelEastCol, str(rowIndex))].value # (sheet["B1"].value)


maxCol = sheet.max_column
cell = get_column_letter(maxCol+1) + str(rowIndex)

# or, eventually:
# columnIndexToWrite = 3
# cell = get_column_letter(columnIndexToWrite) + str(rowIndex)

sheet[cell] = valueToWrite

wb.save("name.xlsx")