Data Extraciton with xlrd

Instal the parsing dependency via the package manager:

pip install xlrd

Load the target spreadsheet, select a specific worksheet, and access grid coordinates. The library returns a workbook object that must be navigated by sheet endex.

import xlrd

file_location = "./source_data.xls"
workbook = xlrd.open_workbook(file_location)
primary_sheet = workbook.sheet_by_index(0)

Retrieve specific entries, inspect grid boundaries, and iterate through headers or records. Coordinates follow a zero-based (row, column) convention.

top_left_value = primary_sheet.cell_value(0, 0)
print(top_left_value)

row_count = primary_sheet.nrows
col_count = primary_sheet.ncols
print(f"Grid size: {row_count} x {col_count}")

headers = [primary_sheet.cell_value(0, c) for c in range(col_count)]
for h in headers:
    print(h)

first_column = [primary_sheet.cell_value(r, 0) for r in range(row_count)]
for name in first_column:
    print(name)

second_row = primary_sheet.row_values(1)
print(second_row)

Workbook Generation with xlwt

For programmatic spreadsheet creation, leverage the xlwt module. It handles data insertion, cell formatting, and file sreialization.

pip install xlwt

Instantiate a new workbook, generate a worksheet, and popultae cells using explicit coordinate mapping. The save() method must be called to persist the binary output.

import xlwt
from xlwt import Workbook

output_book = Workbook()
data_sheet = output_book.add_sheet("Locations")

entries = [
    (1, 0, "Station A"), (2, 0, "Station B"), (3, 0, "Station C"),
    (4, 0, "Station D"), (5, 0, "Station E"),
    (0, 1, "Zone 1"), (0, 2, "Zone 2"), (0, 3, "Zone 3"),
    (0, 4, "Zone 4"), (0, 5, "Zone 5")
]

for r, c, val in entries:
    data_sheet.write(r, c, val)

output_book.save("exported_locations.xls")

Visual styling is applied by defining an XFStyle object through the easyxf syntax parser. Attach the style object as an optional parameter during the write operation.

styled_workbook = Workbook()
design_sheet = styled_workbook.add_sheet("Metrics")

highlight_format = xlwt.easyxf(
    "font: bold on, color white; "
    "pattern: pattern solid, fore_colour navy;"
)

design_sheet.write(0, 0, "ANALYSIS RESULT", highlight_format)
styled_workbook.save("styled_metrics.xls")