ucsb-nlp-chang/federatedskill/excel-manipulation

import openpyxl
wb = openpyxl.load_workbook('file.xlsx')
ws = wb['SheetName']  # or wb.active

# List all sheets
print(wb.sheetnames)
# Read first few rows to understand layout
for row in ws.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)

# Example: Row 2 has week numbers, Row 4 has values
weeks = [ws.cell(row=2, column=c).value for c in range(2, 42)]
values = [ws.cell(row=4, column=c).value for c in range(2, 42)]

for row in ws.iter_rows(min_row=2, values_only=True):
    col_a, col_b, col_c = row[0], row[1], row[2]

wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Plan'
# Write headers
headers = ['Week', 'Value', 'Status']
for col, header in enumerate(headers, 1):
    ws.cell(row=1, column=col, value=header)
# Write data rows
for row_idx, data in enumerate(data_list, 2):
    for col_idx, value in enumerate(data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)
wb.save('output.xlsx')

from openpyxl.styles import Font, Alignment, Border, Side
# Bold headers
for col in range(1, num_cols + 1):
    ws.cell(row=1, column=col).font = Font(bold=True)
# Column width
ws.column_dimensions['A'].width = 15

wb_check = openpyxl.load_workbook('output.xlsx')
ws_check = wb_check['SheetName']
print(f'Rows: {ws_check.max_row}, Columns: {ws_check.max_column}')
print('Headers:', [ws_check.cell(row=1, column=c).value for c in range(1, ws_check.max_column + 1)])