Python 3 in Excel

Python is a popular choice for automating tasks and analyzing data—and working with Excel files is one of the most common use cases. With the help of a few powerful libraries, you can read, write, and manipulate Excel (.xlsx) files directly in Python 3.

Here’s a beginner-friendly guide to get you started.

Step 1: Install Required Libraries

The two most common libraries are:

• openpyxl – for .xlsx files (Excel 2007+)
• pandas – for data analysis, also uses openpyxl or xlrd in the background

Install them using pip:

pip install openpyxl pandas

Step 2: Reading an Excel File

Using pandas (recommended for most use cases):

import pandas as pd

# Load the Excel file
df = pd.read_excel('data.xlsx')  # Add sheet_name='Sheet1' if needed

# Display the data
print(df.head())

Using openpyxl (more control at the cell level):

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')
sheet = wb.active

# Read the value of cell A1
print(sheet['A1'].value)

Step 3: Writing to Excel

With pandas:

df = pd.DataFrame({
    'Name': ['Alice', 'Bob'],
    'Age': [25, 30]
})

df.to_excel('output.xlsx', index=False)

With openpyxl:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Write data
ws['A1'] = 'Name'
ws['B1'] = 'Age'
ws.append(['Alice', 25])
ws.append(['Bob', 30])

# Save the file
wb.save('output.xlsx')

Step 4: Updating Existing Excel Files

You can load, edit, and save Excel files without losing formatting using openpyxl:

wb = load_workbook('existing.xlsx')
ws = wb['Sheet1']
ws['C1'] = 'Email'
ws['C2'] = '[email protected]'
wb.save('updated.xlsx')

Summary