How to import data from excel to mysql database using php

Question

I am using PHPExcel to import a XLSX file to my related database. But while running the function I am getting the error. My code looks like shown below.

Controller:

   <?php    if (!defined ('BASEPATH')) exit ('No direct access allowed');
      class ExcelController extends CI_Controller
      {


      public function index()
      {
          //load library excel
          $this->load->library('excel');

          //Here i used microsoft excel 2007
          $objReader= PHPExcel_IOFactory::createReader('Excel2007');

          //Set to read only
          $objReader->setReadDataOnly(true);


          //Load excel file
          $objPHPExcel=$objReader->load('data.xls'); // error in this line
          $objWorksheet=$objPHPExcel->setActiveSheetIndex(0);

          //load model

          $this->load->model('user_model');

          //loop from first data untill last data
          for($i=2;$i<=77;$i++)
          {
              $name= $objWorksheet->getCellByColumnAndRow(0,$i)->getValue();
              $address= $objWorksheet->getCellByColumnAndRow(1,$i)->getValue();

              $data_user=array('name'=>$name, 'username'=>$address);

              $this->user_model->add_data($data_user);
          }

      }


  }           

  ?>

model:

 <?php
if (!defined ('BASEPATH')) exit ('No direct access allowed');
    class User_model extends CI_Controller
{
    public function __construct() {
        parent::__construct();
    }

    public function add_data($data_user)
    {
        $this->load->database();

        $this->db->insert('data',$data_user);
        return $this->db->insert_id();

    }
}

?>

Error in my code:

Fatal error: Uncaught exception 'PHPExcel_Reader_Exception' with message 'Could not open data.xls for reading! File does not exist.' in C:\xampp\htdocs\ci_excel\application\third_party\PHPExcel\Reader\Excel2007.php:347 Stack trace: #0 C:\xampp\htdocs\ci_excel\application\controllers\excelcontroller.php(19): PHPExcel_Reader_Excel2007->load('data.xls') #1 [internal function]: ExcelController->index() #2 C:\xampp\htdocs\ci_excel\system\core\CodeIgniter.php(359): call_user_func_array(Array, Array) #3 C:\xampp\htdocs\ci_excel\index.php(202): require_once('C:\xampp\htdocs...') #4 {main} thrown in C:\xampp\htdocs\ci_excel\application\third_party\PHPExcel\Reader\Excel2007.php on line 347

Solution

Judging by the error message and your comment, it looks like you are using an incorrect filepath.

$objPHPExcel=$objReader->load('data.xls');

In CodeIgniter paths are relative to the entry script, usually index.php.

Use a relative file path to this location or alternatively an absolute path.

Comments

[bhanu]

Thank you for the reply. I am beginner. can you suggest me where i save my file?

[Mitch Satchwell]

Either move it to the same folder as the CodeIgniter entry script or reference the file path as Shanoop described ('./application/third_party/Excel5/data.xlsx')

[bhanu]

thank you!! but i am receive error Fatal error: Uncaught exception 'PHPExcel_Reader_Exception'....

[Sanoob]

@bhanu I not familiar with PHPEXcel check this link phpexcel.codeplex.com/discussions/61890

[bhanu]

i got error again. could i need some configuration??