Codeigniter 4 Export Data to Excel Example

Export data to excel in codeigniter 4 using phpexcel example; In this tutorial, i will show you how to export data from database using phpexcel library in codeigniter 4 app. And as well learn how to install and configure PHPexcel library into your codeigniter 4 app.

Export data from database to excel in codeigniter 4 tutorial; In this example, I will create function on controller, which is used to export data from database using phpexcel in codeigniter 4 app.

How to Export Excel Data From MySQL Database in Codeigniter 4 using phpexcel

Install Codeigniter 4 Application
Basic App Configurations
Create Database and Table
Connect App to Database
Install PhpSpreadsheet Libraray
Create Controller Class
Setup Routes
Start Development server

Step 1 - Install Codeigniter 4 Application

First of all, you need to ownload the latest version of Codeigniter 4. So, visit this link https://codeigniter.com/download Download Codeigniter 4 app and unzip the setup in your local system xampp/htdocs/ .

Note that, please change the download folder name “demo”.

Step 2 - Basic App Configurations

Now, you need to some basic configuration on the app/config/app.php file, so let’s go to application/config/config.php and open this file on text editor.

Set Base URL like this

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

Step 3 - Create Database and Table

Create a database and table by executing the following SQL query:

CREATE DATABASE demo;
CREATE TABLE users (
    id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    name varchar(100) NOT NULL COMMENT 'Name',
    email varchar(255) NOT NULL COMMENT 'Email Address',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='demo database' AUTO_INCREMENT=1;
INSERT INTO `users` (`id`, `name`, `email`) VALUES
(1, 'Paul Bettany', '[email protected]'),
(2, 'Vanya', '[email protected]'),
(3, 'Luther', '[email protected]'),
(4, 'John Doe', '[email protected]'),
(5, 'Paul Bettany', '[email protected]'),
(6, 'Vanya', '[email protected]'),
(7, 'Luther', '[email protected]'),
(8, 'Wayne Barrett', '[email protected]'),
(9, 'Vincent Ramos', '[email protected]'),
(10, 'Susan Warren', '[email protected]'),
(11, 'Jason Evans', '[email protected]'),
(12, 'Madison Simpson', '[email protected]'),
(13, 'Marvin Ortiz', '[email protected]'),
(14, 'Felecia Phillips', '[email protected]'),
(15, 'Tommy Hernandez', '[email protected]');

Step 4 - Connect App to Database

To connect your codeigniter 4 app to the database. So, visit app/Config/ directory and open Database.php. Then add the databasae details like below into database.php file:

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

Step 5 - Install PhpSpreadsheet Libraray

Install PHP libraray PhpSpreadsheet to create and save dynamic Excel file. So, open your terminal and execute the following command on it:

composer require phpoffice/phpspreadsheet

then open app/Config/autoload.php. add namespace to $psr4 array. see the code below:

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

Step 6 - Create Controller Class

Create ExcelExport.php file. So, visit app/Controllers directory and create ExcelExport.php.Then add the following code into it:

<?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
{
  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 - Setup Routes

To define a route, So, visit app/Config/ directory and open Routes.php file. Then add the following routes into it:

// CRUD RESTful Routes
$routes->setDefaultController('ExcelExport');
$routes->get('/', 'ExcelExport::index');

Step 8 - Start Development server

Execute the following command into command prompt or terminal to start the codeigniter 4 application:

php spark serve

Then visit your web browser and hit the following url on it:

http://localhost/demo/
OR
http://localhost:8080/

Conclusion

Export data to excel in codeigniter 4 using phpexcel example; In this tutorial, You have learned how to export data from database using phpexcel library in codeigniter 4 app.