Fail wheni tried add craph Formula Error: An unexpected error

[uldcra]

atal 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 wroken.

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');

[oleibman]

Your ranges in xAxisTickValues and dataSeriesValues are mis-coded. They should be:
'Worksheet!$A$1:$A$5'
'Worksheet!$B$1:$B$5'
In addition, you haven't attached the chart to the worksheet, with code like:

$chart->setTopLeftPosition('I7');
$chart->setBottomRightPosition('P20');

It still doesn't look exactly right, but I'm out of time for this morning. Hopefully you can take it from here.

[uldcra]

Thanks oleiban it´s work but the only issue, when i open the xlsx, i need to recover for view document, show xml error

?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error138360_01.xml</logFileName><summary>Se han detectado errores en el archivo "C:\xampp\htdocs\graficas_excel\graficas\passengers1.xlsx"</summary><repairedRecords><repairedRecord>Registros reparados: Dibujo de /xl/drawings/drawing1.xml parte (Forma de dibujo)</repairedRecord></repairedRecords></recoveryLog>

Aprecciate your helpful.

[oleibman]

In the new Chart statement, I believe you have inverted $xAxis and $yAxis at the end of the parameters. In addition, $yAxis->setAxisOptionProperties sets the maximum to 100, which is too low for the values you are using. When I change those 2 statements (setting maximum to 1000), I now have a spreadsheet with a chart that looks right. It opens correctly in Excel (Office 365). This is true for both the current (18.0) and master versions of PhpSpreadsheet, using PHP7.4. What release and PHP version are you using?

[uldcra]

you are a awesome a lot of thanks for great helpful.

[uldcra]

I remove $yaxis, $xaxis it works