Simple PhpSpreadsheet helper class

André Luiz
Nov 3 · 2 min read

I was working on an Excel data export from a small App made with WordPress and, since there are a lot of exports in this project I’m working on, I made a simple helper class that abstracts the usage of PhpSpreadsheet. This small class allows you to create an Excel file with as many tabs as you want.

PhpSpreadsheet is a continuation of the former PHPExcel library. Find more about it here: https://phpspreadsheet.readthedocs.io/en/latest/

First, you need to install the library using composer, by executing the following command:

composer require phpoffice/phpspreadsheet

The helper class:

<?php
require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class ExcelHelper
{
private $sheets = [];

function __construct() {
$this->sheets = [];
}

#region manipulate the sheets
public function create_sheet($name, array $header_columns_names) {

if(!array_key_exists($name, $this->sheets)) {
$this->sheets[$name] = [
'name' => $name,
'header_columns' => $header_columns_names,
'rows' => []
];
}
}

public function add_row_to_sheet($sheet_name, $row) {
if(array_key_exists($sheet_name, $this->sheets)) {
$this->sheets[$sheet_name]['rows'][] = $row;
}
}
#endregion

#region Generate the excel

public function download_excel($download_name)
{
$spreadsheet = new Spreadsheet();
$cont = 0;

foreach ($this->sheets as $key => $sheet) {

$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex($cont);
$spreadsheet->getActiveSheet()->setTitle($sheet['name']);
$spreadsheet->setActiveSheetIndex($cont);

// add the header
$spreadsheet->getActiveSheet()->fromArray($sheet['header_columns'], null, 'A1');
$spreadsheet->getActiveSheet()->fromArray($sheet['rows'], null, 'A2');

$cont++;
}

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$download_name.'.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


$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;

}

#endregion

}

Below an example of how to use it, pretty simple. Just import into your other classes or just include in your .php file.

$excel = new ExcelHelper();

// Spreadt sheet 1
$spreadsheet_name = 'Spreadsheet Test 1';
$excel->create_sheet($spreadsheet_name, ['Column 1', 'Column 2', 'Column 3']);
$excel->add_row_to_sheet($spreadsheet_name, ['Value 1', 'Value 2', 'Value 3']);
$excel->add_row_to_sheet($spreadsheet_name, ['Value 11', 'Value 22', 'Value 33']);

// Spreadt sheet 1
$spreadsheet_name = 'Spreadsheet Test 2';
$excel->create_sheet($spreadsheet_name, ['Name', 'Gender', 'City']);
$excel->add_row_to_sheet($spreadsheet_name, ['Carlos', 'M', 'Toronto']);
$excel->add_row_to_sheet($spreadsheet_name, ['Alice', 'F', 'Montreal']);

// Dowload the file
$excel->download_excel('test-file');

That’s it, simple and quick. You may want to add some formatting, data types, etc.. all of these is possible using PhpSpreadsheet, just look into the documentation: https://phpspreadsheet.readthedocs.io/en/latest/

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade