Sai A Sai A
Updated date Nov 26, 2023
In this blog, we will learn how to transform Excel sheets into powerful arrays using PHP. This beginner-friendly guide explores multiple methods, providing step-by-step instructions, sample code, and real-world outputs.

Introduction:

Data is the heartbeat of modern applications, and the ability to effortlessly handle data is a key skill for any developer. Excel sheets are a ubiquitous format for storing tabular data, but integrating this data into web applications often requires a different data structure. In this guide, we will explore how to convert Excel sheets into arrays using PHP,.

Method 1: PHPExcel Library

Our first method involves using the PHPExcel library, a powerful tool for working with Excel files. Begin by installing the library using Composer:

composer require phpoffice/phpexcel

Now, let's dive into the PHP code:

<?php
require 'vendor/autoload.php';

$inputFileName = 'example.xlsx';
$excelReader = PHPExcel_IOFactory::createReaderForFile($inputFileName);
$excelObj = $excelReader->load($inputFileName);
$sheet = $excelObj->getSheet(0);
$data = $sheet->toArray();

// Output the array
print_r($data);
?>
  • We use Composer to install the PHPExcel library.
  • Load the Excel file and retrieve the first sheet.
  • Convert the sheet to an array using the toArray method.

Output:

Array
(
    [0] => Array
        (
            [0] => Name
            [1] => Age
            [2] => City
        )

    [1] => Array
        (
            [0] => John Doe
            [1] => 25
            [2] => New York
        )

    [2] => Array
        (
            [0] => Jane Smith
            [1] => 30
            [2] => San Francisco
        )
    // ... additional rows
)

Method 2: PhpSpreadsheet Library

As PHPExcel is deprecated, let's explore a more modern alternative - PhpSpreadsheet. Install it using Composer:

composer require phpoffice/phpspreadsheet

Here's the code:

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'example.xlsx';
$spreadsheet = IOFactory::load($inputFileName);
$sheetData = $spreadsheet->getActiveSheet()->toArray();

// Output the array
print_r($sheetData);
?>
  • We use Composer to install the PhpSpreadsheet library.
  • Load the Excel file and retrieve the active sheet.
  • Convert the sheet to an array using the toArray method.

Output:

Array
(
    [0] => Array
        (
            [0] => Name
            [1] => Age
            [2] => City
        )

    [1] => Array
        (
            [0] => John Doe
            [1] => 25
            [2] => New York
        )

    [2] => Array
        (
            [0] => Jane Smith
            [1] => 30
            [2] => San Francisco
        )
    // ... additional rows
)

Method 3: Spout Library

For our third method, we'll use the Spout library, a lightweight and fast library for reading and writing spreadsheet files:

composer require box/spout

And the PHP code:

<?php
require 'vendor/autoload.php';

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

$inputFileName = 'example.xlsx';
$reader = ReaderEntityFactory::createXLSXReader();
$reader->open($inputFileName);

foreach ($reader->getSheetIterator() as $sheet) {
    $data = [];
    foreach ($sheet->getRowIterator() as $row) {
        $data[] = $row->toArray();
    }
}

$reader->close();

// Output the array
print_r($data);
?>
  • We use Composer to install the Spout library.
  • Create an XLSX reader and open the Excel file.
  • Iterate through the rows and columns, building the array.

Output:

Array
(
    [0] => Array
        (
            [0] => Name
            [1] => Age
            [2] => City
        )

    [1] => Array
        (
            [0] => John Doe
            [1] => 25
            [2] => New York
        )

    [2] => Array
        (
            [0] => Jane Smith
            [1] => 30
            [2] => San Francisco
        )
    // ... additional rows
)

Conclusion:

In this blog, we have explored three methods to convert Excel sheets into arrays using PHP. Whether you prefer the familiarity of PHPExcel, the modern PhpSpreadsheet, or the lightweight Spout library, each method allows you to seamlessly integrate Excel data into your web applications.

Comments (0)

There are no comments. Be the first to comment!!!