-1

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Formula Error: An unexpected error occurred in C:\xampp\htdocs\graficas_excel\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:5224
Stack trace:
#0 C:\xampp\htdocs\graficas_excel\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php(4305): PhpOffice\PhpSpreadsheet\Calculation\Calculation->raiseFormulaError('Formula Error: ...')
#1 C:\xampp\htdocs\graficas_excel\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php(3539): PhpOffice\PhpSpreadsheet\Calculation\Calculation->internalParseFormula('! Worksheet $ B...', Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#2 C:\xampp\htdocs\graficas_excel\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Chart\DataSeriesValues.php(363): PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue('! Worksheet $ B...', NULL, Object(PhpOffice\PhpSpreadsheet\Cell\Cell))
#3 C:\xampp\htdocs\graficas_excel\vendor\phpoffice\phpspreadsh in C:\xampp\htdocs\graficas_excel\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php on line 5224

Hi everybody.

I tried create chart in excel fo phpoffice/phpspreadsheet, but when i tried add graph the app is broken.

my code

<?php

require '../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
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;
use PhpOffice\PhpSpreadsheet\Chart\Axis;

$spreadsheet = new Spreadsheet();

$worksheet = $spreadsheet->getActiveSheet();

$worksheet->fromArray(
        [
            ['Shinjuku', 778, 618],
            ['Ikebukuro', 566, 516],
            [' Tokyo ', 452, 549],
            [' Shinagawa ', 378, 566],
            [' Shibuya ', 370, 669],
        ]
);
$xAxisTickValues = [
    New DataSeriesValues(DataSeriesValues :: DATASERIES_TYPE_STRING, '! Worksheet $ A $ 1: $ A $ 5', Null, 5), //Shinjuku-Shibuya
];

$dataSeriesValues = [
    New DataSeriesValues(DataSeriesValues :: DATASERIES_TYPE_NUMBER, '! Worksheet $ B $ 1: $ B $ 5', Null, 5), //each value of the number of passengers
];

$series = new DataSeries(
        DataSeries::TYPE_BARCHART, //plotType
        DataSeries::GROUPING_STANDARD, //plotGrouping
        range(0, count($dataSeriesValues) - 1), //plotOrder
        [], //plotLabel
        $xAxisTickValues, //plotCategory
        $dataSeriesValues//plotValues
);

$series->setPlotDirection(DataSeries::DIRECTION_COL);
$plotArea = new PlotArea(null, [$series]);
$title = new Title('number of passengers per day');

$yaxis = new Axis();
$xaxis = new Axis();
$yaxis->setAxisOptionsProperties('low', null, null, null, null, null, 0, 100, null, null);
$yaxis->setLineParameters('FFFFFF', 100, Axis::EXCEL_COLOR_TYPE_ARGB);
$xaxis->setAxisOptionsProperties('low', null, null, null, null, null, 0, 0, null, null);
$legend1 = new Legend(Legend::POSITION_RIGHT, null, false);
$yAxisLabel = new Title('');
$chart = new Chart(
        'bar chart', //name
        $title, //title
        $legend1, //legend
        $plotArea, //plotArea
        true, //plotVisibleOnly
        DataSeries::EMPTY_AS_GAP, // displayBlanksAs
        null, // xAxisLabel
        $yAxisLabel, // yAxisLabel
        $yaxis,
        $xaxis
);

$worksheet->addChart($chart);

$writer = new Xlsx($spreadsheet);

$writer->setIncludeCharts(true);

$writer->save('passengers.xlsx');
Paul T.
  • 4,703
  • 11
  • 25
  • 29
Juna
  • 1
  • 1
  • Not sure if it will make any difference, but at `new Chart`, the last two variables should be swapped. The `$xaxis` comes before `$yaxis`. – Paul T. Jul 18 '21 at 14:29

1 Answers1

0

I have change my code.

$worksheet->fromArray(
        [
            ['', 'number of passengers per day',''],
            ['Shinjuku', 7787.81, 618],
            ['Ikebukuro ', 566, 516],
            [' Tokyo ', 452, 549],
            [' Shinagawa ', 378, 566],
            [' Shibuya ', 370, 669],
        ]
);

$xAxisTickValues = [
    New DataSeriesValues(DataSeriesValues :: DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$6', Null, 5), //Shinjuku-Shibuya
];

$dataSeriesValues = [
    New DataSeriesValues(DataSeriesValues :: DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$6', Null, 5), //each value of the number of passengers
    New DataSeriesValues(DataSeriesValues :: DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$6', Null, 5), //each value of the number of passengers
];

It´s work but when i open excel i need to recover form xml error?

Juna
  • 1
  • 1