<?php
$config['composer_autoload'] = 'vendor/autoload.php';
?>
<?php
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;
INSERT INTO `import` (`id`, `first_name`, `last_name`, `email`, `dob`, `contact_no`) VALUES
(1, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),
(2, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002'),
(3, 'User', '4rth', 'user@techarise.com', '21-02-2011', '9000000003'),
(4, 'Editor', '3rd', 'editor@techarise.com', '21-02-2011', '9000000004'),
(5, 'Writer', '2nd', 'writer@techarise.com', '21-02-2011', '9000000005'),
(6, 'Contact', 'one', 'contact@techarise.com', '21-02-2011', '9000000006'),
(7, 'Manager', '1st', 'manager@techarise.com', '21-02-2011', '9000000007'),
(8, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),
(9, 'Team', 'Tech Arise', 'info@techarise.com', '21-02-2011', '9000000001'),
(10, 'Admin', '1st', 'admin@techarise.com', '21-02-2011', '9000000002');
ALTER TABLE `import`
ADD PRIMARY KEY (`id`);
ALTER TABLE `import`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
?>
<?php
/*
* 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 Export Model
*
* @author TechArise Team
*
* @email info@techarise.com
*/
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Site extends CI_Model {
/* 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();
}
}
?>
<?php
/**
* @package Phpspreadsheet : Phpspreadsheet
* @author TechArise Team
*
* @email info@techarise.com
*
* Description of Phpspreadsheet Controller
*/
defined('BASEPATH') OR exit('No direct script access allowed');
/* Spreadsheet */
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class Phpspreadsheet extends CI_Controller {
public function __construct()
{
parent::__construct();
/* load model */
$this->load->model('Site', 'site');
}
/* index */
public function index()
{
$data = array();
$data['title'] = 'Import Excel Sheet | Coders Mag';
$data['breadcrumbs'] = array('Home' => '#');
$data['empInfo'] = $this->site->employeeList();
$this->load->view('spreadsheet/export', $data);
}
/* export file Xlsx, Xls and Csv */
public function export()
{
$extension = $this->input->post('export_type');
if(!empty($extension)){
$extension = $extension;
} else {
$extension = 'xlsx';
}
$this->load->helper('download');
$data = array();
$data['title'] = 'Export Excel Sheet | Coders Mag';
/* get employee list */
$empInfo = $this->site->employeeList();
$fileName = 'employee-'.time();
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'First_Name');
$sheet->setCellValue('B1', 'Last_Name');
$sheet->setCellValue('C1', 'Email');
$sheet->setCellValue('D1', 'DOB');
$sheet->setCellValue('E1', 'Contact_No');
$rowCount = 2;
foreach ($empInfo as $element) {
$sheet->setCellValue('A' . $rowCount, $element['first_name']);
$sheet->setCellValue('B' . $rowCount, $element['last_name']);
$sheet->setCellValue('C' . $rowCount, $element['email']);
$sheet->setCellValue('D' . $rowCount, $element['dob']);
$sheet->setCellValue('E' . $rowCount, $element['contact_no']);
$rowCount++;
}
if($extension == 'csv'){
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);
$fileName = $fileName.'.csv';
} elseif($extension == 'xlsx') {
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$fileName = $fileName.'.xlsx';
} else {
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
$fileName = $fileName.'.xls';
}
$this->output->set_header('Content-Type: application/vnd.ms-excel');
$this->output->set_header("Content-type: application/csv");
$this->output->set_header('Cache-Control: max-age=0');
$writer->save(ROOT_UPLOAD_PATH.$fileName);
/*redirect(HTTP_UPLOAD_PATH.$fileName); */
$filepath = file_get_contents(ROOT_UPLOAD_PATH.$fileName);
force_download($fileName, $filepath);
}
}
?>
<?php $this->load->view('templates/header');?>
<section class="showcase">
<div class="container">
<div class="pb-2 mt-4 mb-2 border-bottom">
<h2>Export Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL</h2>
</div>
<form action="<?php print site_url();?>phpspreadsheet/export" class="excel-upl" id="excel-upl" enctype="multipart/form-data" method="post" accept-charset="utf-8">
<div class="row padall">
<div class="col-lg-12">
<div class="float-right">
<input type="radio" checked="checked" name="export_type" value="xlsx"> .xlsx
<input type="radio" name="export_type" value="xls"> .xls
<input type="radio" name="export_type" value="csv"> .csv
<button type="submit" name="import" class="btn btn-primary">Export</button>
</div>
</div>
</div>
</form>
<div class="row">
<div class="col-lg-12">
<table class="table table-striped">
<thead>
<tr class="table-primary">
<th scope="col">First Name</th>
<th scope="col">Last Name</th>
<th scope="col">Email</th>
<th scope="col">DOB</th>
<th scope="col">Contact No</th>
</tr>
</thead>
<tbody>
<?php foreach($empInfo as $element) {?>
<tr>
<td><?php print $element['first_name'];?></td>
<td><?php print $element['last_name'];?></td>
<td><?php print $element['email'];?></td>
<td><?php print $element['dob'];?></td>
<td><?php print $element['contact_no'];?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</section>
<?php $this->load->view('templates/footer');?>