KoolReport's Forum

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

Export pivot table to excel resulting no data #2531

Closed saiful opened this topic on on Jan 10, 2022 - 6 comments

saiful commented on Jan 10, 2022

hello, after some days with the same error on this problem: https://www.koolreport.com/forum/topics/2518 i decide to export manually each reports. but it still get the same error when i try to export pivot data into excel:

i don't know what's wrong with my code, i was use the same template from the docs. this is the setup code:

   protected function setup()
    {
        $this->src("sqlserver")
       ... (select query) ...
        ->pipe(new ColumnRename([
            'LOCATION_NM'=>'Location',
            'CURR'=>'Currency',
            'CURR_NM'=>'Currency Name',
            'ACC_NM'=>'Account Name',
            'PRE_CU_AMT'=>'Beginning Balance',
            'DR_CU_AMT'=>'Debit',
            'CR_CU_AMT'=>'Credit',
            'BAL_CU_AMT'=>'Last Balance'
        ]))
        ->pipe(new Pivot([
            "dimensions"=>[
                "row"=>"Location, Currency Name, Account Name",
            ],
            "aggregates"=>[
                "sum"=>"Beginning Balance, Debit, Credit, Last Balance",
            ],
        ]))
        ->pipe($this->dataStore("FNAFAD0001_100"));

this is my export function:

    public function fna_100_excel()
    {
        $report = new FNAFAD0001_100([
            "session_id"=>$this->session_id,
            "location_cd"=>$this->location_cd
        ]);
        $report->run()
        ->exportToExcel("FNAFAD0001_100_EXCEL")
        ->toBrowser("FINANCE_DAILY_REPORT_HEADER.xlsx");
    }


this is my excel.view file:


<?php
    use \koolreport\excel\PivotTable;
    $sheet1 = "Finance Daily Report";
?>
<meta charset="UTF-8">
<meta name="title" content="Finance Daily Report">

<div sheet-name="<?php echo $sheet1; ?>">
    <?php
    $allStylesArray = [
        'font' => [
            'name' => 'Calibri', //'Verdana', 'Arial'
            'size' => 30,
            'bold' => false,
            'italic' => FALSE,
            'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
            'strikethrough' => FALSE,
            'superscript' => false,
            'subscript' => false,
            'color' => [
                'rgb' => '000000',
                'argb' => 'FF000000',
            ]
        ],
        'alignment' => [
            'horizontal' => 'general',//left, right, center, centerContinuous, justify, fill, distributed
            'vertical' => 'bottom',//top, center, justify, distributed
            'textRotation' => 0,
            'wrapText' => false,
            'shrinkToFit' => false,
            'indent' => 0,
            'readOrder' => 0,
        ],
        'borders' => [
            'top' => [
                'borderStyle' => 'none', //dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
                'color' => [
                    'rgb' => '808080',
                    'argb' => 'FF808080',
                ]
            ],
            //left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
        ],
        'fill' => [
            'fillType' => 'none', //'solid', 'linear', 'path', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray'
            'rotation' => 90,
            'color' => [
                'rgb' => 'A0A0A0',
                'argb' => 'FFA0A0A0',
            ],
            'startColor' => [
                'rgb' => 'A0A0A0',
                'argb' => 'FFA0A0A0',
            ],
            'endColor' => [
                'argb' => 'FFFFFF',
                'argb' => 'FFFFFFFF',
            ],
        ],
    ];
    $styleArray = [
        'font' => [
            'italic' => true,
            'color' => [
                'hex' => 'a83234',
            ]
        ],
    ];
    ?>
    <div>Finance Daily Report</div>
    <div>
        <?php
        PivotTable::create(array(
                "dataSource" => $this->dataStore('FNAFAD0001_100'),
                'hideSubTotalRows' => true,
                'hideSubTotalColumns' => true,
                'hideGrandTotalRow' => true,
                'hideGrandTotalColumn' => true,
                "showDataHeaders" => true,
               
                'map' => array(
                    'rowField' => function($rowField, $fieldInfo) {
                        return $rowField;
                    },
                    'columnField' => function($colField, $fieldInfo) {
                        return $colField;
                    },
                    'dataField' => function($dataField, $fieldInfo) {
                        // Util::prettyPrint($fieldInfo);
                        $v = $dataField;
                        if ($v === 'Beginning Balance - sum')
                            $v = 'Beginning balance';
                        else if ($v === 'Debit - sum')
                            $v = 'Debit';
                        else if ($v === 'Credit - sum')
                            $v = 'Credit';
                        else if ($v === 'Last Balance - sum')
                            $v = 'Last Balance';
                        return $v;
                    },
                    'dataHeader' => function($dataField, $fieldInfo) {
                        // Util::prettyPrint($fieldInfo);
                        $v = $dataField;
                        if ($v === 'Beginning Balance - sum')
                            $v = 'Beginning balance';
                        else if ($v === 'Debit - sum')
                            $v = 'Debit';
                        else if ($v === 'Credit - sum')
                            $v = 'Credit';
                        else if ($v === 'Last Balance - sum')
                            $v = 'Last Balance';
                        return $v;
                    },
                    'waitingField' => function($waitingField, $fieldInfo) {
                        return $waitingField;
                    },
                    'rowHeader' => function($rowHeader, $headerInfo) {
                        // Util::prettyPrint($headerInfo);
                        $v = $rowHeader;
                        if ($headerInfo['fieldName'] === 'Location')
                            $v = 'Location ' . $v;
                        else if ($headerInfo['fieldName'] === 'Currency Name')
                            $v = 'Currency Name' . $v;
                        else if ($headerInfo['fieldName'] === 'Account Name')
                            $v = 'Account Name' . $v;
                        
                        
                        if (isset($headerInfo['childOrder']))
                            $v = $headerInfo['childOrder'] . ". " . $v;
                        return $v;
                    },
                    'columnHeader' => function($colHeader, $headerInfo) {
                        $v = $colHeader;
                       
                        if ($headerInfo['fieldName'] === 'Beginning Balance')
                            $v = 'Beginning Balance ' . $v;
                        else if ($headerInfo['fieldName'] === 'Debit')
                            $v = 'Debit' . $v;
                        else if ($headerInfo['fieldName'] === 'Credit')
                            $v = 'Credit' . $v;
                        else if ($headerInfo['fieldName'] === 'Last Balance')
                            $v = 'Last Balance' . $v;
            
                        if (isset($headerInfo['childOrder']))
                            $v = $headerInfo['childOrder'] . ". " . $v;
                        return $v;
                    },
                    'dataCell' => function($value, $cellInfo) {
                        return $value;
                        // Util::prettyPrint($cellInfo);
                        $rfOrder = $cellInfo['row']['fieldOrder'];
                        $cfOrder = $cellInfo['column']['fieldOrder'];
                        $df = $cellInfo['fieldName'];
                        $dfOrder = $cellInfo['fieldOrder'];
                        // return "$rfOrder:$cfOrder:$df. $value";
                        return $cellInfo['formattedValue'];
                    },
                ),
                'excelStyle' => array(
                    'dataField' => function($dataFields) use ($styleArray) {
                        return $styleArray;
                    },
                    'dataHeader' => function($dataFields, $fieldInfo) use ($styleArray) {
                        return $styleArray;
                    },
                    'columnHeader' => function($header, $headerInfo) use ($styleArray) {
                        return $styleArray;
                    },
                    'rowHeader' => function($header, $headerInfo) use ($styleArray) {
                        return $styleArray;
                    },
                    'dataCell' => function($value, $cellInfo) use ($styleArray) {                    
                        return $styleArray;
                    },
                )
        ));
        ?>
    </div>
</div>

Sebastian Morales commented on Jan 10, 2022

Pls make sure your excel view file is "FNAFAD0001_100_EXCEL.view.php" and in your excel view try removing all properties except for "dataSource":

//excel view
        \koolreport\excel\PivotTable::create(array(
                "dataSource" => $this->dataStore('FNAFAD0001_100')
        ));

Let us know the result. Rgds,

saiful commented on Jan 11, 2022

here is the result:

Sebastian Morales commented on Jan 12, 2022

Pls replace excel PivotTable with excel Table in your excel view to see if there's data in your datastore:

        \koolreport\excel\Table::create(array(
                "dataSource" => $this->dataStore('FNAFAD0001_100')
        ));
saiful commented on Jan 12, 2022

it works, thanks

Sebastian Morales commented on Jan 12, 2022

Pls be clearer. Does excel Table or excel PivotTable work? And if excel PivotTable works, why didn't it work before? Tks,

saiful commented on Jan 12, 2022

my apologies, my params is missing. it works both on excel Table or Pivot now.

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
None yet

Excel