Hi Team,

I have created a Pivot Table datastore in setup of KoolReport and specified 4 aggregate fields : "quote_total,quotation_count,confirm_amount,quotation_confirm_count"

Below is the code for report creation

class CustomersYears extends koolreport\KoolReport
{
use \koolreport\excel\ExcelExportable;

    function settings()
    {
     global $dsn;
     global $dbuser;
     global $dbpwd;

        return array(
            "dataSources"=>array(
                "xxx"=>array(
                 "connectionString"=>$dsn,
                    "username"=>$dbuser,
                    "password"=>$dbpwd,
                    "charset"=>"utf8"
                )
            )
        );
    }
    function setup()
    {
    

        $node =$this->src('xxx')
        ->query('SELECT ....... ')
        ->pipe(new Pivot(array(
            "dimensions" => array(
                "row" => "team",
                "column" => "year,month"
            ),
            "aggregates"=>array(
                "sum"=>"quote_total,quotation_count,confirm_amount,quotation_confirm_count"
            )
            )))

           ->pipe($this->dataStore('pivotDataByMonth'));
    
    }
}

The html Pivot report is generated successfully

 <?php
           $dataStoreM = $this->dataStore('pivotDataByMonth');
            PivotTable::create(array(
              "dataStore"=>$dataStoreM,
              "columnDimension"=>"column",
              "measures"=>array(           
                "quote_total - sum", 
                "quotation_count - sum", 
                "confirm_amount - sum", 
                "quotation_confirm_count - sum", 
              ),
              'rowSort' => array(
                'team' => 'asc',
              ),
              'columnSort' => array(
                'month' => function($a, $b) {
                  return (int)$a < (int)$b;
                },
              ),
              'headerMap' => function($v, $f) {
            
                if ($v === 'confirm_amount - sum'){
                    $v = 'Confirm Amount';
                }
                if ($v === 'quotation_confirm_count - sum'){
                    $v = 'Confirm Count';
                }
               if ($v === 'quotation_count - sum'){
                    $v = 'Quote Count';
                }
               if ($v === 'quote_total - sum'){
                    $v = 'Quote Amount';
                }
                if ($f === 'Year'){
                    $v = 'Year ' . $v;
                }
                
                return $v;
            },
              'showDataHeaders' => true,
              'hideTotalColumn' => true,
              'columnCollapseLevels' => array(0),
              'width' => '100%',

            ));
          ?>
        </div>

Below is the screenshot of generated pivot table in html format

I have created a new page for exporting the excel

use \koolreport\widgets\koolphp\Table;
@session_start();

$CustomersYears = new CustomersYears();



$CustomersYears->run()->exportToExcel(array(
      "dataStores"=> array(
      'pivotDataByMonth'   //specify the datastore
    )
      )
    )->toBrowser('export.xlsx');

?>

Below is the screen of the exported excel, it does not contain header labels. Is there any options required to be set to show the header label in exported excel?

If I use the excel template to export the excel as below

use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google\ColumnChart;

@session_start();

$CustomersYears = new CustomersYears();



$CustomersYears->run()->exportToExcel('report_download_by_team'
    )->toBrowser('export.xlsx');



report_download_by_team.view.php

use \koolreport\pivot\widgets\PivotTable;
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google\ColumnChart;
?>

<div sheet-name="Team">
    <div>
        <?php
        PivotTable::create(array(
            "dataSource" => $this->dataStore('pivotDataByMonth'),  //specified the datastore
        ));
        ?>
    </div>
</div>

It seems html content is stored in the excel directly.

If I use the General Exporting Options to export excel:

Following the sample below:

$CustomersYears = new CustomersYears();


$CustomersYears->run()->exportToExcel(array(
      "dataStores"=> array(
      'pivotDataByMonth' => array(
       'showDataHeaders' => true  //add showDataHeaders
      )
    ),
   
      )
    )->toBrowser('excel.xlsx');

It did not work. The option value is put in the wrong place? Thank you for your kind support.

Regards