Ublaboo datagrid – export to XLS/XLSX

Martin Vágovszký
Member | 14
+
0
-

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 | 51
+
+1
-
		// 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 | 1180
+
0
-

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
+
0
-

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.