Вот моя функция.
Код: Выделить всё
def create_master_list(excel_file):
print('Creating Master List')
wb = load_workbook(excel_file)
master_ws = wb.create_sheet(title='Master List')
# Initialize a flag to check if headers are added
headers_added = False
headers = []
# Iterate through each sheet in the workbook, skipping the first three tabs
for sheet_name in wb.sheetnames[3:]:
ws = wb[sheet_name]
# Convert the generator to a list
rows = list(ws.iter_rows(values_only=True))
# Skip sheets without tables
if not rows:
continue
# Check if the header has already been added
if not headers_added and rows:
headers = rows[0] # Assume first row is the header
master_ws.append(headers)
headers_added = True
# Append the rest of the rows, skipping the header
added_rows = set()
for row in rows[1:]:
if row not in added_rows: # Add only if row hasn't been added
master_ws.append(row)
added_rows.add(tuple(row)) # Convert to tuple to make it hashable
# Define the table range using get_column_letter to convert column indices to letters
last_col = get_column_letter(len(headers))
table_range = f"A1:{last_col}{master_ws.max_row}"
# Create a table
tab = Table(displayName="MasterTable", ref=table_range)
# Add a default style with striped rows
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
# Add the table to the worksheet
master_ws.add_table(tab)
# Auto-adjust column size
min_column_width = 8 # Define a minimum column width
for column_cells in master_ws.columns:
max_length = max(len(str(cell.value)) if cell.value is not None else 0 for cell in column_cells)
adjusted_width = max((max_length + 2), min_column_width) # Ensure minimum column width
master_ws.column_dimensions[get_column_letter(column_cells[0].column)].width = adjusted_width
# Ensure left alignment and freeze panes
left_alignment = Alignment(horizontal='left')
for row in master_ws.iter_rows(min_row=1, max_row=master_ws.max_row):
for cell in row:
cell.alignment = left_alignment
master_ws.freeze_panes = 'B2'
# Save the workbook with the new Master List sheet
wb.save(excel_file)
print("Master List Created")
Подробнее здесь: https://stackoverflow.com/questions/790 ... nd-creates
