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.
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
The downloaded excel sheet looks like this:
Company.xlsxEmployeesA2 to B8Here 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
Here is the code that:
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:
