Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save calvinchoy/5821235 to your computer and use it in GitHub Desktop.
Save calvinchoy/5821235 to your computer and use it in GitHub Desktop.
PHP - excel to array helper function
<?php
/*
|--------------------------------------------------------------------------
| Excel To Array
|--------------------------------------------------------------------------
| Helper function to convert excel sheet to key value array
| Input: path to excel file, set wether excel first row are headers
| Dependencies: PHPExcel.php include needed
*/
function excelToArray($filePath, $header=true){
//Create excel reader after determining the file type
$inputFileName = $filePath;
/** Identify the type of $inputFileName **/
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
/** Create a new Reader of the type that has been identified **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Set read type to read cell data onl **/
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
//Get worksheet and built array with first row as header
$objWorksheet = $objPHPExcel->getActiveSheet();
//excel with first row header, use header as key
if($header){
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
++$r;
foreach($headingsArray as $columnKey => $columnHeading) {
$namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
}
}
}
}
else{
//excel sheet with no header
$namedDataArray = $objWorksheet->toArray(null,true,true,true);
}
return $namedDataArray;
}
?>
@kambona
Copy link

kambona commented Mar 13, 2016

Thanks,

@pakoneko
Copy link

pakoneko commented Sep 1, 2016

Nice method and improved my code

@sajjad-dehghani
Copy link

Very nice ...

@adaniello
Copy link

Thanks

@u-nel
Copy link

u-nel commented Sep 7, 2017

Thanks.

@jokeclancool
Copy link

Thanks

@nahmedpk
Copy link

Thanks man!

@hometgah
Copy link

Thanks man,

@jaydeepakbari
Copy link

Thanks

@willyopudo
Copy link

Thanks, got me going quickly..

@solomon3569
Copy link

Awesome!

@iamumairayub
Copy link

thanks a million

@xiaohutai
Copy link

thanks a billion

@lberriosa
Copy link

thanks !!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment