dcsimg

Making the Switch from PhpExcel to PhpSpreadsheet

By Rob Gravelle

WEBINAR:
On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


The PhpExcel library that I recently wrote about in my Export Data from Your Website as an Excel Spreadsheet tutorial, has been officially deprecated and soon be marked as archived in Github. It has been superseded by the bigger and better PhpSpreadsheet library. Like PhpExcel, it's written in pure PHP that provides a set of classes that allow you to read from and write to spreadsheets. The difference is that PhpSpreadsheet can work with a variety of different spreadsheet file formats, including multiple versions of Excel as well as other formats such as Open Document Format/OASIS (.ods). Its creators chose to break compatibility with the old PhpExcel code base in order to dramatically improve code quality as well as include a wider variety of spreadsheet formats. In today's tutorial, we'll get acquainted with PhpSpreadsheet and learn how it both resembles and differs from its predecessor.

Installation

Including the PhpSpreadsheet in your project has changed. One could easily add the requisite PhpExcel files by including the top-level "Classes" folder and the PHPExcel.php main library interface file. Referencing it via the require_once at the top of your PHP file was all that was necessary to add the library. PhpSpreadsheet, on the other hand, employs the Composer dependency management tool to install it. This is not surprising, as dependency management tools are becoming more and more commonplace as the ever-increasing number of project dependencies have made dependency management tools vital in alleviating the burden of keeping dependencies organized and up-to-date.

Strongly inspired by node's npm and ruby's bundler, Composer is not a package manager in the same sense as Yum or Apt. Although it does deal with "packages" and/or libraries, it is a more pure dependency manager, as it manages dependencies on a per-project basis, installing them in a directory (usually named "vendor") inside your project. By default, it does not install anything globally. (It does however support a "global" project for convenience via the global command.)

For example, if our project resided in the web server's www/myproject directory, we would launch a command prompt, CD to the myproject directory, and issue the composer require phpoffice/phpspreadsheet command. Composer will then proceed to download all of the required files and place them within a folder named "vendor".

Coding Basics

The code side of PhpSpreadsheet has evolved since PhpExcel as well. Whereas the entry point class of PhpExcel - Classes/PHPExcel.php - reflected its namesake, PhpSpreadsheet includes the autoload.php file in the root of the vendor directory. It also utilizes some namespaces to simplify coding:

<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

require_once 'vendor/autoload.php';

Instantiating a New Spreadsheet Object

Not surprisingly, in the new library, the Spreadsheet() object replaces the PhpExcel() object:

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

Setting Document Properties

One thing that hasn't changed is the instance methods to set workbook and worksheet properties:

// Set workbook properties
$spreadsheet->getProperties()->setCreator('Rob Gravelle')
        ->setLastModifiedBy('Rob Gravelle')
        ->setTitle('A Simple Excel Spreadsheet')
        ->setSubject('PhpSpreadsheet')
        ->setDescription('A Simple Excel Spreadsheet generated using PhpSpreadsheet.')
        ->setKeywords('Microsoft office 2013 php PhpSpreadsheet')
        ->setCategory('Test file');

// Set worksheet title
$spreadsheet->getActiveSheet()->setTitle('Simple');

Inserting Data

Most of the worksheet methods also remain intact:

//Set active sheet index to the first sheet, 
//and add some data
$spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A1', 'This')
        ->setCellValue('B2', 'is')
        ->setCellValue('C1', 'a')
        ->setCellValue('D2', 'test.');

Redirecting Output to the Browser

With regards to outputting the spreadsheet to the browser, note that the PHPExcel_IOFactory has been renamed to simply "IOFactory" to remove the reference to the old library. Its createWriter() method also accepts the file extension ("Xls") rather than a description ("Excel2007").

Of course, generating the headers has not changed since header() is a native PHP function:

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


//old PhpExcel code:
//$writer = PHPExcel_IOFactory::createWriter($spreadsheet, 'Excel2007');
//$writer->save('php://output');

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

Here is the entire source code for the file that we worked on here today:

<?php

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

require_once 'vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set workbook properties
$spreadsheet->getProperties()->setCreator('Rob Gravelle')
        ->setLastModifiedBy('Rob Gravelle')
        ->setTitle('A Simple Excel Spreadsheet')
        ->setSubject('PhpSpreadsheet')
        ->setDescription('A Simple Excel Spreadsheet generated using PhpSpreadsheet.')
        ->setKeywords('Microsoft office 2013 php PhpSpreadsheet')
        ->setCategory('Test file');

//Set active sheet index to the first sheet, 
//and add some data
$spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A1', 'This')
        ->setCellValue('B2', 'is')
        ->setCellValue('C1', 'a')
        ->setCellValue('D2', 'test.');

// Set worksheet title
$spreadsheet->getActiveSheet()->setTitle('Simple');

// 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="simple.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


//old PhpExcel code:
//$writer = PHPExcel_IOFactory::createWriter($spreadsheet, 'Excel2007');
//$writer->save('php://output');

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

Bring the file up on your server and the browser should ask you what you'd like to do with it.

Here is the simple.xlsx spreadsheet on Windows 10:

Conclusion

In today's tutorial, we familiarized ourselves with the new PhpSpreadsheet library and learned how it both resembles and differs from its predecessor, PhpExcel. In the next instalment, we'll go over some of its many formatting methods.



Rob Gravelle

Rob Gravelle resides in Ottawa, Canada. His design company has built web applications for numerous businesses and government agencies. Email him.

Rob's alter-ego, "Blackjacques", is an accomplished guitar player, who has released several CDs and cover songs. His band, Ivory Knight, was rated as one of Canada's top hard rock and metal groups by Brave Words magazine (issue #92).



Make a Comment

Loading Comments...

  • Web Development Newsletter Signup

    Invalid email
    You have successfuly registered to our newsletter.
  •  
  •  
  •  
Thanks for your registration, follow us on our social networks to keep up-to-date