[1036] Extracting hyperlink information from an Excel file

alex_bn_lee / 2024-07-18 / 原文

Certainly! Extracting hyperlink information from an Excel file (specifically .xlsx format) in Python can be done using the openpyxl library. Let’s dive right in:

Using openpyxl:

First, make sure you have the openpyxl library installed. If not, you can install it using pip:
```
pip install openpyxl
```

Now, let’s assume you have an Excel file named yourfile.xlsx with hyperlinks. Here’s how you can extract the hyperlink URLs:

import openpyxl

# Load the workbook
wb = openpyxl.load_workbook('yourfile.xlsx')

# Choose the specific worksheet (e.g., 'Sheet1')
ws = wb['Sheet1']

# Example: Get the hyperlink target from cell B2
try:
    hyperlink_url = ws.cell(row=2, column=2).hyperlink.target
    print(f"Hyperlink URL in B2: {hyperlink_url}")
except AttributeError:
    print("Cell B2 does not contain a hyperlink.")

# You can adjust the row and column indices as needed.
# Remember to handle exceptions if a cell doesn't have a hyperlink.

Replace 'Sheet1' with the actual sheet name in your Excel file, and adjust the row and column indices accordingly.

Remember to handle exceptions (like the AttributeError in the openpyxl example) to gracefully handle cases where cells don’t contain hyperlinks.

Feel free to adapt these examples to your specific use case, and let me know if you need further assistance! 📊🔗🐍 Learn more¹²³⁴⁵

In openpyxl, the location is row=2, column=5. Start from 1 based on the excel info.
In pandas, the location is row=0, column=4. Start from 0 based the data table.