CodeIgniter 4 Export Data to Excel in Using PHPexcel Download - Yudhy Network

Share on:

Yudhy Network - Export data to excel in CodeIgniter 4 using phpexcel. In this tutorial, you will learn how to export data in excel format using phpexcel with CodeIgniter 4.

Export data to excel in CodeIgniter 4 using phpexcel. In this tutorial, you will learn how to export data in excel format using phpexcel with CodeIgniter 4.

Sometimes, you need to export data from database in excel format file with codeigniter 4 app. So, this tutorial will help you step by step on how to export dynamic data into Excel format using CodeIgniter 4 app. We will use PHP library PhpSpreadsheet to create and save dynamic Excel file to export and save data.

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

  • Download Codeigniter 4 Project
  • Basic Configurations
  • Create Table in Database
  • Setup Database Credentials
  • Download PhpSpreadsheet Libraray
  • Create Controller
  • Create Route
  • Start Development Server

Step 1: Download 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 in Database

In this step, you need to create table in database and as well as insert some data to implement an autocomplete search app in codeiginter 4. So visit your phpmyadmin panel and execute the following sql query in 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

In this step, you need to connect our project to the database. you need to go app/Config/Database.php and open database.php file in text editor. After opening the file in a text editor, you need to set up database credentials in this file like below.

	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 Libraray

In this step, you need to download PHP libraray PhpSpreadsheet to create and save 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

In this step, Visit 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

In this step, you need to create a route that renders the table into the view, place the following code in app/Config/Routes.php file.

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

Step 8: Start Development Server

In this step, open your terminal and execute the following command to start development sever:

php spark serve

Then, Go to the browser and hit below the URL:

http://localhost:8080

Conclusion

Export data to excel in CodeIgniter using PHP excel. In this tutorial, you have learned how to export data to excel in codeigniter 4 app using phpexcel.

If you have any questions or thoughts to share, use the comment form below to reach us.

Recommended CodeIgniter 4 Tutorial

Recommended:- CodeIgniter 4 jQuery Form Validation Example
Recommended:- CodeIgniter 4 Ajax Form Submit Validation Example
Recommended:- CodeIgniter 4 Image Upload With Preview Example
Recommended:- CodeIgniter 4 Pagination Example Tutorial
Recommended:- Codeigniter 4 Ajax Image Upload with Preview Example
Recommended:- Codeigniter 4 PDF Generator Tutorial Example
Recommended:- CodeIgniter 4 First Crud Example Tutorial
Recommended:- CodeIgniter 4 Rest Api Example Tutorial
Recommended:- Codeigniter 4 Login And Registration Tutorial Example
Recommended:- Send Email in CodeIgniter 4 With SMTP
Recommended:- CodeIgniter 4 Ajax Crud with datatables and Bootstrap Modals
Recommended:- Crop and Save Image using jQuery Coppie in Codeigniter 4
Recommended:- Codeigniter 4 Pie Chart Using Google JS Example
Recommended:- Codeigniter 4 Load More Data on Page Scroll using jQuery Ajax
Recommended:- Codeigniter 4 Google Map Multiple Markers Example
Recommended:- Codeigniter 4 Dynamic Dependent Dropdown with Ajax
Recommended:- Codeigniter 4 Autocomplete Textbox From Database using Typeahead JS
Recommended:- Codeigniter 4 Get Latitude and Longitude From Address
Recommended:- PHP Codeigniter 4 Google Column Charts Tutorial
Recommended:- Codeigniter 4 Autocomplete Search from Database using Ajax and Select2
Recommended:- Codeigniter 4 Google Bar & Line Charts Example
Recommended:- Codeigniter 4 Autocomplete Address using Google API Example
Recommended:- Codeigniter 4 Google ReCaptcha V2 Example

Please ask in the comments!

Suport : Cotekno.Com

comments powered by Disqus