How to use phpOffice / PhpSpreadsheet Library in codeigniter?

Open your terminal. Check your current working directory using pwd command (You must be inside of your project folder). Enter the command composer require phpoffice/phpspreadsheet to download PhpOffice PhpSpreadSheet library and it’s dependencies.

Step 3: Set Vendor Directory Path
$config[‘composer_autoload’] = ‘vendor/autoload.php’;

application/config/config.php
$config[‘composer_autoload’] = ‘vendor/autoload.php’;

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportExcelFile extends CI_Controller {
/**
* Index Page for this controller.
*
* Maps to the following URL
* http://example.com/index.php/welcome
* - or -
* http://example.com/index.php/welcome/index
* - or -
* Since this controller is set as the default controller in
* config/routes.php, it's displayed at http://example.com/
*
* So any other public methods not prefixed with an underscore will
* map to /index.php/welcome/<method_name>
* @see https://codeigniter.com/user_guide/general/urls.html
*/

public function index() {

$spreadsheet = new Spreadsheet(); // instantiate Spreadsheet

$sheet = $spreadsheet->getActiveSheet();

// manually set table data value
$sheet->setCellValue('A1', 'Gipsy Danger');
$sheet->setCellValue('A2', 'Gipsy Avenger');
$sheet->setCellValue('A3', 'Striker Eureka');

$writer = new Xlsx($spreadsheet); // instantiate Xlsx

$filename = 'list-of-jaegers'; // set filename for excel file to be exported

header('Content-Type: application/vnd.ms-excel'); // generate excel file
header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
header('Cache-Control: max-age=0');

$writer->save('php://output'); // download file
}

}

<?php
if (!defined('BASEPATH'))exit('No direct script access allowed');


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Emp extends CI_Controller {
public function __construct() {
parent::__construct();
$this->load->model('Auth_model');
}
public function index() {
$data['page'] = 'export-excel';
$data['title'] = 'Export Excel data';
$data['employeeData'] = $this->Auth_model->employeeList();

$this->load->view('emp', $data);
}
public function createExcel() {
$fileName = 'employee.xlsx';
$employeeData = $this->Auth_model->employeeList();
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Id');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Skills');
$sheet->setCellValue('D1', 'Address');
$sheet->setCellValue('E1', 'Age');
$sheet->setCellValue('F1', 'Designation');
$rows = 2;
foreach ($employeeData as $val){
$sheet->setCellValue('A' . $rows, $val['id']);
$sheet->setCellValue('B' . $rows, $val['name']);
$sheet->setCellValue('C' . $rows, $val['skills']);
$sheet->setCellValue('D' . $rows, $val['address']);
$sheet->setCellValue('E' . $rows, $val['age']);
$sheet->setCellValue('F' . $rows, $val['designation']);
$rows++;
}
$writer = new Xlsx($spreadsheet);
$writer->save("uploads/".$fileName);
header("Content-Type: application/vnd.ms-excel");
redirect(base_url()."/uploads/".$fileName);
}
}
?>

/* ############## download mysql data in excel sheet ################# */
public function employeeList() {
$this->db->select(array('id', 'name', 'skills', 'address', 'designation', 'age'));
$this->db->from('emp');
$this->db->limit(10);
$query = $this->db->get();
return $query->result_array();
}
/* ###################################### */

<table class="table table-hover tablesorter">
<thead>
<tr>
<th class="header">Id.</th>
<th class="header">Name</th>
<th class="header">Skills</th>
<th class="header">Address</th>
<th class="header">Age</th>
<th class="header">Designation</th>
</tr>
</thead>
<a class="pull-right btn btn-warning btn-large" style="margin-right:40px" href="<?php echo base_url(); ?>/emp/createexcel"><i class="fa fa-file-excel-o"></i> Export to Excel</a>
<tbody>
<?php
if (isset($employeeData) && !empty($employeeData)) {
foreach ($employeeData as $key => $emp) {
?>
<tr>
<td><?php echo $emp['id']; ?></td>
<td><?php echo $emp['name']; ?></td>
<td><?php echo $emp['skills']; ?></td>
<td><?php echo $emp['address']; ?></td>
<td><?php echo $emp['age']; ?></td>
<td><?php echo $emp['designation']; ?></td>
</tr>
<?php
}
} else {
?>
<tr>
<td colspan="5" class="alert alert-danger">No Records founds</td>
</tr>
<?php } ?>
</tbody>
</table>

Download phpOffice package

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class PhpspreadsheetController extends CI_Controller {
public function __construct(){
parent::__construct();
}
public function index(){
$this->load->view('spreadsheet');
}
public function export(){
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
$writer = new Xlsx($spreadsheet);
$filename = 'name-of-the-generated-file';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output'); // download file
}
public function import(){
$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['upload_file']['name']) && in_array($_FILES['upload_file']['type'], $file_mimes)) {
$arr_file = explode('.', $_FILES['upload_file']['name']);
$extension = end($arr_file);
if('csv' == $extension){
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} else {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
}
$spreadsheet = $reader->load($_FILES['upload_file']['tmp_name']);
$sheetData = $spreadsheet->getActiveSheet()->toArray();
echo "<pre>";
print_r($sheetData);
}
}
}

<html>
<head>
<title>Import/Export using phpspreadsheet in codeigniter</title>
</head>
<body>
<style>
h3
{
font-family: Verdana;
font-size: 14pt;
font-style: normal;
font-weight: bold;
color:red;
text-align: center;
}
table.tr{
font-family: Verdana;
color:black;
font-size: 12pt;
font-style: normal;
font-weight: bold;
text-align:left;
}
</style>
<h3><u>Import/Export using phpspreadsheet in codeigniter</u></h3>
<?php echo form_open_multipart('spreadsheet/import',array('name' => 'spreadsheet')); ?>
<table align="center" cellpadding = "5">
<tr>
<td>File :</td>
<td><input type="file" size="40px" name="upload_file" /></td>
<td class="error"><?php echo form_error('name'); ?></td>
<td colspan="5" align="center">
<input type="submit" value="Import Users"/></td>
</tr>
</table>
<?php echo form_close();?>
</body>
</html>

$route['spreadsheet'] = 'PhpspreadsheetController';
$route['spreadsheet/import'] = 'PhpspreadsheetController/import';
$route['spreadsheet/export'] = 'PhpspreadsheetController/export';