Research e-Labs

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

• http://pear.php.net/package/Spreadsheet_Excel_Writer - Spreadsheet Excel Writer - PEAR package homepage
• http://pear.php.net/bugs/bug.php?id=1572 - Description of Bug
• http://bugs.gentoo.org/attachment.cgi?id=136412 - Patched Workbook.php

(151 votes, average: 2.97 out of 5)

Tags: Developer Resources, macrons, Māori, Open Source, PHP

This entry was posted on Wednesday, January 9th, 2008 at 12:15 am under Developer Resources. You can leave a response, or trackback from your site.

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

1. Anonymous says:

   March 31st, 2008 at 11:57 am

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

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

   April 9th, 2008 at 2:11 am

   [...] 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:

   April 25th, 2008 at 12:47 am

   thanks, that saved me a lot of time

4. caleb says:

   June 18th, 2008 at 10:55 pm

   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:

   June 20th, 2008 at 9:49 pm

   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:

   June 26th, 2008 at 2:02 am

   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:

   September 12th, 2008 at 2:27 am

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

8. carter says:

   November 17th, 2008 at 11:27 pm

   Thank you for this! Very helpful.

9. Austin Bynum says:

   January 27th, 2009 at 6:59 am

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

10. Ewen says:

    January 27th, 2009 at 9:35 pm

    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:

    February 4th, 2009 at 2:05 am

    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:

    April 16th, 2009 at 9:09 pm

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

13. Mick says:

    April 16th, 2009 at 10:52 pm

    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:

    May 6th, 2009 at 8:57 pm

    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:

    June 9th, 2009 at 12:42 pm

    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:

    February 1st, 2010 at 11:00 pm

    Thanks, very helpful.
    works only for 0.9.0