Excel Exporting Template
Exporting table with template
customerName | productName | productLine | orderDay | orderMonth | orderYear | orderQuarter | dollar_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 |
- Description
- index.php
- MyReport.php
- MyReport.view.php
- MyReportExcel.view.php
- export.php
- customer_product_dollarsales2.csv
This example demonstrates how to export table to an excel file.
<div>
<?php
\koolreport\Excel\Table::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;
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(new Map([
"{meta}" => function($meta) {
$cMeta = & $meta["columns"]["dollar_sales"];
$cMeta["footer"] = "sum";
$cMeta["type"] = "number";
// print_r($meta); exit;
return $meta;
}
]))
->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">Exporting table with template</p>
<form>
<button type="submit" class="btn btn-primary" formaction="export.php">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>
</div>
</div>
<?php
use \koolreport\excel\Table;
use \koolreport\excel\PivotTable;
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
$styleArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 30,
'bold' => true,
'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>Orders Table</div>
<div>
<?php
Table::create(array(
"dataSource" => $this->dataStore('orders'),
// "dataSource" => 'orders',
// "filtering" => ['age','between',45,65],
"filtering" => function($row, $index) {
if (stripos($row['customerName'], "Baane Mini Imports") !== false)
return false;
return true;
},
"sorting" => ['dollar_sales' => 'desc'],
"paging" => [5, 2],
"showHeader" => false, //default: true
"showBottomHeader" => true, //default: false
"showFooter" => true, //default: false
"map" => [
"header" => function($colName) { return $colName; },
"bottomHeader" => function($colName) { return $colName; },
"cell" => function($colName, $value, $row) { return $value; },
"footer" => function($colName, $footerValue) { return $footerValue; },
],
"excelStyle" => [
"header" => function($colName) {
return [
'font' => [
'italic' => true,
'bold' => false,
'color' => [
'rgb' => '808080',
]
],
];
},
"bottomHeader" => function($colName) { return []; },
"cell" => function($colName, $value, $row) {
return [
'font' => [
'italic' => true,
'color' => [
'rgb' => '808080',
]
],
];
},
"footer" => function($colName, $footerValue) { return []; },
]
));
?>
</div>
</div>
<?php
include "MyReport.php";
$report = new MyReport;
$report->run();
$report->exportToExcel('MyReportExcel')->toBrowser("MyReport.xlsx");
customerName | productName | productLine | orderDate | orderDay | orderMonth | orderYear | orderQuarter | dollar_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.3300000000002 |
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