Library Overview

The openpyxl package enables Python applicasions to read and write Excel 2010 formats including .xlsx, .xlsm, .xltx, and .xltm. Legacy .xls binary files are not supported by this library.

Core componants include:

• Workbook: The entire Excel file object.
• Worksheet: Individual sheets contained within the workbook.
• Cell: Specific data points defined by row and column intersection.

The standard process involves loading a workbook, selecting a target worksheet, and performing operations on specific cells.

Reading Spreadsheet Data

Data extraction begins by loading the file path. Sheets can be accessed via index or name. Deprecated methods such as get_sheet_names should be avoided to prevent warnings.

from openpyxl import load_workbook

file_location = "dataset.xlsx"
wb = load_workbook(filename=file_location)

# Select the first worksheet
ws = wb.worksheets[0]

# Fetch value from specific coordinates
target = ws.cell(row=2, column=2)
print(f"Value at B2: {target.value}")

# Direct access via coordinates
print(f"Column A: {ws['A']}")
print(f"Row 1: {ws['1']}")
print(f"Cell C4: {ws['C4'].value}")

# Retrieve dimensions
print(f"Max Row: {ws.max_row}")
print(f"Max Column: {ws.max_column}")

# Iterate through column C
print("Values in Column C:")
for cell_obj in ws["C"]:
    print(cell_obj.value, end=" ")

# Iterate through row 2
print("\nValues in Row 2:")
for cell_obj in ws["2"]:
    print(cell_obj.value)

Writing Spreadsheet Data

Generating a new file requires instantiating a Workbook. The active sheet title can be customized, and values are assigned directly to cell coordinates. Formulas are entered as strings.

from openpyxl import Workbook

new_wb = Workbook()
active_ws = new_wb.active
active_ws.title = "Generated_Report"

# Assign static text
active_ws["C3"] = "Process Finished"

# Populate a column with numbers
for idx in range(1, 11):
    active_ws[f"A{idx}"] = idx

# Insert a formula
active_ws["E1"] = "=SUM(A1:A10)"

# Persist changes to disk
new_wb.save("output_result.xlsx")

Updating an existing file follows a similar pattern: load the workbook, modify the specific sheet, and save.

from openpyxl import load_workbook

existing_path = "output_result.xlsx"
wb = load_workbook(existing_path)
ws = wb["Generated_Report"]

ws["C1"] = "Updated Content"
wb.save(existing_path)