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;
}

Answer 2

I use this:

include 'simplexlsx.class.php';
$xlsx = @(new SimpleXLSX('myFile.xlsx'));
$data =  $xlsx->rows();

You can simplexslx from here.

UPDATE

Apparently the link above doesn't work anymore. You can now use this. (Thanks @Basti)

Answer 3

Problem can be solved using PHPExcel library:

$data = [];

$type = PHPExcel_IOFactory::identify($filepath);
$objReader = PHPExcel_IOFactory::createReader($type);

$objPHPExcel = $objReader->load($filepath);

$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
foreach($rowIterator as $row){
    $cellIterator = $row->getCellIterator();
    foreach ($cellIterator as $cell) {
        $data[$row->getRowIndex()][$cell->getColumn()] = $cell->getCalculatedValue();
    }
}

where $filepath - path to your xls or xlsx file.

Answer 4

Yes with phpspreadsheet :

  include 'vendor/autoload.php';
        if($_FILES["import_excel"]["name"] != '')
        {
            $allowed_extension = array('xls', 'csv', 'xlsx');
            $file_array = explode(".", $_FILES["import_excel"]["name"]);
            $file_extension = end($file_array);
            if(in_array($file_extension, $allowed_extension))
            {
                $file_name = time() . '.' . $file_extension;
                move_uploaded_file($_FILES['import_excel']['tmp_name'], $file_name);
                $file_type = \PhpOffice\PhpSpreadsheet\IOFactory::identify($file_name);
                $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($file_type);
                $spreadsheet = $reader->load($file_name);
                unlink($file_name);
                $data = $spreadsheet->getActiveSheet()->toArray();
                foreach($data as $row)
                {
                    $insert_data = array(
                        ':test1'          =>  $row[0],
                        ':test2'          =>  $row[1],
                        ':test3'          =>  $row[2],
                        ':test4'          =>  $row[3]
                    );
                 };
                $query = "
                    INSERT INTO post
                    (  test1, test2, test3, test4)
                    VALUES
                    ( :test1, :test2, :test3, :test4)
                ";
                $statement = $connect->prepare($query);
                $statement->execute($insert_data);
             }
             echo "succes";
        }else{
           echo "only xls,csv,xlsx are allowed";
        }

Answer 5

With the new version of PHPSpreadSheet you can simply do that :

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

/*...*/

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('upload/file.xls');
$sheet = $spreadsheet->getSheet($spreadsheet->getFirstSheetIndex());
$data = $sheet->toArray();

Just be careful, you have all cells as value. For exemple, date is converted to int so you need to convert it

You can use NumberFormat to see all converter.

Exemple to convert an int cell to date :

use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
/*....*/
//$value is an integer of a cell value
$value = 44823
$stringDate = NumberFormat::toFormattedString($value, 'YYYY-MM-DD');
// 2022-09-19 is displayed
echo $stringDate;

Found here : https://blog.programster.org/phpspreadsheet-read-excel-file-to-array

More information in the documentation : https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files/ https://phpspreadsheet.readthedocs.io/en/latest/

Source code of NumberFormat : https://phpoffice.github.io/PhpSpreadsheet/classes/PhpOffice-PhpSpreadsheet-Style-NumberFormat.html

Answer 6

<?php
require_once 'SimpleXLSX.php';

if ( $xlsx = SimpleXLSX::parse('pricelist.xlsx') ) {
  print_r( $xlsx->rows() );
} else {
  echo SimpleXLSX::parseError();
}
?>

SimpleXLSX