Skip to content

Instantly share code, notes, and snippets.

@BrendonKoz

BrendonKoz/PHPExcelHelper.php

Created October 21, 2014 03:23
Show Gist options
  • Save BrendonKoz/68763189359401a82c6c to your computer and use it in GitHub Desktop.
Save BrendonKoz/68763189359401a82c6c to your computer and use it in GitHub Desktop.
Download ZIP
A helper class to make creating robust, but simple Excel workbooks with PHPExcel even easier.
Raw
PHPExcelHelper.php
<?php
// import (and instantiate) the required 3rd party autoload class for all required classes
require_once('./path_to/PHPExcel.php');
// Call a member method that automatically guesses and assigns cell properties (Date, Number, Text, etc.)
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
class ExcelHelper {
// Storage of default settings
public $settings = array(
'author' => '',
'lastModifiedBy' => '',
'title' => '',
'subject' => '',
'description' => '',
'keywords' => array(),
'category' => ''
);
private $defaultFilename = 'Untitled'; //.xslx will be appended automatically
private $converter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
public function __construct($options = array()){
$this->settings = array_merge($this->settings, $options);
$this->book = new PHPExcel();
$this->book->getProperties()->setCreator($this->settings['author'])
->setLastModifiedBy($this->settings['lastModifiedBy'])
->setTitle($this->settings['title'])
->setSubject($this->settings['subject'])
->setDescription($this->settings['description'])
->setKeywords(implode(' ', $this->settings['keywords']))
->setCategory($this->settings['category']);
}
public function addWorksheet($data, $sheetname = '', $autowidth = false){
if (empty($data)) return;
$temp = array();
$columns = array();
$temp = array_keys($data[0]);
foreach ($temp as $col) {
$columns[] = str_replace('_', ' ', $col);
}
unset($temp);
$this->book->createSheet();
$this->book->setActiveSheetIndex($this->book->getSheetCount()-1);
foreach ($columns as $key => $column) {
// I don't expect more than 26 fields in a table, if there are, '1' needs to increment here and below
$this->book->getActiveSheet()->setCellValue($this->converter[$key].'1', $column);
}
$this->book->getActiveSheet()->fromArray($data, NULL, 'A2');
$this->book->getActiveSheet()->getStyle('A1:'.$this->converter[count($data[0])-1].'1')->getFont()->setBold(true);
$this->book->getActiveSheet()->setAutoFilter($this->book->getActiveSheet()->calculateWorksheetDimension());
if ($sheetname) {
$this->book->getActiveSheet()->setTitle($sheetname);
}
// Autosizing of columns must be done after all data has been entered UNLESS you only want to initially see the column name
if ($autowidth) {
foreach ($columns as $key => $column) {
$this->book->getActiveSheet()->getColumnDimension($this->converter[$key])->setAutoSize(true);
}
}
}
public function render($filename = '') {
// If the filename is empty...
if (!$filename) {
$filename = $this->defaultFilename;
}
// Remove the first sheet created during instantiation, after adding our data sheets
$this->book->removeSheetByIndex(0);
// Make sure the first sheet in the workbook is the active sheet
$this->book->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($this->book, 'Excel2007');
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you'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($this->book, 'Excel2007');
$objWriter->save('php://output');
}
}
@BrendonKoz
Copy link
Author 

<?php

// --- ... include necessary files ... --- //

// Simplest example
$book = new ExcelHelper();
$book->addWorksheet(array(
    // Row 1 contains the associative array index key names
    array( // Row 2
        'Column_1' => 'A2 data',
        'Column_2' => 'B2 data',
        'Column_3' => 'C2 data'
    ),
    array( // Row 3
        'Column_1' => 'A3 data',
        'Column_2' => 'B3 data',
        'Column_3' => 'C3 data'
    )
));
$book->render();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment