KoolReport's Forum

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

Excel export data field mapping not working #1119

Open Ralf Föhring opened this topic on on Oct 9, 2019 - 10 comments
Ralf Föhring commented on Oct 9, 2019

Hi, it looks like in the new release there are some changes in the pivot how map/header_map. Data field mapping does not seem to work when exporting to excel although it's rendering fine in browser.

KoolReport commented on Oct 9, 2019

Thank you for letting us know. I have sent your case to dev.team to investigate. I will keep you update.

David Winterburn commented on Oct 10, 2019

Hi Ralf,

Would you please post your export code? Thanks!

Ralf Föhring commented on Oct 11, 2019

This was working in the previous version

'map' => [
                'dataCell' => function($value, $cellInfo) {
                    $columns = [];
                    $rows = [];
                    $data = '';

                    foreach( $cellInfo['row'] as $k=>$v ){

                        if( is_array( $v ) && $v['value'] != '{{all}}' && $v['value'] != '{{other}}'){

                            $rows['$k'] = $v;
                            $y = $v['value'];
                            $data .= " data-$k='$y' "; 
                        }
                    }
                    foreach( $cellInfo['column'] as $k=>$v ){

                        if( is_array( $v ) && $v['value'] != '{{all}}' && $v['value'] != '{{other}}'){

                            $columns['$k'] = $v;
                            $y = $v['value'];
                            $data .= " data-$k='$y' "; 
                        }
                    }

                    return "<a target='_blank' class='run-subreport' $data>$cellInfo[formattedValue]</a>";
                },
                'rowField' => function($rowField, $fieldInfo) {
                    return translate( $rowField, '' );
                },
                'columnField' => function($colField, $fieldInfo) {
                    return translate( $colField, '' );
                },
                'dataField' => function($dataField, $fieldInfo) {
                    return translate( $dataField, '' );
                },
                'waitingField' => function($waitingField, $fieldInfo) {
                    return translate( $waitingField, '' );
                },
                'columnHeader' => function($colHeader, $headerInfo) {
                    $v = $colHeader;
                    if ($headerInfo['fieldName'] === 'month')
                        $v = translate($v, $headerInfo['fieldName']);
        
                    return $v;
                },
            ],

David Winterburn commented on Oct 14, 2019

Hi Ralf,

Please post your whole php code when exporting. Did you use "headerMap" property together or there's only "map" property? Thanks!

Ralf Föhring commented on Oct 16, 2019

KoolReport v4.3.1 This is in the view:

function translate( $v, $f ){

                switch ($v) {
                    case 'sale_amount - sum': return 'Summe';
                    case 'sale_amount - count': return 'Anzahl';
                    case 'sale_amount - avg': return 'Durchschnitt';
                    case 'price - avg': return 'Preis &Oslash';
                    case 'quantity_amount - sum': return 'Menge';
                    case 'factor - sum': return 'Faktor';
                    case 'detail_id - count': return 'Positionen';
                    case 'order_id': return 'Id';
                    case 'order_id - distinctCount': return 'Aufträge';
                    case 'year': return 'Jahr';
                    case 'quarter': return 'Quartal';
                    case 'month': return 'Monat';
                    case 'day': return 'Tag';
                    case 'from_location_text': return 'Von Standort';
                    case 'to_location_text': return 'Nach Standort';
                    case 'from_city': return 'Von Ort';
                    case 'to_city': return 'Nach Ort';
                    case 'article_text': return 'Artikel';
                    case 'customer_text': return 'Kunde';
                    case 'employee_text': return 'Mitarbeiter';
                    case 'duration_hours - sum': return 'Dauer (Std.)';
                    case 'duration_days - sum': return 'Dauer (Tage)';
                }
                $r = $v;
                if ($f === 'year')
                    $r = 'Year ' . $v;
                $map = array(
                    '' => '',
                    '1' => 'Januar',
                    '2' => 'Februar',
                    '3' => 'März',
                    '4' => 'April',
                    '5' => 'Mai',
                    '6' => 'Juni',
                    '7' => 'Juli',
                    '8' => 'August',
                    '9' => 'September',
                    '10' => 'Oktober',
                    '11' => 'November',
                    '12' => 'Dezember',
                );
                if ($f === 'month')
                    $r = $map[$v];
                return $r;
            }
PivotMatrix::create(array(
                "id" => "pivotMatrix1",
                'dataSource' => $this->dataStore('test')->count() ? $this->dataStore('sales') : $this->dataStore('default'),
                'scope' => [
                    'typeId' => isset($this->params['typeId']) ? $this->params['typeId'] : '',
                    'statusId' => isset($this->params['statusId']) ? $this->params['statusId'] : '',
                    'startDate' => isset($this->params['startDate']) ? $this->params['startDate'] : '',
                    'endDate' => isset($this->params['endDate']) ? $this->params['endDate'] : '',
                    'selectedDate' => isset($this->params['selectedDate']) ? $this->params['selectedDate'] : '',
                    // 'nodeId' => isset($this->params['nodeId']) ? $this->params['nodeId'] : '',
                ],
                "measures"=>array(
                    "sale_amount - sum", 
                    "quantity_amount - sum",
                ),
                'columnSort' => array(
                    'month' => function($a, $b) {
                        return (int)$a < (int)$b;
                    },
                ),
                'rowSort' => [
                    'article_text' => 'asc'
                ],
                // 'rowCollapseLevels' => array(0,1),
                // 'columnCollapseLevels' => array(0),
                'partialProcessing' => true,
                'width' => '100%',
                'height' => '1000px',
                "showDataHeaders" => true,
                'totalName' => 'Gesamt',
                // 'headerMap' => array(
                //     'sale_amount - sum' => 'Summe',
                //     'price - avg' => 'Preis Ø',
                //     'quantity_amount - sum'=> 'Menge',
                //     'factor - sum'=> 'Faktor',
                //     'detail_id - count'=> 'Positionen',
                //     'order_id - distinctCount'=> 'Aufträge'
                // ),
                'headerMap' => function($v,$f){

                    return translate($v,$f);
                },
                'map' => [
                    'dataCell' => function($value, $cellInfo) {
                        $columns = [];
                        $rows = [];
                        $data = '';

                        foreach( $cellInfo['row'] as $k=>$v ){

                            if( is_array( $v ) && $v['value'] != '{{all}}' && $v['value'] != '{{other}}'){

                                $rows['$k'] = $v;
                                $y = $v['value'];
                                $data .= " data-$k='$y' "; 
                            }
                        }
                        foreach( $cellInfo['column'] as $k=>$v ){

                            if( is_array( $v ) && $v['value'] != '{{all}}' && $v['value'] != '{{other}}'){

                                $columns['$k'] = $v;
                                $y = $v['value'];
                                $data .= " data-$k='$y' "; 
                            }
                        }

                        return "<a target='_blank' class='run-subreport' $data>$cellInfo[formattedValue]</a>";
                    },
                    // 'rowField' => function($rowField, $fieldInfo) {
                    //     return translate( $rowField, '' );
                    // },
                    // 'columnField' => function($colField, $fieldInfo) {
                    //     return translate( $colField, '' );
                    // },
                    // 'dataField' => function($dataField, $fieldInfo) {
                    //     return translate( $dataField, '' );
                    // },
                    // 'waitingField' => function($waitingField, $fieldInfo) {
                    //     return translate( $waitingField, '' );
                    // },
                    // 'columnHeader' => function($colHeader, $headerInfo) {
                    //     $v = $colHeader;
                    //     if ($headerInfo['fieldName'] === 'month')
                    //         $v = translate($v, $headerInfo['fieldName']);
            
                    //     return $v;
                    // },
                ],
                'waitingFields' => array(
                    'duration_days - sum' => 'data',
                    'duration_hours - sum' => 'data',
                    'price - avg' => 'data', 
                    'factor - sum' => 'data', 
                    'detail_id - count' => 'data', 
                    'order_id - distinctCount' => 'data', 
                    'order_id' => 'label',
                    'quarter' => 'label',
                    'to_location_text' => 'label',
                    'from_location_text' => 'label',
                    'to_city' => 'label',
                    'from_city' => 'label',
                    'customer_text' => 'label',
                    'employee_text' => 'label',
                    'article_text' => 'label',
                    'month' => 'label',
                    
                ),
                'paging' => array(
                    'size' => 1000,
                    'maxDisplayedPages' => 1000,
                    // 'sizeSelect' => 10000
                    'sizeSelect' => array(1000)
                )
            ));
        ?>

This is my export file:

include "MyReport.php";
$report = new MyReport;
$report->run()->exportToExcel('MyReportExcel')->toBrowser("Auftragspivot.xlsx");

This is the html:

Here is the excel:

David Winterburn commented on Oct 17, 2019

Hi Ralf,

Do you use the same "headerMap" and "map" in your export.php or excel php view file as in your report view file?

Ralf Föhring commented on Oct 23, 2019

Hi David,

indeed I has some missing translations in the excel view. But still, if I use only the map function the agregate column headers do not get translated. Here is an exemple where I only use the map. Do we need to use headerMap and map together?

<?php
            function translate( $v, $f ){

                switch ($v) {
                    case 'sale_amount - sum': return 'Summe';
                    case 'sale_amount - count': return 'Anzahl';
                    case 'sale_amount - avg': return 'Durchschnitt';
                    case 'price - avg': return 'Preis &Oslash';
                    case 'quantity_amount - sum': return 'Menge';
                    case 'factor - sum': return 'Faktor';
                    case 'detail_id - count': return 'Positionen';
                    case 'order_id': return 'Id';
                    case 'order_id - distinctCount': return 'Aufträge';
                    case 'year': return 'Jahr';
                    case 'quarter': return 'Quartal';
                    case 'month': return 'Monat';
                    case 'day': return 'Tag';
                    case 'from_location_text': return 'Von Standort';
                    case 'to_location_text': return 'Nach Standort';
                    case 'from_city': return 'Von Ort';
                    case 'to_city': return 'Nach Ort';
                    case 'article_text': return 'Artikel';
                    case 'customer_text': return 'Kunde';
                    case 'employee_text': return 'Mitarbeiter';
                    case 'duration_hours - sum': return 'Dauer (Std.)';
                    case 'duration_days - sum': return 'Dauer (Tage)';
                }
                $r = $v;
                if ($f === 'year')
                    $r = 'Year ' . $v;
                $map = array(
                    '' => '',
                    '1' => 'Januar',
                    '2' => 'Februar',
                    '3' => 'März',
                    '4' => 'April',
                    '5' => 'Mai',
                    '6' => 'Juni',
                    '7' => 'Juli',
                    '8' => 'August',
                    '9' => 'September',
                    '10' => 'Oktober',
                    '11' => 'November',
                    '12' => 'Dezember',
                );
                if ($f === 'month')
                    $r = $map[$v];
                return $r;
            }
            PivotMatrix::create(array(
                "id" => "pivotMatrix1",
                'dataSource' => $this->dataStore('test')->count() ? $this->dataStore('sales') : $this->dataStore('default'),
                'scope' => [
                    'typeId' => isset($this->params['typeId']) ? $this->params['typeId'] : '',
                    'statusId' => isset($this->params['statusId']) ? $this->params['statusId'] : '',
                    'startDate' => isset($this->params['startDate']) ? $this->params['startDate'] : '',
                    'endDate' => isset($this->params['endDate']) ? $this->params['endDate'] : '',
                    'selectedDate' => isset($this->params['selectedDate']) ? $this->params['selectedDate'] : '',
                    // 'nodeId' => isset($this->params['nodeId']) ? $this->params['nodeId'] : '',
                ],
                "measures"=>array(
                    "sale_amount - sum", 
                    "quantity_amount - sum",
                ),
                'columnSort' => array(
                    'month' => function($a, $b) {
                        return (int)$a < (int)$b;
                    },
                ),
                'rowSort' => [
                    'article_text' => 'asc'
                ],
                // 'rowCollapseLevels' => array(0,1),
                // 'columnCollapseLevels' => array(0),
                'partialProcessing' => true,
                'width' => '100%',
                'height' => '1000px',
                "showDataHeaders" => true,
                'totalName' => 'Gesamt',
                // 'headerMap' => array(
                //     'sale_amount - sum' => 'Summe',
                //     'price - avg' => 'Preis &Oslash',
                //     'quantity_amount - sum'=> 'Menge',
                //     'factor - sum'=> 'Faktor',
                //     'detail_id - count'=> 'Positionen',
                //     'order_id - distinctCount'=> 'Aufträge'
                // ),
                // 'headerMap' => function($v,$f){

                //     return translate($v,$f);
                // },
                'map' => [
                    'dataCell' => function($value, $cellInfo) {
                        $columns = [];
                        $rows = [];
                        $data = '';

                        foreach( $cellInfo['row'] as $k=>$v ){

                            if( is_array( $v ) && $v['value'] != '{{all}}' && $v['value'] != '{{other}}'){

                                $rows['$k'] = $v;
                                $y = $v['value'];
                                $data .= " data-$k='$y' "; 
                            }
                        }
                        foreach( $cellInfo['column'] as $k=>$v ){

                            if( is_array( $v ) && $v['value'] != '{{all}}' && $v['value'] != '{{other}}'){

                                $columns['$k'] = $v;
                                $y = $v['value'];
                                $data .= " data-$k='$y' "; 
                            }
                        }

                        return "<a target='_blank' class='run-subreport' $data>$cellInfo[formattedValue]</a>";
                    },
                    'rowField' => function($rowField, $fieldInfo) {
                        return translate( $rowField, '' );
                    },
                    'columnField' => function($colField, $fieldInfo) {
                        return translate( $colField, '' );
                    },
                    'dataField' => function($dataField, $fieldInfo) {
                        return translate( $dataField, '' );
                    },
                    'waitingField' => function($waitingField, $fieldInfo) {
                        return translate( $waitingField, '' );
                    },
                    'columnHeader' => function($colHeader, $headerInfo) {
                        if ($headerInfo['fieldName'] === 'month')
                        {
                            $v = translate($colHeader, $headerInfo['fieldName']);
                        }
                        else
                        {
                            $v = translate($colHeader, '');
                        }
            
                        return $v;
                    },
                ],
                'waitingFields' => array(
                    'duration_days - sum' => 'data',
                    'duration_hours - sum' => 'data',
                    'price - avg' => 'data', 
                    'factor - sum' => 'data', 
                    'detail_id - count' => 'data', 
                    'order_id - distinctCount' => 'data', 
                    'order_id' => 'label',
                    'quarter' => 'label',
                    'to_location_text' => 'label',
                    'from_location_text' => 'label',
                    'to_city' => 'label',
                    'from_city' => 'label',
                    'customer_text' => 'label',
                    'employee_text' => 'label',
                    'article_text' => 'label',
                    'month' => 'label',
                ),
                'paging' => array(
                    'size' => 1000,
                    'maxDisplayedPages' => 1000,
                    // 'sizeSelect' => 10000
                    'sizeSelect' => array(1000)
                )
            ));
        ?>

David Winterburn commented on Oct 23, 2019

Hi Ralf,

Unfortunately, the data headers could only be mapped with the newer map property and not with the older headerMap. An example code is:

            'map' => array(
                ...
                'dataHeader' => function($dataField, $fieldInfo) {
                    $v = $dataField;
                    return $v . '-data';
                }, 
                ...

Let us know if this works for you. Thanks!

Ralf Föhring commented on Oct 23, 2019

Hi,

thanks, that works. Would be helpful to update the pivot matrix documention since this dataHeader function is not found on the documentaion page.

David Winterburn commented on Oct 23, 2019

Thanks for your suggestion! We will update the documentation soon for this property.

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
bug
solved
Excel

Designed and built with by KoolPHP Inc.

Code licensed MIT.