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/phpspreadsheetStep 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.