I am developing a component from which user can upload excel sheet and the data must be saved into database. When excel sheet rows are 250
then insertion works fine but when the rows are 2500
or more it gives a blank page with no error and rows are not inserted completely into the database. I am using PHPExcel
library for importing excel sheets. Following is the code:
controllers/fileuploads.php:
public function uploadData($fileData, $data,$tablename){ //Validate file by checking if excel sheet is uploaded $isValid = $this->validateFileType($fileData); if($isValid){ $inputFilename = $fileData['excelfile']['tmp_name']; try { $objPHPExcel = PHPExcel_IOFactory::load($inputFilename); //read the spreadsheet workbook } catch (Exception $e) { die('Error loading file "'.pathinfo($inputFilename,PATHINFO_BASENAME).'": '.$e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); for ($row = 4; $row <= $highestRow; $row++){ // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row); if(!empty($rowData[0][0])){ //if row is null // Insert row data array into database $isInserted = $this->insertData($rowData, $data); } }// excel for loop ends if($isInserted){ //redirect after importing the data from excel into database $this->setRedirect(JRoute::_('index.php?option=com_mycomponent&view=fileuploads', false), 'File uploaded successfully','message'); } }//if($isValid) }
controllers/fileuploads - insertData method: this method calls different model methods based on which section's sheet is uploaded. Ex.
Section Test
will call a methodgetInsertSectionTest()
from the model. There are 6 different sections.models/fileupload.php: Following is the code for inserting data
public function getInsertSectionTest($rowData, $data){ $objData = new stdClass(); $objData->section_id = $data['section'][0]; $objData->category = str_replace("'","",$rowData[0][0]); $objData->subcategory = str_replace("'","",$rowData[0][6]); $objData->year = $data['year']; $objData->number_type = $rowData[0][7]; . . // and so on . $objData->region_northwest = $rowData[0][43]; $objData->region_southwest = $rowData[0][44]; $objData->region_unknown = $rowData[0][45]; $insertResult = JFactory::getDbo()->insertObject('#__testsection_tablename', $objData); if( $insertResult){ return true; } else{ $this->setRedirect(JRoute::_('index.php?option=com_mycomponent&view=fileuploads', false), 'Data could not be imported. Please try again' ,'error'); return false; } }
Kindly, let me know how more than 2000 rows be inserted at once. I also referred this question but not able to form the array of values to be inserted.