Python is an efficient programming language that can easily handle various file formats, including Excel.
In Excel, hyperlinks are a very useful feature that allow users to quickly jump to specific cells in other worksheets or other files.
In this article, we will introduce how to use Python to handle hyperlinks in Excel, and how to link hyperlinks with corresponding worksheets.
1. Install necessary libraries
Before using Python to process Excel files, some necessary libraries need to be installed.
Among them, the most commonly used library is openpyxl, which is a library for reading and writing Excel files.
Openpyxl can be installed with the following command:
pip install openpyxl
2. Read Excel file
In Python, you can use the load_workbook() function in the openpyxl library to open Excel files.
This function returns a Workbook object, which contains the contents of the entire Excel file.
You can use the following code to open an Excel file:
from openpyxl import load_workbook
# Open the Excel file
workbook = load_workbook('example.xlsx')
3. Traverse all hyperlinks in the Excel file
The Workbook object contains all worksheets and hyperlinks.
You can use the following code to iterate through all hyperlinks in an Excel file:
# traverse all hyperlinks in the Excel file
for sheet in workbook:
for row in sheet.iter_rows():
for cell in row:
if cell.hyperlink is not None:
print(cell. hyperlink. target)
In the above code, we first traverse all the worksheets in the Excel file, and then traverse each cell, if the cell contains a hyperlink, then output the target of the hyperlink.
4. Link the hyperlink to the corresponding worksheet
In Excel files, hyperlinks usually link to other sheets or cells.
Therefore, we need to link hyperlinks to corresponding worksheets so that users can quickly jump to other worksheets.
You can use the following code to achieve this functionality:
# link the hyperlink to the corresponding worksheet
for sheet in workbook:
for row in sheet.iter_rows():
for cell in row:
if cell.hyperlink is not None:
target = cell.hyperlink.target
if '!' in target:
# Handle the case of links to other worksheets
sheet_name, cell_name = target. split('!')
sheet_name = sheet_name.replace("'", "")
worksheet = workbook[sheet_name]
cell = worksheet[cell_name]
cell.hyperlink = cell.hyperlink.target
else:
# Handle the case of linking to other cells in the same worksheet
cell.hyperlink = '#' + target
In the above code, we first judge whether the hyperlink links to other worksheets.
If so, we decompose the target of the hyperlink into worksheet name and cell name, and use the workbook object to get the corresponding worksheet.
We then replace the target of the hyperlink with the value of the cell. If the hyperlink links to other cells in the same worksheet, replace the target of the hyperlink with the cell name preceded by a ‘#’ sign.
5. Save the Excel file
After processing the Excel file, the results need to be saved to a file.
Excel files can be saved using the save() function in the Workbook object.
The following code can be used to save the Excel file:
# Save the Excel file
workbook.save('example.xlsx')
Full code:
from openpyxl import load_workbook
# Open the Excel file
workbook = load_workbook('example.xlsx')
# Traverse all hyperlinks in the Excel file
for sheet in workbook:
for row in sheet.iter_rows():
for cell in row:
if cell.hyperlink is not None:
target = cell.hyperlink.target
if '!' in target:
# Handle the case of links to other worksheets
sheet_name, cell_name = target. split('!')
sheet_name = sheet_name.replace("'", "")
worksheet = workbook[sheet_name]
cell = worksheet[cell_name]
cell.hyperlink = cell.hyperlink.target
else:
# Handle the case of linking to other cells in the same worksheet
cell.hyperlink = '#' + target
# Save the Excel file
workbook.save('example.xlsx')
6. Summary
In this article, we introduced how to use Python to handle hyperlinks in Excel and link the hyperlinks to the corresponding worksheets.
To implement this functionality, you need to use the openpyxl library to read and write Excel files, and use the Worksheet object to access worksheets and cells.
Using Python to process Excel files can greatly improve work efficiency, especially when dealing with large amounts of data.
About the Python Study Guide
Learning Python well is good whether it is employment or sideline business to make money, but to learn Python, you still need to have a study plan. Finally, I will share with you a full set of Python learning materials, and give some help to those who want to learn Python!
Including: Python activation code + installation package, Python web development, Python crawler, Python data analysis, artificial intelligence, office automation and other learning tutorials. Take you to learn Python systematically from zero foundation!
Python learning routes in all directions
The route of all directions in Python is to organize the commonly used technical points of Python to form a summary of knowledge points in various fields. Its usefulness lies in that you can find corresponding learning resources according to the above knowledge points to ensure that you learn more comprehensively. (Collect at the end of the full set of tutorials)
Python learning video 600 collection
Watching the zero-based learning video is the fastest and most effective way to learn. Following the teacher’s ideas in the video, it is still very easy to get started from the basics to the in-depth.
Reminder: The space is limited, the folder has been packaged, and the way to get it is at the end of the article
Python70 Practical Practice Cases & Source Code
Optical theory is useless, you have to learn to follow along, and you have to do it yourself, so that you can apply what you have learned to practice. At this time, you can learn from some actual combat cases.
Python big factory interview information
We must learn Python to find a high-paying job. The following interview questions are the latest interview materials from Ali, Tencent, Byte and other first-tier Internet companies, and the authoritative answers are given by Ali. Answer, after reviewing this set of interview materials, I believe everyone can find a satisfactory job.
Python sideline part-time job route & amp;method
It is good to learn Python whether it is employment or sideline business to make money, but you still need to have a study plan to learn part-time orders.
This full version of the full set of Python learning materials has been uploaded. If you need it, you can scan the CSDN official certification QR code below or click the link to get it for free【 Guaranteed 100% Free】
Click to receive “CSDN Gift Package” for free: Python entry to advanced information & amp; actual combat source code & amp; part-time order receiving method safe link to receive for free
