horrid_example_of_xls_export.php

<?php
use PHPExcel;
use PHPExcel_Cell as Cell;
use PHPExcel_IOFactory as IOFactory;
use PHPExcel_Style_Alignment as Alignment;
use XMod\DataTable;

class XLS {

    public $datatable;

    public function __construct($datatable) {
        $this->datatable = $datatable;
    }

    public function render() {
        $this->datatable->getResponse()->output(function() {
            if (!class_exists('PHPExcel')) {
                require_once DataTable\ROOT.'/dependencies/PHPExcel.php';
            }

            if ($this->datatable->download === true ||
                    $this->datatable->download === null) {
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="' . $this->datatable->getName() . '.xls"');
                header('Cache-Control: max-age=0');
            }

            // Create new PHPExcel object
            $excel = new PHPExcel();

            $sheet = $excel->setActiveSheetIndex(0);
            $style = $sheet->getDefaultStyle();
            $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

            $i = 0;
            foreach ($this->datatable->getHeaders() as $header) {
                $sheet->setCellValueByColumnAndRow($i, 1, $header);
                $style = $sheet->getStyleByColumnAndRow($i, 1);
                $style->getFont()->setBold(true);
                $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getColumnDimensionByColumn($i)->setAutoSize(true);
                $i++;
            }

            // Current row number
            $rowCount = 2;
            // Output data in chunks to increase speed
            $chunkSize = 5000;
            $data = [];
            foreach ($this->datatable->getIterator() as $i => $row) {
                $result = [];
                foreach ($row as $value) {
                    $result[] = $value->format('xls');
                }
                // Format the row
                $data[] = $result;
                if (sizeof($data) === $chunkSize) {
                    // Output the chunk to the spread sheet
                    $sheet->fromArray($data, null, 'A' . $rowCount);
                    $rowCount += $chunkSize;
                    $data = [];
                }
            }
            // Output any remaining data
            $sheet->fromArray($data, null, 'A' . $rowCount);
            $rowCount += sizeof($data);


            // Output footers
            foreach ($this->datatable->getFooters() as $row) {
                $xlsRow = [];
                foreach ($row as $value) {
                    $xlsRow[] = $value->format('csv');
                }
                $sheet->fromArray($xlsRow, null, 'A' . $rowCount++);
            }

            $writer = IOFactory::createWriter($excel, 'Excel5');
            $writer->setPreCalculateFormulas(false);
            $writer->save('php://output');
        });
    }

}