Exporting to an excel file

Richb201 commented on Dec 20, 2022

I am creating a number of reports and displaying them in the browser. ->toBrowser("executive_summary.pdf"); But I want to keep a copy of each in S3. Is there a way to also make a disk copy (at the same time)? If so I will then upload them to S3 at some later time.

Richb201 commented on Dec 20, 2022

I also need to export all of my existing datastores into Excel. I do need to also filter them via pipe. Is there a way to specify "all datastores", or must I name them one by one? all fields is OK. And should I be using BigSpreadsheet?

Sebastian Morales commented on Dec 21, 2022

You can try this code to both save the exported pdf file and output it to browser:

    $exportHandler = $report->export("SalesQuartersPdf")
        ->settings([...])
        ->pdf([...]);

    $exportHandler->saveAs("path/on/server/SalesQuarters.pdf");
    $exportHandler->toBrowser("SalesQuarters.pdf");

Richb201 commented on Dec 21, 2022

Thx. Does this work for .xls as well as .pdf?

Sebastian Morales commented on Dec 26, 2022

Yes, this method works for excel as well as pdf export.

Richb201 commented on Dec 28, 2022

I have exported the file with

 $report->run()->exportToExcel(array(
            "properties" => array(
                "creator" => "Research Study Online",
                "title" => $title,
                "description" => $description,
                "subject" => "Audit Workpapers",
                "keywords" => "",
                "category" => "",
            )
        ))->toBrowser($this->session->userdata('campaign') . "_workbook.xlsx");

This opens fine in Google Sheets and also in LibreOffice. But in Excel it won't work. Any ideas?

Sebastian Morales commented on Dec 29, 2022

Pls send us the file that can not be opened in MS Excel so that we can check it for you. Rgds,

Richb201 commented on Dec 29, 2022

Thnks. Here is an example of an xls that can't be opened in MS excel but works in the other tools. How can I upload this file?

Also, to create this file I create many datastores (all the data stores from the system, but for this user). It takes a REALLY long time. Any other ideas t speed this up?

Sebastian Morales commented on Dec 30, 2022

You can email the corrupted file to us at support@koolreport.com / support@koolphp.net. Send us the report and export php files as well. Tks,

Richb201 commented on Jan 3, 2023

I am in Europe on vacation. I will send you the file when I get back to my hotel. My plan was to create a workbook for a specific user. The workbook will be used to audit the report. It has a copy of each table, filtered for only the current user. It does however, contain info that needs to kept secure. The copy I will send is just a demo workbook. My app uses ssl so can I assume that the data is secure while being transferred to the user, under normal koolreport use?

Sebastian Morales commented on Jan 4, 2023

If you use ssl connection I think it's secure enough for koolreport data export as long as your ssl certificate is valid.

As for your corrupted excel export file I checked it and found there's html output at the end of the excel content:

xl/worksheets/_rels/sheet22.xml.relsPK55N%�<html lang="en">
<head>
    <!-- Delighted -->
    <script type="text/javascript">
        ...
    </script>
    <!-- MS Clarity -->
    ...

Some applications such as Google Sheets, LibreOffice, Mac OS Number, etc can realize the redundant part and open the file but MS Excel refuses it. Please make sure to either exit PHP processing after excel export's saveAs(), toBrowser() or buffer any output except for export content. For example, call ob_start() right after the first saveAs(), toBrowser() and ob_end_clean() right before the next saveAs(), toBrowser().

Richb201 commented on Jan 4, 2023

Thx. I am not exactly sure where to place the start and end. This is what I did:

        ob_start();
//        $report->run()->exportToExcel()->toBrowser($this->session->userdata('campaign') . "_workbook.xlsx");
        $report->run()->exportToExcel(array(
            "properties" => array(
                "creator" => "Research Study Online",
                "title" => $title,
                "description" => $description,
                "subject" => "Audit Workpapers",
                "keywords" => "",
                "category" => "",
            )
        ))->toBrowser($this->session->userdata('campaign') . "_workbook.xlsx");
        ob_end_clean();

I do not have a Windows PC with me here so I wont be able to see if that fixed until I get back the US in Feb.

Sebastian Morales commented on Jan 4, 2023

Pls try this:

        ob_start();       
        //...put any previous command here if needed
        ob_end_clean(); 
//        $report->run()->exportToExcel()->toBrowser($this->session->userdata('campaign') . "_workbook.xlsx");
        $report->run()->exportToExcel(array(
            "properties" => array(
                "creator" => "Research Study Online",
                "title" => $title,
                "description" => $description,
                "subject" => "Audit Workpapers",
                "keywords" => "",
                "category" => "",
            )
        ))->toBrowser($this->session->userdata('campaign') . "_workbook.xlsx");
        exit; //quit processing right after exporting

KoolReport's Forum

Exporting to an excel file #2913

Build Your Excellent Data Report