PHPExcel -从阵列填充数据

Question

到目前为止，我已经成功地通过PHPExcel将数据从Mysql导出到了Excel中，制作了表格，进行了格式化，一切都很棒。

然而，到目前为止，从所述表格制作图形的任何尝试都失败了。不仅如此，经过几天的谷歌搜索，我还没有找到一个简单的示例/教程来说明如何从MySQL填充图形值。

这一切都可以归结为：

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),
    )
);

我该如何从a) Mysql或b) excel填充$objWorksheet->fromArray？

如果是SQL，我将需要对数据库中的所有表运行此查询。

$sql = "SELECT Functional_Area，Passed__Status，Blocked__Status，Failed__Status FROM $tbl_name WHERE 1；

上下文:我有几个功能区域，每个功能区域在通过/失败/阻止中都有不同的值。我需要为每个功能领域的一个图表。平均每个表有14个功能区。

然后，我会为每个功能区域构建一个饼状图(或条形图，我并不挑剔)。

选项2) Excel

因为我已经在excel中提到了数据，所以我需要从以下位置填充数据：

​

同样，每行1个图(第5行(FA = EPG)将是1个图，第6行(FA = VOD)将是另一个图，依此类推)。

必须对每个工作表重复此操作...

到目前为止，我的尝试如下，这将为每个工作表(这很好)生成一个图，但它是空的(不好)。

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++;
}

Answer 1

伙计们，我做了更多的调查，得到了一些答案。

答:如果数据在excel上，你不需要从Mysql b查询它。这是一个可以工作的代码片段，100%的时间可以从之前已经填充的工作表中制作堆叠条形图：

                //  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
                $dataseriesLabels1 = array(
                    new PHPExcel_Chart_DataSeriesValues('String', 'report!$E$4', NULL, 4),    //  Jan to Dec
                    new PHPExcel_Chart_DataSeriesValues('String', 'report!$F$4', NULL, 4),    //  Jan to Dec
                    new PHPExcel_Chart_DataSeriesValues('String', 'report!$G$4', NULL, 4)    //  Jan to Dec
                );

                //  Set the X-Axis Labels
                //      Datatype
                //      Cell reference for data
                //      Format Code
                //      Number of datapoints in series
                //      Data values
                //      Data Marker

                $xAxisTickValues = array(
                    new PHPExcel_Chart_DataSeriesValues('String', 'report!$C$4:$C$20', NULL, 4)    //  Jan to Dec    
                );

                //  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
                $dataSeriesValues1 = array(
                    new PHPExcel_Chart_DataSeriesValues('Number', 'report!$E$5:$E$20', NULL, 16),
                    new PHPExcel_Chart_DataSeriesValues('Number', 'report!$F$5:$F$20', NULL, 16),
                    new PHPExcel_Chart_DataSeriesValues('Number', 'report!$G$5:$G$20', NULL, 16)
                );

                //  Build the dataseries
                $series1 = new PHPExcel_Chart_DataSeries(
                    PHPExcel_Chart_DataSeries::TYPE_BARCHART,        // plotType
                    PHPExcel_Chart_DataSeries::GROUPING_STACKED,    // plotGrouping
                    range(0, count($dataSeriesValues1)-1),          // plotOrder
                    $dataseriesLabels1,                             // plotLabel
                    $xAxisTickValues,                               // plotCategory
                    $dataSeriesValues1                              // plotValues
                );
                //  Set additional dataseries parameters
                //      Make it a vertical column rather than a horizontal bar graph
                $series1->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_COL);

                //  Set the series in the plot area
                $plotarea = new PHPExcel_Chart_PlotArea(NULL, array($series1));
                //  Set the chart legend
                $legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, NULL, false);

                $title = new PHPExcel_Chart_Title('Average Weather Chart for Crete');


                //  Create the chart
                $chart = new PHPExcel_Chart(
                    'chart1',       // name
                    $title,         // title
                    $legend,        // legend
                    $plotarea,      // plotArea
                    true,           // plotVisibleOnly
                    0,              // displayBlanksAs
                    NULL,           // xAxisLabel
                    NULL            // yAxisLabel
                );

                //  Set the position where the chart should appear in the worksheet
                $chart->setTopLeftPosition('F2');
                $chart->setBottomRightPosition('O16');

                //  Add the chart to the worksheet
                $objWorksheet->addChart($chart);




                // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
                $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
                $objWriter->setIncludeCharts(true);

d.因此，要自动创建图形，过程将是: d1。打开您需要从d2获取数据的工作表。读取或硬编码标签、x轴和y轴d3的值。创建图表元素d4。需要张贴所述元素w/e。

我希望这篇文章能帮助其他一些对PHPExcel+Graphs感到头疼的糟糕程序员

==========================================================================

更新25/9

我试图动态地构建数组，但有点失败。

  Fellas, question.

我现在正在尝试动态地填充它，换句话说，构建：

   $dataseriesLabels1 = "array(";

   foreach ($Labels as $k => $s) 
   {
     echo "Parsing $s <br/>";
     $dataseriesLabels1 .= "new PHPExcel_Chart_DataSeriesValues('String', ".$s.", NULL, 4),";
     // echo "We have $dataseriesLabels1 in this loop <br/>";
   }
   $dataseriesLabels1 = rtrim($dataseriesLabels1, "," );
   $dataseriesLabels1 .= ");";

    echo "BUilt : $dataseriesLabels1 <br/>";

这一切都很好，因为它为我创建了：

   array(new PHPExcel_Chart_DataSeriesValues('String', 'Sprint!$D$4:$D$16', NULL, 4),new PHPExcel_Chart_DataSeriesValues('String', 'Sprint!$E$4:$E$16', NULL, 4),new PHPExcel_Chart_DataSeriesValues('String', 'Sprint!$F$4:$F$16', NULL, 4),new PHPExcel_Chart_DataSeriesValues('String', 'Sprint!$G$4:$G$16', NULL, 4),new PHPExcel_Chart_DataSeriesValues('String', 'Sprint!$H$4:$H$16', NULL, 4));

不幸的是，这被解释为字符串，而不是数组，因此图表生成失败:/

想法？