Course: Handling of Excel files in Python
Course objectives:
At the end of this chapter, you will be able to:
Read and write Excel files (.xlsx).
Edit an Excel file cell by cell by openpyxl.
Understand the difference between Pandas and openpyxl.
The Excel files (.xlsx):
Pandas makes it easy to read and write Excel files.
1.1 Read an Excel file
import pandas as pd
df = pd.read_excel("[Link]", sheet_name="Feuille1")
print(df)
Useful parameters:
sheet_name="Feuille1" name of the sheet
nrows=10 read the first 10 lines
usecols="A:C" read columns A to C
skiprows=2 ignore the first 2 lines
1.2 Write in an Excel file
df.to_excel("new_file.xlsx", index=False)
1.3 Write in several Excel sheets
with [Link]("multi_feuilles.xlsx") as writer:
df1.to_excel(writer, sheet_name="Clients")
df2.to_excel(writer, sheet_name="Commandes")
2. Pandas boundaries
Pandas allows you to read and write Excel files, but not to modify the existing
cells (style, colors, fusion, formulas...).
➡️To actually modify an Excel file cell by cell, you must use openpyxl.
3. Introduction to openpyxl
openpyxlis a specialized library for working with Excel files in .xlsx..
It allows:
✔Read an Excel file✔Edit cells
✔Add rows✔Create sheets
✔Insert Excel formulas✔Change colors, borders, sizes
3.1 Load an Excel file
from openpyxl import load_workbook
wb = load_workbook("[Link]")
ws = [Link]
wb= workbook (workbook)
ws = worksheet (feuille active)
3.2 Read a cell
valeur = ws["A1"].value
print(valeur)
3.3 Modify a cell
ws["B2"] = 42
[Link]("xlsx file")
3.4 Browse multiple lines
for row in ws.iter_rows(min_row=1, max_row=5, values_only=True):
print(row)
3.5 Create a new excel file
from openpyxl import Workbook
wb = Workbook()
ws = [Link]
ws["A1"] = "Name"
ws["B1"] = "Age"
[Link]("[Link]")
Pandas vs openpyxl: When to use what?
Need Pandas openpyxl
Read an Excel ✔️ ✔️
Write an Excel ✔️ ✔️
Modify an existing cell ❌ ✔️
Change the color/ style ❌ ✔️
Add formulas ❌ ✔️
Data analysis ✔️ ❌
TP – Exercises + Solutions
✔ TP1 – Write an Excel file from a DataFrame
Statement:
Create a DataFrame containing 3 students:
Name, NoteRecord it in an Excel file.
Solution:
import pandas as pd
data = {
"Name": ["Sara", "Yanis", "Amine"],
"Note": [15, 12, 18]
}
df = [Link](data)
df.to_excel("[Link]", index=False)
✔ TP2 – Edit an Excel file with openpyxl
Statement:
1. Upload a [Link] file
2. Add 2 points to all notes
3. Save as notes_corrigees.xlsx
Solution:
from openpyxl import load_workbook
wb = load_workbook("[Link]")
ws = [Link]
for row in ws.iter_rows(min_row=2, max_col=2):
note = row[1].value
row[1].value = note + 2
[Link]("notes_corrigees.xlsx")