Read and Write to an excel file using Python openpyxl module

Python provides the openpyxl module for reading and writing Excel files. This powerful library allows you to create, modify, and extract data from Excel workbooks programmatically.

Installation

Install openpyxl using pip ?

pip install openpyxl

Getting Sheet Title

When you create a new workbook, it comes with a default sheet ?

import openpyxl

my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet_title = my_sheet.title
print("My sheet title: " + my_sheet_title)

My sheet title: Sheet

Changing Sheet Title

You can customize the sheet name by modifying the title property ?

import openpyxl

my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet.title = "My New Sheet"
print("Sheet name is: " + my_sheet.title)

Sheet name is: My New Sheet

Writing Data to Excel

You can insert data using cell coordinates or Excel notation ?

import openpyxl

my_wb = openpyxl.Workbook()
my_sheet = my_wb.active

# Using cell coordinates
c1 = my_sheet.cell(row=1, column=1)
c1.value = "Aadrika"
c2 = my_sheet.cell(row=1, column=2)
c2.value = "Adwaita"

# Using Excel notation
c3 = my_sheet['A2']
c3.value = "Satyajit"
c4 = my_sheet['B2']
c4.value = "Bivas"

my_wb.save("sample_data.xlsx")
print("Data written to Excel file successfully")

Adding Multiple Sheets

Create additional worksheets in your workbook ?

import openpyxl

my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_wb.create_sheet(index=1, title="New Sheet")
my_wb.save("workbook_with_sheets.xlsx")
print("New sheet added successfully")

Reading Excel Data

Getting Total Rows

import openpyxl

my_path = "sample_data.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
print("Total rows:", my_sheet_obj.max_row)

Reading Specific Cell Value

import openpyxl

my_path = "sample_data.xlsx"
wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = wb_obj.active
my_cell_obj = my_sheet_obj.cell(row=1, column=1)
print("Cell A1 value:", my_cell_obj.value)

Getting Total Columns

import openpyxl

my_path = "sample_data.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
print("Total columns:", my_sheet_obj.max_column)

Reading All Data

Reading First Row

import openpyxl

my_path = "sample_data.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column

print("First row values:")
for i in range(1, my_max_col + 1):
    my_cell_obj = my_sheet_obj.cell(row=1, column=i)
    print(my_cell_obj.value, end=" ")

Reading First Column

import openpyxl

my_path = "sample_data.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_row = my_sheet_obj.max_row

print("First column values:")
for i in range(1, my_row + 1):
    cell_obj = my_sheet_obj.cell(row=i, column=1)
    print(cell_obj.value)

Reading Specific Row

import openpyxl

my_path = "sample_data.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column

print("Second row values:")
for i in range(1, my_max_col + 1):
    cell_obj = my_sheet_obj.cell(row=2, column=i)
    print(cell_obj.value, end=" ")

Summary

Conclusion

The openpyxl module provides comprehensive functionality for Excel file manipulation in Python. Use Workbook() to create new files and load_workbook() to read existing ones. Always remember to save your changes using save().