phpspreadsheet - Laravel Excel 尝试使用图表导出但没有图表输出

Question

我正在尝试使用图表导出 laravel-excel 数据，但在 excel 上没有图表输出。只有数组数据显示为表格数据。 这是我的出口课程

class ReportExport implements WithEvents
{
    use Exportable, RegistersEventListeners;

    public static function beforeExport(BeforeExport $event)
    {
        $event->writer->getProperties()->setCreator('Test 1');
    }

    public static function afterSheet(AfterSheet $event)
    {
        $event->sheet->getDelegate()->fromArray(
            [
                ['', 2010, 2011, 2012],
                ['Q1', 12, 15, 21],
                ['Q2', 56, 73, 86],
                ['Q3', 52, 61, 69],
                ['Q4', 30, 32, 0],
            ]
        );

        //  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 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', null, 1, ['2010']), // 2010
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1, ['2011']), // 2011
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$D$1', null, 1, ['2012']), // 2012
        ];
        //  Set the X-Axis Labels
        //      Datatype
        //      Cell reference for data
        //      Format Code
        //      Number of datapoints in series
        //      Data values
        //      Data Marker
        $xAxisTickValues = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4, ['Q1', 'Q2', 'Q3', 'Q4']), // Q1 to Q4
        ];
        //  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 = [
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$5', null, 4, [1]),
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4, [55]),
            new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$D$2:$D$5', null, 4, [23]),
        ];
        $dataSeriesValues[2]->setLineWidth(60000);

        //  Build the dataseries
        $series = new DataSeries(
            DataSeries::TYPE_BARCHART, // plotType
            DataSeries::GROUPING_STACKED, // plotGrouping
            range(0, count($dataSeriesValues) - 1), // plotOrder
            $dataSeriesLabels, // plotLabel
            $xAxisTickValues, // plotCategory
            $dataSeriesValues        // plotValues
        );

        //  Set the series in the plot area
        $plotArea = new PlotArea(null, [$series]);
        //  Set the chart legend
        $legend = new Legend(Legend::POSITION_TOPRIGHT, null, false);

        $title = new Title('Test Stacked Line Chart');
        $yAxisLabel = new Title('Value ($k)');

        //  Create the chart
        $chart = new Chart(
            'chart1', // name
            $title, // title
            $legend, // legend
            $plotArea, // plotArea
            true, // plotVisibleOnly
            0, // displayBlanksAs
            null, // xAxisLabel
            $yAxisLabel  // yAxisLabel
        );
        //  Set the position where the chart should appear in the worksheet
        $chart->setTopLeftPosition('A7');
        $chart->setBottomRightPosition('H20');

        // dd($event->sheet->getDelegate()->addChart($chart));
        //  Add the chart to the worksheet
        $event->sheet->getDelegate()->addChart($chart);
    }
}

然后在我的控制器上我下载了类似这样的东西

    public function export() 
    {
        return (new ReportExport)->download('reports.xlsx');
    }

你能告诉我我的代码缺少什么吗？此代码是从此链接复制和修改的。

请帮助我这几天一直在努力解决这个问题。

https://github.com/PHPOffice/PhpSpreadsheet/tree/master/samples/Chart

标签： phpspreadsheetlaravel-excel

Answer 1

我面临同样的问题。我的代码有AfterSheet事件，我已经尝试了所有其他事件，但它要么不起作用，要么我的数据尚未在方法中定义

public function collection()

我的图表值数组为空

作为我的 AfterSheet 关闭的最后一行，我正在记录$event->sheet->getDelegate()->getChartCount()并且图表的数量是正确的

我也尝试过 WithCharts 问题，但似乎图表方法在收集方法之前执行

- 编辑 -

我已经更改了我的代码以使其正常工作。我所有的数据数组构造都是在类上进行的__construct，我的图表的所有数据都可以在图表方法上使用

<?php namespace App\Support\Export;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithCalculatedFormulas;
use Maatwebsite\Excel\Concerns\WithCharts;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Layout;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;

class ExcelExport implements
    FromCollection,
    WithCharts,
    WithHeadings,
    WithEvents,
    WithTitle,
    ShouldAutoSize,
    WithCalculatedFormulas,
    WithStrictNullComparison
{

    private $currentProcess;
    private $data;
    private $chartLocations = [];

    public function __construct($currentProcess)
    {
        $this->currentProcess= $currentProcess;

        // a simple array with months: 'Jun/18', 'Jul/18' (...)
        $months = $this->currentProcess->getMonthsAndYearsOperationList();
        // a array with my 6 indicators 
        $indicatorsByMonth = $this->currentProcess->getIndicatorByMonth();

        $data = [];
        $i = 1;

        foreach ($months as $month) {
            $data[] = [[$month]];
            $i++;
            $data[] = [['1st - indicator']];
            $i++;
            $this->printTable($data, $i, $indicatorsByMonth, 'array key', 'Table header');
            $data[] = [['']];
            $i++;
            $data[] = [['']];
            $i++;
            $data[] = [['']];
            $i++;
        }

        $this->data = $data;
    }

    public function title(): string
    {
        return 'Charts';
    }

    // set the headings
    public function headings() : array
    {
        return [];
    }

    // freeze the first row with headings
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
            }
        ];
    }

    private function printTable(&$data, &$i, $indicators, $key, $headerTitle)
    {

        $data[] = [
            ['', '', '', 'Gender']
        ];
        $i++;
        $data[] = [
            [$headerTitle, 'Nº', '%', 'M', 'F']
        ];
        $i++;
        $start = $i;
        foreach ($indicators[$key] as $m => $params) {
            foreach ($indicadores[$key][$m] as $tipo => $count) {
                $data[] = [
                    [$tipo, $count['total'], '', $count['male'], $count['female']]
                ];
                $i++;
            }
            $end = $i-1;
            for ($j = $start; $j <= $end; $j++) {
                $data[$j-1][0][2] = "=B$j/SUM(B$start:B$end)";
            }

            $this->chartLocations[] = [
                'label' => $this->title() . '!$A$' . ($start-1),
                'categories' => $this->title() . '!$A$' . $start . ':$A$' . $end,
                'values' => $this->title() . '!$B$' . $start . ':$B$' . $end,
                'pointCount' => $end - $start + 1,
                'start' => 'F'.($start - 3),
                'finish' => 'N'.($end + 1),
                'name' => $key . $start,
                'title' => $headerTitle,
            ];
        }
    }

    public function collection()
    {
        return collect($this->data);
    }

    public function charts()
    {
        $charts = [];

        foreach ($this->chartLocations as $i => $cl) {
            $label      = [new DataSeriesValues('String', $cl['label'])];
            $category = [new DataSeriesValues('String', $cl['categories'], null, $cl['pointCount'])];
            $value     = [new DataSeriesValues('Number', $cl['values'], null, $cl['pointCount'])];

            $series = new DataSeries(
                DataSeries::TYPE_DONUTCHART,
                null,
                range(0, count($value) - 1),
                $label,
                $category,
                $value
            );

            $layout = new Layout();
            $layout->setShowPercent(true);

            $legend = new Legend();

            $plot   = new PlotArea($layout, [$series]);

            $chart  = new Chart(
                $cl['name'],
                new Title($cl['title']),
                $legend,
                $plot
            );

            $chart->setTopLeftPosition($cl['start']);
            $chart->setBottomRightPosition($cl['finish']);

            $charts[] = $chart;
        }

        return $charts;
    }
}