Generate XLS (Excel Spreadsheet) in PHP

If you ever need to generate an XLS (Excel Spreadsheet) from PHP, the following function will let you easily accomplish this. This is adapted from code I found online, but I think my function is a lot easier to use since you just pass in an associative array of data. The aforementioned site did not cite any source, so if you are the author of the original xls functions leave a comment and I’ll give you a cookie.

function arrayToXls($input) {
    // BoF
    $ret = pack('ssssss', 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);

    // array_values is used to ensure that the array is numerically indexed
    foreach (array_values($input) as $lineNumber => $row) {
        foreach (array_values($row) as $colNumber => $data) {
            if (is_numeric($data)) {
                // number, store as such
                $ret .= pack('sssssd', 0x203, 14, $lineNumber, $colNumber, 0x0, $data);
            } else {
                // everything else store as string
                $len = strlen($data);
                $ret .= pack('ssssss', 0x204, 8 + $len, $lineNumber, $colNumber, 0x0, $len) . $data;
            }
        }
    }

    //EoF
    $ret .= pack('ss', 0x0A, 0x00);

    return $ret;
}

A quick example of how to use it:

$input = array(
    array('Step', 'Action'),
    array(1, 'Collect Underpants'),
    array(2, '?'),
    array(3, 'Profit')
);

file_put_contents('lawl.xls', arrayToXls($input));
  1. Thanks! Let’s create a class with this code! =D

  2. Any method to set the name of the sheet?

  3. This was an awesome time-saver. And it worked superbly!!!
    Thank you for putting it together in an easy to use, and easy-to-understand format.

    Just a note, in your “Quick example of how to use it”, you are missing the leading ‘$’ character at the “input” array declaration. (Line 1).

  4. worked perfectly, thanks!

    the only problem I found was that Excel was not displaying correctly data encoded in UTF8, but using utf8_decode() solved it.

  5. This is great it is working fine and am going to use it on my website.

Leave a Reply

%d bloggers like this: