Generating Excel Spreadsheets with Māori Macrons in PHP

January 9th, 2008 by Ewen Cumming

After recently working on an application to generate Microsoft Excel documents using a PHP script, I ran into trouble with the PEAR package “Spreadsheet Excel Writer” which didn’t correctly display UTF-8 characters (Māori macrons in this case). Here is a fix!

You just need to replace the Workbook.php file in the Spreadsheet Excel Writer package with this patched version of the file. For more information on the problem and the patch check out the comments on the bug report page (most of the good stuff is right near the bottom).

Then your spreadsheet generation code would look something like:

$workbook = new Spreadsheet_Excel_Writer();

// Send HTTP headers to tell the browser what’s coming
$workbook->send(“file.xls”);

$workbook->setVersion(8);
$worksheet =& $workbook->addWorksheet(“My worksheet”);
$worksheet->setInputEncoding(“UTF-8″);

// .. add content to spreadsheet here

$workbook->close();

This worked for me with version 0.9.0 of the package and it should also work with 0.9.1. I’d expect the fix to be included in upcoming releases of the package, but until then the above patch works great.

Links


Slashdot Digg Reddit del.icio.us Facebook Technorati Google StumbleUpon

1 Star2 Stars3 Stars4 Stars5 Stars (151 votes, average: 2.97 out of 5)

Tags: , , , ,

16 Responses to “Generating Excel Spreadsheets with Māori Macrons in PHP”

  1. Anonymous says:

    Do we have to call them “Maori macrons”? They’re just macrons!

  2. Pretzels and Cake » PHP: UTF-8 and Spreadsheet Excel Writer says:

    [...] Spreadsheet Excel Writer is a handy tool to generate MS Excel files with php. But it has some trouble handling utf-8 sources. Luckily Research e-Labs describes a simple solution: After recently working on an application to generate Microsoft Excel documents using a PHP script, I… [...]

  3. bytepirate says:

    thanks, that saved me a lot of time :)

  4. caleb says:

    Thank you for this !

    It is too bad the project is not being updated anymore its a very nice and simple way of doing xls output

  5. caleb says:

    This might fix the display problem but now the xls crashes excel with some strange resource error.
    I though this is supposed to fix this too according to what it says in bug comments but it doesnt.

    Any suggestions ?

  6. Ewen says:

    Hi Caleb,

    That sounds like the behaviour produced if the functions in Workbook.php aren’t patched - it still works but breaks if the spreadsheets are large (over somewhere like 500 rows).

    Can you confirm that the copy of Workbook.php you’re using is definitely the patched one?

    Thanks,
    Ewen

  7. Noah says:

    Caleb, didn’t you forget to do this:
    $workbook->setVersion(8);

  8. carter says:

    Thank you for this! Very helpful.

  9. Austin Bynum says:

    how would i go about patching the class.writeexcel_workbook.inc.php file in the same way?

  10. Ewen says:

    Hi Austin,

    In the Spreadsheet_Excel_Writer PEAR package I’ve used there is no class.writeexcel_workbook.inc.php file.

    Which package are you working with?

    Thanks,
    Ewen

  11. Captain Sumo says:

    This was becoming a very annoying problem for me. And you’ve just solved it. Many thanks!

    Our Russian customers will be happy!

  12. ap says:

    Thank you! Too bad the Spreadsheet Writer project seems to be dead in the water.

  13. Mick says:

    This doesn’t solve all the problems, I create an Excel file with multiple sheets and some formatting. The UTF-8 problem is fixed, but Excel produces an error and after clicking fix in Excel all my formatting is gone. So I guess we’re on our way to solve this problem, but we’re not there yet.

  14. airween says:

    I have problems too after I patched Workbook.php. If I set the name of worksheet which contains non-ascii chars (eg: latin2), name of worksheet displayed as “bad-utf8-encoded”, eg: “Keresési eredmények listája”.
    And if I wish to write a Note-type cell to worksheet, it will be empty - it contains nothing. (you mean: $worksheet->writeNote($row, $col, $note)).

    Any suggestion?

    Thank you:

    a.

  15. Paul says:

    You can also achieve this by outputting an html document (with UTF8 doctype) and sending it with an excel header (header(”Content-Type: application/vnd.ms-excel”); in PHP) and .xls file extension (so it’s handled by excel upon download)

    Excel then sees the file as a worksheet and the table cells as worksheet cells.

    I’ve been using this wee trick for years without any dramas and it sidesteps installing PEAR or working with hinky spreadsheet libraries.

    Paul

  16. Superk says:

    Thanks, very helpful.
    works only for 0.9.0

Leave a Comment





Is rain wet or dry?