<?php
// include the export_spreadsheet_class
require_once ("export_spreadsheets_class.php");
// create an instance of the export_spreadsheet_class object
$myExport = new exportSpreadsheets();
// set instance properties
$myExport->mysqlServer = "localhost";
$myExport->mysqlUsername = "exportUser";
$myExport->mysqlPassword = "exportPassword";
$myExport->mysqlDatabaseName = "export_spreadsheets";
$workbook_array = array ();
$workbook_array['Best Customers'] = array (
'connection' => array ('mysqlQuery'=>'select Concat(RTrim(customers.firstName), " ", RTrim(customers.lastName)) as customer, sum(orders.totalPrice) as revenue from customers, orders where orders.customerID=customers.id group by lastName, firstName order by revenue desc'),
'sheet' => array ('pageTitle'=>'Best Customers', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ('fieldName'=>'customer', 'columnTitle'=>'Customer', 'alignment'=>'left','sum'=>'no'),
'column2' => array ('fieldName'=>'revenue', 'columnTitle'=>'Total Revenue', 'numberFormat'=>'0.00', 'sum'=>'yes')
);
$workbook_array['Most Popular Products'] = array (
'connection' => array ('mysqlQuery'=>'select products.name, sum(orders.orderQuantity) as totalQuantity, sum(orders.totalPrice) as revenue from products, orders where orders.productID=products.id group by productID order by totalQuantity desc'),
'sheet' => array ('pageTitle'=>'Most Popular Products', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ('fieldName'=>'name', 'columnTitle'=>'Product Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ('fieldName'=>'totalQuantity', 'columnTitle'=>'Units Sold', 'sum'=>'no'),
'column3' => array ('fieldName'=>'revenue', 'columnTitle'=>'Total Revenue', 'numberFormat'=>'0.00','sum'=>'yes')
);
$workbook_array['Price Changes'] = array (
'connection' => array ('mysqlQuery'=>'select products.name, sum(orders.orderQuantity) as totalQuantity, orders.unitPrice from products, orders where orders.productID=products.id group by productID, unitPrice order by name asc, unitPrice desc'),
'sheet' => array ('pageTitle'=>'Sales Volume Compared to Price', 'orientation'=>'landscape', 'numberPagesWide'=>'', 'numberPagesTall'=>'', 'repeatRows'=>'', 'repeatColumns'=>'', 'leftMargin'=>0.25, 'rightMargin'=>0.25, 'topMargin'=>0.25, 'bottomMargin'=>0.25, 'centerHorizontally'=>'yes','centerVertically'=>'yes' ),
'column1' => array ('fieldName'=>'name', 'columnTitle'=>'Product Name', 'alignment'=>'left','sum'=>'no'),
'column2' => array ('fieldName'=>'totalQuantity', 'columnTitle'=>'Units Sold', 'sum'=>'no'),
'column3' => array ('fieldName'=>'unitPrice', 'columnTitle'=>'Unit Price', 'numberFormat'=>'0.00','sum'=>'no'),
'column4' => array ('fieldName'=>'rowFormula', 'formula'=>'=IF(ISBLANK(column2)+ISBLANK(column3),"",column2*column3)', 'columnTitle'=>'Revenue', 'numberFormat'=>'0.00','vAlign'=>'top', 'sum'=>'yes')
);
$workbook_name = "complex.xls";
$myExport->export_spreadsheets($workbook_name,$workbook_array);
?>
|
Stumble It!
Bookmark in del.icio.us
Download
