Extract URLs from hyperlinks in Excel using openpyxl and pandas

extract_hyperlinks_from_excel.py

import pandas as pd
import openpyxl


def _get_link_if_exists(cell) -> str | None:
    try:
        return cell.hyperlink.target
    except AttributeError:
        return None


def extract_hyperlinks_from_xlsx(
    file_name: str, sheet_name: str, columns_to_parse: list[str], row_header: int = 1
) -> pd.DataFrame:
    df = pd.read_excel(file_name, sheet_name)
    ws = openpyxl.load_workbook(file_name)[sheet_name]
    for column in columns_to_parse:
        row_offset = row_header + 1
        column_index = list(df.columns).index(column) + 1
        df[column] = [
            _get_link_if_exists(ws.cell(row=row_offset + i, column=column_index))
            for i in range(len(df[column]))
        ]
    return df

Thank you!

Excellent, thank you

Thanks!