Home » File Formats » Spreadsheet_Excel_Writer » Bug #2159

[2004-08-18 19:03 UTC] yacine at winbond dot com

Description:
------------
The following code cause Excel to crash or report severe damage on the generated Excel file:
Setting SetVersion(8) causes Excel to complain of severe damage to the file. Not setting it, causes the cell to contain more than the string I wrote in it (and overloading the string functions in php.ini does not help)
Note that the chinese characters are passed in UTF-8 (editor settings under Zend) and codepage is 65001 for UTF-8

Reproduce code:
---------------
<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();
$workbook->_codepage = '65001';
//$workbook->SetVersion(8);
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$worksheet =& $workbook->addWorksheet("Hello");
$worksheet->write(0, 0, "Quarterly Profits for Dotcom.Com", $format_bold);
// While we are at it, why not throw some more numbers around
$worksheet->write(1, 0, "˜ÄŒv“I”N¾“ü (Thousands US$)", $format_bold);
$worksheet->write(1, 1, "Profit", $format_bold);
$worksheet->write(2, 0, "Q1");
$worksheet->write(2, 1, 0);
$worksheet->write(3, 0, "Q2");
$worksheet->write(3, 1, 0);
$workbook->send('test.xls');
$workbook->close();
?>

Expected result:
----------------
With SetVersion(8), file should open and display peoper content.
Without SetVersion(8), Cell A2 should contain the string "˜ÄŒv“I”N¾“ü (Thousands US$)" with no garbage past the parenthesis

Actual result:
--------------
When SetVersion(8) is used: Can't open the file in Excel.
When SetVersion(8) is not used, Cell A2 contains:
"˜ÄŒv“I”N¾“ü (Thousands US$)Dotcom.ComSe"

[2004-09-01 07:20 UTC] jacqui at vivamusica dot org

Excel 97/2000 is using UTF-16LE encoding.
Try changing multibyte internal enciding to UTF-16LE,
and using iconv to convert to UTF016LE.
So that should change to mb_strlen such as writeStringBIFF8() from worksheet class.
I can export Excel2000 file in Chinese characters and readable from any language of Windows OS.

[2004-09-07 19:43 UTC] yacine at winbond dot com

I have tried Jacqui's suggestions as follows:
Added mb_internal_encoding("UTF-16LE");
Converted the string using iconv("UTF-8","UTF-16LE",$mystring); // $mystring is in UTF-8
Changed strlen to mb_strlen in the writeBiff8 function.
I still got the same problem.
Changed mb_strlen to strlen in the writeBiff8 function and turned on string function overloading in php.ini
I still got the same problem.

[2005-02-09 03:24 UTC] pear-bug-2159 at ryandesign dot com

When using the defaulte BIFF5 writer, I believe the
Excel file is assumed to be in the Windows-1252 charset,
and when using BIFF8, it's assumed to be in UTF-16LE, so
that explains any problems you had in your initial
tests.

But there are also bugs in the BIFF8 writer, which I
believe I've fixed in a patch I posted in bug 1572:

http://pear.php.net/bugs/bug.php?id=1572

I modified your posted code to not set the codepage but
instead set the version to 8 and the charset to UTF-8
using the method supplied by my patch, and it works for
me. Give it a try.

[2005-11-08 03:54 UTC] xnoguer at php dot net

This bug has been fixed in CVS.

If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET).

If this was a problem with the pear.php.net website, the change should be live shortly.

Otherwise, the fix will appear in the package's next release.

Thank you for the report and for helping us make PEAR better.

As pear-bug-2159 at ryandesign dot com says, check http://pear.php.net/bugs/bug.php?id=1572 . You should use
setInputEncoding('utf-8');

[2005-11-10 22:02 UTC] yacine at winbond dot com

Thanks a bunch for the hard work!