Export Data Into Excel Report In CodeIgniter 4 Tutorial

 
#--------------------------------------------------------------------
# DATABASE
#-------------------------------------------------------------------- 
 database.default.hostname = localhost
 database.default.database = codeigniter4_app
 database.default.username = root
 database.default.password = root
 database.default.DBDriver = MySQLi
   

//...

$routes->group("student", function ($routes) {

    $routes->get("/", "StudentController::index");
    $routes->get("download-report", "StudentController::downloadExcelReport");
});

<?php

namespace App\Models;

use CodeIgniter\Model;

class StudentModel extends Model
{
    protected $DBGroup = 'default';
    protected $table = 'students';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;
    protected $insertID = 0;
    protected $returnType = 'array';
    protected $useSoftDelete = false;
    protected $protectFields = true;
    protected $allowedFields = [
        "name",
        "email",
        "mobile",
        "branch",
    ];

    // Dates
    protected $useTimestamps = false;
    protected $dateFormat = 'datetime';
    protected $createdField = 'created_at';
    protected $updatedField = 'updated_at';
    protected $deletedField = 'deleted_at';

    // Validation
    protected $validationRules = [];
    protected $validationMessages = [];
    protected $skipValidation = false;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert = [];
    protected $afterInsert = [];
    protected $beforeUpdate = [];
    protected $afterUpdate = [];
    protected $beforeFind = [];
    protected $afterFind = [];
    protected $beforeDelete = [];
    protected $afterDelete = [];
}

<?php

namespace App\Database\Seeds;

use App\Models\StudentModel;
use CodeIgniter\Database\Seeder;
use Faker\Factory;

class StudentSeeder extends Seeder
{
    public function run()
    {
        $data = [];

        for ($i = 0; $i < 50; $i++) {

            $data[] = $this->generateTestStudent();
        }

        $student_obj = new StudentModel();

        $student_obj->insertBatch($data);
    }

    public function generateTestStudent()
    {
        $faker = Factory::create();

        return [
            "name" => $faker->name(),
            "email" => $faker->email,
            "mobile" => $faker->phoneNumber,
            "branch" => $faker->randomElement([
                "Computer Science",
                "Mechancial",
                "Electrical",
                "Civil",
                "Robotics",
                "Medical",
            ]),
        ];
    }
}

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\StudentModel;

// Import Excel Package
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class StudentController extends BaseController
{
	public function index()
	{
		$student_obj = new StudentModel();

		$students = $student_obj->findAll();

		return view("list-student", [
			"students" => $students,
		]);
	}

	public function downloadExcelReport()
	{
		$student_obj = new StudentModel();

		$students = $student_obj->findAll();

		$fileName = 'students.xlsx'; // File is to create

		$spreadsheet = new Spreadsheet();

		$sheet = $spreadsheet->getActiveSheet();
		$sheet->setCellValue('A1', 'Id');
		$sheet->setCellValue('B1', 'Name');
		$sheet->setCellValue('C1', 'Email');
		$sheet->setCellValue('D1', 'Mobile');
		$sheet->setCellValue('E1', 'Branch');
		$rows = 2;

		foreach ($students as $val) {
			$sheet->setCellValue('A' . $rows, $val['id']);
			$sheet->setCellValue('B' . $rows, $val['name']);
			$sheet->setCellValue('C' . $rows, $val['email']);
			$sheet->setCellValue('D' . $rows, $val['mobile']);
			$sheet->setCellValue('E' . $rows, $val['branch']);
			$rows++;
		}

		$writer = new Xlsx($spreadsheet);

		// file inside /public folder
		$filepath = $fileName;

		$writer->save($filepath);

		header("Content-Type: application/vnd.ms-excel");
		header('Content-Disposition: attachment; filename="' . basename($filepath) . '"');

		header('Expires: 0');
		header('Cache-Control: must-revalidate');
		header('Pragma: public');
		header('Content-Length: ' . filesize($filepath));
		flush(); // Flush system output buffer
		readfile($filepath);

		exit;
	}
}

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container" style="margin-top:30px;">
  <h2 style="text-align: center;">Student Report</h2>
  <h3  style="text-align: center;">Online Web Tutor - Export Excel in CodeIgniter 4 Tutorial</h3>

  <div class="panel panel-primary">
    <div class="panel-heading">
       Student Report
       <a href="<?= base_url('student/download-report') ?>" class="btn btn-info pull-right" style="margin-top: -7px;">Download Excel Report</a>
    </div>
    <div class="panel-body">
      <table class="table table-striped">
        <thead>
          <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Mobile</th>
            <th>Branch</th>
          </tr>
        </thead>
        <tbody>

          <?php
            if(count($students) > 0){

              $count = 1;

              foreach($students as $student){
                ?>
                  <tr>
                    <td><?= $count++ ?></td>
                    <td><?= $student['name'] ?></td>
                    <td><?= $student['email'] ?></td>
                    <td><?= $student['mobile'] ?></td>
                    <td><?= $student['branch'] ?></td>
                  </tr>
                <?php
              }
            }
          ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
</body>
</html>