Step 1: Install PHPSpreadsheet

composer require phpoffice/phpspreadsheet

Step 2: Create a Controller

Create a new controller that will handle the export functionality. For example, create a file named Export.php in the app/Controllers/export directory.

<?php
  namespace App\Controllers\export;

  use App\Controllers\BaseController;

  use App\Models\customer\CustomerModel;

  use PhpOffice\PhpSpreadsheet\Spreadsheet;
  use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

  class Export extends BaseController {

      public function __construct() {
          $db                     = db_connect();
          $this->session          = \Config\Services::session();

          $this->customer         = new CustomerModel($db);

          $this->ip_address       = $_SERVER['REMOTE_ADDR'];
          $this->datetime         = date("Y-m-d H:i:s");

      }

      public function index() {
          $this->list();
      }

      public function list() {
          $data                   = [];
          $data ['content_title'] = 'Export to Excel';
          echo view('export/list', $data);
      }

      public function excel() {
          $alphabets  = [
          'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X','Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX','AY', 'AZ'
          ];
          $query      = '
          SELECT *
                  FROM customer_info
          ';
          $query_result   = $this->customer->runQuery($query);
          $list_fields = $query_result->getFieldNames();
          $result        = $query_result->getResult();
          if($result) {
              $spreadsheet     = new Spreadsheet();
              $worksheet      = $spreadsheet->getActiveSheet();
              foreach ($list_fields as $key => $field) {
                  $field_name = str_replace("_", " ", strtoupper($field));
                  $worksheet->setCellValue($alphabets[$key] . '1', $field_name);
              }

              $index         = 2;
              foreach ($result as $key => $row) {
                  $sub_index = 0;
                  foreach ($row as $sub_key => $value) {
                      $worksheet->setCellValue($alphabets[$sub_index] . $index, $value);
                      $sub_index++;
                  }
                  $index++;
              }

              $writer  = new Xlsx($spreadsheet);
              $fileName    = 'customer_info_'.date('dmYHis').'.xlsx';
              $writer->save($fileName);
              $json = [
              'message'        => showSuccessMessage("File has been exported successfully"),
                      'status'        => true,
                      'location'      => base_url($fileName),
   ];
          } else {
              $json = [
              'status'    => false,
                      'message'   => showDangerMessage("Entered email address does not exists. Please try again"),
          ];
          }
          echo json_encode($json);
      }
  }

Step 3: Create a Model

Create a new model that will handle the database functionality. For example, create a file named CustomerModel.php in the app/Models/customer directory.

<?php
namespace App\Models\customer;

use CodeIgniter\Model;
use CodeIgniter\Database\ConnectionInterface;

class CustomerModel extends Model {

    protected $db;
    public function __construct(ConnectionInterface &$db) {
        $this->db =& $db;

        $this->table = 'customer_info';
    }

    public function addEntry($data) {
        $this->db
                ->table($this->table)
                    ->insert($data);
        return $this->db->insertID();
    }

    public function updateEntry($where, $data) {
        return $this->db
                ->table($this->table)
                    ->where($where)
                    ->set($data)
                    ->update();
    }

    public function deleteEntry($where) {
        return $this->db
                ->table($this->table)
                    ->where($where)
                    ->delete();
    }

    public function getEntry($where) {
        return $this->db
                ->table($this->table)
                    ->where($where)
                    ->get()
                    ->getRow();

    }

    public function getEntryList($where = 0) {
        if($where) {
            return $this->db
                    ->table($this->table)
                ->where($where)
                ->get()
                ->getResult();
        } else {
            return $this->db
                    ->table($this->table)
                ->get()
                ->getResult();
        }
    }

    public function getNumRows($where) {
        return $this->db
                ->table($this->table)
                    ->where($where)
                    ->get()
                    ->getNumRows();

    }

    public function runQuery($query) {
        return $this->db->query($query);
    }
}

Step 4: Create Views

Create views (list.php) in the app/Views/export folder.

<!DOCTYPE html>
<html lang="en">

<head>
   <title><?php echo $content_title; ?></title>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css"/>
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
   <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
   <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
</head>

<body>

<div class="jumbotron text-center">
    <h1>Welcome to Infovistar.in</h1>
    <p>Export to Excel with AJAX</p>
   </div>
   <div class="container">
        <div class="row">
            <div class="col-sm-12 message">
            </div>
            <div class="col-sm-12 text-center">
                <button type="button" class="btn btn-primary" id="btn-export">Export to Excel</button>
            </div>
        </div>
   </div>

    <script>
    $(document).ready(function() {

        $("body").on("click", "#btn-export", function() {
            $.post('<?php echo base_url("export/excel"); ?>', null, function(result) {
                $(".message").html(result.message);
                if(result.status) {
                    window.location.replace(result.location);
                }
            }, 'json');
        });

    });
    </script>
</body>

</html>

Step 5: Create a Route

Open the app/Config/Routes.php file and add a route for the Export:

// Export Excel Routes
$routes->group('export', function($routes) {
    $routes->get('/',                   'export\Export::index');
    $routes->get('list',                'export\Export::list');
    $routes->post('excel',              'export\Export::excel');
});

Step 6: Output