Which one to use PHPExcel or PHPSpreadsheet?

Excel is an excellent tool to organize the data in the form of cells, perform basic and complex mathematical operations, create helpful graphics and charts, and many more. In this tutorial, we’ll see which one to use PHPExcel or PHPSpreadsheet.

Using PHP, we can perform many useful operations and for this purpose, there are two famous libraries available. PHPExcel & PHPSpreadsheet

The main difference between these two is that PHPSpreadsheet is the upgraded version of PHPExcel. PHPSpreadsheet contains the latest features introduced in the newer version of the PHP i.e., v5.5.

So in this tutorial, we are going to see PHPExcel and PHPSpreadsheet one by one. Now the question arises if PHPExcel is deprecated then what is the use of creating special tutorial about it.

There are several projects (in maintenance) that are using the older PHP version (before PHP 5.5). So in such a project, there might be a requirement to implement Excel-related operations to store/collect users/project-specific data. So, for the same purpose, I have considered PHPExcel in this tutorial.

How to use PHPEXcel?

To install PHPExcel in the current project, first, we need to copy Classes folder to your desired location. This location can be the project root classes folder and secondly, we need to copy the PHPExcel.php file that is the entry point for the Excel operations.

<?php

/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

// write data to first sheet
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'id')
            ->setCellValue('A2', 1)
            ->setCellValue('B1', 'name')
			->setCellValue('B2', 'John');

// write data to defined excel sheet
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('Classes/studentInformation.xlsx');

Let’s understand above example line by line-

First, we included PHPExcel class file which will perform all the excel related operations. This can either be included using include/require statement OR it is recommended to use autoload to make sure that once the application boots, then this class should be available automatically.

After creating PHPExcel object, we use method chaining to perform multiple operations on the active sheets one by one. Here, setActiveSheetIndex() is used to set the active sheet to be used in the Workbook. In our case, we used index 0 to work on the first sheet.

Then, we added values in the A1, A2, B1, B2. In real-world example this could be multiple rows by using for() / foreach() loop. To set each cell values, we used setCellValue() function.

Once everything is set, now our data is ready to be written in the excel. for this, we used PHPExcel_IOFactory class which is used to perform write operation using createWriter() function.

This PHPExcel_IOFactory class can write data in different formats like-

Excel2007
Excel5
Excel2003XML
OOCalc
SYLK
Gnumeric
HTML
CSV

Now, this will save excel sheet to the mentioned location in the save() function

How to write data in multiple sheets using PHPExcel?

<?php

/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();

// write data to first sheet
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'id')
            ->setCellValue('A2', 1)
            ->setCellValue('B1', 'name')
			->setCellValue('B2', 'John');

// add new sheet to the same excel 
$objPHPExcel->createSheet();

// write data to second sheet in the same workbook
$objPHPExcel->setActiveSheetIndex(1)
			->setCellValue('A1', 'id')
			->setCellValue('A2', 1)
			->setCellValue('B1', 'name')
			->setCellValue('B2', 'Alexa');

// set focus to first sheet of the workbook
$objPHPExcel->setActiveSheetIndex(0);

// write data to defined excel sheet
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('Classes/studentInformation.xlsx');

So, in the code, we have added a new function right after updating sheet 0 which is $objPHPExcel->createSheet(); which adds a new sheet in the existing workbook.

After that, we did the same operation as that of the sheet 0 by providing index 1 to the setActiveSheetIndex() method.

Many times, we need to set focus on the first sheet rather than the last one for the obvious reason. So that can be done via calling the first sheet again i.e., $objPHPExcel->setActiveSheetIndex(0);

So, we learned how to create a new workbook/excel and add different sheets and write data into it.

Now, let’s see about PHPSpreadsheet.

What is PHPSpreadsheet?

PhpSpreadsheet is a library written in PHP that offers read and writes various spreadsheet file formats such as Excel and LibreOffice Calc and many more. Basically, it is an advanced version of PHPExcel with more format supports and support to the new PHP version.

PhpSpreadsheet requires a minimum PHP 5.6 version to support its functionalities.

Following table shows list of file format supported.

Format	Reading	Writing
Open Document Format/OASIS (.ods)	✓	✓
Office Open XML (.xlsx) Excel 2007 and above	✓	✓
BIFF 8 (.xls) Excel 97 and above	✓	✓
BIFF 5 (.xls) Excel 95	✓
SpreadsheetML (.xml) Excel 2003	✓
Gnumeric	✓
HTML	✓	✓
SYLK	✓
CSV	✓	✓
PDF (using either the TCPDF, Dompdf or mPDF libraries, which need to be installed separately)		✓

Let’s see the basic example of using phpspreadsheet and then we’ll see it step by step-

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'id')
    ->setCellValue('A2', 1)
    ->setCellValue('B1', 'name')
    ->setCellValue('B2', 'John');

$writer = new Xlsx($spreadsheet);
$writer->save('studentInformation.xlsx');

So, in above example, we have autoload all the necessary library files needed for excel operations.

Next, we imported our spreadsheet class necessary to perform an operation on excel just like we did for the Excel in PHPExcel functionality and also included the writer to write the data in the spreadsheet.

After creating a spreadsheet object, we then use getActiveSheet() function to get the active sheet. Here, we can see that, we do not pass index parameter which means the spreadsheet object is smart enough to pick the currently active sheet. Then, we updated the spreadsheet with values using setCellValue() function.

Finally, we passed all the spreadsheet object to the writer class (new Xlsx($spreadsheet)) object to write data in the excel and save using studentInformation.xlsx file.

Now, what if we want to write multiple sheet at a time. So, in this case we’ll use the same object and add another sheet like below.

How to write data in multiple sheets using PHPSpreadsheet?

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'id')
    ->setCellValue('A2', 1)
    ->setCellValue('B1', 'name')
    ->setCellValue('B2', 'John');

$sheet_2 = $spreadsheet->createSheet();
$sheet_2->setCellValue('A1', 'id')
    ->setCellValue('A2', 1)
    ->setCellValue('B1', 'name')
    ->setCellValue('B2', 'Alexa');

$writer = new Xlsx($spreadsheet);
$writer->save('studentInformation.xlsx');

We used createSheet() function which we also used in the PHPExcel library example which eventually will create a new sheet in the same workbook.

Now, with all this information, the main question still remains the same