Home » File Formats » Spreadsheet_Excel_Writer » Bug #6108

[2005-11-29 16:13 UTC] walen at mimuw dot edu dot pl

Description:
------------
I've got a code that requires outputting many strings
in the custom input encoding.
Unfortunatelly the module returns broken XLS files.

For the test script some strings are broken
(random characters).

I think that there is an error in the
function _storeSharedStringsTable().

Test script:
---------------
$xls = new Spreadsheet_Excel_Writer();
$xls->setVersion(8);
$xls->setCountry(48);

$xls_s = $xls->addWorksheet("test");
$xls_s->setInputEncoding('ISO-8859-2');

for($i=0;$i<500;$i++) {
$str="aceąćę".$i;
$xls_s->writeString($i,0,$str);
}

$xls->send('test.xls');
$xls->close();

Expected result:
----------------
500 lines:
"aceąćę".$i; (0<=i<500)

Actual result:
--------------
400 lines:
"aceąćę".$i; (0<=i<=400)

then garbage

[2005-12-05 23:40 UTC] fpascutti at tdcom dot fr

Doing a bit of debugging (with my absencde of knowledge of the Excel file format), I narrowed the bug down to _storeSharedStringsTable() as pointed before.
It seems that you get an error after the first time you "cross the CONTINUE boundary".
That is, the bug occurs after the first string that does not pass this test:
// We can write the string if it doesn't cross a CONTINUE boundary
if ($block_length < $continue_limit) {
/* snip */
}

Hope it will help...

[2006-02-12 15:54 UTC] daniel dot burckhardt at sur-gmbh dot ch

I ported the ommitted aligment marked by
TODO: Unicode data should only be split on char (2 byte)
from
http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.16/lib/Spreadsheet/WriteExcel/Workbook.pm
to _calculateSharedStringsSizes and _storeSharedStringsTable and that seems to do the trick.

[2006-02-12 16:15 UTC] daniel dot burckhardt at sur-gmbh dot ch

Sorry, that was premature. Still croaking on longer outputs :-(

[2006-03-24 14:40 UTC] danielpeake at hotmail dot com

I've also enountered this bug and this is what I've found:

- The bug happens when you use setInputEncoding to set the input encoding (obviously in a BIFF8 file). As far as I can tell this is because it only stores strings as UTF-16LE if you set the input encoding.

- This being the case, it doesn't matter what characters (unicode/ascii) you input, it's simply the fact that they are being converted to, and stored as, UTF-16LE.

- Also, when I tested it, there seemed to be magic numbers of characters that let you store as many lines as you liked (I think 20, 25, possibly 50-something..)

- I also tracked down the TODO parts in calculateSharedStringsSizes and _storeSharedStringsTable and ported the missing bits from the PERL script. As Mr Burckhardt states, these made no difference whatsoever.

To conclude, Spreadsheet_Excel_Writer can't currently store lots of unicode strings in an Excel sheet :(