PHPOffice/PHPSpreadsheet

Updated on 28 Dec 2018

Installation

PhpOffice/PhpSpreadsheet can be installed with composer.

composer require phpoffice/phpspreadsheet

Basic Usage

To use the library, we just include the vendor autoload directory and then do stuff.

require 'vendor/autoload.php';

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

Accessing Cells

$spreadsheet->getActiveSheet()->getCell('A2')->getValue();

Read an Excel File and dump the data into an array

This example uses the IOFactory to determine the file type and create the appropriate reader.

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = __DIR__ . '/data/example1.xls';
$inputFileType = IOFactory::identify($inputFileName);

$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

$a2 = $spreadsheet->getActiveSheet()->getCell('A2')->getValue();
echo "\nValue of A2: $a2\n\n";

var_dump($sheetData);

Excel File

Output

Value of A2: Floating Point Numbers

array(8) {
  [1]=>
  array(6) {
    ["A"]=>
    string(15) "Integer Numbers"
    ["B"]=>
    string(3) "123"
    ["C"]=>
    string(3) "234"
    ["D"]=>
    string(4) "-345"
    ["E"]=>
    string(3) "456"
    ["F"]=>
    NULL
  }
  [2]=>
  array(6) {
    ["A"]=>
    string(22) "Floating Point Numbers"
    ["B"]=>
    string(4) "1.23"
    ["C"]=>
    string(5) "23.45"
    ["D"]=>
    string(7) "3.45E-6"
    ["E"]=>
    string(6) "-45.68"
    ["F"]=>
    string(7) "£56.78"
  }
  ...

Write to an Excel File

This example will look at writing to a:

  • individual cell
  • row
  • column

Individual Cell

$sheet->setCellValue('A1', 'Hello World !');

Row

$rowArray = array('Value1', 'Value2', 'Value3', 'Value4');
$sheet->fromArray(
        $rowArray,   // The data to set
        NULL,        // Array values with this value will not be set
        'C3'         // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );

Column

$rowArray = array('Column1', 'column2', 'column3', 'Column4');
$columnArray = array_chunk($rowArray, 1);
$spreadsheet->getActiveSheet()
    ->fromArray(
        $columnArray,   // The data to set
        NULL,           // Array values with this value will not be set
        'I1'            // Top left coordinate of the worksheet range where
                        //  we want to set these values (default is A1)
    );

Code

require 'vendor/autoload.php';

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

//-----
//-- create a spreadsheet
//-----
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//-----

//--write Hello world in cell A1
$sheet->setCellValue('A1', 'Hello World !');


//-----
//--write an array of data to a line in the spreadsheet.
//-----
$rowArray = array('Value1', 'Value2', 'Value3', 'Value4');
$sheet->fromArray(
        $rowArray,   // The data to set
        NULL,        // Array values with this value will not be set
        'C3'         // Top left coordinate of the worksheet range where
                     //    we want to set these values (default is A1)
    );
//-----


//-----
//--use an array of data as a column
//-----
$rowArray = array('Column1', 'column2', 'column3', 'Column4');
$columnArray = array_chunk($rowArray, 1);
$spreadsheet->getActiveSheet()
    ->fromArray(
        $columnArray,   // The data to set
        NULL,           // Array values with this value will not be set
        'I1'            // Top left coordinate of the worksheet range where
                        //  we want to set these values (default is A1)
    );
//-----


//-----
//--write the data to a spreadsheet file...
//-----
$writer = new Xlsx($spreadsheet);
$writer->save('data/hello world.xlsx');
//-----

Excel SpreadSheet

Moving columns around

This example will show how to move columns around the sheet, and in the process it will touch on these internal methods:

  • $sheet->insertNewColumnBefore($new_column, 1);
  • $sheet->removeColumn($column);

Get chunked_array of data column

$dataArray = $sheet
    ->rangeToArray(
        $column . '1:' . $column . $num_rows,     // The worksheet range that we want to retrieve
        NULL,        // Value that should be returned for empty cells
        TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
        TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
        TRUE         // Should the array be indexed by cell row and cell column
    );

//-----
//--rework above column data as 'chunked' array and rewrite into new column
//-----
$data1 = [];
foreach($dataArray as $arrayValue) {
  $data1[] = $arrayValue[$column];
}

$columnArray = array_chunk($data1, 1);
//-----

Now we can remove the existing column, add a new column and add the columnArray data.

$sheet->removeColumn($column);
$sheet->insertNewColumnBefore($new_column, 1);

$sheet->fromArray(
            $columnArray,   // The data to set
            NULL,           // Array values with this value will not be set
            $new_column . '1'            // Top left coordinate of the worksheet range where
                            //  we want to set these values (default is A1)
        );

Reading an entire row to array

We can read a row of data from a spreadsheet and put it into a normal array. The code would essentially be the same as the previous example, except that we specify a row instead of a column.

require 'vendor/autoload.php';

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

$filename = 'data/9589083.xlsx';

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($filename);
$worksheet = $spreadsheet->getActiveSheet();


//-----
//--retrieve the tag names...
//-----
$dataArray = $worksheet->rangeToArray(
                        'A1:Z4',     // The worksheet range that we want to retrieve
                        NULL,        // Value that should be returned for empty cells
                        TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
                        TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
                        TRUE         // Should the array be indexed by cell row and cell column
                    );

$header_array_letter = $dataArray[1];
$header_array = array_values($header_array_letter);

print_r($header_array_letter);
print_r($header_array);

This will produce the following array, where the letters are the column letters in the xlsx spreadsheet.

array_values returns an array of all the values. Essentially it strips out the index and replaces it with a zero based index.

You also need to keep in mind that with multiple rows, we’d have a multi dimensional array to deal with. Hence the reason for $header_array_letter = $dataArray[1]; to only get the first row.

Long numbers formatted properly

Unfortunately Excel has a habit of formatting numbers is ways that you dont want. Take this simple example.

require 'vendor/autoload.php';

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

//-----
//-- create a spreadsheet
//-----
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//-----

//--write Hello world in cell A1
$value = 67619437146973189791341;
$sheet->setCellValue('A1', $value);


//-----
//--write the data to a spreadsheet file...
//-----
$writer = new Xlsx($spreadsheet);
$writer->save('data/big-number.xlsx');
//-----

When we look at the spreadsheet, we get something that may not be what we really want.

Using quotes, trying to force the variable as text makes no difference. Even trying to change the number format wont, help. But what does is setValueExplicit

This code does not help

//-----
//--set the column formatting...
//-----
$sheet->getStyle('A1:A10') 
      ->getNumberFormat()
      ->setFormatCode(PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
//-----

This code works

$sheet->getCell('A1')
      ->setValueExplicit($value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

Please note that this example only seems to work when I read in a spreadsheet, make modifications and then save it. Doesn’t seem to work with a brand new spreadsheet at the moment. I will have to investigate further.

Vendor code examples

The library vendor has provided some really good examples of how to use the library. You should definitely look there for guidance on how to do other things not covered by my examples.

PHPSpreadsheet Examples