Excel Exporting Template

Using template to create excel report

customerNameproductNameproductLineorderDayorderMonthorderYearorderQuarterdollar_sales
Vitachrome Inc. 1937 Lincoln Berline Vintage Cars 10 1 2003 Q1 3,726
Vitachrome Inc. 1936 Mercedes-Benz 500K Special Roadster Vintage Cars 10 1 2003 Q1 1,768
Baane Mini Imports 1952 Alpine Renault 1300 Classic Cars 29 1 2003 Q1 5,572
Baane Mini Imports 1962 LanciaA Delta 16V Classic Cars 29 1 2003 Q1 5,026
Baane Mini Imports 1958 Setra Bus Trucks and Buses 29 1 2003 Q1 3,284
Baane Mini Imports 1940 Ford Pickup Truck Trucks and Buses 29 1 2003 Q1 3,308
Baane Mini Imports 1926 Ford Fire Engine Trucks and Buses 29 1 2003 Q1 1,283
Baane Mini Imports 1913 Ford Model T Speedster Vintage Cars 29 1 2003 Q1 2,489
Baane Mini Imports 1934 Ford V8 Coupe Vintage Cars 29 1 2003 Q1 2,164
Baane Mini Imports 18th Century Vintage Horse Carriage Vintage Cars 29 1 2003 Q1 2,173
Baane Mini Imports 1917 Maxwell Touring Car Vintage Cars 29 1 2003 Q1 3,970
Baane Mini Imports 1940s Ford truck Trucks and Buses 29 1 2003 Q1 3,531
Baane Mini Imports 1939 Cadillac Limousine Vintage Cars 29 1 2003 Q1 1,671
Baane Mini Imports 1962 Volkswagen Microbus Trucks and Buses 29 1 2003 Q1 3,864
Baane Mini Imports 1936 Chrysler Airflow Vintage Cars 29 1 2003 Q1 2,216
Baane Mini Imports 1980’s GM Manhattan Express Trucks and Buses 29 1 2003 Q1 2,866
Baane Mini Imports 1996 Peterbilt 379 Stake Bed with Outrigger Trucks and Buses 29 1 2003 Q1 2,851
Baane Mini Imports 1982 Camaro Z28 Classic Cars 29 1 2003 Q1 3,951
Euro+ Shopping Channel 1969 Corvair Monza Classic Cars 31 1 2003 Q1 4,469
Euro+ Shopping Channel 1957 Chevy Pickup Trucks and Buses 31 1 2003 Q1 4,567
Euro+ Shopping Channel 1998 Chrysler Plymouth Prowler Classic Cars 31 1 2003 Q1 3,262
Euro+ Shopping Channel 1964 Mercedes Tour Bus Trucks and Buses 31 1 2003 Q1 3,559
Euro+ Shopping Channel 1992 Ferrari 360 Spider red Classic Cars 31 1 2003 Q1 3,817
Euro+ Shopping Channel 1970 Triumph Spitfire Classic Cars 31 1 2003 Q1 4,530
Euro+ Shopping Channel 1970 Dodge Coronet Classic Cars 31 1 2003 Q1 1,821
Euro+ Shopping Channel 1958 Chevy Corvette Limited Edition Classic Cars 31 1 2003 Q1 1,338
Euro+ Shopping Channel 1992 Porsche Cayenne Turbo Silver Classic Cars 31 1 2003 Q1 2,768
Euro+ Shopping Channel 1954 Greyhound Scenicruiser Trucks and Buses 31 1 2003 Q1 1,818
Euro+ Shopping Channel 1950's Chicago Surface Lines Streetcar Trains 31 1 2003 Q1 2,771
Euro+ Shopping Channel Diamond T620 Semi-Skirted Tanker Trucks and Buses 31 1 2003 Q1 3,781
Euro+ Shopping Channel 1962 City of Detroit Streetcar Trains 31 1 2003 Q1 1,706
Danish Wholesale Imports 1972 Alfa Romeo GTA Classic Cars 11 2 2003 Q1 6,392
Danish Wholesale Imports 2001 Ferrari Enzo Classic Cars 11 2 2003 Q1 8,435
Danish Wholesale Imports 1969 Ford Falcon Classic Cars 11 2 2003 Q1 4,115
Danish Wholesale Imports 1903 Ford Model A Vintage Cars 11 2 2003 Q1 3,005
Danish Wholesale Imports Collectable Wooden Train Trains 11 2 2003 Q1 3,334
Danish Wholesale Imports 1904 Buick Runabout Vintage Cars 11 2 2003 Q1 3,095
Danish Wholesale Imports 18th century schooner Ships 11 2 2003 Q1 5,073
Danish Wholesale Imports 1912 Ford Model T Delivery Wagon Vintage Cars 11 2 2003 Q1 3,232
Danish Wholesale Imports 1940 Ford Delivery Sedan Vintage Cars 11 2 2003 Q1 3,774
Danish Wholesale Imports The Schooner Bluenose Ships 11 2 2003 Q1 2,214
Danish Wholesale Imports The Mayflower Ships 11 2 2003 Q1 2,512
Danish Wholesale Imports The USS Constitution Ship Ships 11 2 2003 Q1 1,882
Danish Wholesale Imports The Titanic Ships 11 2 2003 Q1 3,594
Danish Wholesale Imports The Queen Mary Ships 11 2 2003 Q1 2,185
Danish Wholesale Imports Pont Yacht Ships 11 2 2003 Q1 1,119
Rovelli Gifts 1980s Black Hawk Helicopter Planes 17 2 2003 Q1 4,825
Rovelli Gifts P-51-D Mustang Planes 17 2 2003 Q1 2,757
Rovelli Gifts 1999 Yamaha Speed Boat Ships 17 2 2003 Q1 3,315
Rovelli Gifts 1941 Chevrolet Special Deluxe Cabriolet Vintage Cars 17 2 2003 Q1 3,863



customerName{{all}}Q1Q2Q3Q4
Vitachrome Inc. 72497.61 5494.78 22602.34 0 44400.49
Baane Mini Imports 104224.72 50218.92 0 0 54005.8
Euro+ Shopping Channel 820689.16 285274.47 249673.6 64949.33 220791.76
Danish Wholesale Imports 129085.1 53959.2 54561.04 0 20564.86
Rovelli Gifts 127529.63 52151.78 0 0 75377.85



productName{{all}}Q1Q2Q3Q4
1937 Lincoln Berline 102563.48 21151.11 21166.55 19598.63 40647.19
1936 Mercedes-Benz 500K Special Roadster 46078.28 12146.66 8092.53 7590.46 18248.63
1952 Alpine Renault 1300 190017.95 43938.09 34943.71 33460.91 77675.24
1962 LanciaA Delta 16V 123122.96 32472.34 22874.64 19918.52 47857.46
1958 Setra Bus 119085.22 27629.36 23291.46 20586.1 47578.3



Excel package of KoolReport is able to export data from database or any other kinds of datasoure to excel file. Only that, it is able to create charts and graphs in excel files.

This example demonstrates how to use PHP template file to dynamically customize exported excel files. The template using familiar html and widgets that allows you to create excel file easily.

<?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;

    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",
            ),
            "aggregates" => array(
                "sum" => "dollar_sales",
            ),
        )))
        ->pipe($this->dataStore('salesPivot'));
    }
}
<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\widgets\koolphp\Table;
?>
<div class="report-content">
	<div style='text-align: center;margin-bottom:30px;'>
        <h1>Excel Exporting Template</h1>
        <p class="lead">Using template to create excel report</p>
		<form method="post">
			<button type="submit" class="btn btn-primary" formaction="export.php?type=excel">Download Excel</button>
		</form>
	</div>
	<div class='box-container'>
		<div>
			<?php
			Table::create(array(
				"dataSource" => $this->dataStore('orders'),
				"columns"=>array(
					"customerName",
					"productName",
					"productLine",
					"orderDay",
					"orderMonth",
					"orderYear",
					"orderQuarter",
					"dollar_sales"=>array(
						"type"=>"number",
					)
				),
				"paging"=>array(
					"pageSize"=>5
				)
			));
			?>
		</div>
		<br><br><br>
		<div>
			<?php
			Table::create(array(
				"dataSource" => $this->dataStore('salesQuarterCustomer'),
			));
			?>
		</div>
		<br><br><br>
		<div>
			<?php
			Table::create(array(
				"dataSource" => $this->dataStore('salesQuarterProduct'),
			));
			?>
		</div>
		<br><br><br>
		<div>
			<?php
			PivotTable::create(array(
				"dataSource" => $this->dataStore('salesPivot'),
			));
			?>
		</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; ?>">
    <?php
    $styleArray = [
        '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',
            ],
        ],
    ];
    ?>
    <div cell="A1" range="A1:H1" excelstyle='<?php echo json_encode($styleArray); ?>' >
        Sales Report
    </div>

    <div>Orders</div>

    <div>
        <?php
        \koolreport\excel\Table::create(array(
            "dataSource" => $this->dataStore('orders'),
        ));
        ?>
    </div>

    <?php
        $datastores = ['salesQuarterCustomer', 'salesQuarterProduct'];
        foreach ($datastores as $datastore) { 
            if ($datastore === 'salesQuarterCustomer') {
                $title = 'Sales by Customer';
                $name = 'customerSales';
                $column = 'customerName';
            } else {
                $title = 'Sales by Product';
                $name = 'productSales';
                $column = 'productName';
            }
            ?>
            <div>
                <?php echo $title; ?>
            </div>
            <div>
                <?php
                \koolreport\excel\Table::create(array(
                    "name" => $name,
                    // "dataSource" => $this->dataStore($datastore),
                    "dataSource" => $datastore,
                    "columns" =>[
                        $column, "Q1", "Q2", "Q3", "Q4"
                    ],
                ));
                ?>
            </div>
        <?php }
    ?>

    <div>
        <?php
        \koolreport\excel\PivotTable::create(array(
            "dataSource" => 'salesPivot',
        ));
        ?>
    </div>
    
    <div range="A25:H45">
        <?php
        \koolreport\excel\BarChart::create(array(
            // "dataSource" => 'salesQuarterCustomer',
            // "columns" =>[
            //     'customerName', "Q1", "Q2", "Q3", "Q4"
            // ],
            'excelDataSource' => 'customerSales', //table_0, table_1
            'title' => 'Customer Sales by Quarter',
            'xAxisTitle' => 'Customers',
            'yAxisTitle' => 'Sales($)',
            // 'stacked' => true,
            'direction' => 'horizontal',
        ));
        ?>
    </div>
    
</div>

<div>
    <div>
        <?php
        \koolreport\excel\PivotTable::create(array(
            "dataSource" => 'salesPivot',
        ));
        ?>
    </div>
    <div>
        <?php
        \koolreport\excel\PivotTable::create(array(
            "dataSource" => 'salesPivot',
        ));
        ?>
    </div>
</div>

<div sheet-name="Sale By Quarter">
    <div>
        <?php
        \koolreport\excel\LineChart::create(array(
            "dataSource" => $this->dataStore('salesQuarterProduct'),
            "columns" =>[
                'productName', "Q1", "Q2", "Q3", "Q4"
            ]
        ));
        ?>
    </div>
</div>
<?php

include "MyReport.php";
$report = new MyReport;
$report->run();
$report->exportToExcel('MyReportExcel')->toBrowser("MyReport.xlsx");
// $report->exportToExcel('MyReportExcel', ['rtl' => true])->toBrowser("MyReport.xlsx");

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