Ublaboo datagrid – export to XLS/XLSX
- Martin Vágovszký
- Member | 14
Dear Community members,
I would like to finish part of my code, which is responsible for exporting
datagrid content to XLS/XLSX (via PhpSpreadsheet library).
I was able to successfully setup CSV export (integrated in Ublaboo), but I am
still struggling how to finish my code to be able to offer XLS for direct
download when clicking an Export button. Please see a snippet of my code.
use App\Model\Database\Entity\Project;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\EntityManagerInterface;
use App\UI\Control\DataGridFactory;
use App\UI\Control\BaseDataGrid;
use App\UI\Control\BaseControl;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Nette\Localization\ITranslator;
class ProjectDataGrid extends BaseControl
{
/** @var EntityManager */
private $entityManager;
/** @var DataGridFactory */
private $datagridFactory;
/** @var Spreadsheet */
private $spreadsheet;
/** @var ITranslator */
private $translator;
/**
* @param DataGridFactory $dataGridFactory
* @param EntityManagerInterface $entityManager
* @param Spreadsheet $spreadsheet
*/
public function __construct(
DataGridFactory $dataGridFactory,
EntityManagerInterface $entityManager,
Spreadsheet $spreadsheet,
ITranslator $translator
)
{
$this->datagridFactory = $dataGridFactory;
$this->entityManager = $entityManager;
$this->spreadsheet = $spreadsheet;
$this->translator = $translator;
}
/**
* @throws \ReflectionException
*/
public function render(): void
{
$this->getTemplate()
->setFile(__DIR__ . DIRECTORY_SEPARATOR . 'templates' . DIRECTORY_SEPARATOR . 'projectGrid.latte')
->render();
}
/**
* @return BaseDataGrid
*/
public function createComponentProjectGrid(): BaseDataGrid
{
$datagrid = $this->datagridFactory->forBackend();
$datagrid->setDefaultSort(['number' => 'ASC']);
$datagrid->setDataSource($this->entityManager->getProjectRepository()->getGridDataSource());
// ...
// === Coluns setup here ===
// ...
// Export
$datagrid->addExportCallback('export', [$this, 'generateXls'], true);
return $datagrid;
}
public function generateXls($data){
// Iterate over filtrated projects
$sheetData = array();
$worksheet = $this->spreadsheet->getActiveSheet();
// ...
// === $sheetData array contains two dimensional data for XLS ===
// ...
$worksheet->fromArray($sheetData);
// ====================================
// >>> What I should do here <<<
// ????????????????????????????????????
//=====================================
}
}
- Ozzrel
- Generous Backer | 54
// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="filename.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($worksheet, 'Xlsx');
$writer->save('php://output');
exit;
Last edited by Ozzrel (2023-01-12 08:01)
- Pavel Kravčík
- Member | 1195
Lot of packages has already implemented function:
$writer = IOFactory::createWriter($worksheet, 'Xlsx');
$writer->openToBrowser('downloadname');
$writer->close();
//can be use instead of exit
$this->getPresenter()->terminate();
Also is possible send "fileResponse ":https://blog.nette.org/…onses-part-2 (if file is saved on disk)
- Martin Vágovszký
- Member | 14
Ozzrel wrote:
// Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="filename.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = IOFactory::createWriter($worksheet, 'Xlsx'); $writer->save('php://output'); exit;
Thank you, it is working as expected. The code is not ultimate-pretty, but it it does what it should do.