1

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:

  1. 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)
    }
    
  2. controllers/fileuploads - insertData method: this method calls different model methods based on which section's sheet is uploaded. Ex. Section Test will call a method getInsertSectionTest() from the model. There are 6 different sections.

  3. 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.

  • the blank screen is almost certainly a fatal error that is hidden. can you turn on all errors and stick the following prior to the code you are running? ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL); – jamesgarrett Feb 8 '17 at 8:01
  • @jamesgarrett thanks james but i tried this earlier and it didn't return anything. However, i got the solution. Check the answer – Liz. Feb 8 '17 at 8:47
2

I solved the issue. Problem was that when insert query was called in a loop 2000 times, deadlock occurred and returned a blank page. The solution to this is to construct one single insert query and then insert the values. Following is the code I added in model file -

Instead of calling insert method in loop I formed the insert values array:

$getValues = array();

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
        $getValues[] = $this->getValuesModel($rowData, $data);
    }

}// excel for loop ends

$columns = $this->getColumns($data); //call columns method to fetch different section's columns

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert($db->quoteName($tablename));
$query->columns($columns);
$query->values($getValues);
$db->setQuery($query);
$isInserted = $db->query();

if($isInserted){
    //redirect after importing the data from excel into database
    $this->setRedirect(JRoute::_('index.php?option=com_componentname&view=fileuploads', false), 'File uploaded successfully','message');
}

For complete code and files, you refer Github.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.