Tutorials › Python › Excel Automation with Python

Excel Automation with Python

Automate Excel report generation, formatting, and multi-sheet workbooks with openpyxl

One of the most valuable Python skills for data analysts at Indian companies is automating Excel reports. Many teams spend hours every week manually updating Excel files — Python can do this in seconds. openpyxl writes formatted Excel files with charts, formulas, and styles. Combined with Pandas, you can generate professional Excel reports automatically.

Examples

Generate formatted Excel with Pandas and openpyxl
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

# Save multiple DataFrames to multiple sheets
with pd.ExcelWriter("Sales_Report.xlsx", engine="openpyxl") as writer:
    df_q1.to_excel(writer, sheet_name="Q1 Sales", index=False)
    df_q2.to_excel(writer, sheet_name="Q2 Sales", index=False)
    summary.to_excel(writer, sheet_name="Summary", index=False)

# Apply formatting with openpyxl
wb = load_workbook("Sales_Report.xlsx")
ws = wb["Summary"]

# Bold header row
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="0a1628")
    cell.alignment = Alignment(horizontal="center")

# Auto-fit column widths
for col in ws.columns:
    max_len = max(len(str(cell.value or "")) for cell in col)
    ws.column_dimensions[col[0].column_letter].width = max_len + 4

wb.save("Sales_Report_Formatted.xlsx")
print("Report generated successfully!")
💡 pip install openpyxl — it is required by Pandas for reading/writing .xlsx files.
Schedule automated report generation
import pandas as pd
from datetime import datetime
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders

def generate_daily_report():
    # Load data
    df = pd.read_csv("live_sales.csv")

    # Process
    summary = df.groupby("Region").agg(
        Total_Sales=("Amount", "sum"),
        Orders=("OrderID", "count"),
        Avg_Order=("Amount", "mean"),
    ).reset_index()

    # Save
    filename = f"Daily_Report_{datetime.today().strftime('%Y-%m-%d')}.xlsx"
    summary.to_excel(filename, index=False)
    return filename

# Run manually or schedule with Windows Task Scheduler / cron
if __name__ == "__main__":
    report_file = generate_daily_report()
    print(f"Report saved: {report_file}")

Key Points

  • ✓pd.ExcelWriter with multiple .to_excel() calls writes multiple sheets in one file
  • ✓openpyxl handles formatting: fonts, fills, borders, column widths, number formats
  • ✓Schedule Python scripts with Windows Task Scheduler (Windows) or cron (Linux/Mac)
  • ✓This skill adds significant value at companies where analysts manually update Excel daily
  • ✓xlsxwriter engine supports more chart types; openpyxl is better for reading + writing

Practice Question

You want to write two DataFrames (df_sales, df_costs) to two sheets in one Excel file. Which approach is correct?

Related Topics

File Handling in PythonRead and write text files, CSV files, and automate file operations for data pipelinesPandas DataFramesThe core Pandas data structure — a 2D table with rows and columnsLoops — for and whileIterate over data, automate repetitive tasks, and process multiple files with loops