exel.png

Upload data using excel sheet in CodeIgniter

In this example, we are going to show you how to How to import excel and CSV file in CodeIgniter framework MySQL PHP.

In this post, we will discuss how can we import Excel Sheet data into Mysql database in Codeigniter framework. For this things we will use third party PHPExcel library in Codeigniter for Import Excel sheet data into Mysql Database by using Ajax Jquery. Importing for Excel sheet data into Mysql database that means first we will read excel sheet data and then after we will store into PHP array and lastly we will perform batch operation that means in a single query execution we will insert multiple data. In Codeigniter framework there is one insert_batch() query builder method by using this method we can insert multiple data in single query execution.
  

There are 3 steps in which this can be done

First, you have to download this library and past it into the “third_party” folder inside the “application” folder

Download from here

Step 1:

First create a form that will take a excel file from user and send it to our server.

The code is as follows

This page will be your home page

<form action=”action.php” method=”post” enctype=”multipart/form-data”>
<div class=”form-group”>
<label for=”title”>select_file</label>
<input class=”form-control” type=”file” id=”file” name=”file” required>
<small class=”text-muted”>select_xlsx_file_format</small>
</div>

<div class=”text-right”>
<input type=”submit” name=”preview” value=”upload” class=”btn btn-success”>
</div>
</form>

Step 2:

Create action.php file.
In this file we will push the data of excel file into an array.
this will be our controller part
 {
    $filename  =  strtotime(date(‘D, d-M-Y’));
    $this->excel_model->upload_bulk_excel_file($filename);
    include APPPATH . ‘third_party/PHPExcel/PHPExcel.php’;
    $excelreader = new PHPExcel_Reader_Excel2007();
    $loadexcel = $excelreader->load(‘excel/’ . $filename . ‘.xlsx’);
    $sheet = $loadexcel->getActiveSheet()->toArray(nulltruetruetrue);
    $data = array();
    $numrow = 1;
    $total = sizeof($sheet) – 1;
    $slug = COMPANY_URL;

    foreach ($sheet as $row) {

      if ($numrow > 1) {

        array_push($data, array(
          ‘date_added’ =>   strtotime(date(‘D, d-M-Y’)),
          ‘company_url’ =>  $slug,
          ‘user_id’ =>  $row[‘B’],
          ‘course_id’ =>  $row[‘D’],
        ));
      }
      $numrow++;
    }
    $this->crud_model->insert_multiple_users($data);
    unlink(“excel/” . $filename . “.xlsx”);
    redirect(site_url(‘admin/enrol_history’));
  }
step 3:

Now in model make 2 functions one to upload the excel file and another is to save the data.

to  upload excel file use the following function
    public function upload_bulk_excel_file($filename)
    {
        $this->load->library(‘upload’);

        $config[‘upload_path’] = ‘./excel/’;
        $config[‘allowed_types’] = ‘xlsx’;
        $config[‘max_size’]    = ‘2048’;
        $config[‘overwrite’] = true;
        $config[‘file_name’] = $filename;

        $this->upload->initialize($config);
        if ($this->upload->do_upload(‘file’)) {
            $return = array(‘result’ => ‘success’‘file’ => $this->upload->data(), ‘error’ => );
            return $return;
        } else {
            $return = array(‘result’ => ‘failed’‘file’ => ‘error’ => $this->upload->display_errors());
            return $return;
        }
    }
The next function is to insert data in db
    public function insert_multiple_users($data)
    {
        $this->db->insert_batch(‘users’, $data);
    }

Tags: No tags

Leave A Comment

Your email address will not be published. Required fields are marked *