Export Data from Excel to Database in PHP Codeigniter using PHPExcel Library

Codeigniter PHP

This section cover how toimport Data from excel to Database(MySql)  in Codeigniter Framework by using PHPExcel library. For this follow the instructions given below,

Step-1 : Open database.php file under application/config folder of your codeigniter application.

update hostname, username, password and database.

Step-2 : Open config.php file under application/config folder of your codeigniter application and fill    $config[‘base_url’].

Step-3: Create a controller called Import_controller.php and paste the below code in your controller.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

<?php  defined(‘BASEPATH’) OR exit(‘No direct script access allowed’);
class Import_controller extends CI_Controller {
public function __construct() {
parent::__construct();
$this->load->helper(array(‘form’,’url’,’file’));
}
public function index()
{
$this->load->view(‘import/upload’);
}

public function importUploadData()

{
if(isset($_POST[‘btnUpload’]))
{

$config[‘upload_path’] = ‘./uploads/’;
$config[‘allowed_types’] = ‘xls|ods’;
$this->upload->initialize($config);
if($this->upload->do_upload(‘fileUpload’))
{
$this->upload->data();
$this->load->library(‘excel’);
$filename = $this->upload->data(‘file_name’);
$ext = $this->upload->data(‘file_ext’);
if($ext == “.ods”){
$objectImport = PHPExcel_IOFactory::createReader(‘OOCalc’); // create object for ods
}elseif($ext == “.xls”){
$objectImport = PHPExcel_IOFactory::createReader(‘Excel5’); // create object for 2003 excel
}

$object = $objectImport->load(‘./uploads/’.$filename); //load excel file
$countRows = $object->setActiveSheetIndex(0)->getHighestRow();
$i = 2; $fields = array();
while($i < $countRows) { $carid = $object->setActiveSheetIndex(0)->getCellByColumnAndRow(0,$i)->getValue();
$carname = $object->setActiveSheetIndex(0)->getCellByColumnAndRow(1,$i)->getValue();
$carcolor = $object->setActiveSheetIndex(0)->getCellByColumnAndRow(2,$i)->getValue();
$fields = array(
‘car_id’ => $carid,
‘car_name’ => $carname,
‘car_color’ => $carcolor
);
$i++;
}
$this->load->model(‘Import_model’);
$this->Import_model->uploadData($fields);
redirect(‘import_controller/index’);
}
}
}
}

Step-4 : Create a model for insert data to database and name the model as Import_model.php, paste below code into the model.
( Create a table with fields for inserting the data to database.Here the database name is car and fields are car_id,car_name and car_color.)

1
2
3
4
5
6
7
8
9
10
11
<?php
defined(‘BASEPATH’) OR exit(‘No direct script access allowed’);
class Import_model extends CI_Model
{
public function fuploadData($input) {
$this->db->insert(‘car’,$input);
return true;
}
}
?>

 

Step-5 : Use PHPExcel Library to export data into excel. For this we can download PHPExcel Library from https://github.com/PHPOffice/PHPExcel

Step-6 : Extract the downloaded file. Then copy PHPExcel folder and PHPExcel.php file from extracted folder  and paste this under application/libraries folder of your codeigniter application.

Step-7 : Create a new file in your application/libraries folder and name it as  Excel.php, after paste below code.

1
2
3
4
5
6
7
8
9
<?php
if(!defined(‘BASEPATH’)) exit(‘No direct script access allowed’);
require_once(‘PHPExcel.php’);
class Excel extends PHPExcel{
public function __construct() {
parent::__construct();
}
}
?>

Step-8 : Create another file under application/libraries, named as IOFactory.php. which used for extending the properties of excel library to export the data into excel.

1
2
3
4
5
6
7
8
9
<?php
if(!defined(‘BASEPATH’)) exit(‘No direct script access allowed’);
require_once(‘PHPExcel/IOFactory.php’);
class IOFactory extends PHPExcel_IOFactory{
public function __construct() {
parent::__construct();
}
}
?>

Step-9 : Create a view for the data fetching from database and  to download the data into excel format. For this create a file upload.php in application/views/import and paste the below code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<html>

<head>
<title>Upload</title>
<!– Latest compiled and minified CSS –>
<link rel=”stylesheet” href=”https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css”>
<!– jQuery library –>
<script src=”https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js”></script>
<!– Latest compiled JavaScript –>
<script src=”https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js”></script>
</head>
<body>
<div class=”container”>
<h2>Export Data from Excel to Database in PHP Codeigniter using PHPExcel Library</h2>
<form method=’POST’ enctype=’multipart/form-data’ action=”<?php echo site_url(‘Import_controller/importUploadData’);?>” >
<div class=”form-group”>
<div class=”col-md-4″><input type=”file” name=”fileUpload”/ class=”form-control”></div>
<div class=”col-md-4″><button type=”submit” name=”btnUpload” class=”btn btn-info”>UPLOAD</button></div> </div>
</form>
</div>
</body>
</html>

Step-10 :  Open your browser and type the url . See the result.

Leave a Reply

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