Using Python for Excel Automation

Excel is widely used for data storage, analysis, and reporting. Python provides several libraries to automate Excel tasks like:
Creating and editing Excel files
Reading and writing data
Formatting cells and sheets
Generating reports

Popular Python libraries for Excel automation:

openpyxl → Works with .xlsx files
pandas → Handles data efficiently
xlrd → Reads .xls files (older Excel format)
xlsxwriter → Creates and writes .xlsx files

2. Installing Required Libraries

To work with Excel files, install the necessary libraries:

pip install openpyxl pandas xlsxwriter xlrd

3. Creating a New Excel File

Using openpyxl

from openpyxl import Workbook

wb = Workbook()  # Create a new workbook
ws = wb.active  # Select the active sheet
ws.title = "Sales Data"  # Rename sheet

wb.save("sales_data.xlsx")  # Save the file

This creates a blank Excel file named sales_data.xlsx!

4. Writing Data to an Excel File

Writing Data to Specific Cells

ws["A1"] = "Product"
ws["B1"] = "Price"
ws["A2"] = "Laptop"
ws["B2"] = 1000

wb.save("sales_data.xlsx")  # Save changes

Now you have data in Excel!

5. Reading Data from an Excel File

Read a Specific Cell

from openpyxl import load_workbook

wb = load_workbook("sales_data.xlsx")
ws = wb.active

print(ws["A1"].value)  # Output: Product
print(ws["B2"].value)  # Output: 1000

Read All Rows Using a Loop

for row in ws.iter_rows(values_only=True):
    print(row)

Now you can extract data easily!

6. Using Pandas to Read and Write Excel Files

Read an Excel File into a DataFrame

import pandas as pd

df = pd.read_excel("sales_data.xlsx")
print(df)

Write a DataFrame to an Excel File

data = {"Product": ["Laptop", "Phone"], "Price": [1000, 500]}
df = pd.DataFrame(data)

df.to_excel("new_sales.xlsx", index=False)

Now you can manage Excel data with Pandas!

7. Formatting Excel Sheets

Change Font Style

from openpyxl.styles import Font

ws["A1"].font = Font(bold=True, color="FF0000")  # Make text bold & red
wb.save("formatted.xlsx")

Change Cell Background Color

from openpyxl.styles import PatternFill

fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws["B1"].fill = fill  # Highlight cell
wb.save("formatted.xlsx")

Now your Excel sheets look better!

8. Adding and Deleting Sheets

Create a New Sheet

ws2 = wb.create_sheet("Summary")
wb.save("sales_data.xlsx")

Delete a Sheet

wb.remove(wb["Summary"])
wb.save("sales_data.xlsx")

Now you can manage multiple sheets!

9. Merging and Unmerging Cells

Merge Cells

ws.merge_cells("A1:B1")  # Merge A1 and B1
wb.save("merged.xlsx")

Unmerge Cells

ws.unmerge_cells("A1:B1")
wb.save("merged.xlsx")

Now you can merge and unmerge cells!

10. Adding Formulas in Excel

Use Excel Formulas in Cells

ws["C2"] = "=SUM(B2:B10)"  # Add sum formula
wb.save("formulas.xlsx")

Now Excel calculates values dynamically!

11. Creating Charts in Excel

Create a Simple Bar Chart

from openpyxl.chart import BarChart, Reference

chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=3)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, "D5")

wb.save("chart.xlsx")

Now your Excel file has charts!

12. Automating Excel Reports

Generate Monthly Sales Report

import datetime

filename = f"sales_report_{datetime.date.today()}.xlsx"
df.to_excel(filename, index=False)

Now reports are automatically named with dates!

13. Sending Excel Files via Email

🔹 Send Excel File via SMTP

import smtplib
from email.message import EmailMessage

msg = EmailMessage()
msg["Subject"] = "Sales Report"
msg["From"] = "your_email@gmail.com"
msg["To"] = "recipient@gmail.com"
msg.set_content("Attached is the sales report.")

with open("sales_data.xlsx", "rb") as file:
    msg.add_attachment(file.read(), maintype="application", subtype="vnd.ms-excel", filename="sales_data.xlsx")

server = smtplib.SMTP_SSL("smtp.gmail.com", 465)
server.login("your_email@gmail.com", "your_password")
server.send_message(msg)
server.quit()

Now Excel files are sent via email!

14. Automating Excel Tasks with Schedule

Run a Task Every Day

import schedule
import time

def generate_report():
    df.to_excel("daily_report.xlsx", index=False)
    print("Report generated!")

schedule.every().day.at("09:00").do(generate_report)

while True:
    schedule.run_pending()
    time.sleep(60)

Now Excel tasks run automatically!