How to Read Excel Files in PHP Using PhpSpreadsheet

zeroexp.dev ZeroonLeave a Comment on How to Read Excel Files in PHP Using PhpSpreadsheet
How to Read Excel Files in PHP Using PhpSpreadsheet

Introduction

Reading Excel files in PHP is essential for applications that require data processing, report generation, and bulk data importation. Instead of manually entering data, PhpSpreadsheet allows PHP applications to extract, manipulate, and display Excel data dynamically.

With PhpSpreadsheet, you can:

✅ Read Excel files (.xls and .xlsx) and extract data
✅ Access specific rows, columns, or sheets dynamically
✅ Process Excel data for database storage or reporting
✅ Convert Excel data into other formats (CSV, JSON, etc.)

In this guide, we will:

1️⃣ Install PhpSpreadsheet
2️⃣ Load and read Excel files in PHP
3️⃣ Extract data from specific rows, columns, and sheets
4️⃣ Convert Excel data into an array for further processing

Let’s get started! 🚀

1. Installing PhpSpreadsheet in PHP

To work with Excel files in PHP, install PhpSpreadsheet via Composer.

Install via Composer (Recommended)

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in Your PHP Script

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

✅ Now, your PHP application is ready to process Excel files.

2. Reading an Excel File in PHP

Let’s load an Excel file (data.xlsx) and display its contents.

Example: Load and Read an Excel File

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$file = 'data.xlsx';

// Load the Excel file
$spreadsheet = IOFactory::load($file);
$worksheet = $spreadsheet->getActiveSheet();

// Loop through the rows and display data
foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);

    foreach ($cellIterator as $cell) {
        echo $cell->getValue() . " | ";
    }
    echo "<br>";
}

Explanation:

✅ IOFactory::load($file) – Loads the Excel file
✅ getActiveSheet() – Retrieves the default worksheet
✅ Loops through rows & columns and extracts cell values

🔹 Output Example (Excel Content Displayed in HTML):

ID | Name | Age | City  
1  | John | 28  | New York  
2  | Jane | 25  | Los Angeles  
3  | Bob  | 32  | Chicago

🔹 Works for both .xls and .xlsx formats.

3. Extracting Data from a Specific Sheet

If an Excel file has multiple sheets, specify which one to read.

Example: Read Data from a Specific Sheet

$spreadsheet = IOFactory::load('data.xlsx');

// Load sheet by name
$worksheet = $spreadsheet->getSheetByName('Customers');

// Display sheet name
echo "Reading Sheet: " . $worksheet->getTitle() . "<br>";

// Extract data
foreach ($worksheet->getRowIterator() as $row) {
    foreach ($row->getCellIterator() as $cell) {
        echo $cell->getValue() . " | ";
    }
    echo "<br>";
}

✅ Reads a sheet named "Customers" instead of the default active sheet.

🔹 Works for multi-sheet Excel files containing different data sections.

4. Extracting Data from a Specific Column or Row

Sometimes, you only need data from a specific row or column instead of scanning the entire Excel file.

Example: Extract Data from Column B (Names Column)

$worksheet = $spreadsheet->getActiveSheet();

foreach ($worksheet->getRowIterator() as $row) {
    $cell = $worksheet->getCell('B' . $row->getRowIndex());
    echo $cell->getValue() . "<br>";
}

Example: Extract Data from Row 2

$rowData = $worksheet->rangeToArray('A2:D2', null, true, true, true);
print_r($rowData);

✅ Helps when extracting targeted data like emails, IDs, or product prices.

5. Converting Excel Data to an Array for Further Processing

To process Excel data in databases or APIs, convert it into an array.

Example: Convert Excel Data to an Array

$data = $worksheet->toArray(null, true, true, true);

echo "<pre>";
print_r($data);
echo "</pre>";

✅ Returns structured data that can be stored in MySQL or JSON.

🔹 Ideal for handling bulk data processing.

6. Handling Large Excel Files Efficiently

If the Excel file is large, use a streaming reader to avoid memory issues.

Example: Read Large Excel Files Efficiently

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('large_file.xlsx');

✅ Optimized for large datasets without excessive memory usage.

7. Importing Excel Data into a MySQL Database

Once the Excel file is read, store its contents in MySQL.

Example: Save Excel Data into MySQL

$conn = new mysqli("localhost", "root", "", "excel_db");

foreach ($worksheet->getRowIterator() as $row) {
    $id = $worksheet->getCell('A' . $row->getRowIndex())->getValue();
    $name = $worksheet->getCell('B' . $row->getRowIndex())->getValue();
    $age = $worksheet->getCell('C' . $row->getRowIndex())->getValue();
    $city = $worksheet->getCell('D' . $row->getRowIndex())->getValue();

    $stmt = $conn->prepare("INSERT INTO users (id, name, age, city) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("isis", $id, $name, $age, $city);
    $stmt->execute();
}

echo "Excel data imported into MySQL!";

✅ Useful for bulk data uploads from Excel to a database.

Best Practices for Reading Excel Files in PHP

✔ Use PhpSpreadsheet for maximum compatibility (.xls, .xlsx).
✔ Optimize memory usage for large Excel files using setReadDataOnly().
✔ Convert Excel data into arrays for structured processing.
✔ Use getSheetByName() when working with multiple sheets.
✔ Store extracted data in a database for search and retrieval.

Conclusion

With PhpSpreadsheet, PHP can read and process Excel files dynamically.

✅ Extract data from .xls and .xlsx files efficiently.
✅ Read specific sheets, rows, or columns from Excel files.
✅ Convert Excel data into structured arrays for processing.
✅ Import Excel data into MySQL databases for further use.

By implementing these techniques, you can automate Excel data extraction for web applications, reports, and database imports! 🚀

Leave a Reply