KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

PIVOT EXCEL #813

Open Hana opened this topic on on Apr 17, 2019 - 5 comments

Hana commented on Apr 17, 2019

Hi, how i can export excel a pivot table with a custom query??

<?php
require APPPATH."/libraries/koolreport/autoload.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\PivotExcelExport;
use \koolreport\processes\Map;
use \koolreport\processes\Limit;
use \koolreport\processes\Filter;
use \koolreport\cube\processes\Cube;

class ReporteGastos extends \koolreport\KoolReport
{
    use \koolreport\codeigniter\Friendship;
    use \koolreport\clients\Bootstrap;
    use \koolreport\export\Exportable;
    use \koolreport\excel\ExcelExportable;  
    
    function setup()
    {
        $plantas_gasolineras_empresa = array_unique($_SESSION['logged_in']['plantas_id_empresa']);
        $plantas_gasolineras_planta = $_SESSION['logged_in']['plantas_id_planta'];

        $query = "SELECT copd_id_cuenta_contable,
                         gept_nombre,
                         copd_cargo,
                         'SEMANA '||to_char(to_date(copd_fecha,'YYYYMMDD'),'w') semana
                FROM co_polizas_detalle 
                LEFT OUTER JOIN ge_plantas ON gept_id_planta = copd_id_planta AND copd_id_empresa = gept_id_empresa
                WHERE copd_id_planta IN :planta 
                AND   copd_fecha BETWEEN '20170101' AND '20170131' 
                AND   copd_id_empresa = 402 
                AND   copd_estatus='APL'
                AND   copd_id_cuenta_contable IN ('0104-0402-0402-0001') 
                ORDER BY copd_id_cuenta_contable, copd_id_planta, gept_nombre, semana";
                
        $this->src('a3_oracle')->query($query)
        ->params(array(
            ":planta"=>$plantas_gasolineras_planta,
            ":empresa"=>$plantas_gasolineras_empresa
        ))
        ->pipe(new ColumnMeta(array(
            'COPD_CARGO'=>array(
                'type' => 'number',
                'prefix' => '$',
                'decimals'=>2,
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "column"=>"SEMANA",
                "row" => "COPD_ID_CUENTA_CONTABLE, GEPT_NOMBRE, COPD_CARGO",
            ),
            "aggregates"=>array(
                "sum"=>"COPD_CARGO",
            )
        )))
        ->pipe($this->dataStore('co_polizas_detalle'));
    }
}

it create file excel but shows an error opening the file.

KoolReport commented on Apr 18, 2019

Could you please post the part of the code that you export to excel

Hana commented on Apr 22, 2019

David Winterburn commented on Apr 23, 2019

Hi Hana,

Please open the exported excel file with your text editor. There might be some error message outputed when exporting. Please let us know if there's any message in the excel file when opening with a text editor like Visual Code, Sublime Text or Notepad++. Thanks!

Hana commented on Apr 23, 2019

Me genera un renglón al inicio, lo que hace que no quiera abrir el archivo. Saludos!!

David Winterburn commented on Apr 24, 2019

Hi Hana,

What is the line at the beginning of the excel file? Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

None