First, We need to download PHPExcel Library. Then extract PHPExcel Library
Step 1: Extract PHPExcel Library
Note: Copy and Paste inside “application/third_party” folder.
Step 2: Create file
Create a file named Excel.php inside “application/libraries” folder.
if (!defined('BASEPATH')) exit('No direct script access allowed');
/*
* =======================================
* Author : Team Tech Arise
* License : Protected
* Email : info@techarise.com
*
* =======================================
*/
require_once APPPATH . "/third_party/PHPExcel.php";
class Excel extends PHPExcel {
public function __construct() {
parent::__construct();
}
}
?>
Step 3: Create Database and Table: DB Name: import_DB and Table Name:import
For this tutorial, you need a MySQL database with the following table:
//Table structure for table import
CREATE TABLE `import` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`first_name` varchar(100) NOT NULL COMMENT 'First Name',
`last_name` varchar(100) NOT NULL COMMENT 'Last Name',
`email` varchar(255) NOT NULL COMMENT 'Email Address',
`dob` varchar(20) NOT NULL COMMENT 'Date of Birth',
`contact_no` int(11) NOT NULL COMMENT 'Contact No',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;
?>
Step 4: Create Controller and load class
Syntax:
Load “excel” class in controller.
$this->load->library('excel');
?>
Create a controller file like contactus.php inside “application/controllers” folder.
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
* Description of Import Controller
*
* @author TechArise Team
*
* @email info@techarise.com
*/
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Import extends CI_Controller {
public function __construct() {
parent::__construct();
$this->load->model('Import_model', 'import');
}
// upload xlsx|xls file
public function index() {
$data['page'] = 'import';
$data['title'] = 'Import XLSX | TechArise';
$this->load->view('import/index', $data);
}
// import excel data
public function save() {
$this->load->library('excel');
if ($this->input->post('importfile')) {
$path = ROOT_UPLOAD_IMPORT_PATH;
$config['upload_path'] = $path;
$config['allowed_types'] = 'xlsx|xls|jpg|png';
$config['remove_spaces'] = TRUE;
$this->upload->initialize($config);
$this->load->library('upload', $config);
if (!$this->upload->do_upload('userfile')) {
$error = array('error' => $this->upload->display_errors());
} else {
$data = array('upload_data' => $this->upload->data());
}
if (!empty($data['upload_data']['file_name'])) {
$import_xls_file = $data['upload_data']['file_name'];
} else {
$import_xls_file = 0;
}
$inputFileName = $path . $import_xls_file;
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());
}
$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$arrayCount = count($allDataInSheet);
$flag = 0;
$createArray = array('First_Name', 'Last_Name', 'Email', 'DOB', 'Contact_NO');
$makeArray = array('First_Name' => 'First_Name', 'Last_Name' => 'Last_Name', 'Email' => 'Email', 'DOB' => 'DOB', 'Contact_NO' => 'Contact_NO');
$SheetDataKey = array();
foreach ($allDataInSheet as $dataInSheet) {
foreach ($dataInSheet as $key => $value) {
if (in_array(trim($value), $createArray)) {
$value = preg_replace('/\s+/', '', $value);
$SheetDataKey[trim($value)] = $key;
} else {
}
}
}
$data = array_diff_key($makeArray, $SheetDataKey);
if (empty($data)) {
$flag = 1;
}
if ($flag == 1) {
for ($i = 2; $i <= $arrayCount; $i++) {
$addresses = array();
$firstName = $SheetDataKey['First_Name'];
$lastName = $SheetDataKey['Last_Name'];
$email = $SheetDataKey['Email'];
$dob = $SheetDataKey['DOB'];
$contactNo = $SheetDataKey['Contact_NO'];
$firstName = filter_var(trim($allDataInSheet[$i][$firstName]), FILTER_SANITIZE_STRING);
$lastName = filter_var(trim($allDataInSheet[$i][$lastName]), FILTER_SANITIZE_STRING);
$email = filter_var(trim($allDataInSheet[$i][$email]), FILTER_SANITIZE_EMAIL);
$dob = filter_var(trim($allDataInSheet[$i][$dob]), FILTER_SANITIZE_STRING);
$contactNo = filter_var(trim($allDataInSheet[$i][$contactNo]), FILTER_SANITIZE_STRING);
$fetchData[] = array('first_name' => $firstName, 'last_name' => $lastName, 'email' => $email, 'dob' => $dob, 'contact_no' => $contactNo);
}
$data['employeeInfo'] = $fetchData;
$this->import->setBatchImport($fetchData);
$this->import->importData();
} else {
echo "Please import correct file";
}
}
$this->load->view('import/display', $data);
}
}
?>
Step 5: Create Model
Create a model file named Import_model.php inside “application/models” folder.
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
/**
* Description of Import Model
*
* @author TechArise Team
*
* @email info@techarise.com
*/
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Import_model extends CI_Model {
private $_batchImport;
public function setBatchImport($batchImport) {
$this->_batchImport = $batchImport;
}
// save data
public function importData() {
$data = $this->_batchImport;
$this->db->insert_batch('import', $data);
}
// get employee list
public function employeeList() {
$this->db->select(array('e.id', 'e.first_name', 'e.last_name', 'e.email', 'e.dob', 'e.contact_no'));
$this->db->from('import as e');
$query = $this->db->get();
return $query->result_array();
}
}
?>
Step 6: Create views (Upload Excel file)
Create a views file named index.php inside “application/views/import” folder.
Lawyers Overview
$output = '';
$output .= form_open_multipart('import/save');
$output .= '';';
$output .= '';';*Please choose an Excel file(.xls or .xlxs) as Input
$output .= form_label('Import Lawyers', 'image');
$data = array(
'name' => 'userfile',
'id' => 'userfile',
'class' => 'form-control filestyle',
'value' => '',
'data-icon' => 'false'
);
$output .= form_upload($data);
$output .= '
$output .= '';
$data = array(
'name' => 'importfile',
'id' => 'importfile-id',
'class' => 'btn btn-primary',
'value' => 'Import',
);
$output .= form_submit($data, 'Import Data');
$output .= '
$output .= form_close();
echo $output;
?>
Step 7: Create views (Display Excel data)
Create a views file named display.php inside “application/views/import” folder.
First Name
Last Name
DOB
Contact Name
if (isset($employeeInfo) && !empty($employeeInfo)) {
foreach ($employeeInfo as $key => $element) {
?>
}
} else {
?>
There is no employee.
Demo [sociallocker] Download[/sociallocker]