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.

2. 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() {
$this->loadModel('Users');
$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")
                                 ->setCategory("Export");

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

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

    $row++;
}

// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);

// 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');
$objWriter->save('php://output');
exit;
}

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.

3. 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.