How to Export Excel in CodeIgniter Using PhpOffice PhpSpreadsheet Library
Last updated on February 20th, 2021
Exporting table data to a file such as Excel is a more often requirement (for lots of systems and applications) today. Export excel in CodeIgniter app is one of the most common features we (CI developers) develop. Clients often ask such a feature for them to read data or report in a more convenient way.
We're using PHPExcel library years ago to add export excel feature in CodeIgniter. Sadly the legendary library has been deprecated and CI developers have to migrate to its direct successor -- PhpSpreadsheet. PhpOffice PhpSpreadsheet is a library purely made in PHP. This library created to provide set of classes allowing you to read and write spreadsheet files such as excel, libreoffice, etc. We're going to use this library instead of PHPExcel (the first version) to break compatibility issues, improve codebase quality, and for easy implementation of this export excel in CodeIgniter feature.
Requirements
- Your CodeIgniter app running on your server
- Composer (to easily add PhpSpreadsheet library)
- PHP version 5.6 or higher
- PHP modules: php_zip, php_xml, php_gd2 (run the command php -m to list all installed PHP modules in your system)
Steps to Add Export Excel Feature in CodeIgniter App
Step 1: Download and Install CodeIgniter
Skip this step if you already have your CodeIgniter app running on your server.
Download CodeIgniter here if you don't have it yet; Then follow CodeIgniter installation instruction here.
Step 2: Install PhpOffice PhpSpreadSheet Library
Open your terminal. Check your current working directory using pwd command (if you're using Linux) or cd command (if you're using Windows). You must be inside your project folder. Enter the command composer require phpoffice/phpspreadsheet to download PhpOffice PhpSpreadSheet library and it's dependencies.
install phpoffice phpspreadsheet library using composer
Composer will automatically download PhpSpreadSheet library and it's dependencies. Upon installation of PhpOffice PhpSpreadSheet library, you'll see a newly created folder named "phpoffice" inside "vendor" folder. That folder contains all the files necessary to export data to excel in CodeIgniter.
Step 3: Set Vendor Directory Path of your CodeIgniter App
Open your config.php located at /application/config directory and set directory path value.
$config['composer_autoload'] = 'vendor/autoload.php';
Step 4: Use PhpOffice PhpSpreadSheet Library
Having PhpOffice PhpSpreadSheet library installed in our CodeIgniter app, it is now much easier to add export excel feature.
Create a new controller file "ExportExcelFile.php" at /application/controllers directory, then copy-paste the code below.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class ExportExcelFile extends CI_Controller {
/**
* Index Page for this controller.
*
* Maps to the following URL
* http://example.com/index.php/welcome
* - or -
* http://example.com/index.php/welcome/index
* - or -
* Since this controller is set as the default controller in
* config/routes.php, it's displayed at http://example.com/
*
* So any other public methods not prefixed with an underscore will
* map to /index.php/welcome/<method_name>
* @see https://codeigniter.com/user_guide/general/urls.html
*/
public function index() {
$spreadsheet = new Spreadsheet(); // instantiate Spreadsheet
$sheet = $spreadsheet->getActiveSheet();
// manually set table data value
$sheet->setCellValue('A1', 'Gipsy Danger');
$sheet->setCellValue('A2', 'Gipsy Avenger');
$sheet->setCellValue('A3', 'Striker Eureka');
$writer = new Xlsx($spreadsheet); // instantiate Xlsx
$filename = 'list-of-jaegers'; // set filename for excel file to be exported
header('Content-Type: application/vnd.ms-excel'); // generate excel file
header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output'); // download file
}
}
Source Code Explanation
line 5 & 6: We use the PHP use operator to import PhpOffice PhpSpreadSheet library, this is referred as aliasing or importing.
line 25: Create index function. It'll be responsible for generating and exporting the excel file. The generated excel file will be downloaded automatically from your computer right after you access the route to this Controller using your browser. This functionality would be useful when you have HTML table data; you just have to add a button that will trigger the function.
You may change the variable $filename.
As you can see, the code actually works. The screenshot above is the actual excel file exported after running the script that we've created.
Does it work on your end? Let me know in the comments below. We hope you've learned how to use PhpOffice PhpSpreadSheet library to export excel in CodeIgniter app.
please can you do one for import into mysql from codeigniter. thanks
I think I do have “import into mysql from codeigniter” source code from one of my repo. Will try to find it and publish here.
My Excel Can’t Open this that file
Hi Cosmos,
Is the excel file successfully downloaded from your browser upon running the php script?
May I know the version of Microsoft Excel you are using?
Hey, the xlsx file is downloaded but can’t open it, i’m using office 2016
https://imgur.com/a/pH2JJN2
you outputting more content than just the file, try calling die right after the output
$writer->save(‘php://output’); // download file
die();
No meu dá uma mensagem de arquivo não encontrado !! 🙁
I have this error:
The use statement with non-compound name ‘PhpOfficePhpSpreadsheetSpreadsheet’ has no effect
I figured it out. You missed the backlashes.
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
Hi Tsioh!
Thanks for pointing it out.
I’ve updated the source code. : D
Thank you for the tutorial. But, I followed everything then when I run it I got “Fatal error: Acquisition cannot use PhpOffice\PhpSpreadsheet\Spreadsheet “.
Nevermind, it is okay now. I’m an idiot. Thank you so much!
Glad you solved it!
i copy and do what are you say in this toturial,
but, when i export my file.. why allaways say. “cannot open, because the file format or file extension is not valid
can you help me to solve this ??
Great tutorial, just a question about manual istallation of the PHPspreadsheet library.
In this case, should I use $config[‘composer_autoload’] = ‘vendor/autoload.php’;? How to call the library in controller in this case?
Thanks a lot
It works perfect, thanks a lot!