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 export data to excel and csv file using PhpSpreadsheet library in CodeIgniter and MySQL, look files structure:
- codeigniter-export-excel-csv-file-data-into-mysql
- application
- config
- autoload.php
- constants.php
- database.php
- routes.php
- controllers
- Phpspreadsheet.php
- models
- Site.php
- views
- spreadsheet
- export.php
- spreadsheet
- templates
- footer.php
- header.php
- templates
- config
- vender
- phpoffice
- system
- index.php
- assets
- css
- upload
- 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;
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;
?>
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 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();
}
}
?>
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');
// 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);
}
}
?>
Step 7: Create View:
Create a view named export.php inside application/views directory. Please find the code for view file.
load->view('templates/header');?>
Export Data to Excel and CSV file using PhpSpreadsheet library in CodeIgniter and MySQL
First Name
Last Name
DOB
Contact No
load->view('templates/footer');?>
Demo [sociallocker] Download[/sociallocker]