Chart in 3.0.10 is not showing using multiple sheets

[krisnamargono]

I'm currently creating multiple sheets and in each sheet there should be a Chart based on a certain condition

namespace App\Services\Excel\Exports;

use App\Services\Excel\SheetData;
use Exception;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

class MultipleSheetsExport implements WithMultipleSheets {

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

    public function sheets(): array {
        $sheetsExport = [];

        foreach($this->sheetNameToDataArray as $sheetName => $data) {
            if ($data instanceof SheetData) {
                $perSheetExport = new PerSheetExport(
                    $sheetName,
                    $data->rows,
                    $data->charts ?? [],
                    $data
                );

                array_push($sheetsExport, $perSheetExport);
            } else {
                throw new Exception(
                    "Must be a type of SheetData (MultipleSheetsExport)"
                );
            }
        }

        return $sheetsExport;
    }
}

namespace App\Services\Excel\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithCharts;

class PerSheetExport implements 
    FromCollection,
    ShouldAutoSize,
    WithTitle,
    WithCharts{

    /**
     * @property $charts: Array of PhpOffice\PhpSpreadsheet\Chart\Chart
     */
    public function __construct(string $title, Array $rows, Array $charts) {
        $this->title = $title;
        $this->rows = $rows;
        $this->charts = $charts;
    }

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

    public function title(): string {
        return $this->title;
    }

    public function charts() {
        return $this->charts;
    }
}

And this is the function to create the Charts

private static function makeCharts(
        // $sheet,
        $data,
        $sheetName,
        $headerRowNum
    ) {
        $charts = [];

        // GRAFIK
        //  Set the Labels for each data series we want to plot
        $dataseriesLabels1 = [
            new DataSeriesValues(
                'String',
                $sheetName . '!$H$' . $headerRowNum,
                null,
                1
            )   //  Temperature
        ];

        //  Set the X-Axis Labels
        $xAxisTickValues = [
            new DataSeriesValues(
                'String',
                $sheetName . '!$A$' . ($headerRowNum + 1) . ':$A$' . (($headerRowNum+ 1) + (count($data) - 1)),
                null, 
                12
            ),    //  Jan to Dec
        ];

        //  Set the Data values for each data series we want to plot
        $dataSeriesValues1 = [
            new DataSeriesValues(
                'Number',
                $sheetName . '!$H$' . ($headerRowNum + 1) . ':$H$' . (($headerRowNum + 1) + (count($data) - 1)),
                null,
                12
            ),
        ];

        //  Build the dataseries
        $series1 = new DataSeries(
            DataSeries::TYPE_BARCHART,       // plotType
            DataSeries::GROUPING_CLUSTERED,  // 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(DataSeries::DIRECTION_COL);

        //  Set the series in the plot area
        $plotarea = new PlotArea(null, array($series1));
        
        //  Set the chart legend
        $legend= new Legend(Legend::POSITION_RIGHT, null, false);

        $title = new Title('Total Nominal');

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

        //  Set the position where the chart should appear in the worksheet
        $nominalChart->setTopLeftPosition('B'.(9 + (count($data) - 1)));
        $nominalChart->setBottomRightPosition('E'.(18 + (count($data) - 1)));

        //  Add the chart to the worksheet
        array_push($charts, $nominalChart);
        // $sheet->addChart($nominalChart);

        // GRAFIK 2
        //  Set the Labels for each data series we want to plot
        $dataseriesLabels2 = [
            new DataSeriesValues(
                'String',
                $sheetName . '!$I$' . $headerRowNum,
                null,
                1
            )   //  Rainfall
        ];

        //  Set the X-Axis Labels
        $xAxisTickValues = [
            new DataSeriesValues(
                'String',
                $sheetName . '!$A$' . ($headerRowNum + 1) . ':$A$' . (($headerRowNum + 1) + (count($data) - 1)),
                null,
                12
            )    //  Jan to Dec
        ];

        //  Set the Data values for each data series we want to plot
        $dataSeriesValues2 = [
            new DataSeriesValues(
                'Number',
                $sheetName . '!$I$' . ($headerRowNum + 1) . ':$I$' . (($headerRowNum + 1) + (count($data) - 1)),
                null,
                12
            )
        ];

        //  Build the dataseries
        $series2 = new DataSeries(
            DataSeries::TYPE_LINECHART,                 // plotType
            DataSeries::GROUPING_STANDARD,              // plotGrouping
            range(0, count($dataSeriesValues2) - 1),    // plotOrder
            $dataseriesLabels2,                         // plotLabel
            $xAxisTickValues,                           // plotCategory
            $dataSeriesValues2                          // plotValues
        );

        //  Set the series in the plot area
        $plotarea = new PlotArea(null, array($series2));

        $title = new Title('Total Frekuensi');

        //  Create the chart
        $frequencyChart = new Chart(
            'chart2',       // name
            $title,         // title
            null,        // legend
            $plotarea,      // plotArea
            true,           // plotVisibleOnly
            0,              // displayBlanksAs
            null,           // xAxisLabel
            null            // yAxisLabel
        );

        //  Set the position where the chart should appear in the worksheet
        $frequencyChart->setTopLeftPosition('G'.(9 + (count($data) - 1)));
        $frequencyChart->setBottomRightPosition('J'.(18 + (count($data) - 1)));

        //  Add the chart to the worksheet
        array_push($charts, $frequencyChart);
        // $sheet->addChart($frequencyChart);
        
        return $charts;
    }

is this not possible? I couldn't update to the latest laravel-excel since this is a legacy code and if I upgrade it to 3.1, the changes are too big (due to library dependencies).. I'm currently upgrading from 2.1 to 3.0 for now.

[patrickbrouwers]

3.0 doesn't have support for charts like you are trying to use it, you'll have to be on the latest 3.1 version. The upgrade from 2.1 to 3.1 should be more or less the same size as to 3.0. The 3.1 release was mostly to add imports to the library, but has a couple of years of added small features and bug fixes in there. I wouldn't recommend using the 3.0, but go for 3.1 straight away.

[krisnamargono]

got it.. yeah I just realized as well that in 3.0 there's no imports function to replace Excel::load 😢
I guess I just have to upgrade to 3.1 along with the conflicted dependencies. Thanks for the reply!

[krisnamargono]

hi @patrickbrouwers , is the chart function available in 3.1.0? I couldn't update to the latest one due to dependencies conflict..

[patrickbrouwers]

I believe you have to be at least on 3.1.10

As I mentioned before, a lot of things have been improved and fixed over the course of the last 4 years, best to try and get to the latest version.

[Hiuhu]

I am using v3.1 and the problem still occurs under queue but works okay under download method.
I am using laravel 8.83 & php version 7.4.
My QUEUE_CONNECTION=database.

Here is my code:

//Parent class
class MultipleUsersExport implements WithMultipleSheets {
use Exportable;

public function sheets(): array
{
    $sheets = [];

    $query = DB::table('users')
        ->select('id', 'name')
        ->orderBy('updated_at')
        ->get();

    foreach($query as  $user) {
        $sheets[] = new ActiveUsersExport($user->name);
    }

    return $sheets;
}

}

//Child class

class ActiveUsersExport implements
FromQuery, ShouldAutoSize, WithMapping, WithHeadings, WithEvents, WithCharts, WithTitle {

use Exportable;

public $name;

public function __construct($name) {

  $this->name = $name;

}

public function query()
{

    $query = DB::table('users')
        ->select('users.*')
        ->orderBy('updated_at');

    return $query;

}

public function headings(): array
{

    return [

        'ID',
        'Name',
        'Email',

    ];

}

public function charts()
{
    $label      = [new DataSeriesValues('String', $this->name.'!$B$1', null, 1), ];
    $categories = [new DataSeriesValues('String', $this->name.'!$B$2:$B$8', null, 2)];
    $values     = [new DataSeriesValues('Number', $this->name.'!$A$2:$A8', null, 2)];

    $series = new DataSeries(DataSeries::TYPE_BARCHART_3D, NULL,
        range(0, \count($values) - 1), $label, $categories, $values);
    $plot   = new PlotArea(NULL, [$series]);

    $legend = new Legend(Legend::POSITION_RIGHT, NULL, false);
    $chart  = new Chart('Overview X', new Title('Overview'), $legend, $plot,true,'gap',NULL,NULL);
    $chart->setTopLeftPosition('E1');
    $chart->setBottomRightPosition('K20');

    return $chart;
}

public function registerEvents(): array {

    return [
        AfterSheet::class => function(AfterSheet $event) {

            $chart = $this->charts();
            $event->sheet->getDelegate()->addChart($chart);

        }

    ];

}

public function map($user): array
{

    return [

        [$user->id,
            $user->name,
            $user->email,],

    ];

}

public function title(): string
{

    return strtoupper($this->name);

}

}

// code on controller
public function export() {

// Download spreadsheet directly (Shows charts)
return Excel::download(new MultipleUsersExport(), 'Multiple.xlsx', \Maatwebsite\Excel\Excel::XLSX);

// Download spreadsheet via queue (Does not show charts)
(new MultipleUsersExport())->queue('/public/uploads/Multiple.xlsx', '', \Maatwebsite\Excel\Excel::XLSX)->onQueue('high');

}

How can I fix this when working with queues ?