CodeIgniter 4 – Export data to excel in CodeIgniter using PhpSpreadsheet

Last updated on November 28, 2022

In this CodeIgniter 4 tutorial, you will learn, exporting data to excel using the PhpSpreadsheet library.

Similar Posts
CodeIgniter 4 – Export data to excel in CodeIgniter using PhpSpreadsheet
How to use PHPExcel with CodeIgniter?
How to generate excel from the array using PHPExcel

Install CodeIgniter 4

Let’s create a brand new CodeIgniter 4 project with the composer command.

composer create-project codeigniter4/appstarter ci4-excel

The preceding command will generate the ci4-excel folder along with CodeIgniter project files. After creating the project you can run and see the output. Upon successful setup, you should be able to see the below output in the browser without any error. Let’s start the development server with Command Line Tool as shown below.

$ cd ci4-excel
$ php spark serve

Now access the application at http://localhost:8080/

Install PHP spreadsheet

Now install phpoffice/phpspreadsheet the library with composer with the below-shown command.

composer require phpoffice/phpspreadsheet

CodeIgniter 4 Database Connection

Rename the env file to .env which will be located at the project root and update database details and environment details.

CI_ENVIRONMENT = development

database.default.hostname = localhost
database.default.database = ci4_demo
database.default.username = root
database.default.password =
database.default.DBDriver = MySQLi

Import the below table scheme to create a task table in your database.

-- 
-- Table structure for `tasks`
-- 
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL,
  `task` varchar(200) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tasks` ADD PRIMARY KEY (`id`);
ALTER TABLE `tasks` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Now import sample data into the tasks table.

INSERT INTO `tasks` (`id`, `task`, `status`, `created_at`) VALUES
(1, 'Find bugs', 1, '2021-04-10 23:50:40'),
(2, 'Review code', 1, '2021-04-10 23:50:40'),
(3, 'Fix bugs', 1, '2021-04-10 23:50:40'),
(4, 'Refactor Code', 1, '2021-04-10 23:50:40'),
(5, 'Push to prod', 1, '2021-04-10 23:50:50');

Now let’s create a Model class for the tasks table called app\Models\TaskModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class TaskModel extends Model
{
    protected $table = 'tasks';

}

Create your controller file app\Controllers\Excel.php.

<?php

namespace App\Controllers;

use App\Models\TaskModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Excel extends BaseController
{
	public function index()
	{
	    $taskModel = new TaskModel();
	    $tasks = $taskModel->findAll();

        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Id');
        $sheet->setCellValue('B1', 'Task');
        $sheet->setCellValue('C1', 'Status');
        $rows = 2;

        foreach ($tasks as $task){
            $sheet->setCellValue('A' . $rows, $task['id']);
            $sheet->setCellValue('B' . $rows, $task['task']);
            $sheet->setCellValue('C' . $rows, $task['status']);
            $rows++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save('world.xlsx');
        return $this->response->download('world.xlsx', null)->setFileName('sample.xlsx');
        
    }
}

Now start your application

$ cd ci4-excel
$ php spark serve

Now access your application at http://localhost:8080/excel/