Search code examples
pythonexcelopenpyxlxlsxwriter

Insert or attach one excel workbook into another excel workbook using python


I have generated multiple excel workbook using openpyxl. Now I need to create a consolidated kind of excel workbook, which would be having all the above created excel workbooks inside it as clickable.

For example: consolidated excel workbook will look like:

enter image description here

Where if I click on abc.xlsx, then abc.xlsx excel workbook should open up as a separate excel. Just like we manually attach the xlsx files using hyperlink. May be by same method or differently I need to attach one excel workbook into another excel workbook programmatically using python.

Twist: I need to give abc.xlsx, sdf.xlsx with relative path also. Because one can give all these excels to somebody by zipping then the path of abc.xlsx, sdf.xlsx will change.


Solution

  • It seems all you do want is hyperlinks;

    Add to the cell and ensure the target is a relative path from where the Excel sheet resides.

    from openpyxl import load_workbook
    from openpyxl.worksheet.hyperlink import Hyperlink
    
    xlfile = "foo.xlsx"
    sheet_name = 'Sheet1'
    
    wb = load_workbook(xlfile)
    ws = wb[sheet_name]
    
    ### Column A text
    ws['A1'].value = 'abc'
    
    ### Hyperlink
    cell_coord = 'B1'
    hyperlink = Hyperlink(
                          target='..\<path>\abc.xlsx',  #  Relative path to Excel file
                          ref=cell_coord,
                          )
    ws[cell_coord].value = "abc.xlsx"  # Text to appear in cell
    
    ### Add hyperlink to cell
    ws[cell_coord].hyperlink = hyperlink
    
    ### Save file
    wb.save(xlfile)