CODEWITHSUNDEEP
phplaravelphpexcelphpspreadsheet
oliverbj
oliverbj

Reputation: 6062

PHP - PHPExcel Range to Array ignores empty column

I have below the Excel sheet, where I want to get the range A1:D4 into a PHP array.

enter image description here

For this, I am using PHPExcel as below. I have a simple Excel class:

Excel.php

public function __construct($file)
{
     if ($file instanceof \SplFileInfo) {
         $filename = $file->getRealPath();
     } else {
         $filename = $file;
     }

     $this->objPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
     $this->objPHPExcel->setActiveSheetIndex(0);
}

And then I have a simple function to get the range, using the rangeToArray() function:

public function getRange($range)
{
    $spreadsheet = $this->objPHPExcel;
    return $spreadsheet->rangeToArray($range,'  ', true, true, true);

}

$excel = new ExcelManipulator(storage_path() . '/app/temp_files/myfile.xlsx');
$array = $excel->getRange($range);

return response()->json(['result' => $array], 200);

Now the problem is that the above function "switches" the columns. See below output:

[
  '1': [
     "A": "  ", 
     "B": "  ",
     "C": "  ",
     "D": "  "
  ],
  '2': [
     "A": "Company", 
     "B": "Acme Inc",
     "C": "  ",
     "D": "  "
  ],
  '3': [
     "A": "Address", 
     "B": "New York",
     "C": "  ",
     "D": "  "
  ],
  '4': [
     "A": "  ", 
     "B": "  ",
     "C": "  ",
     "D": "  "
  ]
]

As you can see in the array for row 2 and 3, the company and address text starts already in Column A, while they should start in Column B

Upvotes: 3

Views: 1588

Answers (2)

diegowc
diegowc

Reputation: 455

This worked fine for me using php 7.2.26 and phpoffice/phpspreadsheet 1.10.1. I created an xlsx file with the same cell values as yours.

$excel = new Excel('file.xlsx');
$range = 'A1:D4';
$array = $excel->getRange($range);

var_dump($array);

class Excel
{
  private $objPHPExcel;

  public function __construct($file)
  {
     if ($file instanceof \SplFileInfo) {
         $filename = $file->getRealPath();
     } else {
         $filename = $file;
     }

     $this->objPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
     $this->objPHPExcel->setActiveSheetIndex(0);
  }

  public function getRange($range)
  {
    $spreadsheet = $this->objPHPExcel->getActiveSheet();
    return $spreadsheet->rangeToArray($range,'  ', true, true, true);
  }
}

And this is the output:

array(4) {
  [1] =>
  array(4) {
    'A' =>
    string(2) "  "
    'B' =>
    string(2) "  "
    'C' =>
    string(2) "  "
    'D' =>
    string(2) "  "
  }
  [2] =>
  array(4) {
    'A' =>
    string(2) "  "
    'B' =>
    string(7) "Company"
    'C' =>
    string(8) "Acme Inc"
    'D' =>
    string(2) "  "
  }
  [3] =>
  array(4) {
    'A' =>
    string(2) "  "
    'B' =>
    string(7) "Address"
    'C' =>
    string(8) "New York"
    'D' =>
    string(2) "  "
  }
  [4] =>
  array(4) {
    'A' =>
    string(2) "  "
    'B' =>
    string(2) "  "
    'C' =>
    string(2) "  "
    'D' =>
    string(2) "  "
  }
}

Upvotes: 1

Fabrice Fabiyi
Fabrice Fabiyi

Reputation: 361

You can try it

function getRange($range, $inputFileName)
{
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFilename);
    return $spreadsheet->getActiveSheet()->rangeToArray($range," ", true, true, true);

}
$inputFilename = storage_path() . '/app/temp_files/myfile.xlsx';
$array = getRange($range, $inputFilename);

Upvotes: 4

Related Questions