7

I need to export data from php to Excel, and be able to format various items in the excel spreadsheet.

So far, the best library that I have found is PHPExcel. However, it seems very heavy, and somewhat slow. Granted, it is very powerful.

Is there anything a bit more lightweight and faster, that allows me to export to excel and be able to apply simple formatting (bold, alignment, borders)?

SteAp
  • 11,853
  • 10
  • 53
  • 88
JonoB
  • 5,801
  • 17
  • 55
  • 77
  • 3
    I answered this question just a week or so ago http://stackoverflow.com/questions/3930975/alternative-for-php-excel/3931142#3931142 – Mark Baker Oct 23 '10 at 20:50

2 Answers2

3

I just got done with this yesterday. Using PHPExcel, I had no problems reading in a "master" document with formatting, writing 20-100 rows of content, and saving off the file (I save it "to screen" for immediate download. While some people on the forums complained about speed and overhead, I'm pushing a lot of data its way and it doesn't have any problem at all doing what it advertises.

Note that somewhere I read to do styling in series as opposed to in loops when possible. For example, style a1:a50 as opposed to style->a1, style->a2 in a loop. Apparently, the two different scenarios have very different memory implications.

The only gotcha I found was a few quirks between outputting and reading Excel 2003 files. If you're working entirely in XLSX files, it should function exactly as documented.

bpeterson76
  • 12,918
  • 5
  • 49
  • 82
  • We always recommend applying styles to ranges rather than looping through individual cells... it's faster and uses less memory, and the outputted Excel file is smaller because of the shared styles. Another useful style trick is applyFromArray(), defining a set of style features (bold, colour, background colour, borders, etc) in an array and then applying them to cells in a single call rather than setting each element individually. – Mark Baker Oct 24 '10 at 09:33
  • Yep, I definately try format ranged instead of cells, but to be honest, there really isnt that much formatting in this sheet at all. At the moment, I'm exporting about 1000 rows and 6 columns, with some range formatting on 3 rows and 3 columns. It takes about 6 seconds (of which about 1 second is query time) to run on my localhost, which is usually a decent approximation of the server's speed. – JonoB Oct 24 '10 at 18:08
  • @JonoB - For writing your data to the PHPExcel object, have you looked at the PHPExcel_Worksheet's fromArray() method rather than setting each cell's value individually. This can be used with a Value Binder to force correct datatyping of cell values to speed up the process of building the workbook in memory. – Mark Baker Oct 25 '10 at 07:58
  • Having worked in an enterprise shop for a major international bank, I can tell you that 6 seconds or processing is nothing compared to the convoluted online systems in place for Credit Cards. We would have been happy with 36 seconds..... – bpeterson76 Oct 25 '10 at 14:01
  • Why is this the accepted answer when it doesn't answer the question of using something other than PHPExcel? – Bryan C. Sep 18 '20 at 22:02
  • @BryanC. the question has been edited a few times since 2010.... – bpeterson76 Sep 22 '20 at 15:52
2

Here is simple Excel file generation function, very fast and exactly .xls file.

$filename = "sample_php_excel.xls";
$data = array(
array("User Name" => "Abid Ali", "Q1" => "$32055", "Q2" => "$31067", "Q3" => 32045, "Q4" => 39043),
array("User Name" => "Sajid Ali", "Q1" => "$25080", "Q2" => "$20677", "Q3" => 32025, "Q4" => 34010),
array("User Name" => "Wajid Ali", "Q1" => "$93067", "Q2" => "$98075", "Q3" => 95404, "Q4" => 102055),
);
to_xls($data, $filename);

function to_xls($data, $filename){
$fp = fopen($filename, "w+");
$str = pack(str_repeat("s", 6), 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); // s | v
fwrite($fp, $str);
if (is_array($data) && !empty($data)){
    $row = 0;
    foreach (array_values($data) as $_data){
        if (is_array($_data) && !empty($_data)){
            if ($row == 0){
                foreach (array_keys($_data) as $col => $val){
                    _xlsWriteCell($row, $col, $val, $fp);
                }
                $row++;
            }
            foreach (array_values($_data) as $col => $val){
                _xlsWriteCell($row, $col, $val, $fp);
            }
            $row++;
        }
    }
}
$str = pack(str_repeat("s", 2), 0x0A, 0x00);
fwrite($fp, $str);
fclose($fp);
}

function _xlsWriteCell($row, $col, $val, $fp){
if (is_float($val) || is_int($val)){
    $str  = pack(str_repeat("s", 5), 0x203, 14, $row, $col, 0x0);
    $str .= pack("d", $val);
} else {
    $l    = strlen($val);
    $str  = pack(str_repeat("s", 6), 0x204, 8 + $l, $row, $col, 0x0, $l);
    $str .= $val;
}
fwrite($fp, $str);
}
Abid Ali
  • 679
  • 7
  • 10