Mastering Excel Data Importation in PHP

You may use an Excel spreadsheet to manage your data for many cases, and had some chances that you want to use it as a direct data source from time to time.

You can use PHP libraries like PHPExcel or PhpSpreadsheet to handle Excel files with multiple sheets. Here's an example using PhpSpreadsheet, which is a newer and more actively maintained library:

First, you'll need to install PhpSpreadsheet using Composer:

composer require phpoffice/phpspreadsheet

Then you can use the following PHP code to import an Excel spreadsheet with multiple sheets into an array:

<?php
require 'vendor/autoload.php'; // Include PhpSpreadsheet's autoloader

use PhpOffice\PhpSpreadsheet\IOFactory;

$filename = 'path_to_your_excel_file.xlsx';

// Load the Excel spreadsheet
$spreadsheet = IOFactory::load($filename);

$data = [];

// Loop through each sheet
foreach ($spreadsheet->getSheetNames() as $sheetName) {
    $sheet = $spreadsheet->getSheetByName($sheetName);
    
    $sheetData = [];
    
    // Loop through each row in the sheet
    foreach ($sheet->getRowIterator() as $row) {
        $rowData = [];
        
        // Loop through each cell in the row
        foreach ($row->getCellIterator() as $cell) {
            // Get the value of the cell
            $rowData[] = $cell->getValue();
        }
        
        // Add the row data to the sheet data
        $sheetData[] = $rowData;
    }
    
    // Add the sheet data to the main data array
    $data[$sheetName] = $sheetData;
}

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

Replace 'path_to_your_excel_file.xlsx' with the path to your Excel file. This code will load each sheet of the Excel file into a separate array within the $data array. Each sheet's data will be stored under its sheet name as the key.

Remember to adjust this code according to your specific needs, like error handling, if required.