I've built a system where the client uploads data to it using a spreadsheet. It was using CSV's but the data contained within them mean that the upload wasn't too reliable. So instead I'm going to use PHPExcel and the PHPExcel bundle for Symfony2 to read the uploaded Excel spreadsheet and insert this data to the database.
I have two problems with using PHPExcel. The first is that there will be a column in the uploaded file that contains a date in a UK format (DD/MM/YYYY). However, obviously, MySQL uses an American format (YYYY-MM-DD). So what I'd like to do is format that one particular cell to use the YYYY-MM-DD format instead of DD/MM/YYYY.
The second issue I have is that I'd like to then insert all of this data in to the MySQL database in the most efficient way possible. I'm not sure exactly how best to way to do this. I know I will have memory issues with PHPExcel when using huge spreadsheets, so I want to make sure that saving to the database is the most efficient as possible.
So far, as I've been concentrating on reading the Excel file, this is my code:
$objPHPExcel = $this->get('phpexcel')->createPHPExcelObject($dir.$fileName);
$objPHPExcel->setActiveSheetIndex(0);
$row = $objPHPExcel->getActiveSheet()->getHighestRow()+1;
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
echo 'Worksheet - ' , $worksheet->getTitle();
echo '<table>';
foreach ($worksheet->getRowIterator() as $row) {
//echo '<trRow number - ' , $row->getRowIndex() , EOL;
echo '<tr>';
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
foreach ($cellIterator as $cell) {
if (!is_null($cell)) {
echo '<td>'.$cell->getFormattedValue().'</td>';
}
}
echo '<tr>';
}
echo '</table>';
}
When trying to format the individual cell, I then changed my code to this:
$objPHPExcel = $this->get('phpexcel')->createPHPExcelObject($dir.$fileName);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
echo 'Worksheet - ' , $worksheet->getTitle();
echo '<table>';
foreach ($worksheet->getRowIterator() as $row) {
//echo '<trRow number - ' , $row->getRowIndex() , EOL;
echo '<tr>';
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
foreach ($cellIterator as $cell) {
if (!is_null($cell)) {
echo '<td>'.$cell[0]->getFormattedValue().'</td>';
echo '<td>'.$cell[1]->getFormattedValue().'</td>';
echo '<td>'.$cell[2]->getFormattedValue().'</td>';
echo '<td>'.$cell[3]->getFormattedValue().'</td>';
echo '<td>'.$cell[4]->getFormattedValue().'</td>';
echo '<td>'.$cell[5]->getFormattedValue().'</td>';
echo '<td>'.$cell[6]->getFormattedValue().'</td>';
echo '<td>'.$cell[7]->getFormattedValue().'</td>';
echo '<td>'.$cell[8]->getFormattedValue().'</td>';
echo '<td>'.$cell[9]->getFormattedValue().'</td>';
echo '<td>'.date('Y-m-d', strtotime($cell[10]->getFormattedValue())).'</td>';
echo '<td>'.$cell[11]->getFormattedValue().'</td>';
echo '<td>'.$cell[12]->getFormattedValue().'</td>';
echo '<td>'.$cell[13]->getFormattedValue().'</td>';
echo '<td>'.$cell[14]->getFormattedValue().'</td>';
echo '<td>'.$cell[15]->getFormattedValue().'</td>';
echo '<td>'.$cell[16]->getFormattedValue().'</td>';
echo '<td>'.$cell[17]->getFormattedValue().'</td>';
echo '<td>'.$cell[18]->getFormattedValue().'</td>';
echo '<td>'.$cell[19]->getFormattedValue().'</td>';
echo '<td>'.$cell[20]->getFormattedValue().'</td>';
echo '<td>'.$cell[21]->getFormattedValue().'</td>';
}
}
echo '<tr>';
}
echo '</table>';
}
But this gave the following error in the Apache2 log:
PHP Fatal error: Cannot use object of type PHPExcel_Cell as array
Any help would be appreciated.