CodeIgniter 4 - Export Data to Excel in Using PHPexcel Example

Hi Guys,

I am going to show you example of export data to excel in codeigniter 4 using phpexcel download. you can understand a concept of codeigniter 4 export excel. this example will help you export data to excel in codeigniter 4 using spreadsheet. if you have question about export data to excel in php codeigniter 4 then I will give simple example with solution.

So, here We will use PHP library PhpSpreadsheet to create and save dynamic Excel file to export and save data.

If you need sometimes export data from database in excel format file in codeigniter 4 if you have question about export data to excel in php codeigniter 4 then I will give simple example with solution.

So let's start to the example.

Step 1: Install Codeigniter 4

This is optional; however, if you have not created the codeigniter app, then you may go ahead and execute the below command:

composer create-project codeigniter4/appstarter ci-news

After Download successfully, extract clean new Codeigniter 4 application.

Step 2 : Basic Configurations

So, we will now set basic configuration on the app/config/app.php file, so let’s implement to application/config/config.php and open this file on text editor.

app/config/app.php

public $baseURL = 'http://localhost:8080';
To
public $baseURL = 'http://localhost/example/';

Step 3 : Create Table in Database

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO `users` (`id`, `name`, `skills`, `address`, `designation`, `age`) VALUES
(1, 'Smith s', 'Java', 'Sydney', 'Software Engineer', 34),
(2, 'David', 'PHP', 'London', 'Web Developer', 28),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Web Developer', 30),
(4, 'Sara', 'JavaScript', 'Delhi', 'Web Developer', 25),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Programmer', 35),
(6, 'Steve', 'Angular', 'London', 'Web Developer', 28),
(7, 'Cook', 'MySQL', 'Paris', 'Web Developer', 26),
(8, 'Root', 'HTML', 'Paris', 'Web Developer', 28),
(9, 'William', 'jQuery', 'Sydney', 'Web Developer', 23),
(10, 'Nathan', 'PHP', 'London', 'Web Developer', 28),
(11, 'Shri', 'PHP', 'Delhi', 'Web Developer', 38),
(12, 'Jay', 'PHP', 'Delhi, India', 'Web Developer', 30);

Step 4 : Database Configurations

application/config/database.php

public $default = [
    'DSN'      => '',
    'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'demo',
    '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 Library

in this step we will require install phpspreadsheet library following bellow command.

composer require phpoffice/phpspreadsheet

After, successfully run this command go to application/config/config.php file and set you vendor directory path.

application/config/config.php

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

Step 6 : Set Up Controller

Further, you need to generate a new controller that manages the online stripe transaction, hence create a ExcelExport.php file and append the example code in..

app/Controllers/ExcelExport.php

<?php
namespace App\Controllers;
use CodeIgniter\Controller;
use CodeIgniter\HTTP\RequestInterface;
use App\Models\UserModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 
class ExcelExport extends Controller
{
    
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index() {
 
        $db      = \Config\Database::connect();
        $builder = $db->table('users');   
 
        $query = $builder->query("SELECT * FROM users");
 
        $users = $query->getResult();
       
        $fileName = 'users.xlsx';  
        $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 ($users 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("upload/".$fileName);
        header("Content-Type: application/vnd.ms-excel");
        redirect(base_url()."/upload/".$fileName); 
    }
 
}

Step 7 : Add Routes

app/Config/Routes.php

$routes->get('/', 'ExcelExport::index');

Step 8 : Run Codeigniter App:

All the required steps have been done, now you have to type the given below command and hit enter to run the Codeigniter app:

php spark serve

Now, Go to your web browser, type the given URL and view the app output:

http://localhost:8080/

I hope it can help you...