How to Export Data to Excel Using PHPexcel in CodeIgniter 4?

Apr 20, 2022 . Admin



Hi Guys,

If you need to see example of export data to excel in codeigniter 4 using spreadsheet example. This post will give you simple example of how to export data in excel using codeigniter 4. we will help you to give example of export data to excel in codeigniter 4 using phpexcel download. we will help you to give example of codeigniter 4 export excel.

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

I’m going to show you about codeigniter 4 export excel. You just need to some step to done export data to excel in php codeigniter 4.

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

First of all we will require install phpspreadsheet library following bellow command.

composer require phpoffice/phpspreadsheet

After, successfully execute 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 ExcelExportController.php file and append the example code in..

app/Controllers/ExcelExportController.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 ExcelExportController 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('/excel-export', 'ExcelExportController::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/excel-export

I hope it can help you...

#Codeigniter 4