Import and export database table to excel using phpexcel

Import and export database table to excel using phpexcel

In this post, I am going to show how to export data into excel sheet using PHPExcel. PHPExcel is one of the best third party library for import or export data.
1.) Download PHPExcel library.Unzip the folder inside application/third_party directory.
2.) Create a view inside application/views directory to listing all user’s data from database table to export and also to import from excel sheet.Find below the code.


user_import.php

<html>
<head>
<title>Import/Export Users </title>
</head>
<body>
<style>
h3
{
font-family: Verdana;
font-size: 14pt;
font-style: normal;
font-weight: bold;
color:red;
text-align: center;
}

table.tr{
font-family: Verdana;
color:black;
font-size: 12pt;
font-style: normal;
font-weight: bold;
text-align:left;
}
</style>
<h3><u>Import/Export Users</u></h3>

<?php echo form_open_multipart('user/import',array('name'=>'user_import')); ?>
    <table align="center" cellpadding = "5">
        <tr>
            <td>File :</td>
            <td><input type="file" size="40px" name="upload_file" /></td>
            <td class="error"><?php echo form_error('name'); ?></td>
            <td colspan="5" align="center">
            <input type="submit" value="Import Users"/></td>
        </tr>
    </table>
<?php echo form_close();?>
<?php echo form_open('user/export',array('name' => 'user_import')); ?>
    <table align="center" cellspacing="0" cellpadding="5" border="1">
        <thead>
<tr>
<th>Name</th>
<th>Mobile</th>
<th>Address</th>
</tr>
</thead>
<tbody>
<?phpforeach($users as $row) { ?>
<tr>
<td><?php echo $row->name; ?></td>
<td><?php echo $row->mobile; ?></td>
<td><?php echo $row->address; ?></td>
</tr>
<?php } ?>
        <tr>
            <td colspan="3" align="center">
      <input type="submit" value="Export Users"/></td>
        </tr>
</tbody>
    </table>
<?php echo form_close();?>
</body>
</html>

3.) create a class Excel.php inside myapp/application/library to initiate phpexcel library already exported inside application/third_party directory.Find below the code.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once APPPATH."/third_party/PHPExcel.php";
class Excel extends PHPExcel {
publicfunction __construct() {
parent::__construct();
}
}

?>

4.) Now let’s Create a Controller and put all import and export functionality into this.Find below the code.


ImportExportController.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class ImportExportController extends CI_Controller {
public function __construct()
{
parent::__construct();
$this->load->model('importexportmodel');
$this->load->library('excel');
}
public function index(){
   $data = array();
   $data['users'] = $this->db->get('tbl_user')->result();
    $this->load->view('user_import',$data);
}
public function import(){
ob_start();
$file = $_FILES["upload_file"]["tmp_name"];
if (!empty($file)) {
$valid = false;
$types = array('Excel2007', 'Excel5');
foreach ($types as $type) {
$reader = PHPExcel_IOFactory::createReader($type);
if ($reader->canRead($file)) {
$valid = true;
}
}
if (!empty($valid)) {
try {
$objPHPExcel = PHPExcel_IOFactory::load($file);
} catch (Exception $e) {
die("Error loading file :". $e->getMessage());
}
//All data from excel
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
for ($x =2; $x <= count($sheetData); $x++) {

$data['name'] = trim($sheetData[$x]["A"]);
$data['mobile'] = trim($sheetData[$x]["B"]);
$data['address'] = trim($sheetData[$x]["C"]);
$id = $this->importexportmodel->save($data);
}
} else {
echo "Sorry your uploaded file type not allowed ! please upload XLS/CSV File ";
exit;
}
} else {
echo "You did not Select File! please upload XLS/CSV File ";
exit;
}
redirect('user/importexport');
}
public function export() {
$allUsers = $this->db->get('tbl_user')->result();
$heading=array('Sl no.','Name','Mobile','Address');
$this->load->library('PHPExcel');
//Create a new Object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Users');
$rowNumberH = 1;
$colH = 'A';
foreach($heading as $h){
$objPHPExcel->getActiveSheet()->setCellValue($colH.$rowNumberH,$h);
$colH++;
}
//Loop Result
$row = 2;
$no = 1;
foreach($allUsers as $user){
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row,$no);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$row,$user->name);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$row,$user->mobile);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$row,$user->address);
$row++;
$no++;
}
$styleArray = array(
'borders'=> array(
'allborders'=> array(
'style'=> PHPExcel_Style_Border::BORDER_THIN
)
)
);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Users_'.date('Y-m-d').'.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
}
}
?>

5.) Create a Model file called ImportExportModel.php inside application/models which will interact to database table.Please find below the code.

<?php
class ImportExportModel extends CI_Model {
    private $table_name = 'tbl_user';
     public function __construct() {
     $this->load->database();
}
    public function save($data){
        $return = $this->db->insert($this->table_name, $data);
        return $this->db->insert_id();
    }
}

?>

6.) Change the routes inside application/config/routes.php

$route['user/importexport'] = 'ImportExportController/index';
$route['user/import'] = 'ImportExportController/import';
$route['user/export'] = 'ImportExportController/export';

Now if you visit http://localhost/myapp/user/importexport you should get a page like below.

Thank you for reading this post. we hope you like this Post, Please feel free to comment below, your suggestion and problems if you face – let us know. We’d love to help!