Generating Excel files with PHP

Posted at July 10, 2007

I’m on this project at work where we need to create an excel file and send it via email or ftp to a recipient. The normal answer to this is to create a comma separated file and name it .xls and excel will work it out. However, that only works if you open it directly through an http request and are able to set the http headers correctly. I’m not going to elaborate on the requirements but they want an excel file generated and sent to them. They don’t want to and can’t download the file themselves.

Naturally I went to the almighty Google for the answer. There are a few excel writers written out there. I found a tutorial on using PEAR Spreadsheet_Excel_Writer and an MS-Excel Stream Handler class to mention two solutions.

I found one piece of code floating around numerous places that I fell for. I am not sure about the original source for this code though. Extremely simple.

function xlsBOF() {
    return pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
}

function xlsEOF() {
    return pack("ss", 0x0A, 0x00);
}

function xlsWriteNumber($Row, $Col, $Value) {
    return pack("sssss", 0x203, 14, $Row, $Col, 0x0)
      .pack("d", $Value);
}

function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    return pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L)
      .$Value;
}

I liked it because it did what I wanted with a minimum of fuss. I was happy until I realized I don’t understand it enough to be able to set the character set. Right now question marks appear in the texts instead of national Swedish characters as å, ä and ö.

PHP

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

One Response to “Generating Excel files with PHP”

Leave Comment

(required)

(required)