Import/Upload Excel file into MySQL using Codeigniter
Using Excel file you can store all the data and import the Excel file data into the database at once using MYSQL and Codeigniter.Import Excel into MySQL helps to save the user time and avoid repetitive work.In this tutorial, We will explain how to import an Excel into MySQL database using CodeIgniter.
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.
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:
Step 4: Create Controller and load class
Syntax:
Load “excel” class in controller.
Create a controller file like contactus.php inside “application/controllers” folder.
Step 5: Create Model
Create a model file named Import_model.php inside “application/models” folder.
Step 6: Create views (Upload Excel file)
Create a views file named index.php inside “application/views/import” folder.
Step 7: Create views (Display Excel data)
Create a views file named display.php inside “application/views/import” folder.
Demo [sociallocker] Download[/sociallocker]
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]
Komentar
Posting Komentar