In this tutorial how to explain Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL.As PHPExcel is deprecated, so we will use PhpSpreadsheet to create excel file with collection data. We will cover this tutorial with live demo to Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL.
Here another article, you might be interested in – How to use PHPExcel with CodeIgniter
Step 1: Download and install CodeIgniter.
Step 2: composer or direct download
Run below composer command to download phpspreadsheet library from your project folder. It will create a new folder called “vendor” and it will download phpoffice/phpspreadsheet library.
$ composer require phpoffice/phpspreadsheetHere is my the directory structure after installing phpoffice/phpspreadsheet
3.Setup Composer Autoload
You need to set vendor directory path inside
application/config/config.php
$config['composer_autoload'] = 'vendor/autoload.php';
?>
Before started to implement the import data to excel and csv file using PhpSpreadsheet library in CodeIgniter and MySQL, look files structure:
- codeigniter-import-excel-csv-file-data-into-mysql
- application
- config
- autoload.php
- constants.php
- database.php
- routes.php
- controllers
- Phpspreadsheet.php
- models
- Site.php
- views
- spreadsheet
- index.php
- display.php
- templates
- header.php
- footer.php
- spreadsheet
- config
- vender
- phpoffice
- system
- index.php
- assets
- css
- uploads
- sample-xlsx.xlsx
- sample-xls.xls
- sample-csv.csv
- application
Step 4: Create MySQL Database and Table
The following SQL creates a customer table in the MySQL database.
CREATE TABLE `import` (
`id` int(11) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`email` varchar(255) NOT NULL,
`dob` varchar(20) NOT NULL,
`contact_no` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `import`
ADD PRIMARY KEY (`id`);
ALTER TABLE `import`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
?>
Step 5: The Site model handles the database .
Create a file named Site.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 Coders Mag Team
*
* @email info@techarise.com
*/
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Site 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 Controller and load class
Create a controller named Phpspreadsheet.php and use phpspreadsheet library inside controller.Please find below the code for controller.
/**
* @package Phpspreadsheet : Phpspreadsheet
* @author TechArise Team
*
* @email info@techarise.com
*
* Description of Phpspreadsheet Controller
*/
defined('BASEPATH') OR exit('No direct script access allowed');
//PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Phpspreadsheet extends CI_Controller {
public function __construct()
{
parent::__construct();
// load model
$this->load->model('Site', 'site');
}
// index
public function import()
{
$data = array();
$data['title'] = 'Import Excel Sheet | TechArise';
$data['breadcrumbs'] = array('Home' => '#');
$this->load->view('spreadsheet/index', $data);
}
// file upload functionality
public function upload() {
$data = array();
$data['title'] = 'Import Excel Sheet | TechArise';
$data['breadcrumbs'] = array('Home' => '#');
// Load form validation library
$this->load->library('form_validation');
$this->form_validation->set_rules('fileURL', 'Upload File', 'callback_checkFileValidation');
if($this->form_validation->run() == false) {
$this->load->view('spreadsheet/index', $data);
} else {
// If file uploaded
if(!empty($_FILES['fileURL']['name'])) {
// get file extension
$extension = pathinfo($_FILES['fileURL']['name'], PATHINFO_EXTENSION);
if($extension == 'csv'){
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} elseif($extension == 'xlsx') {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
} else {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
}
// file path
$spreadsheet = $reader->load($_FILES['fileURL']['tmp_name']);
$allDataInSheet = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
// array Count
$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;
}
}
}
$dataDiff = array_diff_key($makeArray, $SheetDataKey);
if (empty($dataDiff)) {
$flag = 1;
}
// match excel sheet column
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['dataInfo'] = $fetchData;
$this->site->setBatchImport($fetchData);
$this->site->importData();
} else {
echo "Please import correct file, did not match excel sheet column";
}
$this->load->view('spreadsheet/display', $data);
}
}
}
// checkFileValidation
public function checkFileValidation($string) {
$file_mimes = array('text/x-comma-separated-values',
'text/comma-separated-values',
'application/octet-stream',
'application/vnd.ms-excel',
'application/x-csv',
'text/x-csv',
'text/csv',
'application/csv',
'application/excel',
'application/vnd.msexcel',
'text/plain',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
);
if(isset($_FILES['fileURL']['name'])) {
$arr_file = explode('.', $_FILES['fileURL']['name']);
$extension = end($arr_file);
if(($extension == 'xlsx' || $extension == 'xls' || $extension == 'csv') && in_array($_FILES['fileURL']['type'], $file_mimes)){
return true;
}else{
$this->form_validation->set_message('checkFileValidation', 'Please choose correct file.');
return false;
}
}else{
$this->form_validation->set_message('checkFileValidation', 'Please choose a file.');
return false;
}
}
}
?>
Step 7: Create View:
Create a view named index.php inside application/views directory. Please find the code for view file.
load->view('templates/header');?>
Import Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL
load->view('templates/footer');?>
Step 8: Create View:
Create a view named dispaly.php inside application/views directory. Please find the code for view file.
load->view('templates/header');?>
Import/Export Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL
First Name
Last Name
DOB
Contact No
$element) { ?>
load->view('templates/footer');?>
Demo [sociallocker] Download[/sociallocker]