Read Excel Files Cell Data Only In PHP Using PHPSpreadSheet

| June 21, 2020 | 1,498 views | 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"
    }
}

Step 2.

Install phpspreadsheet.

$ composer install

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();

Test.

Command line testing.

$ php read-data-only.php

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

Result.

It got the actual background color of both cells.

References:

0 Comments

Leave a Reply

Your email address will not be published.