private static function importTableFromExcel($objPHPExcel, $tableName)
{
$activeSheet = $objPHPExcel->getSheetByName($tableName);
//$temp = $activeSheet->GetCell('A1')->getValue();
$highestRow = $activeSheet->getHighestRow();
// e.g. 10
$highestColumn = $activeSheet->getHighestColumn();
// e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
// e.g. 5
$insertTemplate = 'INSERT INTO ' . $tableName . ' (';
$strFormatBuilder = '';
for ($col = 0; $col < $highestColumnIndex; $col++) {
$columnName = $activeSheet->getCellByColumnAndRow($col, 1)->getValue();
if ($columnName && strlen(trim($columnName)) > 0) {
$insertTemplate = $insertTemplate . $columnName;
if (strpos(strtolower($columnName), '_id') === false) {
$strFormatBuilder = $strFormatBuilder . "'%s'";
} else {
$strFormatBuilder = $strFormatBuilder . "%d";
}
if ($col < $highestColumnIndex - 1) {
$insertTemplate = $insertTemplate . ', ';
$strFormatBuilder = $strFormatBuilder . ', ';
}
} else {
$highestColumnIndex = $col;
if (ExcelImportExport::stringEndsWith($insertTemplate, ', ')) {
$insertTemplate = substr($insertTemplate, 0, -2);
}
if (ExcelImportExport::stringEndsWith($strFormatBuilder, ', ')) {
$strFormatBuilder = substr($strFormatBuilder, 0, -2);
}
}
}
$insertTemplate = $insertTemplate . ') VALUES (' . $strFormatBuilder . ')';
$insertStatements = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$id = $activeSheet->getCellByColumnAndRow(0, $row)->getValue();
if ($id) {
$colValues = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$colValues[] = $activeSheet->getCellByColumnAndRow($col, $row)->getValue();
}
$colValues = ExcelImportExport::mysql_escape_mimic($colValues);
array_unshift($colValues, $insertTemplate);
$insertString = call_user_func_array('sprintf', $colValues);
//$temp = $temp . ' -- '. $insertString;
$insertStatements[] = $insertString;
}
}
return $insertStatements;
}
