How to Automate an Excel Sheet in Python?

Excel automation with Python allows you to manipulate spreadsheets programmatically, saving time and reducing manual errors. Python offers several powerful libraries for Excel automation: openpyxl for direct Excel file manipulation, pandas for data analysis operations, and xlwings for advanced Excel integration.

Method 1: Using openpyxl Library

The openpyxl library provides direct access to Excel files, allowing you to read, write, and modify spreadsheets cell by cell.

Installation

pip install openpyxl

Example

Let's create and manipulate an Excel file that adds bonus calculations to employee salaries ?

import openpyxl as xl

# Create a sample workbook with employee data
wb = xl.Workbook()
sheet = wb.active
sheet.title = "Sheet1"

# Add headers and sample data
sheet['A1'] = 'Name'
sheet['B1'] = 'Salary'
sheet['C1'] = 'Salary + Bonus'

employees = [('Alice', 5000), ('Bob', 6000), ('Charlie', 5500)]
for i, (name, salary) in enumerate(employees, 2):
    sheet[f'A{i}'] = name
    sheet[f'B{i}'] = salary

# Save initial file
wb.save('employee_data.xlsx')

# Now automate bonus calculation
for row in range(2, sheet.max_row + 1):
    salary_cell = sheet.cell(row, 2)
    bonus_cell = sheet.cell(row, 3)
    bonus_cell.value = int(salary_cell.value) + 500

# Save modified workbook
wb.save('employee_data_with_bonus.xlsx')
print("Bonus calculations completed and saved!")

Bonus calculations completed and saved!

Method 2: Using pandas Library

The pandas library excels at data manipulation and analysis, making it ideal for working with structured Excel data.

Installation

pip install pandas openpyxl

Example

Using pandas to perform the same bonus calculation with cleaner, more readable code ?

import pandas as pd

# Create sample data
data = {
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Salary': [5000, 6000, 5500]
}

# Create DataFrame and save as Excel
df = pd.DataFrame(data)
df.to_excel('employee_data_pandas.xlsx', index=False)

# Read the Excel file
df = pd.read_excel('employee_data_pandas.xlsx')

# Add bonus column
df['Salary + Bonus'] = df['Salary'] + 1000

# Save updated data
df.to_excel('employee_data_pandas_updated.xlsx', index=False)

print("Data processed with pandas:")
print(df)

Data processed with pandas:
      Name  Salary  Salary + Bonus
0    Alice    5000            6000
1      Bob    6000            7000
2  Charlie    5500            6500

Method 3: Using xlwings Library

The xlwings library provides seamless integration with Excel applications, especially useful for complex Excel operations and real?time interaction.

Installation

pip install xlwings

Example

Note: xlwings requires Excel to be installed and works best on Windows systems ?

import xlwings as xw
import pandas as pd

# Create sample data first
data = {
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Salary': [5000, 6000, 5500]
}
df = pd.DataFrame(data)
df.to_excel('xlwings_sample.xlsx', index=False)

# Open Excel file with xlwings
wb = xw.Book('xlwings_sample.xlsx')
sheet = wb.sheets['Sheet1']

# Add header for bonus column
sheet.range('C1').value = 'Salary + Bonus'

# Calculate bonuses
salary_values = sheet.range('B2:B4').value
bonus_values = [salary + 750 for salary in salary_values]
sheet.range('C2:C4').value = [[value] for value in bonus_values]

# Save and close
wb.save('xlwings_sample_updated.xlsx')
wb.close()
print("Excel automation completed with xlwings!")

Comparison

Conclusion

Choose pandas for data analysis and bulk operations, openpyxl for direct file manipulation without Excel, and xlwings for advanced Excel application control. Each method offers unique advantages depending on your automation requirements.