Python scripts for basic formatting of Excel tabular data

!Python scripts for basic formatting of Excel tabular data.md

Python scripts for basic formatting of Excel tabular data

Requires openpyxl Python library

On RHEL or Fedora:

sudo dnf install python3-openpyxl

bold-header

Make the column labels in the header row bold.

Notes:

• Skips sheets that end in "-SQL". (I use such sheets to hold SQL SELECT statements, which are not tabular data. Adjust or remove as needed.)

autofit-columns

Adjust every populated column to approximately fit its contents.

Notes:

• Excel’s true AutoFit operation is not available through openpyxl, but this script closely reproduces it by measuring the longest value in each column and setting the column width accordingly.
• The maximum_width=80 limit prevents a single cell containing a huge CLOB, SQL statement, or long description from making a column absurdly wide. Adjust or remove that limit as needed.

autofit-columns

#!/usr/bin/env python3

import argparse
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter


def displayed_length(value: object) -> int:
    """Estimate the number of characters needed to display a cell value."""
    if value is None:
        return 0

    # Handle cells containing multiple lines.
    return max(len(line) for line in str(value).splitlines())


def autofit_worksheet(
    worksheet,
    *,
    padding: int = 2,
    minimum_width: int = 8,
    maximum_width: int = 120,
) -> None:
    """Adjust every populated column to approximately fit its contents."""

    for column_cells in worksheet.iter_cols():
        maximum_length = 0

        for cell in column_cells:
            # Ignore non-anchor cells belonging to merged ranges.
            if cell.__class__.__name__ == "MergedCell":
                continue

            maximum_length = max(
                maximum_length,
                displayed_length(cell.value),
            )

        if maximum_length == 0:
            continue

        width = maximum_length + padding
        width = max(minimum_width, min(width, maximum_width))

        column_letter = get_column_letter(column_cells[0].column)
        worksheet.column_dimensions[column_letter].width = width


def autofit_workbook(filename: Path) -> None:
    workbook = load_workbook(filename)

    for worksheet in workbook.worksheets:
        print(f"Adjusting sheet: {worksheet.title}")
        autofit_worksheet(worksheet)

    workbook.save(filename)
    print(f"Saved: {filename}")


def main() -> None:
    parser = argparse.ArgumentParser(
        description="Adjust Excel column widths to fit their contents."
    )
    parser.add_argument(
        "files",
        nargs="+",
        type=Path,
        help="Excel workbook files to modify in place",
    )

    args = parser.parse_args()

    for filename in args.files:
        if not filename.is_file():
            print(f"File not found: {filename}")
            continue

        if filename.suffix.lower() not in {".xlsx", ".xlsm"}:
            print(f"Skipping unsupported file: {filename}")
            continue

        try:
            autofit_workbook(filename)
        except Exception as error:
            print(f"Failed to process {filename}: {error}")


if __name__ == "__main__":
    main()

bold-header

#!/usr/bin/env python3

import sys
import warnings
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.styles import Font

def main() -> None:
    if len(sys.argv) != 2:
        sys.exit(f"Usage: {sys.argv[0]} FILE.xlsx")

    filename = Path(sys.argv[1])

    if not filename.is_file():
        sys.exit(f"File not found: {filename}")

    with warnings.catch_warnings():
        warnings.filterwarnings(
            "ignore",
            message="Workbook contains no default style.*",
            category=UserWarning,
            module="openpyxl.styles.stylesheet",
        )
        workbook = load_workbook(filename)

    for worksheet in workbook.worksheets:
        if worksheet.title.endswith("-SQL"):
            continue

        for cell in worksheet[1]:
            cell.font = Font(
                name=cell.font.name,
                size=cell.font.size,
                bold=True,
                italic=cell.font.italic,
                color=cell.font.color,
            )

    workbook.save(filename)


if __name__ == "__main__":
    main()