Export data in excel using php and cakephp

To export data from a CakePHP application to an Excel file, you can use the PHPExcel library. Here’s how you can do it:

  1. Install the PHPExcel library using Composer. Add the following line to your composer.json file:
"require": {
"phpoffice/phpexcel": "^1.8"

Then run composer install.

  1. Create a new controller action that will generate the Excel file. For example, if you want to export a list of users, you could create a method like this in your UsersController:
public function export() {
$users = $this->Users->find('all');

// Load PHPExcel library
require_once(ROOT . DS . 'vendor' . DS . 'phpoffice' . DS . 'phpexcel' . DS . 'Classes' . DS . 'PHPExcel.php');

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Your Name")
                                 ->setLastModifiedBy("Your Name")
                                 ->setTitle("Users Export")
                                 ->setSubject("Users Export")
                                 ->setDescription("Users Export")
                                 ->setKeywords("users, export")

// Add data to the worksheet
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', 'Name')
            ->setCellValue('C1', 'Email');

$row = 2;
foreach ($users as $user) {
                ->setCellValue('A' . $row, $user->id)
                ->setCellValue('B' . $row, $user->name)
                ->setCellValue('C' . $row, $user->email);


// Set column widths

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="users.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

This method fetches all the users from the database and adds them to a new Excel worksheet. It sets the column widths, and then sends the Excel file as a download to the user’s browser.

  1. Add a link or button to your view to trigger the export action. For example:
echo $this->Html->link('Export Users', ['action' => 'export']);

That’s it! When the user clicks the link, an Excel file will be generated and downloaded to their computer.






Leave a Reply

Your email address will not be published. Required fields are marked *