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:
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.
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)