Re: Formula and 255 character limit

[John Machin]

On Jan 31, 12:46 am, Bob Farrell <robertanthonyfarr...@gmail.com>
wrote:
> Hi, I'm trying to do the following:
>
> Formula('HYPERLINK("%s"; "Foo")' % (url,))
>
> My URL is rather long and I'm receiving this error:
> Exception: String longer than 255 characters
>
> Which emanates from xlwt.UnicodeUtils:74 inside upack1()
>
> The description of this function is "# Same as upack2(), but with a
> one-byte length field." so clearly the restriction of 255 chars is there
> for a reason.
>
> My question is: is there a way to create the Formula as I require ? There
> is no possibility to shorten the URL.

Reason: Open more recent Excel (2007 in my case, not in "compatibility
mode"). In cell A1, type in a string of length say 10. In A2, type
=hyperlink(a1, "Foo"). Works. Change a1 to a string of length > 255.
Get #VALUE! in A2. Note carefully that it doesn't know that you intend
to save it as an XLS!

The only other way of doing it would be to insert hyperlink records.
These are bizarre/monstrous/arcane ... see the handle_hlink method in
xlrd's sheet.py. I'm not contemplating adding an insert_hlink facility
to xlwt any time soon.

[John Yeung]

On Wed, Jan 30, 2013 at 3:43 PM, John Machin <sjma...@lexicon.net> wrote:
>
> On Jan 31, 12:46 am, Bob Farrell <robertanthonyfarr...@gmail.com>
> wrote:
>>
>> The description of this function is "# Same as upack2(), but with a
>> one-byte length field." so clearly the restriction of 255 chars is there
>> for a reason.
>

> Reason: Open more recent Excel (2007 in my case, not in "compatibility
> mode"). In cell A1, type in a string of length say 10. In A2, type
> =hyperlink(a1, "Foo"). Works. Change a1 to a string of length > 255.
> Get #VALUE! in A2. Note carefully that it doesn't know that you intend
> to save it as an XLS!

In other words, strings longer than 255 are not even accepted by Excel
itself. Apparently not even XLSX-era Excel, for XLSX files (but see
below...).

On Wed, Jan 30, 2013 at 3:43 PM, John Machin <sjma...@lexicon.net> wrote:
>
> The only other way of doing it would be to insert hyperlink records.
> These are bizarre/monstrous/arcane ... see the handle_hlink method in
> xlrd's sheet.py. I'm not contemplating adding an insert_hlink facility
> to xlwt any time soon.

I don't know how one would induce Excel to create hyperlink records.
Does it convert hyperlink formulas automatically (i.e. writing
so-called "hyperlink records" instead of formula records that use the
HYPERLINK function)? Well, we've established that you can't enter a
string longer than 255 characters using that method.

How about right-clicking a cell and choosing the "Hyperlink..."
option, which opens up a fancy dialog? Well, when I tried that, it
*still* wouldn't let me enter a string longer than 255 characters
(though it helpfully added a slash automatically as the 256th
character).

So, if there is a way to enter a longer-than-255-character hyperlink
at all using the Excel application (I'm on 2010), I have no idea what
it is.

> On Jan 31, 12:46 am, Bob Farrell <robertanthonyfarr...@gmail.com>
> wrote:
>>
>> My question is: is there a way to create the Formula as I require ? There
>> is no possibility to shorten the URL.

It would seem Excel is determined not to allow you to do it, so it's
quite understandable that xlwt doesn't let you do it.

However, I have just tried writing a too-long hyperlink into an XLSX
file using openpyxl, and it seems to work! So if it is acceptable to
generate XLSX instead of XLS, you still might be able to do it. (I'm
not yet very knowledgeable about openpyxl, but do not be surprised if
there are things that xlwt can do which openpyxl cannot, even aside
from the fact that they cannot write each other's output file format.)

John Y.

[Bob Farrell]

Thanks both for your feedback on this - I did my own research in the meantime and came to the same conclusions. The only solutions I found were people who succeeded in appending to Hyperlinks using VBA. I'm assuming there's no support in xlwt to embed auto-executing VBA scripts in .xls files. :)

Anyway, I wrote up the results of my search for my project manager and wrote this off as being realistically impossible. I appreciate you taking the time to respond and confirm my suspicions, it's been a stressful time on this project and we're about to launch so good to know I did what I could to close this ticket.

Thanks again and, of course, thank you very much for xlwt - it has been consistently one of the most robust, reliable and easy-to-use libraries in our entire stack, and we use it extensively.

All the best,

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python-excel...@googlegroups.com.
To post to this group, send an email to python...@googlegroups.com.
Visit this group at http://groups.google.com/group/python-excel?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.