How to copy cells in Excel like hyperlinks from one Book to another using Python?-CodePudding

I need to copy cells like hyperlinks from one Excel file to another. I can't find anything relating to this problem. I can copy cells values but it's not what I need.

I tried to modify some examples of coping cells from one book to another, but it wasn't success

CodePudding user response：

To copy the value in each cell from a source workbook (in this example 'foo1.xlsx') to a new workbook (destination workbook) and have the destination cells link back to the source cells

from openpyxl import load_workbook, Workbook
from openpyxl.worksheet.hyperlink import Hyperlink

source_path = "foo1.xlsx"
source_sheet = 'Sheet1'

source_wb = load_workbook(source_path)
source_ws = source_wb[source_sheet]

### Create a new workbook and worksheet to copy data to and rename the 
### sheet to 'Sheet1'
destination_wb = Workbook()
destination_ws = destination_wb.active
destination_ws.title = 'Sheet1'

### Loop thru the rows and cells in the source sheet
for row in source_ws.iter_rows():
    for source_cell in row:
        cell_coord = source_cell.coordinate
        ### Create hyperlink to source cell 
        hyperlink = Hyperlink(target=source_path,
                              ref=cell_coord,
                              location = f'{source_sheet}!{cell_coord}')
        ### Copy source cell value to the destination sheet
        destination_ws.cell(source_cell.row, source_cell.column).value = source_cell.value
        ### Update destination cell with hyperlink to source cell
        destination_ws.cell(source_cell.row, source_cell.column).hyperlink = hyperlink

### Save new workbook specifying file name
destination_wb.save(foo2.xlsx')

CodePudding user response：

This is the code to store hyperlink to a cell from a different file:

import os

from openpyxl import load_workbook, Workbook


def main():
    input_workbook_path = r"c:\excel_books\input book.xlsx"
    output_workbook_path = r"c:\excel_books\output book.xlsx"

    input_wb = load_workbook(input_workbook_path)
    output_wb = Workbook()

    sheet_in = input_wb["Sheet1"]
    sheet_out = output_wb["Sheet"]
    cell_index = "B12"
    anchor = "CLICK HERE"

    # =HYPERLINK("[c:\excel_books\input book.xlsx]Sheet1!B12","CLICK HERE")
    external_cell_link = f'=HYPERLINK("[{input_workbook_path}]{sheet_in.title}!{cell_index}", "{anchor}")'

    sheet_out["A2"].value = external_cell_link

    output_wb.save(output_workbook_path)


if __name__ == '__main__':
    main()

This code is for getting the value from a cell from a different file

import os

from openpyxl import load_workbook, utils, Workbook


def construct_link(workbook_absolute_path, sheet_name, cell_index):
    """
    The function onstructs full path to the cell in the external
    book, e.g. - ='c:\excel_books\[input book.xlsx]Sheet1'!C1
    """

    # Adding square brackets arround filename in the path.
    # Before - c:\excel_books\input book.xlsx
    # After - c:\excel_books\[input book.xlsx]
    filename = os.path.basename(workbook_absolute_path)
    dirname = os.path.dirname(workbook_absolute_path)
    full_path = os.path.join(dirname, f"[{filename}]")

    return f"={utils.quote_sheetname(full_path   sheet_name)}!{cell_index}"


def main():
    input_workbook_path = r"c:\excel_books\input book.xlsx"
    output_workbook_path = r"c:\excel_books\output book.xlsx"

    input_wb = load_workbook(input_workbook_path)
    output_wb = Workbook()

    sheet_in = input_wb["Sheet1"]
    sheet_out = output_wb["Sheet"]

    external_cell_link = construct_link(
        input_workbook_path,
        sheet_in.title,
        "C1")

    sheet_out["A2"].value = external_cell_link

    output_wb.save(output_workbook_path)


if __name__ == '__main__':
    main()

This link might be helpful - Control when external references (links) are updated