Which one to use PHPExcel or PHPSpreadsheet?

For more detailed post visit 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 2 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.

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.

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 the above example, we have autoloaded 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 the 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 sheets 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.

Conclusion:

PHPExcel library is now archived because of the old PHP version functionalities. It is always recommended to use the latest PHP version to enjoy the new features in it.

There are so many additional functions available in both the libraries which can be used based on the requirement.

So to answer the main question, which one to use PHPExcel or PHPSpreadsheet depends on the current version of the PHP installed on the server and the requirement. If PHPExcel is incapable of providing certain features that PHPSpreadsheet can provide then it is always good to upgrade the PHP version but with that, we also need to check the deprecated functions and need to work on them