Excel Table Row Group

Exporting table with multi-level row groups

This example demonstrates how to export multi-level row groups with table to an excel file.

<div>
    <?php
    \koolreport\Excel\Table::create(array(
        ...
        "rowGroup" => [
            "customerName" => [
                'direction' => 'desc',
                'calculate' => [
                    'totalSales' => ['sum', 'dollar_sales']
                ],
                "top" => "Customers: {customerName}",
                "columnTops" => [
                    "dollar_sales" => "Total sales: {totalSales}"
                ],
                "bottom" => "Customers: {customerName}",
                "columnBottoms" => [
                    "dollar_sales" => "Total sales: {totalSales}"
                ],
            ],
            "productLine" => [
                "top" => "Product line: {productLine}",
            ]
        ]
    ));
    ?>
</div>
<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();
<?php
require_once "../../../load.koolreport.php";

use \koolreport\processes\Map;
use \koolreport\processes\Limit;
use \koolreport\processes\Filter;
use \koolreport\cube\processes\Cube;
use \koolreport\pivot\processes\Pivot;

class MyReport extends koolreport\KoolReport
{
    use \koolreport\export\Exportable;
    use \koolreport\excel\ExcelExportable;
    use \koolreport\excel\BigSpreadsheetExportable;

    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    'filePath' => '../../../databases/customer_product_dollarsales2.csv',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"      
                ), 
            )
        );
    }    function setup()
    {
        $this->src('dollarsales')
        ->pipe(new Limit(array(200, 0)))
        ->pipe($this->dataStore('sales'));
    }
}
<?php

use \koolreport\pivot\widgets\PivotTable;
use \koolreport\widgets\koolphp\Table;
use \koolreport\datagrid\DataTables;
?>
<style>
	tr[role="row"] td:first-child {
		padding-left: 4em !important;
	}
</style>
<div class="report-content">
	<div style='text-align: center;margin-bottom:30px;'>
		<h1>Excel Table Row Group</h1>
		<p class="lead">Exporting table with multi-level row groups</p>
		<form method="post">
			<button type="submit" class="btn btn-primary" formaction="export.php?type=excel">Download Excel</button>
			<button type="submit" class="btn btn-primary" formaction="export.php?type=bigspreadsheet">Download Big Spreadsheet</button>
		</form>
	</div>
	<div class='box-container'>
		<div>
			<?php
			DataTables::create(array(
				"dataSource" => $this->dataStore('sales'),
				"columns" => array(
					"customerName" => ["visible" => false],
					"productLine" => ["visible" => false],
					"productName" => ["label" => "Product Name"],
					"dollar_sales" => array(
						"label" => "Sales",
						"type" => "number",
					)
				),
				"clientRowGroup" => [
					"customerName" => [
						'direction' => 'asc',
						'calculate' => [
							'totalSales' => [
								'sum',  //'sum', 'count', 'avg', 'min', 'max' 
								'dollar_sales'
							],
						],
						"top" => "<td colspan='999'>{expandCollapseIcon} Top: Customer: {customerName} | Total: {totalSales}</td>",
						"bottom" => "<td colspan='999'>{expandCollapseIcon} Bottom: Customer: {customerName} | Customer sales: {totalSales}</td>",
					],
					"productLine" => [
						'direction' => 'asc',
						'calculate' => [
							'totalSales' => [
								'sum', 'dollar_sales',
								"format" => "function(value) {return value.toFixed(0);}",
							],
						],
						"top" => "<td colspan='999'>{expandCollapseIcon} Top: Line: {productLine} | Total: {totalSales}</td>",
					],
				],
				"options" => [
					"paging" => true,
				]
			));
			?>
		</div>
	</div>
</div>
<?php
    $sheet1 = "Sales by Customer";
?>
<meta charset="UTF-8">
<meta name="description" content="Free Web tutorials">
<meta name="keywords" content="Excel,HTML,CSS,XML,JavaScript">
<meta name="creator" content="John Doe">
<meta name="subject" content="subject1">
<meta name="title" content="title1">
<meta name="category" content="category1">

<div sheet-name="<?php echo $sheet1; ?>">
    <div>Orders Table</div>

    <div>
        <?php
        \koolreport\excel\Table::create(array(
            "dataSource" => $this->dataStore('sales'),
            "columns" => ["productName", "dollar_sales"],
            "rowGroup" => [
                "customerName" => [
                    'direction' => 'asc',
                    'calculate' => [
                        'totalSales' => [
                            'sum', 
                            'dollar_sales',
                            'format' => [
                                "type" => "number",
                                "decimals" => 0,
                                "decimalPoint" => ".",
                                "thousandSeparator" => ",",
                            ]
                        ]
                    ],
                    "top" => "Customers: {customerName}",
                    "columnTops" => [
                        // "productName" => "Customers: {customerName}",
                        // "dollar_sales" => "Total sales: {totalSales}"
                    ],
                    // "bottom" => "Customers: {customerName}",
                    // "columnBottoms" => [
                        // "dollar_sales" => "Total sales: {totalSales}"
                    // ],
                ],
                "productLine" => [
                    'direction' => 'asc',
                    'calculate' => [
                        'totalLineSales' => [
                            'sum', 
                            'dollar_sales',
                            'format' => [
                                "type" => "number",
                                "decimals" => 0,
                                "decimalPoint" => ".",
                                "thousandSeparator" => ",",
                            ]
                        ]
                    ],
                    "top" => "Product line: {productLine}",
                    "columnTops" => [
                        // "productName" => "Product line: {productLine}",
                        "dollar_sales" => "Total sales: {totalLineSales}"
                    ],
                ]
            ]
        ));
        ?>
    </div>
    
</div>
<?php
include "MyReport.php";
$report = new MyReport;
$report->run();

$exportType = $_GET["type"];
if ($exportType === "excel") {
    $report->exportToExcel('MyReportExcel')->toBrowser("MyReportExcel.xlsx");
} else {
    $report->exportToXLSX('MyReportExcel')->toBrowser("MyReportBigSpreadsheet.xlsx");
}
customerNameproductNameproductLineorderDateorderDayorderMonthorderYearorderQuarterdollar_sales
Vitachrome Inc. 1937 Lincoln Berline Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 3726.45
Vitachrome Inc. 1936 Mercedes-Benz 500K Special Roadster Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 1768.33
Baane Mini Imports 1952 Alpine Renault 1300 Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5571.8
Baane Mini Imports 1962 LanciaA Delta 16V Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5026.14
Baane Mini Imports 1958 Setra Bus Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3284.28
Baane Mini Imports 1940 Ford Pickup Truck Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3307.5
Baane Mini Imports 1926 Ford Fire Engine Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 1283.48
Baane Mini Imports 1913 Ford Model T Speedster Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2489.13
Baane Mini Imports 1934 Ford V8 Coupe Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2164.4
Baane Mini Imports 18th Century Vintage Horse Carriage Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2173

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro