How to Export Data in Excel using Codeigniter 4

In this tutorial, you can learn How to Export Data to Excel in Codeigniter 4 using PHPSpreadsheet library. After publishing of new version of Codeigniter framework, then there is lots of changes has come in Codeigniter latest version than previous version. In latest version of Codeigniter framework, we can perform more operation by writing less code. So for this, we have to learn all things again in Codeigniter 4 framework. For this reason here we have again publish tutorial on export mysql data to excel sheet using Codeigniter 4 framework.

Exporting of Mysql data into Excel sheet in Codeigniter 4 framework by using PHPSpreadsheet library. From this post you can find the solution of how to export data in excel format using PHPSpreadsheet library with Codeigniter 4.

In most of the web application, in which we have work with dynamic data then we need to export that data from web application. So at that time we have prefer excel format for get data from web application. This is because Excel format is most of the widely used. So we need to export data from web application to excel format with Codeigniter 4 framework. So for this reason in this tutorial will help because in this post you can find step by step process for how to export dynamic mysql database data into Excel sheet using Codeigniter 4 application. In this post we will use PHPSpreadsheet library with Codeigniter 4 framework for export data into excel format or create and save dynamic data in excel file.

1. Download & Install Codeigniter 4 framework
2. Set Global Environment Variable value
3. Create Mysql Table
4. Create Database connection
5. Download PHPSpreadsheet Library in Codeigniter 4
6. Create Model Class
7. Create Data Seeder Class for Fake data
8. Create Controller Class
9. Create View File
10. Start Codeigniter 4 Server

1 - Download & Install Codeigniter 4 framework

For make Codeigniter 4 application, first we need to download Codeigniter 4 framework. So for download and install Codeigniter 4 framework. We have go to terminal and under this we will use Composer for download Codeigniter 4 framework. So for download Codeigniter 4 framework, we have to run following command in terminal.

composer create-project codeigniter4/appstarter excel_export

This command will first make excel_export folder under define directory and then after it will download Codeigniter 4 framework.

2 - Set Global Environment Variable value

After download Codeigniter 4 framework, so in root folder of Codeigniter 4 framework, you can find env file. So for use Global variable or environment variable, we need to first convert env file to .env file. For this things, we have open terminal and run following command.

copy env .env

After run above command it will copy env file to .env file. So now we are ready to use environment variables in Codeigniter 4 framework.

By default Codeigniter 4 starts in production mode, so it will not display error on web page. So for this first we need to define development mode, so it will display error on web page. For this open .env file and define following enivonment variable value.

.env

#--------------------------------------------------------------------
# ENVIRONMENT
#--------------------------------------------------------------------

CI_ENVIRONMENT = development

After define this environment variable value, now codeigniter 4 application is in development mode.

3 - Create Mysql Table

For work with database, so here first you have to create database in your PhpMyAdmin and after create database you have to create table in that database. So for create table, you have to run following sql script, which will create employee table in your database.

CREATE TABLE students (
    employee_id int(5) unsigned NOT NULL AUTO_INCREMENT,
    employee_name varchar(100) NOT NULL,
    employee_mobile varchar(100) NOT NULL,
    employee_email varchar(20) DEFAULT NULL,
    employee_department varchar(50) DEFAULT NULL
    PRIMARY KEY (employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4 - Create Database connection

After create table in your database, next we needt to connect database with this Codeigniter 4 application. For this we have to open .env file from Codeigniter 4 framework root. Under this file, you have to search DATABASE and then after you can see the database connection envionment variables.

.env

#--------------------------------------------------------------------
# DATABASE
#--------------------------------------------------------------------

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

After set the value of this environment variable it will make mysql database connection with your Codeigniter 4 application using .env file.

5 - Download PHPSpreadsheet Library in Codeigniter 4

In Codeigniter 4 framework, for export data to excel we need to download PHPSpreadsheet library, so for download PHPSpreadsheet we will use composer. So for this we have open terminal and run following command.

composer require phpoffice/phpspreadsheet

This command will download PHPSpreadsheet library package into Codeigniter 4 framework /vendor folder.

6 - Create Model Class

In Codeigniter 4 framework, for perform database related operation, we need to create model file. So for create model file, we have need to run following spark command.

php spark make:model Employee --suffix

This command will create EmployeeModel.php file at /app/Models folder. So you need to open EmployeeModel.php file and write this following code into it.

app/Models/EmployeeModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class EmployeeModel extends Model
{
	protected $DBGroup              = 'default';
	protected $table                = 'employee';
	protected $primaryKey           = 'employee_id';
	protected $useAutoIncrement     = true;
	protected $insertID             = 0;
	protected $returnType           = 'array';
	protected $useSoftDelete        = false;
	protected $protectFields        = true;
	protected $allowedFields        = ["employee_name", "employee_mobile", "employee_email", "employee_department"];

	// Dates
	protected $useTimestamps        = false;
	protected $dateFormat           = 'datetime';
	protected $createdField         = 'created_at';
	protected $updatedField         = 'updated_at';
	protected $deletedField         = 'deleted_at';

	// Validation
	protected $validationRules      = [];
	protected $validationMessages   = [];
	protected $skipValidation       = false;
	protected $cleanValidationRules = true;

	// Callbacks
	protected $allowCallbacks       = true;
	protected $beforeInsert         = [];
	protected $afterInsert          = [];
	protected $beforeUpdate         = [];
	protected $afterUpdate          = [];
	protected $beforeFind           = [];
	protected $afterFind            = [];
	protected $beforeDelete         = [];
	protected $afterDelete          = [];
}

7 - Create Data Seeder Class for Fake data

In this step, we have to create fake data and insert into employee table for demostrate this feature. So for generate dummy data, we need to crate seeder file and then after we will use Faker Library and this library by default available in Codeigniter 4 framework. For create seeder class file, we go to terminal and run following spark command.

php spark make:seeder Employee --suffix

This command will create EmployeeSeeder.php file into /app/Database/Seeds directory. So we have to open EmployeeSeeder.php file and write following code into this file.

app/Database/Seeds/EmployeeSeeder.php

<?php

namespace App\Database\Seeds;

use CodeIgniter\Database\Seeder;
use App\Models\EmployeeModel;
use Faker\Factory;

class EmployeeSeeder extends Seeder
{
	public function run()
	{
		$data = [];

		for($count = 0; $count < 50; $count++)
		{
			$data[] = $this->generate_data();
		}

		$employee_object = new EmployeeModel();

		$employee_object->insertBatch($data);
	}

	function generate_data()
	{
		$faker = Factory::create();

		return [
			"employee_name"		=>	$faker->name(),
			"employee_mobile"	=>	$faker->phoneNumber,
			"employee_email"	=>	$faker->email,
			"employee_department" 	=> $faker->randomElement(["Finance","Marketing","Production","Inventory"])
		];
	}
}

Once you have write above code then we need to run above seeder file for generate fake data and insert into employee table. So for run above script, we need to go terminal and run following spark command.

php spark db:seed EmployeeSeeder

8 - Create Controller Class

For handle http request, we need to create controller class file in Codeigniter 4 framework. So for create controller file, we have to run following spark command in terminal.

php spark make:controller Employee --suffix

Above command will create EmployeeController.php file at /app/Controllers directory. So we shoult open EmployeeController.php file and write following code into it.

app/Controllers/EmployeeController.php

<?php

namespace App\Controllers;

use App\Controllers\BaseController;

use App\Models\EmployeeModel;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class EmployeeController extends BaseController
{
	public function index()
	{
		$employee_object = new EmployeeModel();

		$data = $employee_object->findAll();

		return view("view_employee", ["data" => $data]);
	}

	function export()
	{
		$employee_object = new EmployeeModel();

		$data = $employee_object->findAll();

		$file_name = 'data.xlsx';

		$spreadsheet = new Spreadsheet();

		$sheet = $spreadsheet->getActiveSheet();

		$sheet->setCellValue('A1', 'Employee Name');

		$sheet->setCellValue('B1', 'Email Address');

		$sheet->setCellValue('C1', 'Mobile No.');

		$sheet->setCellValue('D1', 'Department');

		$count = 2;

		foreach($data as $row)
		{
			$sheet->setCellValue('A' . $count, $row['employee_name']);

			$sheet->setCellValue('B' . $count, $row['employee_email']);

			$sheet->setCellValue('C' . $count, $row['employee_mobile']);

			$sheet->setCellValue('D' . $count, $row['employee_department']);

			$count++;
		}

		$writer = new Xlsx($spreadsheet);

		$writer->save($file_name);

		header("Content-Type: application/vnd.ms-excel");

		header('Content-Disposition: attachment; filename="' . basename($file_name) . '"');

		header('Expires: 0');

		header('Cache-Control: must-revalidate');

		header('Pragma: public');

		header('Content-Length:' . filesize($file_name));

		flush();

		readfile($file_name);

		exit;
	}
}

Under this file, we have create two method under this controller class file.

index() - This is root method of this controller class. This method will fetch data from employee table and display on view file in html table format by using EmployeeModel class.

export() - This method has received request for export data into excel file. So under this method we have use PHPSpreadsheet library for export data into excel sheet and download into excel file in local computer.

9 - Create View File

For display output in browser, we need to create view file in Codeigniter 4 framework. So here we have create view_employee.php file in /app/Views folder. Under this file, we will load all employee data in html table format with excel export button.

app/Views/view_employee.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes">
    <!-- CSS only -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-eOJMYsd53ii+scO/bJGFsiCZc+5NDVN2yr8+0RDqr0Ql0h+rP48ckxlpbzKgwra6" crossorigin="anonymous">
    <title>Export Mysql Data to Excel in Codeigniter 4 using PHPSpreadsheet</title>
</head>
<body>
    <div class="container">
        <h2 class="text-center mt-4 mb-4">Export Mysql Data to Excel in Codeigniter 4 using PHPSpreadsheet</h2>
        <span id="message"></span>
        <div class="card">
            <div class="card-header">
                <div class="row">
                    <div class="col-md-6">Employee Data</div>
                    <div class="col-md-6" align="right">
                        <a href="<?php echo base_url('employeecontroller/export'); ?>" class="btn btn-success">Export</a>
                    </div>
                </div>
            </div>
            <div class="card-body">
                <div class="row justify-content-md-center">
                    <div class="table-responsive">
                        <table class="table table-bordered">
                            <tr>                                
                                <th>Employee Name</th>
                                <th>Email</th>
                                <th>Mobile</th>
                                <th>Department</th>                                
                            </tr>
                            <?php
                            foreach($data as $row)
                            {
                            ?>
                            <tr>
                                <td><?php echo $row["employee_name"];?></td>
                                <td><?php echo $row["employee_email"]; ?></td>
                                <td><?php echo $row["employee_mobile"]; ?></td>
                                <td><?php echo $row["employee_department"]; ?></td>
                            </tr>
                            <?php
                            }
                            ?>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

10 - Start Codeigniter 4 Server

After follow all above steps, lastly we have to test this code in browser. So before test, we need to start Codeigniter 4 server. So for start development server, we need to run following spark command in terminal.

php spark serve

So this command will start server and provide us base url of our Codeigniter 4 application. So for run above script you have to hit following url in browser.

http://localhost:8080/employeecontroller

Lastly, we hope this post will really help you to learn How to Export data into Excel format with Codeigniter 4 using PHPSpreadsheet library in a step by step detailed process.