Read Excel Files Cell Data Only In PHP Using PHPSpreadSheet

To just read the cell data (ignore cell styles, etc), set the reader method setReadDataOnly to true.

The generated file from the tutorial Create Xlsx Files With Different Cell Background Colors will be used as a sample xlsx file for this tutorial.

Requirements:

• Composer
• PHP 7.2 or newer

Step 1.

Setup dependencies.

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

composer.json

Step 2.

Install phpspreadsheet.

$ composer install

command line

Step 3.

Create a new PHP file, and start coding.

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the IOFactory class
use \PhpOffice\PhpSpreadsheet\IOFactory;

// Full path of the file to be indentified
$inputFileName = 'create-xlsx-files-with-different-cell-background-colors.xlsx';

// Identify the file type using the IOFactory object
$inputFileType = IOFactory::identify($inputFileName);

// Create the reader object
$reader = IOFactory::createReader($inputFileType);

// Instruct the reader to just read cell data
$reader->setReadDataOnly(true);

// Load the file to read
$spreadsheet = $reader->load($inputFileName);

// Get the active sheet
$sheet = $spreadsheet->getActiveSheet();

/**
 * Get the background color of column A, row 1.
 */
echo $sheet->getStyle('A1')->getFill()->getStartColor()->getRGB();

echo PHP_EOL;

/**
 * Get the background color of column A, row 2.
 */
echo $sheet->getStyle('A2')->getFill()->getStartColor()->getRGB();

read-data-only.php

Test.

Command line testing.

$ php read-data-only.php

command line

Result.

It got the default background color (white) for both cells.

Test again.

Comment out the setReadDataOnly function like below, or delete it.

// $reader->setReadDataOnly(true);

Or set the setReadDataOnly function to false like below (this is the default option).

$reader->setReadDataOnly(false);

And run the code again to test.

$ php read-data-only.php

command line

Result.

It got the actual background color of both cells.

References:

• How to install PhpSpreadsheet
• Reading and writing to file
• Reading Files