Skip to content
Learni
View all tutorials
Automatisation

How to Automate Excel Reports with Python in 2026

18 minBEGINNER
PythonAutomatisationExcel
Lire en français

Introduction

Automating Excel reports transforms repetitive manual tasks into fast, reliable processes. Instead of copying and pasting data by hand each month, Python can read your sources, analyze them, and produce a ready-to-use Excel file in seconds. This tutorial is designed for beginners and teaches you how to use pandas and openpyxl to create professional reports. You will learn to load CSV data, calculate totals, apply styles, and save the final result. This approach reduces human errors and saves you valuable time in your daily work.

Prerequisites

  • Python 3.10 or higher installed
  • Basic Python knowledge (variables, lists)
  • A code editor (VS Code recommended)
  • Access to a terminal or command prompt

Installing the Libraries

terminal
pip install pandas openpyxl

This command installs pandas for data manipulation and openpyxl for creating and formatting Excel files. Run it once in your terminal.

Reading Source Data

read_data.py
import pandas as pd

df = pd.read_csv('ventes.csv')
print(df.head())
print('Colonnes disponibles :', df.columns.tolist())

This script loads a CSV file named ventes.csv and displays the first rows. Always verify column names before continuing.

Processing Steps

Once the data is loaded, we will calculate totals by category and prepare a summary table. These calculations are performed in memory before being exported to Excel.

Calculating Aggregates

process_data.py
import pandas as pd

df = pd.read_csv('ventes.csv')
rapport = df.groupby('categorie')['montant'].sum().reset_index()
rapport.columns = ['Catégorie', 'Total Ventes']
print(rapport)

This code groups sales by category and calculates the total. The result is a clean DataFrame ready for export.

Creating the Excel File

create_excel.py
import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Rapport Ventes"

df = pd.read_csv('ventes.csv')
rapport = df.groupby('categorie')['montant'].sum().reset_index()

# Writing the headers
ws.append(['Catégorie', 'Total Ventes'])
for row in rapport.values:
    ws.append(list(row))

wb.save('rapport_ventes.xlsx')

We create a new Excel workbook, add the data, and save the file. This version is already usable but can be improved with formatting.

Adding Professional Formatting

format_excel.py
from openpyxl.styles import Font, PatternFill, Alignment

# After creating ws and adding the data
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center')

# Adjust column widths
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 18

wb.save('rapport_ventes.xlsx')

This code applies a blue header style with white text and centers the content. Column widths are adjusted for better readability.

Best Practices

  • Always validate the CSV file structure before processing
  • Use explicit variable names (rapport_ventes instead of df2)
  • Add comments in the code for important steps
  • Test the script on a small dataset before applying it in production
  • Save generated files with a date in the name (rapport_2026-01-15.xlsx)

Common Errors to Avoid

  • Forgetting to install openpyxl before using the Excel engine
  • Not checking CSV column names (KeyError issues)
  • Overwriting the Excel file without a backup when running the script multiple times
  • Ignoring missing values in the source data

Going Further

You can now schedule automatic execution using the Windows Task Scheduler or cron. To deepen these techniques, check out our Learni trainings.