0

I have an Excel spreadsheet that contains thousands of rows that represent 'sites'

One of the columns is named "Archive" and most of its cells, but not all, contain a relative hyperlink to a folder that's stored on my local machine

For example, a typical cell might have the following text displayed:

0130-0143

and contains an underlying hyperlink that points to:

Sites\London\0130-0143

So when you click the cell an Explorer window opens the archive folder for site number 0130-0143

I wish to import this spreadsheet into a dataframe df = pd.read_excel('Sites.xlsx')

and then export it again (after having updated the site list it with any new sites or changes) df.to_excel("Updated_Sites.xlsx", index=False)

During the above process the new spreadsheet contains all the display text for the Archive column (0130-0143 and so on) but not the local hyperlinks

Is it even possible to include the local hyperlinks when creating a dataframe?

TiTo
  • 833
  • 2
  • 7
  • 28
Create Tech
  • 1
  • That might help: https://stackoverflow.com/questions/35325799/pandas-read-excel-with-hyperlink – TiTo Jun 19 '20 at 10:08
  • I think you'll need to use xlwings or openpyxl not sure if pandas has the capability to this without adding on a bunch of custom functionality yourself! that said, if you find out post an answer, sounds like an interesting use-case – Umar.H Jun 19 '20 at 10:46
  • Apologies for the late reply. Thank you both for pointing me in the right direction in regards to using the openpyxl module. This code copies the target link from one sheet to another and also formats the cell sheet_wb2.cell(row = j, column = 11).hyperlink = sheet_wb1.cell(row = i, column = 8).hyperlink.target sheet_wb2.cell(row = j, column = 11).value = sheet_wb1.cell(row = i, column = 8).value sheet_wb2.cell(row = j, column = 11).style = "Hyperlink" – Create Tech Jul 02 '20 at 16:18

0 Answers0