KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

Excel export in Pivot 2D #1501

Open ankit opened this topic on on Jun 25 - 3 comments

ankit commented on Jun 25

Hi , I am using pivot 2d and want to export data in excel but with pivot 2D excel formatting is not correct but when I use pivot then excel format is fine, can you tell me that any other requirement for pivot 2d

$report->run()

    ->exportToExcel(array(
      "dataStores" => array(
        'tat1Field' => array(
          'rowSort' => array(
            'quantity_c - count' => 'desc',
          ),
        )
      ),
    ))->saveAs("reports/test.xlsx");

$node = $this->src("mysql")->query($Query)

    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, engname",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c, JSWithRating, noRating,Average, oneStar, oneStarPercent, twoStar, twoStarPercent, threeStar, threeStarPercent, fourStar, fiveStar, fourStarPercent, fiveStartPercent",
        ),
		"fieldDelimiter" => $delimiter,
    )))
	->pipe(new Map(array(
	'{value}' => function ($row) use ($delimiter) {
		foreach ($row as $colName => $colValue) {
				$colFields = substr($colName, 0, strrpos($colName, " || "));
				$customAverageColName =  $colFields . " || Average - sum";
				if($customAverageColName!==' || Average - sum'){
					$row[$customAverageColName] = $row[$colFields . " || JSWithRating - sum"] != 0 ? $row[$colFields . " || quantity_c - sum"] / $row[$colFields . " || JSWithRating - sum"] : 0;
				}
				
				$customPercentFiveColName =  $colFields . " || fiveStartPercent - sum";
				if($customPercentFiveColName!==' || fiveStartPercent - sum'){
					$row[$customPercentFiveColName] = $row[$colFields . $delimiter . "fiveStar - sum"] != 0 ? $row[$colFields . $delimiter . "fiveStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
				}
				
				$customPercentFourColName =  $colFields . " || fourStarPercent - sum";
				if($customPercentFourColName!==' || fourStarPercent - sum'){
					$row[$customPercentFourColName] = $row[$colFields . $delimiter . "fourStar - sum"] != 0 ? $row[$colFields . $delimiter . "fourStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
				}
				
				$customPercentThreeColName =  $colFields . " || threeStarPercent - sum";
				if($customPercentThreeColName!==' || threeStarPercent - sum'){
					$row[$customPercentThreeColName] = $row[$colFields . $delimiter . "threeStar - sum"] != 0 ? $row[$colFields . $delimiter . "threeStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
				}
				
				$customPercentTwoColName =  $colFields . " || twoStarPercent - sum";
				if($customPercentTwoColName!==' || twoStarPercent - sum'){
					$row[$customPercentTwoColName] = $row[$colFields . $delimiter . "twoStar - sum"] != 0 ? $row[$colFields . $delimiter . "twoStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
				}
				
				$customPercentOneColName =  $colFields . " || oneStarPercent - sum";
				if($customPercentOneColName!==' || oneStarPercent - sum'){
					$row[$customPercentOneColName] = $row[$colFields . $delimiter . "oneStar - sum"] != 0 ? $row[$colFields . $delimiter . "oneStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
				}
		}
		//echo '<pre>';print_r($row);
		return $row; 
	},
	'{meta}' => function($meta) {
		$meta['columns']['Average - sum'] = array(
			'type' => 'number',
			'decimals' => 2,
		);
		$meta['columns']['fiveStartPercent - sum'] = array(
			'type' => 'number',
			'decimals' => 2,
			'suffix' => '%',
		);
		$meta['columns']['fourStarPercent - sum'] = array(
			'type' => 'number',
			'decimals' => 2,
			'suffix' => '%',
		);
		$meta['columns']['threeStarPercent - sum'] = array(
			'type' => 'number',
			'decimals' => 2,
			'suffix' => '%',
		);
		$meta['columns']['twoStarPercent - sum'] = array(
			'type' => 'number',
			'decimals' => 2,
			'suffix' => '%',
		);
		$meta['columns']['oneStarPercent - sum'] = array(
			'type' => 'number',
			'decimals' => 2,
			'suffix' => '%',
		);
            return $meta;
    }
)))
    ->pipe($this->dataStore('tat1Field'));

report format : http://15.206.180.82/livservReporting_ankit/reports/super_asi.xlsx

ankit commented 5 days ago

please update

David Winterburn commented 4 days ago

Hi,

Please email your exported excel file with error to support@koolphp.net for us to check it for you. Thanks!

ankit commented 4 days ago

Hi David, I have attached excel file and code files also please help ASAP

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

None