Feature request: read_excel to support hyperlinks split into label and hyperlink

[DocViper666]

Situation:
.xlsx file having a column with label and hyperlinks (see screenshot collage)
In Excel, label: Python (that has a hyperlink to i.e.: www.python.org)

Code Sample, a copy-pastable example if possible

data = pd.read_excel('Book1.xlsx')
column has just "Python" as content. Hyperlink information is lost.
(see screenshot)

Expected Output, Proposal:

Add an argument for example named hyperlinkparser to read_excel()
that offers options: "label", "hyperlink", "both"
label ... just the label (this is how it is handled now)
hyperlink ... just the hyperlink, drop the label
split ... created two separate columns for label and hyperlink each

with example it could look like:
| MyLinks | MyLinks_hyperlink
0 | Python | www.python.org

btw. openpyxl 2.4.+ has this feature now included (was broken in previous versions). Maybe this helps for implementation.

Thank you!

[TomAugspurger]

How difficult is to preprocess the file with xlrd or openpyxl, and pass the modified version (with the additional columns) to read_excel.? I haven't used either of those libraries extensively. If it's not too much code we could include it as a cookbook example.

[DocViper666]

Thanks for the answer.

What I have found as a workaround
(and please excuse clumsy coding style - I'm a python beginner):

# requires openpyxl version 2.4.0+

from openpyxl import load_workbook
wb = load_workbook(filename='input.xlsx')
ws = wb.get_active_sheet() # ws is now an IterableWorksheet

# iterate thru all cells and if hyperlink found attempt modification of cell
for row in ws.rows:
    for cell in row:
        try:
            if len(cell.hyperlink.target)  > 0:
                cell.value = "".join([cell.value,"|",cell.hyperlink.target])
                # Join cell.value and hyperlink target into string (optionally just assign the hyperlink.target to the cell.value
        except:
            pass

# save workbook to temp .xlsx (I could not manage to read from buffer...) .
wb.save("temp.xlsx")

# read with pandas 
data = pd.read_excel("temp.xlsx")

# take DataSeries and rsplit by "|" and expand to 2 columns
hyper = (data.MyLinks.str.rsplit("|", expand=True))

#set labels
hyper.columns=["Label","Hyperlink"]

# join them back to dataframe on index
data = data.join(hyper, how="left")

# done

Maybe this is helpful for somebody.
An integrated pandas function still would be nice.

Br,
Joe

[sinhrks]

One possible enhancement is to allow user funcs (passed via converters kwds) to accept excel Cell instances.

[DocViper666]

@sinhrks:
Thanks for the feedback. Would this allow to target the cell.value and also the cell.hyperlink.target ? Is this supported in the xlrd module that is used for reading the file?

@ALL: I have updated my workaround in case someone is looking for ideas.

@TomAugspurger : Feel free to used it in the cookbook if fitting

[sinhrks]

@DocViper666 The enhancement should allow something like:

pd.read_excel("xxx.xlsx", converters={"target_col_name": lambda x: str(x.value) + "|"+ str(x.hyperlink.target), raw=True)

As long as I know, xlrd also has a class corresponding to cell.

• http://www.lexicon.net/sjmachin/xlrd.html#xlrd.Cell-class

[dustinmichels]

Is anyone working on this? Are pandas maintainers open to pull requests? I would like to work on this, with a little bit of guidance.

[WillAyd]

Not aware of anyone working this. If I'm being honest I'm not entirely sure this is within the scope of pandas as it seems like a very niche use, but you are welcome to submit a PR if you find an easy way to implement

[dustinmichels]

Okay, thanks for your reply!

I came across a StackOverflow question about using pd.read_excel to read a document with hyperlinks, and the top answer said 'Pandas can't do it, but you can use openpyxl instead.'

It seems in some cases, Pandas already acts as a wrapper around openpyxl. For instance, when I try to use pandas.DataFrame.to_excel to save a DataFrame as an Excel file, I get a warning that I should install openpyxl first.

I thought perhaps an option to read an Excel file, while maintaining links, could work the same way?

[WillAyd]

The openpyxl reader is here:

pandas/pandas/io/excel/_openpyxl.py

Line 460 in d48306e

class _OpenpyxlReader(_BaseExcelReader):

get_sheet_data would be the method of most interest

[dustinmichels]

Thanks!! I'll see what I can do.

[srihari15]

Is this being worked on still? Would be very useful if someone could provide a closure. Thanks :)

[yadalik]

Quick monkey patching, without converters or anything like this, if you would like to treat ALL cells with hyperlinks as hyperlinks, more sophisticated way, I suppose, at least be able to choose, what columns treat as hyperlinked or gather data, or save somehow both data and hyperlink in same cell at dataframe, instead of this f'{cell.value}|||{cell.hyperlink.target}. And using converters, dunno. (BTW I played also with data_only, keep_links, did not helped, only changing read_only resulted ok, I suppose it can slow down your code speed).

P.S.: Works only with xlsx, i.e., engine is openpyxl
P.P.S.: If you reading this comment in the future and issue still Open, don't forget to see changes in _convert_cell and load_workbook at pandas.io.excel._openpyxl and update them accordingly.

import pandas
from pandas.io.excel._openpyxl import OpenpyxlReader
import numpy as np
from pandas._typing import FilePathOrBuffer, Scalar


def _convert_cell(self, cell, convert_float: bool) -> Scalar:
    from openpyxl.cell.cell import TYPE_BOOL, TYPE_ERROR, TYPE_NUMERIC
    # here we adding this hyperlink support:
    if cell.hyperlink and cell.hyperlink.target:
        return cell.hyperlink.target
        # just for example, you able to return both value and hyperlink,
        # comment return above and uncomment return below
        # btw this may hurt you on parsing values, if symbols "|||" in value or hyperlink.
        # return f'{cell.value}|||{cell.hyperlink.target}'
    # here starts original code, except for "if" became "elif"
    elif cell.is_date:
        return cell.value
    elif cell.data_type == TYPE_ERROR:
        return np.nan
    elif cell.data_type == TYPE_BOOL:
        return bool(cell.value)
    elif cell.value is None:
        return ""  # compat with xlrd
    elif cell.data_type == TYPE_NUMERIC:
        # GH5394
        if convert_float:
            val = int(cell.value)
            if val == cell.value:
                return val
        else:
            return float(cell.value)

    return cell.value


def load_workbook(self, filepath_or_buffer: FilePathOrBuffer):
    from openpyxl import load_workbook
    # had to change read_only to False:
    return load_workbook(
        filepath_or_buffer, read_only=False, data_only=True, keep_links=False
    )


OpenpyxlReader._convert_cell = _convert_cell
OpenpyxlReader.load_workbook = load_workbook

And after adding this above in your python file, you will be able to call df = pandas.read_excel(input_file)
After writing all this stuff it came to me, that maybe it would be easier and cleaner just use openpyxl by itself ^_^