Second Test of phpspreadsheet

test2.php

<?php
require __DIR__ . '/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
class Master_donor {
    function bulanprint($data){
        $listbulan=$data;
        $test="2019";
        $a=0;
        $spreadsheet = new Spreadsheet();
        foreach($listbulan as $key=> $datanya){
            $countdata=count($datanya);
            $oke=$spreadsheet->setActiveSheetIndex($a);
            $oke->setCellValue('A1', 'Program Donor Darah Tahun '.$test);
            $oke->getStyle('A3:E3')->applyFromArray(
                [
                    'font' => ['bold' => true,],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,],
                ]
            );
            $oke->getStyle('A3:E'.($countdata+4))->applyFromArray(
                [
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,],
                    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN,],]
                ]
            );
            $oke->setCellValue('A3', 'No');
            $oke->setCellValue('B3', 'Site');
            $oke->setCellValue('C3', 'Target');
            $oke->setCellValue('D3', 'Jumlah Pendaftar');
            $oke->setCellValue('E3', 'Jumlah Kantong Darah');
            $oke->getColumnDimension('D')->setAutoSize(true);
            $oke->getColumnDimension('E')->setAutoSize(true);
            $oke->fromArray($datanya,NULL,'A4',true);
            $oke->mergeCells('A'.($countdata+4).':B'.($countdata+4));
            $oke->setCellValue('A'.($countdata+4), 'Total');
            $oke->setCellValue('C'.($countdata+4), '=SUM(C4:C'.($countdata+3).')');
            $oke->setCellValue('D'.($countdata+4), '=SUM(D4:D'.($countdata+3).')');
            $oke->setCellValue('E'.($countdata+4), '=SUM(E4:E'.($countdata+3).')');
            $dataSeriesLabels1 = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$E$3', null, 1), // Target
            ];
            $dataSeriesLabels2 = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$3', null, 1), // Jumlah Kantong
            ];
            $xAxisTickValues = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$4:$B$'.($countdata+3), null, $countdata), // Jan to Dec
            ];
            $dataSeriesValues1 = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$E$4:$E$'.($countdata+3), null, $countdata),
            ];

            // 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
            );
            $series1->setPlotDirection(DataSeries::DIRECTION_COL);
            $dataSeriesValues2 = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$4:$C$'.($countdata+3), null, $countdata),
            ];
            // Build the dataseries
            $series2 = new DataSeries(
                DataSeries::TYPE_LINECHART, // plotType
                DataSeries::GROUPING_STANDARD, // plotGrouping
                range(0, count($dataSeriesValues2) - 1), // plotOrder
                $dataSeriesLabels2, // plotLabel
                [], // plotCategory
                $dataSeriesValues2// plotValues
            );
            $plotArea = new PlotArea(null, [$series1, $series2]);
            // Set the chart legend
            $legend = new Legend(Legend::POSITION_BOTTOM, null, false);
            $title = new Title('Donor Darah: '.$a);
            $yAxisLabel = new Title('Jumlah Kantong Darah');
            // 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('G3');
            $chart->setBottomRightPosition('Q22');
            $oke->addChart($chart);

            // Rename worksheet
            $spreadsheet->getActiveSheet()->setTitle("Title". $a);
            if($a==11){
                break;
            }
            else{
                $spreadsheet->createSheet();
            }
            $a++;
        }
        // Save Excel 2007 file
        $spreadsheet->setActiveSheetIndex($a);
        //$filename = "laporan_donor_".$test;
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->setIncludeCharts(true);
        $writer->save('testothersheet.xlsx');
        exit;
    }
}
$myData = [
    [
        [2,"a string",4,6,7],
        [2,"a string",4,6,7],
        [3,"a string",4,6,7],
        [3,"a string",4,6,7],
        [4,"a string",4,6,7],
        [6,"a string",4,6,7],

    ],
    [
        [1,"a string",4,6,7],
        [2,"a string",4,6,7],
        [2,"a string",4,6,7],
        [2,"a string",4,6,7],
        [4,"a string",4,6,7],
        [6,"a string",4,6,7],

    ],
    [
        [2,"a string",4,6,7],
        [2,"a string",4,6,7],
        [3,"a string",4,6,7],
        [3,"a string",4,6,7],
        [4,"a string",4,6,7],
        [6,"a string",4,6,7],

    ],
    [
        [2,"a string",4,6,7],
        [2,"a string",4,6,7],
        [3,"a string",4,6,7],
        [3,"a string",4,6,7],
        [4,"a string",4,6,7],
        [6,"a string",4,6,7],

    ],
];
$csv_writer = new Master_donor;
$csv_writer->bulanprint($myData);

?>