Reading and writing Excel sheets.

The following excel file has a sheet with employees and their salaries. The goal of this tutorial is to raise the salaries and write them to a new column.

Download the excel sheet

Install openpyxl

This tutorial uses openpyxl, a Python library to read/write Excel 2010 xlsx/xlsm files. To install, create a virtual environment and use the command:

pip install openpyxl

Read from Excel sheet

The downloaded excel sheet looks like this:

• Excel filename: Company.xlsx
• Sheet name: Employees
• Data: cells A2 to B8

Here is the code to read the data:

from openpyxl import load_workbook
workbook = load_workbook(filename = "Company.xlsx")
worksheet = workbook["Employees"]
rows = worksheet["A2:B8"]

print("Current salaries")
print("================")
for r in rows:  # r is a tuple of cell A and B
    print(f"{r[0].value}, ${r[1].value}")

Output:

Current salaries
================
Vera, $2000
Chuck, $1800
Samantha, $1800
Roberto, $2100
Dave, $2200
Tina, $2300
Ringo, $1900

Update and save the Excel file

Here is the code that:

• Reads the sheet
• Raises the salaries and writes them in a new column
• Saves the excel file

from openpyxl import load_workbook
workbook = load_workbook(filename = "Company.xlsx")
worksheet = workbook["Employees"]

rows = worksheet["A2:C8"]

print("Updating salaries")
print("=================")
for r in rows:
    old_salary = r[1].value
    new_salary = old_salary * 1.15
    print(f"{old_salary} -> {new_salary}")
    r[2].value = new_salary
    r[2].number_format = '#,##0.00 €'

workbook.save(filename = "Company.xlsx")

Output:

Updating salaries
=================
2000 -> 2300.0
1800 -> 2070.0
1800 -> 2070.0
2100 -> 2415.0
2200 -> 2530.0
2300 -> 2645.0
1900 -> 2185.0

And here is the resulting sheet in Excel: