Import an XLSX file into a PHP array

Question

Is it possible to import each line of an XLSX file to a row in a PHP array?

Answer 1

You can use PHPExcel which is available here: https://phpexcel.codeplex.com/releases/view/119187

Here is what I use to read either xls or xlsx to an array:

require_once('/path/to/PHPExcel.php');

$filename = "example.xlsx";
$type = PHPExcel_IOFactory::identify($filename);
$objReader = PHPExcel_IOFactory::createReader($type);
$objPHPExcel = $objReader->load($filename);

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheets[$worksheet->getTitle()] = $worksheet->toArray();
}

print_r($worksheets);

UPDATE / 2022-02-13:

PhpSpreadsheet has been available for a few years now and has replaced PHPExcel. The following code is more or less the same as above with a couple small improvements:

1. Converted code to a function or method.
2. Auto detect filetype.
3. Added ability to specify how null values, formatting and formulas are handled.
4. Most importantly, call the destructor and clear memory. Without this last step I was running out of memory all the time after loading large files.

/**
 * Create a multidimensional array of worksheets from a filename.
 *
 * @param mixed $nullValue Value returned in the array entry if a cell doesn't exist
 * @param bool $calculateFormulas Should formulas be calculated?
 * @param bool $formatData Should formatting be applied to cell values?
 *
 * @return array
 */
function spreadsheet_to_array($nullValue = null, $calculateFormulas = true, $formatData = false) {
    $results = [];
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
    foreach ($spreadsheet->getWorksheetIterator() as $worksheet) {
        $results[$worksheet->getTitle()] = $worksheet->toArray($nullValue, $calculateFormulas, $formatData);
    }
    // save memory
    $spreadsheet->__destruct();
    $spreadsheet = NULL;
    unset($spreadsheet);
    return $results;
}