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;

上下文:我有几个功能区域，每个功能区域在通过/失败/阻止方面都有不同的值。每个功能区域需要 1 个图表。我平均每张 table 有 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);

因此，要自动创建图形，过程将是: d1.打开 w/e 工作表，您需要从中获取数据 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));

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

想法？