CODEWITHSUNDEEP
phpxlsx
ddfgdfdfgdfgdfgfdgfdgdg
ddfgdfdfgdfgdfgfdgfdgdg

Reputation: 335

Import an XLSX file into a PHP array

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

Upvotes: 20

Views: 46687

Answers (6)

HRoux
HRoux

Reputation: 505

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

Upvotes: 1

You Old Fool
You Old Fool

Reputation: 22950

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

Upvotes: 22

Sergey Shuchkin
Sergey Shuchkin

Reputation: 2197

<?php
require_once 'SimpleXLSX.php';

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

SimpleXLSX

Upvotes: 0

Wassim Redaouia
Wassim Redaouia

Reputation: 11

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

Upvotes: 1

ns16
ns16

Reputation: 1560

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.

Upvotes: 2

rockstardev
rockstardev

Reputation: 13537

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)

Upvotes: 15

Related Questions