Reading excel file and writing to database using PHPExcel with Codeigniter

Question

I want to write some information in the excel file to my database. The excel file looks like this.

ID123 | subj1 | 50

ID456 | subj2 | 60

ID786 | subj3 | 70

This is the function in view that triggers the function in the controller.

<a href="http://localhost/SEP/index.php/con_test/readExcel"> Click me </a>

This is the code in the controller.

public function readExcel(){            

        $inputFileName = 'C:\wamp\www\SEP\uploads\Format.xlsx';

        //  Read your Excel workbook
        try {
            $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } 

        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();

        //  Loop through each row of the worksheet in turn
        for ($row = 1; $row <= $highestRow; $row++){ 
            //  Read a row of data into an array
            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                            NULL,
                                            TRUE,
                                            FALSE);
            //  Insert row data array into your database of choice here
            $this->mod_test->insertMarks($rowData);
        }
    }

Plus i have included include '../third_party/PHPExcel/IOFactory.php'; on top of the controller im using for this.

This is the code in my model.

public function insertMarks($data){

    $this->db->insert('marks', $data);
    return;
}

This is the first time im using this. i dont get any error and values are not inserted to the db as well. Please help me with this.

score 0 · Answer 1 · answered Nov 13 '15 at 09:32

0

The return from a call to rangeToArray ($rowData) is a 2-d array, even for a single row.... I'd guess that you might want to flatten it to a 1d array before passing it to insertMarks()

$this->mod_test->insertMarks($rowData[0]);

answered Nov 13 '15 at 09:32

Mark Baker

209,507
32
346
385

Then look at your db `insert()` function..... debug what's being passed to it, and what SQL query is being generated from that data..... I don't know what your db `insert()` function actually does, which is why my suggested solution had to be a guess based on what I know of PHPExcel – Mark Baker Nov 13 '15 at 12:08

score 0 · Answer 2 · answered Nov 14 '15 at 22:45

Try this

<?php
include ("./Classes/PHPExcel/IOFactory.php");

      $inputFileName = './marks.xlsx';

        //  Read your Excel workbook
        try {
            $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } 

        catch(Exception $e) {
            die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
        }

        $sheetInsertData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);


      //Read excel rows
      foreach($sheetInsertData as $rec)
      { 
          //If you want row as a array use $rec
          $this->mod_test->insertMarks($rec);

          //Get column values from row array
          /*foreach($rec as $recm)
          {
               echo $recm."<br>";
          }*/
      }
?>

Reading excel file and writing to database using PHPExcel with Codeigniter

2 Answers2