I'm not sure I undestand the second dimension of the three dimensional array that I created in $rowData.

test.xlsx contains 3 row and 2 columns of string values.

Also count($rowData) returns 12, not 6.

//  Include PHPExcel_IOFactory
include '../PHPExcel/Classes/PHPExcel/IOFactory.php';

$inputFileName = 'c:\DOCS\test.xlsx';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
echo $highestRow . "<br/>";
$highestColumn = $sheet->getHighestColumn();
echo $highestColumn. "<br/>";

//  Loop through each row of the worksheet in turn

for ($row = 1; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData[$row-1] = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row);
    echo $row . "<br/>";
    //  Insert row data array into your database of choice here
echo $rowData[0][0][0] . "<br/>";
echo $rowData[0][0][1] . "<br/>";
echo $rowData[1][0][0] . "<br/>";
echo $rowData[1][0][1] . "<br/>";
echo $rowData[2][0][0] . "<br/>";
echo $rowData[2][0][1] . "<br/>";

Edited by PerplexedB: error in code.

3 Years
Discussion Span
Last Post by PerplexedB

That looks like a rather odd use of the rangeToArray method. The selector you pass to it as the first parameter is capable of specifying a range of rows, as well as a range of columns. Using it only to select one row at a time, in a loop like that, is unnecessary; it can get all the data at once.

The extra dimension in your output is a result of this. The method returns a table; a two dimensional array with the given range. The outer array of it's result set indicates the row index. By selecting only one row at a time, that result set will only ever contain one row per loop iteration. Without accounting for that in your code, you are adding the row index twice, only the second time it will always be the index of the row in the result set: 0.

I suggest you replace your loop with this line:

$rowData = $sheet->rangeToArray('A1:' . $highestColumn . $highestRow);

That will get you the entire table into $rowData as the expected two-dimensional/table array. Like:

array (
  0 => array (
    0 => 1A,
    1 => 1B,
  1 => array (
    0 => 2A,
    1 => 2B,
  2 => array (
    0 => 3A,
    1 => 3B,

You can then loop through this array to do whatever it is you need to do with each of the rows.


Thanks. You're right. I added the [$row-1] to the script that showed how to convert an excel spreadsheet to a file in the database. I understand now that the idea is to save the record within the loop.
Without [$row-1] you end up with only the last row in $rowData of course, that's what made me add the it ([$row-1])

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.