Creating a hyperlink in a cell 🔗

[alemangui]

Hello, I can't seem find any documentation on how to add a hyperlink to a cell.

I've also tried looking for a post-process hook to see if I can manually add it with openpyxl but no success.

The only work around I could come up with is to make a custom renderer in which I add the hyperlinks, but this seems overkill and quite klunky (I'm loading the bytes, modifying the workbook, and then converting back to bytes) :

class HyperlinkXLSXRenderer(XLSXRenderer):
    def render(self, data, media_type=None, renderer_context=None):
        excel_data = super().render(data, media_type, renderer_context)
        workbook = load_workbook(filename=BytesIO(excel_data))

        for sheet in workbook.worksheets:
            for row in sheet.iter_rows(min_row=2): # Skip header
                row[0].hyperlink = "https://example.com"

        # Save back to bytes
        output = BytesIO()
        workbook.save(output)
        return output.getvalue()

Am I missing something obvious ? Is this adding a hyperlink a feature worth developing or documenting? How about a post-process hook?

Thanks!

[browniebroke]

Good question, I don't think it's supported at the moment. Here are the DRF field types that we map currently:

drf-excel/drf_excel/renderers.py

Lines 379 to 392 in d58604f

	if isinstance(field, BooleanField):
	return XLSXBooleanField(boolean_display=self.boolean_display, **kwargs)
	elif isinstance(field, (IntegerField, FloatField, DecimalField)):
	return XLSXNumberField(**kwargs)
	elif isinstance(field, (DateTimeField, DateField, TimeField)):
	return XLSXDateField(**kwargs)
	elif (
	isinstance(field, ListField)
	or isinstance(value, Iterable)
	and not isinstance(value, str)
	):
	return XLSXListField(list_sep=self.list_sep, **kwargs)
	return XLSXField(**kwargs)

Which makes me wonder which DRF field are you expecting to use to get a hyperlink cell? Feels like a URLField might be suitable, but what should we use for the label? What type of API would you expect to have to configure such field?

[alemangui]

Thanks for your reply. Good question. I'm not an expert on drf-excel's inner working, but I was thinking of two options :

1. Post-processing hook
   My first reflex would be to add a post-processing hook after the excel file has been generated to eventually allow its modification before it goes to the Response object, perhaps in XLSXFileMixin ? (Although it could also be in the XLSXRenderer) It could be something along the lines of DRF's perform_create which is usually overriden for custom behaviour. This would not only allow users to add hyperlinks but to also customize pretty much anything with openpyxl.

2. Hyperlink kwarg in XLSXField
   Another option could be to do something along the lines of your current get_* functions (like get_row_color). Currently class XLSXField seems to accept certain cell-level parameters (like cell_style, which is taken from the view's column_data_styles). Would it be possible to add a get_cell_hyperlink(self, instance, column) which could be read on _drf_to_xlsx_field and sent to XLSXField as part of the currently sent kwargs? This is much less flexible than the previous suggestion, but it could me more in line with the other functions if you don't think allowing full customization via a hook is desireable.

[FlipperPA]

We haven't added hyperlink support, because we use OpenPyXL under the hood to generate the xlsx file. One of my biggest regrets is not abstracting the layer which creates the .xlsx file from the start!

There's some info here on how to create hyperlinks in OpenPyXL: https://stackoverflow.com/questions/39077661/adding-hyperlinks-in-some-cells-openpyxl

TL;DR:

wbook.active['A8'].hyperlink = "http://www.espn.com"
wbook.active['A8'].value = 'ESPN'
wbook.active['A8'].style = "Hyperlink"

We might be able to do a regex match on cell contents, and if it matches a URL pattern, automatically fill in the hyperlink... but that is a lot of overhead processing for this feature. It'll significant slow down the render of every other spreadsheet. Maybe a separate Mixin, XLSXHyperlinkMixin, to give the option to the developer?