I have so far successfully exported data from Mysql into Excel via PHPExcel, made my tables, formatted, and everything is hunky dory.
However, so far any attempt at making graphs from said tables failed miserably. Not only, after days on end of Google Search, I've not found a simple example/tutorial on how to populate the graph values from MySQL.
It all boils down to this:
Taken from https://github.com/affinitybridge/phpexcel/blob/master/Tests/33chartcreate-pie.php
/** PHPExcel */
include 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objWorksheet = $objPHPExcel->getActiveSheet();
$objWorksheet->fromArray(
array(
array('', 2010, 2011, 2012),
array('Q1', 12, 15, 21),
array('Q2', 56, 73, 86),
array('Q3', 52, 61, 69),
array('Q4', 30, 32, 0),
)
);
How the hell do I populate $objWorksheet->fromArray from either a) Mysql or b) excel.
If it's SQL, I would need to run this query for all the tables in my database.
$sql = "SELECT Functional_Area, Passed__Status , Blocked__Status , Failed__Status FROM $tbl_name WHERE 1;
Context: I have several Functional Areas, each of them has different values in Passed/Failed/Blocked. I need 1 Graph for each Functional Areas. On average I have 14 functional areas per each Table.
I would then construct a Pie chart (or Bar chart, I'm not fussy) for each Functional Area.
Option 2) Excel
Since I already have said data in an excel, I would need to populate the data from:
Again, 1 graph for each ROW (ROW 5 (FA = EPG) will be 1 graph, ROW 6 (FA = VOD) will be another graph, etc...).
This will have to be repeated for EACH worksheet...
My attempt so far is below, this produces for each worksheet (and this is good) a graph, which however is empty (not good).
foreach (glob("*.xls") as $f)
{
$inputFileName = $f;
echo "Found $inputFileName <br/>";
}
// Read your Excel workbook
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
$i = 0;
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
{
$arrayData[$worksheet->getTitle()] = $worksheet->toArray();
echo "Title: ".$worksheet->getTitle()."<br/>";
$wsTitle = $worksheet->getTitle();
$objWorksheet = $objPHPExcel->setActiveSheetIndexByName($wsTitle);
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$endofData = $highestRow -1;
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$colNumberStart = PHPExcel_Cell::columnIndexFromString("D");
$BeforeTOT = $colNumberStart - 2;
$TOT = $colNumberStart - 1;
echo "Highest Row is ==> $highestRow <br/>End of Data - header is $endofData <br/> Highest Col is ==> $highestColumn <br/> Start col is ==> $colNumberStart and Highest Col end is ==> $highestColumnIndex <br/>";
// Set the Labels for each data series we want to plot
// Datatype
// Cell reference for data
// Format Code
// Number of datapoints in series
// Data values
// Data Marker
${'dataseriesLabels' . $i} = array(
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$4', null, 1), // 2010
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$G$4', null, 1), // 2011
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$H$4', null, 1), // 2012
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$I$4', null, 1), // 2012
);
displayArray(${'dataseriesLabels' . $i});
// Set the X-Axis Labels
// Datatype
// Cell reference for data
// Format Code
// Number of datapoints in series
// Data values
// Data Marker
${'xAxisTickValues' . $i} = array(
new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$4:$H$4', null, 19), // Q1 to Q4
);
displayArray(${'xAxisTickValues' . $i});
// Set the Data values for each data series we want to plot
// Datatype
// Cell reference for data
// Format Code
// Number of datapoints in series
// Data values
// Data Marker
${'dataSeriesValues' . $i} = array(
new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$G$5:$I$5', null, 4),
);
displayArray(${'dataSeriesValues' . $i});
// Build the dataseries
${'series' . $i} = new PHPExcel_Chart_DataSeries(
PHPExcel_Chart_DataSeries::TYPE_PIECHART, // plotType
PHPExcel_Chart_DataSeries::GROUPING_PERCENT_STACKED, // plotGrouping
range(0, count(${'dataseriesLabels' . $i})-1), // plotOrder
${'dataseriesLabels' . $i}, // plotLabel
${'xAxisTickValues' . $i}, // plotCategory
${'dataSeriesValues' . $i} // plotValues
);
displayArray(${'series' . $i});
// Set the series in the plot area
${'plotarea' . $i} = new PHPExcel_Chart_PlotArea(null, array(${'series' . $i}));
// Set the chart legend
${'legend' . $i} = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_TOPRIGHT, null, false);
${'title' . $i} = new PHPExcel_Chart_Title('Test %age-Stacked Area Chart');
${'yAxisLabel' . $i} = new PHPExcel_Chart_Title('Value ($k)');
// Create the chart
${'chart' . $i} = new PHPExcel_Chart(
'chart1', // name
${'title' . $i}, // title
${'legend' . $i}, // legend
${'plotarea' . $i}, // plotArea
true, // plotVisibleOnly
0, // displayBlanksAs
null, // xAxisLabel
${'yAxisLabel' . $i} // yAxisLabel
);
// Set the position where the chart should appear in the worksheet
${'chart' . $i}->setTopLeftPosition('A7');
${'chart' . $i}->setBottomRightPosition('H20');
// Add the chart to the worksheet
$objWorksheet->addChart(${'chart' . $i});
// Set the Labels for each data series we want to plot
// Datatype
// Cell reference for data
// Format Code
// Number of datapoints in series
// Data values
// Data Marker
// Save Excel 2007 file
echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;
// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;
$i++;
}
