Python Read/Write Excel File

by

openpyxl for Excel Handling

Install openpyxl (if not installed)

pip install openpyxl

Create / Write to an Excel File

from openpyxl import Workbook

wb = Workbook()                # Create workbook
ws = wb.active                 # Select active sheet
ws.title = "Students"          # Rename sheet

# Write data
ws['A1'] = "Name"
ws['B1'] = "Marks"
ws['A2'] = "Deepesh"
ws['B2'] = 85

wb.save("students.xlsx")       # Save file
print("File created successfully")

Write Multiple Rows

Using append()

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

data = [
    ["Name", "Marks"],
    ["Ankit", 90],
    ["Riya", 88],
    ["Aman", 76]
]

for row in data:
    ws.append(row)

wb.save("marks.xlsx")
print("Data written successfully")

Read Excel File

Read the whole sheet of data

from openpyxl import load_workbook

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

for row in ws.rows:
    for cell in row:
        print(cell.value, end="  |  ")
    print()

Read Specific Cells

By cell address

from openpyxl import load_workbook

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

print(ws['A1'].value)   # Header
print(ws['B2'].value)   # Marks of 2nd row

Read by Rows / Columns

By Row

for row in ws.iter_rows(min_row=1, max_row=4, values_only=True):
    print(row)

By Column

for col in ws.iter_cols(min_col=1, max_col=2, values_only=True):
    print(col)

Update Existing Excel File

from openpyxl import load_workbook

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

ws['B3'] = 95  # Update marks for row 3
wb.save("marks.xlsx")

print("File updated")

Add a New Sheet

wb = load_workbook("marks.xlsx")
wb.create_sheet(title="Summary")   # Add at end
wb.save("marks.xlsx")

Delete Sheet

wb = load_workbook("marks.xlsx")
del wb["Summary"]
wb.save("marks.xlsx")

Get Sheet Names

print(wb.sheetnames)

Apply Basic Formatting

from openpyxl.styles import Font

ws['A1'].font = Font(bold=True, size=12, color="FF0000")
ws['B1'].font = Font(bold=True, size=12)
wb.save("marks.xlsx")

Calculate Total & Average

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

data = [
    ["Name", "M1", "M2", "M3", "Total", "Average"],
    ["Raj", 85, 90, 88],
    ["Simran", 78, 82, 80],
]

for row in data:
    ws.append(row)

# Add formulas
ws['E2'] = "=B2+C2+D2"
ws['F2'] = "=E2/3"
ws['E3'] = "=B3+C3+D3"
ws['F3'] = "=E3/3"

wb.save("report.xlsx")
print("Report created!")

Summary

TaskMethod
Create fileWorkbook()
Load fileload_workbook()
Write cellws[‘A1’] = value
Append rowws.append()
Readws.rows, iter_rows()
FormattingFont()

Program Examples

Read Excel File

import openpyxl

def read_excel_file(file_path, sheet_name, cell_name):
    # Load the workbook
    workbook = openpyxl.load_workbook(file_path)
    # Select the specified sheet
    sheet = workbook[sheet_name]
    # Read the value from the specified cell
    cell_value = sheet[cell_name].value
    print(f"Value in {cell_name} of sheet '{sheet_name}': {cell_value}")

# read one cell data 
read_excel_file(file_path='users_data.xlsx', sheet_name='Sheet1', cell_name='A2')

Write Excel File

def write_excel_file(file_path, sheet_name, cell_name, data):
    # Load the workbook
    workbook = openpyxl.load_workbook(file_path)
    # Select the specified sheet
    sheet = workbook[sheet_name]
    # Write data to the specified cell
    sheet[cell_name] = data
    
    # Save the workbook
    workbook.save(file_path)
    print(f"Data written to sheet '{sheet_name}' starting at {cell_name}.")



write_excel_file(file_path='users_data.xlsx', sheet_name='Sheet1', cell_name='D2', data='Learning Excel with Python')

Leave a Comment