Skip to content

Instantly share code, notes, and snippets.

@depsimon
Created December 9, 2014 11:13
Show Gist options
  • Save depsimon/8d6ae104d8b7e1462604 to your computer and use it in GitHub Desktop.
Save depsimon/8d6ae104d8b7e1462604 to your computer and use it in GitHub Desktop.
PHPExcel - CSV to array helper function
<?php
/**
* Helper function to convert CSV sheet to key value array
* PHPExcel.php class required
* @param string $filePath path to CSV file
* @param string $delimiter delimiter
* @param string $enclosure enclosure
* @param string $lineEnding line ending
* @param integer $sheetIndex sheet index to read
* @param boolean $header set the first row as array keys
* @return array CSV rows
*/
function csvToArray( $filePath, $delimiter = ',', $enclosure = '"', $lineEnding = "\r\n", $sheetIndex = 0, $header = true ) {
//Create excel reader after determining the file type
$inputFileName = $filePath;
/** Identify the type of $inputFileName **/
$inputFileType = 'CSV';
/** Create a new Reader of the type that has been identified **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setDelimiter($delimiter);
$objReader->setEnclosure($enclosure);
$objReader->setLineEnding($lineEnding);
$objReader->setSheetIndex($sheetIndex);
/** 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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment