How can I get around Excel's URL Limitation?

Question

I'm using the excellent xlsxwriter to build spreadsheets with Python. I've become aware of the issue where you can only write 40k or so URLS to a sheet before you start getting warned and the functionality is gated.

Is there a way to write the urls as strings in such a way that the Excel application will not interpret the url as a string on open (and therefore allow me to add unlimited urls)? I get that those URLs won't be clickable, but it's better than nothing in this case for me.

Claret

I take it breaking up the sheet into multiple sheets is not an option? – Necessarily

It's not preferred. I actually prefer that it be a non-clickable link (as for some reasons the links don't work properly anyway for this particular host). I feel like I should be able to write it as a string (maybe with num_format) so that it's copyable by the Excel user but so that Excel doesn't "know" it's a URL. – Claret

Answer 1

Excel's limit is around 65k urls per worksheet. XlsxWriter warns if you exceed that.

Is there a way to write the urls as strings in such a way that the Excel application will not interpret the url as a string on open (and therefore allow me to add unlimited urls)

XlsxWriter has a constructor option to turn off conversion of strings to urls:

workbook = xlsxwriter.Workbook(filename, {'strings_to_urls': False})

Slippage

Thank you. I'm using it with Pandas, which uses the constructor for me. Is there a way to change the setting after the instance exists (or, since I'm guessing that—as the author—you're familiar with what's going on under the hood with Pandas, I can easily modify that functionality when pandas is creating the instance)? – Claret

You can pass XlsxWriter contractor options via Pandas. Something like this: writer = pd.ExcelWriter(filename, engine='xlsxwriter', options={'strings_to_urls': False}) – Slippage

With the newest versions of pandas the syntax has changed to pd.ExcelWriter(filename, engine='xlsxwriter', engine_kwargs={'options': {'strings_to_urls': False}}) – Theaterintheround