Guide to reading Excel files in PHP

A comprehensive guide on how to read content from Excel files (.xlsx, .xls) using PHP, utilizing the PHPExcel library, including installation and implementation steps.

In PHP, you can use the PhpSpreadsheet library to read content from Excel files. This article will guide you on how to install and use this library to extract data from Excel files.

<?php
// Step 1: Install the PHPExcel library via Composer
// Run the following command in your terminal: composer require phpoffice/phpspreadsheet

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$filePath = 'example.xlsx'; // Path to the Excel file

// Step 2: Load the Excel file
$spreadsheet = IOFactory::load($filePath);

// Step 3: Retrieve data from the first sheet
$worksheet = $spreadsheet->getActiveSheet();

// Step 4: Iterate through each row and column to read the data
foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); // Loop through all cells, even if empty

    foreach ($cellIterator as $cell) {
        echo $cell->getValue() . "\t"; // Display the cell's value, using "\t" to separate cells by tab
    }
    echo "\n"; // Move to the next line after processing a row
}
?>

Detailed Explanation:

1. Installing the PHPExcel (or PhpSpreadsheet) library: To use this code, you need to install the PhpSpreadsheet library, which is the updated version of PHPExcel. You can install it via Composer using the command composer require phpoffice/phpspreadsheet.

2. Loading the library and Excel file: After installation, you need to include require 'vendor/autoload.php'; to automatically load the installed packages. Then, IOFactory::load($filePath); helps load the contents of the Excel file.

3. Retrieve data from the first sheet: $spreadsheet->getActiveSheet(); fetches the active (first) sheet in the Excel file.

4. Iterate through rows and columns: Use getRowIterator() and getCellIterator() to loop through each row and cell in the sheet. getValue() retrieves the value of each cell and displays it.

System Requirements:

The code is compatible with PHP 7.1 and later along with PhpSpreadsheet 1.8 and above.

Tips:

1. Check PHP version: Ensure your PHP version is compatible with PhpSpreadsheet.
2. Use try-catch: Use a try-catch block to handle errors when reading the Excel file.
3. Experiment with different Excel files: Try different Excel files with various structures to better understand how the library works.