Description: ------------ I'm including MS Excel export using this class into phpMyAdmin and I would really welcome, if it would support different charsets (at least unicode).

In converting a client's CRM system to UTF-8 we found it necessary to fix some problems preventing Spreadsheet_Excel_Writer 0.8 from working properly with Unicode strings. We also provide a way for application writers to send data to the PEAR module in any encoding, provided iconv is installed. We haven't tested all functionality and can't guarantee this will work for you, but it works for us. Here's the patch: http://test.michel-consulting.de/ryan/ spreadsheet_excel_writer_utf8.patch.bz2 Before you apply it, change Workbook.php from DOS to Unix linefeeds. Here's how to use it to write a Unicode Excel file: <?php require_once 'Spreadsheet/Excel/Writer.php'; // Some sample UTF-8 data. You'd use your real data // of course. $utf_8_data = @pack('H*', 'c485c3b3c5bcc487c582c499c59b'); $workbook = new Spreadsheet_Excel_Writer(); // Use BIFF8 (which supports Unicode) instead of // the default BIFF5. $workbook->setVersion(8); // Inform the module that our data will arrive as UTF-8. // (Use any other iconv-supported encoding here as desired.) $workbook->setBIFF8InputEncoding('utf-8'); $worksheet =& $workbook->addWorksheet('Hello'); $worksheet->write(0, 0, $utf_8_data); $workbook->send('out.xls'); $workbook->close(); ?> Background to our modifications: We looked to the Spreadsheet::WriteExcel PERL module on which this PEAR class is based. They show how to write Excel files with Unicode strings, and it works. The PERL module requires input to be in UTF-16BE encoding. The PEAR module requires UTF-16LE encoding, but even so, files we generated with it didn't work in Excel. We found the one-byte difference between the output generated by the PEAR and PERL modules and corrected it. We also found a difference in the computation of string length in this function. The PERL code counts the number of bytes in the UTF-16 string and divides by two, giving the number of characters. The PEAR code however just takes the number of bytes. We fixed this so that the correct character count is now computed. We briefly examined all other occurrences of strlen() in the module, and believe that this is the only place where it was used improperly. Because Spreadsheet_Excel_Writer makes extensive use of strlen() and related PHP functions, you'll probably have to make sure that you do *not* use the automatic mbstring overrides for these functions, or if you do, your mb internal encoding must be a single-byte charset like iso -8859-1. For our fix to the string length computation above, we use the mb_strlen() function if it's available, otherwise we use the built-in function and divide by two. This can serve as an example for how all other uses of standard PHP string functions in the PEAR module could be replaced so as to provide compatibility with servers where the automatic mbstring overrides are enabled. We also add a new function to both worksheets and workbooks -- setBIFF8InputEncoding(), with which the application author can indicate the charset in which the data will arrive. iconv() is used to convert incoming data to the required UTF-16LE format. Note that this only has effect when the BIFF version is set to 8 with $workbook- >setVersion().

Hi Michael, I have difficulties on patching it. could we have that patched Workbook.php? thx. Bobby

We had a similar requirement (to use Greek in an Excel file produced with PHP) and applied your patch. Although it seemed to work fine at first, in some cases the xls files are broken. Here's a test case: <?php require_once '../include/excel/Writer.php'; $workbook = new Spreadsheet_Excel_Writer(); $workbook->setVersion(8); $workbook->setBIFF8InputEncoding('iso-8859-7'); $workbook->send('listusers.xls'); $worksheet =&$workbook->addWorksheet('List of Users'); // data $a = "τττττττττττττττση ΝΙΦΟΡΑΤΟΥΠάνουΔΗΜΗΤΡΙΟΣΕβελίνα"; $l = strlen($a); $r=0; while ($r < 1000) { $langtest = substr($a, ($r % $l), rand(10,20)); $worksheet->write($r+1, 0, $langtest); $r++; } $workbook->close(); ?> Also, MS Excel says that the file is damaged, while OpenOffice opens it, but has many cells containing the message "*** ERROR IN SST ***" and garbage characters after line 600 or so. Do you have any suggestions? Thanks!

Sorry, the following test code had some raw ISO-8859-7 Greek characters that got converted to character references... Please use the following just after the "data" comment: $a="\364\364\364\364\364\364\364\364\364\364\364\364\364\364\364\363". "\347\040\315\311\326\317\321\301\324\317\325\320\334\355\357\365". "\304\307\314\307\324\321\311\317\323\305\342\345\353\337\355\341";

Hi to all, the link for the patch to be able to use charset utf-8 is a protected area. Does somebody have the patch or even the patched workbook. I`d apreciate your info. Thanks in advance. Stefan

Hi to all, I need the patch urgent, plz send it to my email. I´ll be thankfull forever........... thanks so mucho josant

Would you please to tell me how to use the patch file.. Thanks very much..

I've patched the files. Works for me. No warranties!! http://bruse.com/patched.tar.gz

Anybody help urgently !! I had successfully used the writer to convert my database UTF-8 data (includes both Trad. & Simp. Chinese) to UTF-16LE and write out an Excel file, whereas I found strange problem that I spent much time and cannot find another way to write result .... the strange problem is ... sometimes I could write to the Excel very successfully, whereas I have another database table having 500 records, it cannot write Excel (even it writes to the file w/ file size say 60K but cannot open).... when I tried to select 100 records, it can ... I originally thought it was due to strange characters and try each entry ... but then when I use the same select statment but added 'order by', the Excel again has file size but cannot open .... I then tried to narrow down to select say, 30 records, w/ 'order by' statement, the Excel can read then ... as I'm not that advanced in coding, I really think I could not figure out why and how to fix. Anybody could help ?? Thanks a Million!!

I commited the fix from pear-bug-1572 at ryandesign dot com to cvs, and it seems to work ok, but I would really apreciate it if someone else could test the fix with his own data and charset, and tell me the results.

I got the cvs version. It works fine till the excel gets big. I did some tests with about 5000 records wich resulted in Excel (XP) starting to complain about: - The file being damaged and/or - Not enough resources to open the file (on a P4 3Ghz 1GB Mem) The excel opens fine if I don't use worksheet->setInputEncoding('utf-8') (except for the special characters of course)

I'm using the latest version from CVS. I'm generating a simple 3 columns excel file from a database table. Data in table is UTF8 encoded, and currently contains mixed texts with english and cyrilic characters. The test contains 175 rows. There is only one cell which contains a mixture of latin and non-latin characters. It is show fine in excel, but two rows down, the file breaks. It shows "*** ERROR IN SST ***" in OO and empty cells in Excel (which also complains about broken file). Any ideas why the non-latin characters show up correctly + two more rows afterwards, and then without reason things break ?

mstavrev, do you have sample code for reproducing that behavior?

Hi, I'm sorry for not publishing enough information for you to further investigate. Here: http://www.it-academy.bg/temp/test_scripts.rar you can find two almost identical test scripts with a common set of data which triggers the problem. The first script test_log.php simply calls setVersion(8) and setInputEncoding('UTF-8'). The produced file from the test dataset breaks at line 40 (Excel line 40, counting from 1). The second script which uses the same data, is written in such a way, that strings are manually converted to UTF16-LE before calling writeString. Of course, before such calls setInputEncoding('UTF-16LE') is called. This second example, which should be in fact equivalent to the first, breaks the Excel file at line 44 instead of 40. The test data is a three column array. The first column contains a datetime stamp. The second a UTF8 encoded hexademically packed textual message, and the third - a UTF8 encoded hexademically packed optional note message, which can be an empty string. In fact all UTF8 messages are pure ASCII (so, in fact there will be no difference between their ASCII and UTF8 representation), except for line 27 (counting from 1), which contains a word written in cyrilic. The trouble I have is not with this 27 line - it shows exactly as expected in both Excel and OO, the file breaks some 13-15 lines later. I've tested moving this 27nd line after the 40-44 line barier of the test input data, with no luck. Can we be hitting some php internal bug (possibly in mbstring or in iconv libraries ?). I'm using PHP 4.4.1 on a windows PC running Windows 2003. I'm calling the test scripts from the command line like this: php -f test_log.php > test.html and php -f test_log2.php > test2.html Note: Both will generate an Exel file called test.xls, overwriting the old one. To verify the correctness of the data itself, both test scripts also output the same information in HTML.

Hi, I have also this problem. I need to create an Excel spreadsheet with Thai into it. With a few lines (about 40), it works. But with more than 3000, it doesn't work. I put Thai in only ONE cell, not a row or column, only one cell and it doesn't work. I tried setVersion(8) and setInputEncoding("UTF-8") and nothing works...

I had the same problem with unicode in excel files. There was only a little problem with _calculateSharedStringsSizes() and _storeSharedStringsTable() functions within the Workbook.php. They did not support unicode strings. I have made some changes according to the Spreadsheet::WriteExcel PERL module, so just try to replace them with these ones (sorry for the huge code listing, but it might be quite helpful ...I hope :o) If you will have still some problems, feel free to write me Miso /** * Calculate * Handling of the SST continue blocks is complicated by the need to include an * additional continuation byte depending on whether the string is split between * blocks or whether it starts at the beginning of the block. (There are also * additional complications that will arise later when/if Rich Strings are * supported). * * @access private */ function _calculateSharedStringsSizes() { /* Iterate through the strings to calculate the CONTINUE block sizes. For simplicity we use the same size for the SST and CONTINUE records: 8228 : Maximum Excel97 block size -4 : Length of block header -8 : Length of additional SST header information -8 : Arbitrary number to keep within _add_continue() limit = 8208 */ $continue_limit = 8208; $block_length = 0; $written = 0; $this->_block_sizes = array(); $continue = 0; foreach (array_keys($this->_str_table) as $string) { $string_length = strlen($string); $headerinfo = unpack("vlength/Cencoding", $string); $encoding = $headerinfo["encoding"]; $split_string = 0; // Block length is the total length of the strings that will be // written out in a single SST or CONTINUE block. $block_length += $string_length; // We can write the string if it doesn't cross a CONTINUE boundary if ($block_length < $continue_limit) { $written += $string_length; continue; } // Deal with the cases where the next string to be written will exceed // the CONTINUE boundary. If the string is very long it may need to be // written in more than one CONTINUE record. while ($block_length >= $continue_limit) { // We need to avoid the case where a string is continued in the first // n bytes that contain the string header information. $header_length = 3; // Min string + header size -1 $space_remaining = $continue_limit - $written - $continue; /* TODO: Unicode data should only be split on char (2 byte) boundaries. Therefore, in some cases we need to reduce the amount of available */ $align = 0; # Only applies to Unicode strings if ($encoding == 1) { # Min string + header size -1 $header_length = 4; if ($space_remaining > $header_length) { # String contains 3 byte header => split on odd boundary if (!$split_string && $space_remaining % 2 != 1) { $space_remaining--; $align = 1; } # Split section without header => split on even boundary else if ($split_string && $space_remaining % 2 == 1) { $space_remaining--; $align = 1; } $split_string = 1; } } if ($space_remaining > $header_length) { // Write as much as possible of the string in the current block $written += $space_remaining; // Reduce the current block length by the amount written $block_length -= $continue_limit - $continue - $align; // Store the max size for this block $this->_block_sizes[] = $continue_limit - $align; // If the current string was split then the next CONTINUE block // should have the string continue flag (grbit) set unless the // split string fits exactly into the remaining space. if ($block_length > 0) { $continue = 1; } else { $continue = 0; } } else { // Store the max size for this block $this->_block_sizes[] = $written + $continue; // Not enough space to start the string in the current block $block_length -= $continue_limit - $space_remaining - $continue; $continue = 0; } // If the string (or substr) is small enough we can write it in the // new CONTINUE block. Else, go through the loop again to write it in // one or more CONTINUE blocks if ($block_length < $continue_limit) { $written = $block_length; } else { $written = 0; } } } // Store the max size for the last block unless it is empty if ($written + $continue) { $this->_block_sizes[] = $written + $continue; } /* Calculate the total length of the SST and associated CONTINUEs (if any). The SST record will have a length even if it contains no strings. This length is required to set the offsets in the BOUNDSHEET records since they must be written before the SST records */ $tmp_block_sizes = array(); $tmp_block_sizes = $this->_block_sizes; $length = 12; if (!empty($tmp_block_sizes)) { $length += array_shift($tmp_block_sizes); # SST } while (!empty($tmp_block_sizes)) { $length += 4 + array_shift($tmp_block_sizes); # CONTINUEs } return $length; } /** * Write all of the workbooks strings into an indexed array. * See the comments in _calculate_shared_string_sizes() for more information. * * The Excel documentation says that the SST record should be followed by an * EXTSST record. The EXTSST record is a hash table that is used to optimise * access to SST. However, despite the documentation it doesn't seem to be * required so we will ignore it. * * @access private */ function _storeSharedStringsTable() { $record = 0x00fc; // Record identifier $length = 0x0008; // Number of bytes to follow $total = 0x0000; // Iterate through the strings to calculate the CONTINUE block sizes $continue_limit = 8208; $block_length = 0; $written = 0; $continue = 0; // sizes are upside down $tmp_block_sizes = $this->_block_sizes; // $tmp_block_sizes = array_reverse($this->_block_sizes); # The SST record is required even if it contains no strings. Thus we will # always have a length # if (!empty($tmp_block_sizes)) { $length = 8 + array_shift($tmp_block_sizes); } else { # No strings $length = 8; } // Write the SST block header information $header = pack("vv", $record, $length); $data = pack("VV", $this->_str_total, $this->_str_unique); $this->_append($header . $data); /* TODO: not good for performance */ foreach (array_keys($this->_str_table) as $string) { $string_length = strlen($string); $headerinfo = unpack("vlength/Cencoding", $string); $encoding = $headerinfo["encoding"]; $split_string = 0; // Block length is the total length of the strings that will be // written out in a single SST or CONTINUE block. // $block_length += $string_length; // We can write the string if it doesn't cross a CONTINUE boundary if ($block_length < $continue_limit) { $this->_append($string); $written += $string_length; continue; } // Deal with the cases where the next string to be written will exceed // the CONTINUE boundary. If the string is very long it may need to be // written in more than one CONTINUE record. // while ($block_length >= $continue_limit) { // We need to avoid the case where a string is continued in the first // n bytes that contain the string header information. // $header_length = 3; // Min string + header size -1 $space_remaining = $continue_limit - $written - $continue; // Unicode data should only be split on char (2 byte) boundaries. // Therefore, in some cases we need to reduce the amount of available // space by 1 byte to ensure the correct alignment. $align = 0; // Only applies to Unicode strings if ($encoding == 1) { // Min string + header size -1 $header_length = 4; if ($space_remaining > $header_length) { // String contains 3 byte header => split on odd boundary if (!$split_string && $space_remaining % 2 != 1) { $space_remaining--; $align = 1; } // Split section without header => split on even boundary else if ($split_string && $space_remaining % 2 == 1) { $space_remaining--; $align = 1; } $split_string = 1; } } if ($space_remaining > $header_length) { // Write as much as possible of the string in the current block $tmp = substr($string, 0, $space_remaining); $this->_append($tmp); // The remainder will be written in the next block(s) $string = substr($string, $space_remaining); // Reduce the current block length by the amount written $block_length -= $continue_limit - $continue - $align; // If the current string was split then the next CONTINUE block // should have the string continue flag (grbit) set unless the // split string fits exactly into the remaining space. // if ($block_length > 0) { $continue = 1; } else { $continue = 0; } } else { // Not enough space to start the string in the current block $block_length -= $continue_limit - $space_remaining - $continue; $continue = 0; } // Write the CONTINUE block header if (!empty($this->_block_sizes)) { $record = 0x003C; $length = array_shift($tmp_block_sizes); $header = pack('vv', $record, $length); if ($continue) { $header .= pack('C', $encoding); } $this->_append($header); } // If the string (or substr) is small enough we can write it in the // new CONTINUE block. Else, go through the loop again to write it in // one or more CONTINUE blocks // if ($block_length < $continue_limit) { $this->_append($string); $written = $block_length; } else { $written = 0; } } } }

thanks for the latest code-correction. the above code of "Miso" is working correct with setVersion(8) and setInputEncoding("UTF-8") in my case. before with just a few lines it worked, but with more than 500, it showed up with "ERROR IN SST" with openoffice calc. hopefully the code of "Miso" will be integrated soon. thx, tobias

Thanks for Miso's patch. I had spent several days effort and try to find bugs in my program but with no luck. Now it just works perfectly. Thanks the great work of PEAR too.

Miso a lot of thank's this pathc really works!! We're fighting whith this issue a lot of time and nothing else worked. xavier will you include this code in the next release?

Anyone has that patch saved somewhere? I could not get it from any of links provided in comments. Everything's protected or does not exist anymore.

just copy and paste code from above comment (Michal Fapso's post, wrap, so you need to modify it), replace the code in workbook.php or you can get a no-wrap code from this article: http://blog.teatime.com.tw/post/1/111

I've tried to copy-paste Miso's code with no luck. However, I found the patch file, so I'm posting it here, in case somebody would need it (sorry for the long post, but at least it won't get lost from a file repository :) ... diff -ru ./original/Spreadsheet/Excel/Writer/Workbook.php ./patched/Spreadsheet/Excel/Writer/Workbook.php --- ./original/Spreadsheet/Excel/Writer/Workbook.php Tue Feb 8 23:13:47 2005 +++ ./patched/Spreadsheet/Excel/Writer/Workbook.php Tue Feb 8 22:54:08 2005 @@ -301,6 +301,11 @@ $this->_country_code = $code; } + var $_biff8_input_encoding = 'UTF-16LE'; + function setBIFF8InputEncoding($encoding) { + $this->_biff8_input_encoding = $encoding; + } + /** * Add a new worksheet to the Excel workbook. * If no name is given the name of the worksheet will be Sheeti$i, with @@ -341,6 +346,8 @@ $this->_str_table, $this->_url_format, $this->_parser); + $worksheet->setBIFF8InputEncoding($this->_biff8_input_encoding); + $this->_worksheets[$index] = &$worksheet; // Store ref for iterator $this->_sheetnames[$index] = $name; // Store EXTERNSHEET names $this->_parser->setExtSheet($name, $index); // Register worksheet name with parser diff -ru ./original/Spreadsheet/Excel/Writer/Worksheet.php ./patched/Spreadsheet/Excel/Writer/Worksheet.php --- ./original/Spreadsheet/Excel/Writer/Worksheet.php Tue Feb 8 22:40:08 2005 +++ ./patched/Spreadsheet/Excel/Writer/Worksheet.php Tue Feb 8 22:54:08 2005 @@ -1531,13 +1531,25 @@ return($str_error); } + var $_biff8_input_encoding = 'UTF-16LE'; + function setBIFF8InputEncoding($encoding) { + if ($encoding != 'UTF-16LE' && !function_exists('iconv')) { + $this->raiseError("Using an input encoding other than UTF-16LE requires PHP support for iconv"); + } + $this->_biff8_input_encoding = $encoding; + } + function writeStringBIFF8($row, $col, $str, $format = 0) { - $strlen = strlen($str); + if ($this->_biff8_input_encoding != 'UTF-16LE') { + $str = iconv($this->_biff8_input_encoding, 'UTF-16LE', $str); + } + + $num_chars = function_exists('mb_strlen') ? mb_strlen($str, 'UTF-16LE') : (strlen($str) / 2); $record = 0x00FD; // Record identifier $length = 0x000A; // Bytes to follow $xf = $this->_XF($format); // The cell format - $encoding = 0x0; + $encoding = 0x1; $str_error = 0; @@ -1546,7 +1558,7 @@ return -2; } - $str = pack('vC', $strlen, $encoding).$str; + $str = pack('vC', $num_chars, $encoding).$str; /* check if string is already present */ if (!isset($this->_str_table[$str])) {

I think you don't need the patch for 0.9.0 version, it already support encoding. If you didn't change the function from Michal Fapso's post, it only work for small excel file.

Thanks Miso! this works perfectly for me in Hebrew with UTF-8 encoding.

Thanks Miso. I spent 2 days trying to fix this and never thought it was a bug with the package itself. The code he supplied works. You just need to replace the two functions that he mentioned with the ones he supllied. You will find those inside Workbook.php I was using 0.9.1 and I STILL needed this fix. Common guys, please add this in :)

Amazing work, Miso! Thank you very much! When I approached this bug I even offered money to the original authors to fix this. They never even bothered to answer. :-/ Thanks again & Best regards

Great work Miso. Everything is working perfect. Here's a link to download the patched Workbook.php http://bugs.gentoo.org/attachment.cgi?id=136412 (Patched according Miso's post) Hopefully, this will be integrated soon. UTF-8 is default charset on nearly every new installtion!

I have a real problem patching the default Spreadsheet_Excel_writer's code with the patch provided at http://test.michel-consulting.de/ryan/ spreadsheet_excel_writer_utf8.patch.bz2 Specifically, the patch changes correctly the 'Workbook.php' file, but does not succeed to change the 'Worksheet.php' file, it's returning this error: "patch -b Worksheet.php spreadsheet_excel_writer_utf8.patch patching file Worksheet.php Hunk #1 FAILED at 1531. Hunk #2 FAILED at 1558. 2 out of 2 hunks FAILED -- saving rejects to file Worksheet.php.rej" because of that, when using php, the script returns the error: "Call to undefined method Spreadsheet_Excel_Writer_Worksheet::setBIFF8InputEncoding() in /usr/share/pear/Spreadsheet/Excel/Writer/Workbook.php on line 353" Is there a different patch to apply that changes correctly the Worksheet.php file ? Or how can I solve this problem ? I need this code for exporting data from mysql into excel (reports mainly) that is also able to export utf8 text. Thank you, Emil.

Hi, First of all great job on the Excel_Writer Package. Really does the thing. Secondly an even greater job and many thanks to Miso for the Workbook.php file patch. With polish 'ogonki' in UTF it's been giving me an enormous pain in the backbone for the last couple of days. The one really uncool thing is that the great patch is still not integrated. Had to lose two days myself before I finally found this remedy. Don't get me wrong, absolutely no hard feelings or anything but integrating this into the Writer could really save a lot of people some serious time and hair ;) Thanks again for the package, Thanks again Miso for the bug fix - you the Man! :D Cheerz, John

I find there's a setInputEncoding method provided after the patch. So if you're using the current release , you do not need the patch indeed. Code example from the manual at http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-worksheet.setinputencoding.php <code> <?php require_once 'Spreadsheet/Excel/Writer.php'; $workbook = new Spreadsheet_Excel_Writer('test.xls'); $workbook->setVersion(8); $worksheet =& $workbook->addWorksheet('International worksheet'); $russian = "\xD0\xBF\xD0\xBE\xD0\xBA\xD0\xB0"; $greek = "\342\345\353\355\341"; $worksheet->setInputEncoding('ISO-8859-7'); $worksheet->write(0, 0, $greek); $worksheet->setInputEncoding('utf-8'); $worksheet->write(1, 0, $russian); $workbook->close(); ?> </code> But there's another issue about the sheetname encoding. Just can not use multibytes characters for sheetnames. Anyone know how and where to convert the sheetname?

Fengbo, the problem is that setVersion(8) breaks Excel 10 / X. Miso's patch works very well for me. Thank you very much.

Michal Fapso's patch works =) Thanks very much !

I wanted to thank Michal for his work on the Spreadsheet_Excel_Writer PHP PEAR class correcting the Excel CONTINUE block handling for multi-byte encoded documents. I know it has been nearly 3 years since he posted the fix, but I am grateful for it (and the subsequent formatted repost by Tommy Wu at http://blog.teatime.com.tw/post/1/111 ). I figured out on my own that the CONTINUE block was off, but it hadn't occurred to me to check for corrective logic in the Perl version of that library. I'm surprised that nearly 3 years on, this patch has not been incorporated into the code trunk. Best Wishes, -Matt Dunn

Has been a while that this bug has been discussed.... But still fighting the same issue: I still have the problem that Excel 12 on Mac crashes when I set the setVersion(8). Tried to apply the patch. But in vain. Anybody out there who found a solution? Thanks a lot!

Thank you for taking the time to report a problem with the package. Unfortunately you are not using a current version of the package -- the problem might already be fixed. Please download a new version from http://pear.php.net/packages.php If you are able to reproduce the bug with one of the latest versions, please change the package version on this bug report to the version you tested and change the status back to "Open". Again, thank you for your continued support of PEAR.

I use the version 0.92 and I still have the problem that excel crashes when I set the following parameter: $this->workbook->setVersion(8); $this->worksheet->setInputEncoding('utf-8'); With version 7 it works - but in this case I have problems with special chars like umlaute. Is there a solution for this problem?

Bug #14203 has a patch attached