Excel Exporting Template

Exporting pivot matrix with template

 
  dollar_sales - sum   dollar_sales - count  
  orderYear   orderQuarter  
  customerName   productLine   productName  
2004
2005
Total
Q3
Q4
Total
Q1
Total
dollar_sales - sum
dollar_sales - count
dollar_sales - sum
dollar_sales - count
dollar_sales - sum
dollar_sales - count
dollar_sales - sum
dollar_sales - count
dollar_sales - sum
dollar_sales - count
dollar_sales - sum
dollar_sales - count
AV Stores, Co.
Classic Cars
1949 Jaguar XK 120
1952 Citroen-15CV
1957 Ford Thunderbird
1966 Shelby Cobra 427 S/C
1969 Chevrolet Camaro Z28
1969 Corvair Monza
1969 Ford Falcon
1970 Chevy Chevelle SS 454
1970 Triumph Spitfire
2001 Ferrari Enzo
2002 Chevy Corvette
Total
Ships
18th century schooner
Pont Yacht
The Mayflower
The Queen Mary
The Schooner Bluenose
The Titanic
The USS Constitution Ship
Total
Trains
1950's Chicago Surface Lines Streetcar
1962 City of Detroit Streetcar
Collectable Wooden Train
Total
Vintage Cars
18th Century Vintage Horse Carriage
1903 Ford Model A
1904 Buick Runabout
1911 Ford Town Car
1912 Ford Model T Delivery Wagon
1928 Mercedes-Benz SSK
1932 Alfa Romeo 8C2300 Spider Sport
1932 Model A Ford J-Coupe
1934 Ford V8 Coupe
1936 Mercedes Benz 500k Roadster
1936 Mercedes-Benz 500K Special Roadster
1937 Lincoln Berline
1938 Cadillac V-16 Presidential Limousine
1939 Chevrolet Deluxe Coupe
Total
Total
Alpha Cognac
Ships
Pont Yacht
The Mayflower
The Queen Mary
The Titanic
The USS Constitution Ship
Total
Total
Amica Models & Co.
Classic Cars
1952 Alpine Renault 1300
1969 Corvair Monza
1969 Ford Falcon
2001 Ferrari Enzo
Total
Ships
18th century schooner
Pont Yacht
The Mayflower
Total
Trains
Collectable Wooden Train
Total
Trucks and Buses
1940 Ford Pickup Truck
Total
Vintage Cars
18th Century Vintage Horse Carriage
1903 Ford Model A
1904 Buick Runabout
1911 Ford Town Car
1913 Ford Model T Speedster
1917 Grand Touring Sedan
1917 Maxwell Touring Car
1928 Mercedes-Benz SSK
1932 Alfa Romeo 8C2300 Spider Sport
1932 Model A Ford J-Coupe
1934 Ford V8 Coupe
1936 Chrysler Airflow
1936 Mercedes Benz 500k Roadster
1936 Mercedes-Benz 500K Special Roadster
1937 Lincoln Berline
1938 Cadillac V-16 Presidential Limousine
1939 Chevrolet Deluxe Coupe
Total
Total
Anna's Decorations, Ltd
Classic Cars
1952 Alpine Renault 1300
1962 LanciaA Delta 16V
1992 Ferrari 360 Spider red
Total
Trucks and Buses
1926 Ford Fire Engine
1940 Ford Pickup Truck
1940s Ford truck
1958 Setra Bus
1964 Mercedes Tour Bus
1996 Peterbilt 379 Stake Bed with Outrigger
Total
Vintage Cars
1913 Ford Model T Speedster
1936 Mercedes-Benz 500K Special Roadster
1937 Lincoln Berline
1938 Cadillac V-16 Presidential Limousine
1939 Cadillac Limousine
1939 Chevrolet Deluxe Coupe
Total
Total
Atelier graphique
Motorcycles
1936 Harley Davidson El Knucklehead
1996 Moto Guzzi 1100i
Total
Vintage Cars
1938 Cadillac V-16 Presidential Limousine
Total
Total
Total
-
-
3295.89
1
3295.89
1
-
-
-
-
3295.89
1
-
-
4753.76
1
4753.76
1
-
-
-
-
4753.76
1
-
-
1481.76
1
1481.76
1
-
-
-
-
1481.76
1
-
-
945.8
1
945.8
1
-
-
-
-
945.8
1
-
-
3505.95
1
3505.95
1
-
-
-
-
3505.95
1
-
-
4931.36
1
4931.36
1
-
-
-
-
4931.36
1
-
-
2906.8
1
2906.8
1
-
-
-
-
2906.8
1
-
-
1419.79
1
1419.79
1
-
-
-
-
1419.79
1
-
-
2906.97
1
2906.97
1
-
-
-
-
2906.97
1
-
-
5668.66
1
5668.66
1
-
-
-
-
5668.66
1
-
-
2921.13
1
2921.13
1
-
-
-
-
2921.13
1
-
-
34737.87
11
34737.87
11
-
-
-
-
34737.87
11
-
-
3171.73
1
3171.73
1
-
-
-
-
3171.73
1
-
-
1681.75
1
1681.75
1
-
-
-
-
1681.75
1
-
-
2797.56
1
2797.56
1
-
-
-
-
2797.56
1
-
-
2621.7
1
2621.7
1
-
-
-
-
2621.7
1
-
-
3067
1
3067
1
-
-
-
-
3067
1
-
-
3173.44
1
3173.44
1
-
-
-
-
3173.44
1
-
-
2672.87
1
2672.87
1
-
-
-
-
2672.87
1
-
-
19186.05
7
19186.05
7
-
-
-
-
19186.05
7
-
-
2772.88
1
2772.88
1
-
-
-
-
2772.88
1
-
-
1752.69
1
1752.69
1
-
-
-
-
1752.69
1
-
-
3348
1
3348
1
-
-
-
-
3348
1
-
-
7873.57
3
7873.57
3
-
-
-
-
7873.57
3
-
-
4021.2
1
4021.2
1
-
-
-
-
4021.2
1
-
-
3671.68
1
3671.68
1
-
-
-
-
3671.68
1
-
-
3320.46
1
3320.46
1
-
-
-
-
3320.46
1
-
-
2047.44
1
2047.44
1
-
-
-
-
2047.44
1
-
-
2359.72
1
2359.72
1
-
-
-
-
2359.72
1
-
-
3321.12
1
3321.12
1
-
-
-
-
3321.12
1
-
-
4601.5
1
4601.5
1
-
-
-
-
4601.5
1
-
-
4093.6
1
4093.6
1
-
-
-
-
4093.6
1
-
-
1380.34
1
1380.34
1
-
-
-
-
1380.34
1
-
-
1413.27
1
1413.27
1
-
-
-
-
1413.27
1
-
-
1382.67
1
1382.67
1
-
-
-
-
1382.67
1
-
-
4111.48
1
4111.48
1
-
-
-
-
4111.48
1
-
-
1118.26
1
1118.26
1
-
-
-
-
1118.26
1
-
-
1344.15
1
1344.15
1
-
-
-
-
1344.15
1
-
-
38186.89
14
38186.89
14
-
-
-
-
38186.89
14
-
-
99984.38
35
99984.38
35
-
-
-
-
99984.38
35
-
-
-
-
-
-
1800.64
1
1800.64
1
1800.64
1
-
-
-
-
-
-
2217.28
1
2217.28
1
2217.28
1
-
-
-
-
-
-
2895.84
1
2895.84
1
2895.84
1
-
-
-
-
-
-
4135.2
1
4135.2
1
4135.2
1
-
-
-
-
-
-
1383.36
1
1383.36
1
1383.36
1
-
-
-
-
-
-
12432.32
5
12432.32
5
12432.32
5
-
-
-
-
-
-
12432.32
5
12432.32
5
12432.32
5
6994.82
1
-
-
6994.82
1
-
-
-
-
6994.82
1
3118.32
1
-
-
3118.32
1
-
-
-
-
3118.32
1
7708.04
1
-
-
7708.04
1
-
-
-
-
7708.04
1
8602.92
1
-
-
8602.92
1
-
-
-
-
8602.92
1
26424.1
4
-
-
26424.1
4
-
-
-
-
26424.1
4
2348.43
1
-
-
2348.43
1
-
-
-
-
2348.43
1
1642.24
1
-
-
1642.24
1
-
-
-
-
1642.24
1
2260.55
1
-
-
2260.55
1
-
-
-
-
2260.55
1
6251.22
3
-
-
6251.22
3
-
-
-
-
6251.22
3
2018.72
1
-
-
2018.72
1
-
-
-
-
2018.72
1
2018.72
1
-
-
2018.72
1
-
-
-
-
2018.72
1
2352
1
-
-
2352
1
-
-
-
-
2352
1
2352
1
-
-
2352
1
-
-
-
-
2352
1
2976.27
1
-
-
2976.27
1
-
-
-
-
2976.27
1
2655.36
1
-
-
2655.36
1
-
-
-
-
2655.36
1
3569.64
1
-
-
3569.64
1
-
-
-
-
3569.64
1
1332
1
-
-
1332
1
-
-
-
-
1332
1
3774.76
1
-
-
3774.76
1
-
-
-
-
3774.76
1
4199
1
-
-
4199
1
-
-
-
-
4199
1
3373.14
1
-
-
3373.14
1
-
-
-
-
3373.14
1
3489.85
1
-
-
3489.85
1
-
-
-
-
3489.85
1
2705.85
1
-
-
2705.85
1
-
-
-
-
2705.85
1
4045.21
1
-
-
4045.21
1
-
-
-
-
4045.21
1
2336.62
1
-
-
2336.62
1
-
-
-
-
2336.62
1
1677.06
1
-
-
1677.06
1
-
-
-
-
1677.06
1
1164.57
1
-
-
1164.57
1
-
-
-
-
1164.57
1
1280.87
1
-
-
1280.87
1
-
-
-
-
1280.87
1
4366.5
1
-
-
4366.5
1
-
-
-
-
4366.5
1
1633.05
1
-
-
1633.05
1
-
-
-
-
1633.05
1
597.4
1
-
-
597.4
1
-
-
-
-
597.4
1
45177.15
17
-
-
45177.15
17
-
-
-
-
45177.15
17
82223.19
26
-
-
82223.19
26
-
-
-
-
82223.19
26
-
-
-
-
-
-
4680.24
1
4680.24
1
4680.24
1
-
-
-
-
-
-
8981.1
2
8981.1
2
8981.1
2
-
-
-
-
-
-
4526.55
1
4526.55
1
4526.55
1
-
-
-
-
-
-
18187.89
4
18187.89
4
18187.89
4
-
-
-
-
-
-
3863.64
2
3863.64
2
3863.64
2
-
-
-
-
-
-
6025.04
2
6025.04
2
6025.04
2
-
-
-
-
-
-
3054.86
1
3054.86
1
3054.86
1
-
-
-
-
-
-
10612.33
2
10612.33
2
10612.33
2
-
-
-
-
-
-
2241.14
1
2241.14
1
2241.14
1
-
-
-
-
-
-
1599.75
1
1599.75
1
1599.75
1
-
-
-
-
-
-
27396.76
9
27396.76
9
27396.76
9
-
-
-
-
-
-
3176.96
1
3176.96
1
3176.96
1
-
-
-
-
-
-
1992.48
1
1992.48
1
1992.48
1
-
-
-
-
-
-
3595.89
1
3595.89
1
3595.89
1
-
-
-
-
-
-
870.96
1
870.96
1
870.96
1
-
-
-
-
-
-
835.19
1
835.19
1
835.19
1
-
-
-
-
-
-
876.15
1
876.15
1
876.15
1
-
-
-
-
-
-
11347.63
6
11347.63
6
11347.63
6
-
-
-
-
-
-
56932.28
19
56932.28
19
56932.28
19
1938.24
1
-
-
1938.24
1
-
-
-
-
1938.24
1
4128.54
1
-
-
4128.54
1
-
-
-
-
4128.54
1
6066.78
2
-
-
6066.78
2
-
-
-
-
6066.78
2
-
-
1676.13
1
1676.13
1
-
-
-
-
1676.13
1
-
-
1676.13
1
1676.13
1
-
-
-
-
1676.13
1
6066.78
2
1676.13
1
7742.91
3
-
-
-
-
7742.91
3
88289.97
28
101660.51
36
189950.48
64
69364.6
24
69364.6
24
259315.08
88
Page size:

This example demonstrates how to export pivot matrix to an excel file.

<div>
    <?php
    \koolreport\Excel\PivotMatrix::create(array(
        ...
    ));
    ?>
</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()
    {
        $node = $this->src('dollarsales')
        //->query('select *, dollar_sales as dollar_sales2 from customer_product_dollarsales2')
        ->pipe(new Map([
            '{value}' => function($row, $meta) {
                $row['orderQuarter'] = 'Q' . $row['orderQuarter'];
                return $row;
            },
            '{meta}' => function($meta) {
                $meta['columns']['orderDate']['type'] = 'datetime';
                $meta['columns']['orderQuarter']['type'] = 'string';
                return $meta;
            }
        ]))
        ;

        $node
        ->pipe(new Limit(array(
            50, 0
        )))
        ->pipe($this->dataStore('orders'));

        $node->pipe(new Cube(array(
            "rows" => "customerName",
            "column" => "orderQuarter",
            "sum" => "dollar_sales",
        )))
        ->pipe(new Limit(array(
            5, 0
        )))
        ->pipe($this->dataStore('salesQuarterCustomer'));

        $node->pipe(new Cube(array(
            "rows" => "productName",
            "column" => "orderQuarter",
            "sum" => "dollar_sales",
        )))
        ->pipe(new Limit(array(
            5, 0
        )))
        ->pipe($this->dataStore('salesQuarterProduct'));

        $node
        ->pipe(new Filter(array(
            array('customerName', '<', 'Au'),
            array('orderYear', '>', 2003),
        )))
        ->pipe(new Pivot(array(
            "dimensions" => array(
                "column" => "orderYear, orderQuarter",
                "row" => "customerName, productLine, productName",
            ),
            "aggregates" => array(
                "sum" => "dollar_sales",
                "count" => "dollar_sales",
            ),
        )))
        ->pipe($this->dataStore('salesPivot'));
    }
}
<?php
use \koolreport\pivot\widgets\PivotMatrix;
?>
<form method="post">
	<div class="report-content">
		<div style='text-align: center;margin-bottom:30px;'>
			<h1>Excel Exporting Template</h1>
			<p class="lead">Exporting pivot matrix with template</p>
			<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>
			<input type="hidden" name="koolPivotUpdate" value="1" />
		</div>
		<div class='box-container'>
			<div>
				<?php
				PivotMatrix::create(array(
					"name" => "PivotMatrix1",
					"dataSource" => $this->dataStore('salesPivot'),
					"showDataHeaders" => true,
				));
				?>
			</div>
		</div>
	</div>
</form>
<?php
    use \koolreport\excel\Table;
    use \koolreport\excel\PivotTable;
    use \koolreport\excel\PivotMatrix;
    use \koolreport\excel\BarChart;
    use \koolreport\excel\LineChart;

    $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; ?>">
    <?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' => [
                'rgb' => '808080',
            ]
        ],
    ];
    ?>
    <div>Sales PivotTable</div>
    <div>
        <?php
        // PivotTable::create(array(
        PivotMatrix::create(array(
            "dataSource" => 'salesPivot',
            'hideSubTotalRows' => true,
            'hideGrandTotalRow' => true,
            'hideSubTotalColumns' => 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 === 'dollar_sales - sum')
                        $v = 'Sales (in USD)';
                    else if ($v === 'dollar_sales - count')
                        $v = 'Number of Sales';
                    return $v;
                },
                'dataHeader' => function($dataField, $fieldInfo) {
                    // Util::prettyPrint($fieldInfo);
                    $v = $dataField;
                    if ($v === 'dollar_sales - sum')
                        $v = 'Sales (in USD)';
                    else if ($v === 'dollar_sales - count')
                        $v = 'Number of Sales';
                    return $v;
                },
                'waitingField' => function($waitingField, $fieldInfo) {
                    return $waitingField;
                },
                'rowHeader' => function($rowHeader, $headerInfo) {
                    // Util::prettyPrint($headerInfo);
                    $v = $rowHeader;
                    if (isset($headerInfo['childOrder']))
                        $v = $headerInfo['childOrder'] . ". " . $v;
                    return $v;
                },
                'columnHeader' => function($colHeader, $headerInfo) {
                    $v = $colHeader;
                    if ($headerInfo['fieldName'] === 'orderYear')
                        $v = 'Year-' . $v;
                    else if ($headerInfo['fieldName'] === 'orderQuarter')
                        $v = 'Quarter-' . $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>
<?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