CodeIgniter 4 Export Data to Excel or CSV File Tutorial

In codeIgniter, PHPExcel library allow users to export or import data from database to excel or csv file. In this tutorial, you will learn how to export data in excel or csv file format using PHPexcel with CodeIgniter 4.

How to Export Data In Excel or CSV Format using PHPexcel with Codeigniter 4

Let’s follow the following steps to create & export data into excel csv format or create and save dynamic data in excel file using PHP excel library:

Step 1: Setup Codeigniter 4 Project
Step 2: Basic Configurations
Step 3: Create Table in Database
Step 4: Setup Database Credentials
Step 5: Download PhpSpreadsheet Libraray
Step 6: Create Controller
Step 7: Create Route
Step 8: Start Development Server

Step 1: Setup Codeigniter 4 Project

In this step, you will download the latest version of Codeigniter 4, Go to this link https://codeigniter.com/download Download Codeigniter 4 fresh new setup and unzip the setup in your local system xampp/htdocs/ . And change the download folder name “demo”

Step 2: Basic Configurations

Next, you will set some basic configuration on the app/config/app.php file, so let’s go to application/config/config.php and open this file on text editor.

Set Base URL like this

public $baseURL = 'http://localhost:8080';
To
public $baseURL = 'http://localhost/demo/';

Step 3: Create Table

Run the the following sql query to create table in database and insert data into it:

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: Setup Database Credentials

Edit app/Config/Database.php and set up database in this file like the following:

	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: Download PhpSpreadsheet Library

To download PHP libraray PhpSpreadsheet and create dynamic Excel file. So, open your terminal and execute the following command on it:

composer require phpoffice/phpspreadsheet

Then open application/config/config.php file and set you vendor directory path.

$config['composer_autoload'] = 'vendor/autoload.php';

Step 6: Create Controller

Go to app/Controllers and create a controller name ExcelExport.php. In this controller, you need to add the following methods into it:

<?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 ExcelExport extends Controller
{
  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: Create Route

Create a route in app/Config/Routes.php file that renders the table into the view file:

$routes->get('/', 'ExcelExport::index');

Step 8: Test Application

Run the following command to start development server:

php spark serve

Hit the following URL on browser for testing:

http://localhost:8080

Conclusion

That’s it; In this tutorial, you have learned how to export data to excel in codeigniter 4 app using phpexcel.

Subscribe to Blog via Email