I m using PHPExcel to read data from Excel sheet and store in mysql table, till now i m able to upload .xls as well as .xlsx file and after uploading the xls i got below table structure of data
name start_date end_date city
one 11/25/2011 3:30:00 PM 11/29/2011 4:40:00 AM Jaipur
two 10/22/2011 5:30:00 PM 10/25/2011 6:30:00 AM Kota
three 3/10/2011 2:30:00 PM 3/11/2011 12:30:00 AM Bikaner
chandigarh
now i have some problems, please suggest me the optimized method
for now what i do to store these data into db, below is the code snippet
$inputFileName = "test.xls";
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);
$total_sheets=$objPHPExcel->getSheetCount(); // here 4
$allSheetName=$objPHPExcel->getSheetNames(); // array ([0]=>'student',[1]=>'teacher',[2]=>'school',[3]=>'college')
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0); // first sheet
$highestRow = $objWorksheet->getHighestRow(); // here 5
$highestColumn = $objWorksheet->getHighestColumn(); // here 'E'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // here 5
for ($row = 1; $row getCellByColumnAndRow($col, $row)->getValue();
if(is_array($arr_data) ) { $arr_data[$row-1][$col]=$value; }
}
}
print_r($arr_data);
and this returns
Array
(
[0] => Array
(
[0] => name
[1] => start_date
[2] => end_date
[3] => city
[4] =>
)
[1] => Array
(
[0] => one
[1] => 40568.645833333
[2] => 40570.5
[3] => Jaipur
[4] =>
)
[2] => Array
(
[0] => two
[1] => 40568.645833333
[2] => 40570.5
[3] => Kota
[4] =>
)
[3] => Array
(
[0] => three
[1] => 40568.645833333
[2] => 40570.5
[3] => Bikaner
[4] =>
)
[4] => Array
(
[0] =>
[1] =>
[2] =>
[3] => Chandigarh
[4] =>
)
)
i need that
i.e. desired array should look like
Array
(
[0] => Array
(
[name] => one
[start_date] => 11/25/2011 3:30:00 PM
[end_date] => 11/29/2011 4:40:00 AM
[city] => Jaipur
)
[1] => Array
(
[name] => two
[start_date] => 10/22/2011 5:30:00 PM
[end_date] => 10/25/2011 6:30:00 AM
[city] => Kota
)
[2] => Array
(
[name] => three
[start_date] => 3/10/2011 2:30:00 PM
[end_date] => 3/11/2011 12:30:00 AM
[city] => Bikaner
)
)
and after that i store data into my db using mysql action on desired array.
@Mark Thanks for your solution, it helps me a lot, but still some problems are there
Notice: Undefined index: C in C:\xampp\htdocs\xls_reader\Tests\excel2007.php on line 60
Notice: Undefined index: D in C:\xampp\htdocs\xls_reader\Tests\excel2007.php в строке 60
, тогда как в строке 60 указано
foreach($headingsArray as $columnKey => $columnHeading) {
$namedDataArray[$r][$columnHeading] = $dataRow[$row]$columnKey];
}
спасибо