CodeIgniter 4 Export Data to Excel Using PHPexcel

In this article, we will explain to you how to export data from a database using phpexcel library in Codeigniter 4 application(CodeIgniter 4 Export Data to Excel Using PHPexcel). so we will give you a simple example of how to export Excel file in codeigniter 4. we can easily generate excel Using PHPexcel in CodeIgniter 4.

In this example, we will use the PhpSpreadsheet PHP package library to create a dynamic Excel file in Codeigniter. so you can see our following example.

Overview
Step 1: Download Codeigniter
Step 2: Basic Configurations
Step 3: Create a Database in table
Step 4: Connect to Database
Step 5: Install PhpSpreadsheet Package
Step 6: Create Controller and Model
Step 8: Run The Application

Step 1: Download Codeigniter
If you want to download or install the latest version of CodeIgniter 4 then you can go to Codeigniter’s official site and download the latest version of Codeigniter 4. after the downloaded you can configure in “xampp/htdocs/” directory.

Step 2: Basic Configurations
If you want to Basic Configurations in your project then you can below Url.
Codeigniter 4 Removing Index.Php From Url

Step 3: Create a Database in table
In this step, We will create the database and table.

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `address` text NOT NULL,
  `email` varchar(64) NOT NULL,
  `mobile` varchar(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

INSERT INTO `students` (`id`, `first_name`, `last_name`, `address`, `email`, `mobile`) VALUES
(1, 'john', 'doe', 'USA', '[email protected]', '787878787'),
(2, 'kal', 'mark', 'CANADA', '[email protected]', '7887878787'),
(3, 'James', 'Mary', 'USA', '[email protected]', '4364373434'),
(4, 'William', 'Elizabeth', 'USA', '[email protected]', '7878787'),
(5, 'Matthew', 'Betty', 'USA', '[email protected]', '898989'),
(6, 'Anthony', 'Margaret', 'INDIA', '[email protected]', '8989898989'),
(7, 'Donald', 'Ashley', 'CANADA', '[email protected]', '3343434345'),
(8, 'Kevin', 'Carol', 'USA', '[email protected]', '2223334445');

Step 4: Connect to Database
Go to the “app/Config/Database.php” folder and open the database.php file some changes in this file like hostname, database username, database password, and database name.

	public $default = [
		'DSN'      => '',
		'hostname' => 'localhost',
		'username' => 'root',
		'password' => '',
		'database' => 'codeigniter4_pagination',
		'DBDriver' => 'MySQLi',
		'DBPrefix' => '',
		'pConnect' => false,
		'DBDebug'  => (ENVIRONMENT !== 'production'),
		'cacheOn'  => false,
		'cacheDir' => '',
		'charset'  => 'utf8',
		'DBCollat' => 'utf8_general_ci',
		'swapPre'  => '',
		'encrypt'  => false,
		'compress' => false,
		'strictOn' => false,
		'failover' => [],
		'port'     => 3306,
	];

Step 5: Install PhpSpreadsheet Package
In this step, We will install the dompdf package for the generate pdf in codeigniter 4 application. so you can follow our composer command.

composer require phpoffice/phpspreadsheet

After that, open the app/config/autoload.php file and set vendor directory path, so you can follow configuration.

$config['composer_autoload'] = 'vendor/autoload.php';

Step 6: Create Controller and Model
In this step, we will create the “Student.php” controller and the “StudentModel.php” model.
app/Controllers/Student.php

<?php 

namespace App\Controllers;
 
use CodeIgniter\Controller;
use App\Models\StudentModel;
use CodeIgniter\HTTP\RequestInterface;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
class Student extends Controller
{
 
	public function __construct()
    {
         helper(['form', 'url']);
    }
    public function index()
    {   
        $model = new StudentModel();
		$data_result = $model->orderBy('id', 'DESC')->findAll();	
		
		$fileName = 'students.xlsx';  
		$spreadsheet = new Spreadsheet();

		$sheet = $spreadsheet->getActiveSheet();
		$sheet->setCellValue('A1', 'Id');
		$sheet->setCellValue('B1', 'First Name');
		$sheet->setCellValue('C1', 'Last Name');
		$sheet->setCellValue('D1', 'Email');
		$sheet->setCellValue('E1', 'Address');
		$sheet->setCellValue('F1', 'Mobile');       
		$rows = 2;

		foreach ($data_result as $val){
		  $sheet->setCellValue('A' . $rows, $val['id']);
		  $sheet->setCellValue('B' . $rows, $val['first_name']);
		  $sheet->setCellValue('C' . $rows, $val['last_name']);
		  $sheet->setCellValue('D' . $rows, $val['email']);
		  $sheet->setCellValue('E' . $rows, $val['address']);
		  $sheet->setCellValue('F' . $rows, $val['mobile']);
		  $rows++;
		} 
		$writer = new Xlsx($spreadsheet);
		$writer->save("upload/".$fileName);
		header("Content-Type: application/vnd.ms-excel");
		redirect(base_url()."/upload/".$fileName);
    }
 
}

?>

app/Models/StudentModel.php

<?php 
namespace App\Models;
use CodeIgniter\Database\ConnectionInterface;
use CodeIgniter\Model;
 
class StudentModel extends Model
{
    protected $table = 'Students';
 
    protected $allowedFields = ['first_name','last_name','address','email', 'mobile'];
}
?>

Step 7: Run The Application
We can start the server and run the codeigniter 4 application using the below command.

php spark serve

Now we will run our example using the below Url in the browser.

http://localhost:8080/student

If you liked this article, you can also download it through our Github Repository.