Reading Files

Security

XML-based formats such as OfficeOpen XML, Excel2003 XML, OASIS and Gnumeric are susceptible to XML External Entity Processing (XXE) injection attacks when reading spreadsheet files. This can lead to:

• Disclosure whether a file is existent
• Server Side Request Forgery
• Command Execution (depending on the installed PHP wrappers)

To prevent this, by default every XML-based Reader looks for XML entities declared inside the DOCTYPE and if any is found an exception is raised.

Read more about of XXE injection.

Loading a Spreadsheet File

The simplest way to load a workbook file is to let PhpSpreadsheet's IO Factory identify the file type and load it, calling the static load() method of the \PhpOffice\PhpSpreadsheet\IOFactory class.

$inputFileName = './sampleData/example1.xls';

/** Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

See samples/Reader/01_Simple_file_reader_using_IOFactory.php for a working example of this code.

The load() method will attempt to identify the file type, and instantiate a loader for that file type; using it to load the file and store the data and any formatting in a Spreadsheet object.

The method makes an initial guess at the loader to instantiate based on the file extension; but will test the file before actually executing the load: so if (for example) the file is actually a CSV file or contains HTML markup, but that has been given a .xls extension (quite a common practise), it will reject the Xls loader that it would normally use for a .xls file; and test the file using the other loaders until it finds the appropriate loader, and then use that to read the file.

If you know that this is an xls file, but don't know whether it is a genuine BIFF-format Excel or Html markup with an xls extension, you can limit the loader to check only those two possibilities by passing in an array of Readers to test against.

$inputFileName = './sampleData/example1.xls';
$testAgainstFormats = [
    \PhpOffice\PhpSpreadsheet\IOFactory::READER_XLS,
    \PhpOffice\PhpSpreadsheet\IOFactory::READER_HTML,
];

/** Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName, 0, $testAgainstFormats);

While easy to implement in your code, and you don't need to worry about the file type; this isn't the most efficient method to load a file; and it lacks the flexibility to configure the loader in any way before actually reading the file into a Spreadsheet object.

Creating a Reader and Loading a Spreadsheet File

If you know the file type of the spreadsheet file that you need to load, you can instantiate a new reader object for that file type, then use the reader's load() method to read the file to a Spreadsheet object. It is possible to instantiate the reader objects for each of the different supported filetype by name. However, you may get unpredictable results if the file isn't of the right type (e.g. it is a CSV with an extension of .xls), although this type of exception should normally be trapped.

$inputFileName = './sampleData/example1.xls';

/** Create a new Xls Reader  **/
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xml();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Ods();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Slk();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Gnumeric();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
/** Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/02_Simple_file_reader_using_a_specified_reader.php for a working example of this code.

Alternatively, you can use the IO Factory's createReader() method to instantiate the reader object for you, simply telling it the file type of the reader that you want instantiating.

$inputFileType = 'Xls';
//    $inputFileType = 'Xlsx';
//    $inputFileType = 'Xml';
//    $inputFileType = 'Ods';
//    $inputFileType = 'Slk';
//    $inputFileType = 'Gnumeric';
//    $inputFileType = 'Csv';
$inputFileName = './sampleData/example1.xls';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/03_Simple_file_reader_using_the_IOFactory_to_return_a_reader.php for a working example of this code.

If you're uncertain of the filetype, you can use the IOFactory::identify() method to identify the reader that you need, before using the createReader() method to instantiate the reader object.

$inputFileName = './sampleData/example1.xls';

/**
 * Identify the type of $inputFileName.
 * See below for a possible improvement for release 4.1.0+.
 */
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
/**  Create a new Reader of the type that has been identified  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/04_Simple_file_reader_using_the_IOFactory_to_identify_a_reader_to_use.php for a working example of this code.

Prior to release 4.1.0, identify returns a file type. It may be more useful to return a fully-qualified class name, which can be accomplished using a parameter introduced in 4.1.0:

$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName, null, true);

As with the IOFactory load() method, you can also pass an array of formats for the identify() method to check against if you know that it will only be in a subset of the possible formats that PhpSpreadsheet supports.

$inputFileName = './sampleData/example1.xls';
$testAgainstFormats = [
    \PhpOffice\PhpSpreadsheet\IOFactory::READER_XLS,
    \PhpOffice\PhpSpreadsheet\IOFactory::READER_HTML,
];

/**  Identify the type of $inputFileName  **/
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName, $testAgainstFormats);

You can also use this to confirm that a file is what it claims to be:

$inputFileName = './sampleData/example1.xls';

try {
    /**  Verify that $inputFileName really is an Xls file **/
    $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName, [\PhpOffice\PhpSpreadsheet\IOFactory::READER_XLS]);
} catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
    // File isn't actually an Xls file, even though it has an xls extension 
}

Spreadsheet Reader Options

Once you have created a reader object for the workbook that you want to load, you have the opportunity to set additional options before executing the load() method.

All of these options can be set by calling the appropriate methods against the Reader (as described below), but some options (those with only two possible values) can also be set through flags, either by calling the Reader's setFlags() method, or passing the flags as an argument in the call to load(). Those options that can be set through flags are:

Several flags can be combined in a single call:

$inputFileType = 'Xlsx';
$inputFileName = './sampleData/example1.xlsx';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/** Set additional flags before the call to load() */
$reader->setFlags(IReader::IGNORE_EMPTY_CELLS | IReader::LOAD_WITH_CHARTS);
$reader->load($inputFileName);

or

$inputFileType = 'Xlsx';
$inputFileName = './sampleData/example1.xlsx';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/** Set additional flags in the call to load() */
$reader->load($inputFileName, IReader::IGNORE_EMPTY_CELLS | IReader::LOAD_WITH_CHARTS);

Ignoring Empty Cells

Many Excel files have empty rows or columns at the end of a worksheet, which can't easily be seen when looking at the file in Excel (Try using Ctrl-End to see the last cell in a worksheet). By default, PhpSpreadsheet will load these cells, because they are valid Excel values; but you may find that an apparently small spreadsheet requires a lot of memory for all those empty cells. If you are running into memory issues with seemingly small files, you can tell PhpSpreadsheet not to load those empty cells using the setReadEmptyCells() method.

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader that we only want to load cell's that contain actual content  **/
$reader->setReadEmptyCells(false);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

Note that cells containing formulae will still be loaded, even if that formula evaluates to a NULL or an empty string. Similarly, Conditional Styling might also hide the value of a cell; but cells that contain Conditional Styling or Data Validation will always be loaded regardless of their value.

This option is available for the following formats:

This option is also available through flags.

Ignoring Rows With No Cells

Similar to the previous item, you can choose to ignore rows which contain no cells. This can also help with memory issues.

$inputFileType = 'Xlsx';
$inputFileName = './sampleData/example1.xlsx';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader that we do not want rows with no cells  **/
$reader->setIgnoreRowsWithNoCells(true);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

This option is available only for Xlsx. It is also available through flags.

Reading Only Data from a Spreadsheet File

If you're only interested in the cell values in a workbook, but don't need any of the cell formatting information, then you can set the reader to read only the data values and any formulae from each cell using the setReadDataOnly() method.

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader that we only want to load cell data  **/
$reader->setReadDataOnly(true);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/05_Simple_file_reader_using_the_read_data_only_option.php for a working example of this code.

It is important to note that most Workbooks (and PhpSpreadsheet) store dates and times as simple numeric values: they can only be distinguished from other numeric values by the format mask that is applied to that cell. When setting read data only to true, PhpSpreadsheet doesn't read the cell format masks, so it is not possible to differentiate between dates/times and numbers.

The Gnumeric loader has been written to read the format masks for date values even when read data only has been set to true, so it can differentiate between dates/times and numbers; but this change hasn't yet been implemented for the other readers.

Reading Only Data from a Spreadsheet File applies to Readers:

This option is also available through flags.

Reading Only Named WorkSheets from a File

If your workbook contains a number of worksheets, but you are only interested in reading some of those, then you can use the setLoadSheetsOnly() method to identify those sheets you are interested in reading.

To read a single sheet, you can pass that sheet name as a parameter to the setLoadSheetsOnly() method.

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #2';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load  **/
$reader->setLoadSheetsOnly($sheetname);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/07_Simple_file_reader_loading_a_single_named_worksheet.php for a working example of this code.

If you want to read more than just a single sheet, you can pass a list of sheet names as an array parameter to the setLoadSheetsOnly() method.

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';
$sheetnames = ['Data Sheet #1','Data Sheet #3'];

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader of which WorkSheets we want to load  **/
$reader->setLoadSheetsOnly($sheetnames);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/08_Simple_file_reader_loading_several_named_worksheets.php for a working example of this code.

To reset this option to the default, you can call the setLoadAllSheets() method.

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader to load all Worksheets  **/
$reader->setLoadAllSheets();
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/06_Simple_file_reader_loading_all_worksheets.php for a working example of this code.

Reading Only Named WorkSheets from a File applies to Readers:

Reading Only Specific Columns and Rows from a File (Read Filters)

If you are only interested in reading part of a worksheet, then you can write a filter class that identifies whether or not individual cells should be read by the loader. A read filter must implement the \PhpOffice\PhpSpreadsheet\Reader\IReadFilter interface, and contain a readCell() method that accepts arguments of $column, $row and $worksheetName, and return a boolean true or false that indicates whether a workbook cell identified by those arguments should be read or not.

$inputFileType = 'Xls';
$inputFileName = './sampleData/example1.xls';
$sheetname = 'Data Sheet #3';

/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($columnAddress, $row, $worksheetName = '') {
        //  Read rows 1 to 7 and columns A to E only
        if ($row >= 1 && $row <= 7) {
            if (in_array($columnAddress,range('A','E'))) {
                return true;
            }
        }
        return false;
    }
}

/**  Create an Instance of our Read Filter  **/
$filterSubset = new MyReadFilter();

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Tell the Reader that we want to use the Read Filter  **/
$reader->setReadFilter($filterSubset);
/**  Load only the rows and columns that match our filter to Spreadsheet  **/
$spreadsheet = $reader->load($inputFileName);

See samples/Reader/09_Simple_file_reader_using_a_read_filter.php for a working example of this code.

This example is not particularly useful, because it can only be used in a very specific circumstance (when you only want cells in the range A1:E7 from your worksheet. A generic Read Filter would probably be more useful:

/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    private $startRow = 0;
    private $endRow   = 0;
    private $columns  = [];

    /**  Get the list of rows and columns to read  */
    public function __construct($startRow, $endRow, $columns) {
        $this->startRow = $startRow;
        $this->endRow   = $endRow;
        $this->columns  = $columns;
    }

    public function readCell($columnAddress, $row, $worksheetName = '') {
        //  Only read the rows and columns that were configured
        if ($row >= $this->startRow && $row <= $this->endRow) {
            if (in_array($columnAddress,$this->columns)) {
                return true;
            }
        }
        return false;
    }
}

/**  Create an Instance of our Read Filter, passing in the cell range  **/
$filterSubset = new MyReadFilter(9,15,range('G','K'));

See samples/Reader/10_Simple_file_reader_using_a_configurable_read_filter.php for a working example of this code.

This can be particularly useful for conserving memory, by allowing you to read and process a large workbook in "chunks": an example of this usage might be when transferring data from an Excel worksheet to a database.

Read Filtering does not renumber cell rows and columns. If you filter to read only rows 100-200, cells that you read will still be numbered A100-A200, not A1-A101. Cells A1-A99 will not be loaded, but if you then try to call getCell() for a cell outside your loaded range, then PHPSpreadsheet will create a new cell with a null value.

Methods such as toArray() assume that all cells in a spreadsheet has been loaded from A1, so will return null values for rows and columns that fall outside your filter range: it is recommended that you keep track of the range that your filter has requested, and use rangeToArray() instead.

$inputFileType = 'Xls';
$inputFileName = './sampleData/example2.xls';

/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    private $startRow = 0;
    private $endRow   = 0;

    /**  Set the list of rows that we want to read  */
    public function setRows($startRow, $chunkSize) {
        $this->startRow = $startRow;
        $this->endRow   = $startRow + $chunkSize;
    }

    public function readCell($columnAddress, $row, $worksheetName = '') {
        //  Only read the heading row, and the configured rows
        if (($row == 1) || ($row >= $this->startRow && $row < $this->endRow)) {
            return true;
        }
        return false;
    }
}

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

/**  Define how many rows we want to read for each "chunk"  **/
$chunkSize = 2048;
/**  Create a new Instance of our Read Filter  **/
$chunkFilter = new ChunkReadFilter();

/**  Tell the Reader that we want to use the Read Filter  **/
$reader->setReadFilter($chunkFilter);

/**  Loop to read our worksheet in "chunk size" blocks  **/
for ($startRow = 2; $startRow <= 65536; $startRow += $chunkSize) {
    /**  Tell the Read Filter which rows we want this iteration  **/
    $chunkFilter->setRows($startRow,$chunkSize);
    /**  Load only the rows that match our filter  **/
    $spreadsheet = $reader->load($inputFileName);
    //    Do some processing here
}

See samples/Reader/12_Reading_a_workbook_in_chunks_using_a_configurable_read_filter_ for a working example of this code.

Using Read Filters applies to:

Combining Multiple Files into a Single Spreadsheet Object

While you can limit the number of worksheets that are read from a workbook file using the setLoadSheetsOnly() method, certain readers also allow you to combine several individual "sheets" from different files into a single Spreadsheet object, where each individual file is a single worksheet within that workbook. For each file that you read, you need to indicate which worksheet index it should be loaded into using the setSheetIndex() method of the $reader, then use the loadIntoExisting() method ra...