PHPExcel table export class

TableExport.md

class TableExport {
    private $head;
    private $body;
    private $sheet_title;
    private $sheet_subject;

    /**
     * TableExport constructor
     *
     * @param $head
     * @param $body
     * @param $sheet_title
     * @param $sheet_subject
     */
    public function __construct($head, $body, $sheet_title, $sheet_subject)
    {
        $this->head = $head;
        $this->body = $body;
        $this->sheet_title = $sheet_title;
        $this->sheet_subject = $sheet_subject;
    }

    /**
     * Generate PHPExcel object with the given data
     *
     * @return \PHPExcel
     * @throws \PHPExcel_Exception
     */
    public function generateExcel()
    {
        $objPHPExcel = new \PHPExcel();

        // If cell contains an array, set its content to null
        foreach ($this->body as $r => $row) {
            foreach ($row as $k => $cell) {
                if (is_array($cell)) {
                    $this->body[$r][$k] = "";
                }
            }
        }

        // Set Excel file properties
        $objPHPExcel->getProperties()->setTitle($this->sheet_title);

        // Default sheet is [0]
        $objPHPExcel->setActiveSheetIndex(0);

        $sheet = $objPHPExcel->getActiveSheet();

        $sheet->setTitle($this->sheet_title);

        // Set table header
        $sheet->fromArray(
            $this->head,
            "",
            'A3'
        );

        // Set table body
        $sheet->fromArray(
            $this->body,
            "",
            'A4'
        );

        return $objPHPExcel;
    }

    /**
     * Stylize the table before export
     *
     * @param $objPHPExcel
     * @return mixed
     */
    public function tableStyling($objPHPExcel)
    {
        $sheet = $objPHPExcel->getActiveSheet();

        $left = \PHPExcel_Cell::stringFromColumnIndex(count($this->head) - 1);
        $top = count($this->body) + 3;

        // -- fonts
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
        $objPHPExcel->getDefaultStyle()->getFont()->setSize(12);

        // -- wrap
        $objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);

        // -- zoom
        $sheet->getSheetView()->setZoomScale(75);

        // -- print setup
        $sheet->getPageSetup()->setFitToWidth(1);
        $sheet->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);

        // -- borders
        $style = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN
                )
            ),
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_TOP,
            ),
        );
        $sheet->getStyle("A3:$left$top")->applyFromArray($style);

        // -- auto size
        foreach(range(0, count($this->head) - 1) as $col) {
            $big = false;

            foreach($this->body as $b) {
                if(strlen($b[$col]) > 50 ) $big = true;
            }

            if($big) {
                $sheet->getColumnDimensionByColumn($col)->setWidth(50);
            } else {
                $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
            }
        }

        // -- header style
        // -- -- first row
        $sheet->mergeCells("A1:$left" . "1");
        $sheet->setCellValue('A1', $this->sheet_subject);
        $style = array(
            'font' => array(
                'bold' => true,
                'size' => 16,
            ),
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );
        $sheet->getStyle("A1")->applyFromArray($style);
        $sheet->getRowDimension("1")->setRowHeight(50);

        // -- -- third row
        $style = array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => \PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
            'fill' => array(
                'type' => \PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startcolor' => array(
                    'argb' => 'FFEEEEEE',
                ),
                'endcolor' => array(
                    'argb' => 'FFFFFFFF',
                ),
            ),
        );
        $sheet->getStyle("A3:$left" . "3")->applyFromArray($style);
        $sheet->setAutoFilter("A3:$left$top");
        $sheet->getRowDimension("3")->setRowHeight(30);

        return $objPHPExcel;
    }

    /**
     * Save generated PHPExcel object to file
     *
     * @param $objPHPExcel
     * @param $export_dir
     * @param $file_name
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function saveFile($objPHPExcel, $export_dir, $file_name)
    {
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($export_dir . "$file_name.xlsx");
        @chmod($export_dir . "$file_name.xlsx", 0664);

        $export_name = "$file_name.xlsx";
        $export_path = $export_dir . $export_name;

        $c = file_get_contents($export_path);
        file_put_contents($export_path, $c, LOCK_EX);

        header("Content-Description: File Transfer");
        header("Content-Type: " . mime_content_type($export_path));
        header("Content-Disposition: attachment; filename=" . $export_name);
        header("Content-Transfer-Encoding: binary");
        header("Expires: 0");
        header("Cache-Control: must-revalidate");
        header("Pragma: public");
        header("Content-Length: " . filesize($export_path));

        readfile($export_path);

        unlink($export_path);
    }
}