Read Big Excel File Using PHP Spout Library

Reading a Big Excel file with PHP is not an easy task especially if you code it yourself. You need to use a Library to make it works for you. Once, I tried PHPExcel which is nice for handling Excel with PHP. 

The problem i had is that PHPExcel produces "out of memory" when I read a Big file (thousands of rows) until i finally find out that Spout Library could handle such thing.

For this Example, I am using PHP Codeiginiter Framework. Spout is a Composer based library so its easy to intall it anywhere.

If you are not using Codeigniter, just install it like this. Add this line to your composer.json  file

"require": { "box/spout": "~2.0"}

then Execute it in your command line 

$ php composer.phar install


Or you directly install it like this (without having to add require to composer.json) 

$ composer require box/spout

If you can’t use Composer, no worries! You can still install Spout manually.

Before starting, make sure your system meets the requirements.

1. Download the source code from the Releases page
2. Extract the downloaded content into your project.
3. Add this code to the top controller (e.g. index.php) or wherever it may be more appropriate:

Basic usage

Reader

Regardless of the file type, the interface to read a file is always the same:

use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

$reader = ReaderFactory::create(Type::XLSX); // for XLSX files
//$reader = ReaderFactory::create(Type::CSV); // for CSV files
//$reader = ReaderFactory::create(Type::ODS); // for ODS files

$reader->open($filePath);

foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        // do stuff with the row
    }
}

$reader->close();

If there are multiple sheets in the file, the reader will read all of them sequentially.

Writer

As with the reader, there is one common interface to write data to a file:

use Box\Spout\Writer\WriterFactory;
use Box\Spout\Common\Type;

$writer = WriterFactory::create(Type::XLSX); // for XLSX files
//$writer = WriterFactory::create(Type::CSV); // for CSV files
//$writer = WriterFactory::create(Type::ODS); // for ODS files

$writer->openToFile($filePath); // write data to a file or to a PHP stream
//$writer->openToBrowser($fileName); // stream data directly to the browser

$writer->addRow($singleRow); // add a row at a time
$writer->addRows($multipleRows); // add multiple rows at a time

$writer->close();

For XLSX and ODS files, the number of rows per sheet is limited to 1,048,576. By default, once this limit is reached, the writer will automatically create a new sheet and continue writing data into it.

Reading Big File using codeigniter

1. Download the Spout Library 
2. Copy Spout folder to  application/third_party
3. Prepare your Excel File

The Spout folder structure  will look like this 

Now create a Controller Export.php

/**
* Excel dengan CI & Spout
*
*/
//load Spout Library
require_once APPPATH.'/third_party/spout/src/Spout/Autoloader/autoload.php';

//lets Use the Spout Namespaces
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

class Export extends CI_Controller {

      public function readExcelFile() {

          try {
           
               //Lokasi file excel       
               $file_path = "C:\file_excel.xlsx";                     
               $reader = ReaderFactory::create(Type::XLSX); //set Type file xlsx
               $reader->open($file_path); //open the file          
           
                $i = 0; 
                                   
                /**                  
                * Sheets Iterator. Kali aja multiple sheets                  
                **/           
                foreach ($reader->getSheetIterator() as $sheet) {

                    //Rows iterator                
                    foreach ($sheet->getRowIterator() as $row) {

                        print_r($row); 
                  
                           ++$i;
              }
                }

                echo "Total Rows : " . $i;              
                $reader->close();
                            

               echo "Peak memory:", (memory_get_peak_usage(true) / 1024 / 1024), " MB";

      } catch (Exception $e) {

              echo $e->getMessage();
              exit;   
      }

  }//end of function 


}//end of class

Now run it in your Browser 

http://localhost/my-project/export/readExcelFile

And here's the Output. 

Array
(
    [0] => SPP-16755
    [1] => 42198
    [2] => Mester SERVER
    [3] => Rp.9000
    [4] => Banjarmasin
)
.....
.....
.....

Total Rows : 6171 
Peak memory usage: 2 MB

My Excel file has 6171 rows and 5 Columns, The memory printed is just 2MB compared to 12MB of PHPExcel.