sql2excel.php

<?php
    /**
     * Runs the query and generates an Excel file from the results.
     *
     * @param $query string Regular SQL query.
     * @param $fileName string Name of the file which should be passed to browser, without extension.
     */
    public static function sql2excel($query, $fileName = 'data')
    {
        global $wpdb;
        $result = $wpdb->get_results($query, ARRAY_A);
        require_once(ABSPATH . "/wp-content/plugins/tschachsolutions/libs/PHPExcel/Classes/PHPExcel.php");

        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("ICIS Tschach Solutions");
        $i    = 0;
        $cell = 'A1';

        foreach ($result as $x) { //Creating header row
            foreach ($x as $k => $y) {
                $column = PHPExcel_Cell::stringFromColumnIndex($i);
                $cell   = $column . '1';
                $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
                $objPHPExcel->getActiveSheet()->setCellValue($cell, $k);
                $i++;
            }
            break;
        }
        $objPHPExcel->getActiveSheet()->setAutoFilter('A1:' . $cell);
        $objPHPExcel->getActiveSheet()->getStyle('A1:' . $cell)->applyFromArray(["font" => ["bold" => true]]);

        $ii = 0;
        foreach ($result as $x) { //Filling data rows
            $i = 0;
            foreach ($x as $y) {
                $cell = PHPExcel_Cell::stringFromColumnIndex($i) . ($ii + 2);
                $objPHPExcel->getActiveSheet()->setCellValue($cell, $y);
                $i++;
            }
            $ii++;
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment;filename='$fileName.xlsx'");
        header('Cache-Control: max-age=0');
        // If we're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If we're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }