Code your dreams into reality.
Every line of code is a step towards a better future.
Embrace the bugs, they make you a better debugger.

CodeIgniter 4 Export to Excel Tutorial

Last Updated on August 20, 2024 by

phpoffice/phpspreadsheet 3rd party php library allows developers to build a feature into the application to export data from all database tables to excel, csv, pdf, etc files.

Let’s start to export data from mysql database to excel file or csv file in codeigniter:

Step 1: Install the PHPSpreadsheet Library

Run the following composer command to install PHPSpreadsheet library in your application:

composer require phpoffice/phpspreadsheet

Step 2: Create a Model

Go to the app/Models/ directory and create a model file name UserModel.php that handles the database operations to export data in the Excel file:

<?php

namespace App\Models;

use CodeIgniter\Model;

class UserModel extends Model
{
    protected $table = 'users';
    protected $primaryKey = 'id';
    protected $allowedFields = ['name', 'email', 'phone'];
}

Step 3: Create a Controller for Excel Export

Go to the app/Controllers/ directory and create a controller file named ExcelExport.php that handles the file export mysql data in excel file processing:

<?php

namespace App\Controllers;

use App\Models\UserModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use CodeIgniter\Controller;

class ExcelExport extends Controller
{
    public function export()
    {
        // Load data from the database
        $userModel = new UserModel();
        $users = $userModel->findAll();

        // Create a new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Set document properties
        $spreadsheet->getProperties()
                    ->setCreator("Your Name")
                    ->setLastModifiedBy("Your Name")
                    ->setTitle("CodeIgniter 4 Export Example")
                    ->setSubject("Export Example")
                    ->setDescription("Export data to Excel using PHPExcel in CodeIgniter 4.");

        // Add header row
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', 'Name');
        $sheet->setCellValue('C1', 'Email');
        $sheet->setCellValue('D1', 'Phone');

        // Populate data from database
        $row = 2;
        foreach ($users as $user) {
            $sheet->setCellValue('A' . $row, $user['id']);
            $sheet->setCellValue('B' . $row, $user['name']);
            $sheet->setCellValue('C' . $row, $user['email']);
            $sheet->setCellValue('D' . $row, $user['phone']);
            $row++;
        }

        // Write to Excel file
        $writer = new Xlsx($spreadsheet);
        $filename = 'users_data.xlsx';

        // Save the file to the server or output it directly to the browser
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        exit;
    }
}

Step 4: Define Routes

Edit app/Config/Routes.php file, and define export file routes in it:

$routes->get('export-excel', 'ExcelExport::export');

Step 5: Test the Export Functionality

Type url http://yourdomain/export-excel on browser to test this application.

Leave a Comment