Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Chart: Not able to use multiple bar types (DataSeries::TYPE_BARCHART) in a single chart. #3994

Open
1 of 8 tasks
Jaguar1 opened this issue Apr 18, 2024 · 0 comments
Open
1 of 8 tasks
Labels

Comments

@Jaguar1
Copy link

Jaguar1 commented Apr 18, 2024

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

To create a chart inside Excel with multiple 'column' bar plot types.

What is the current behavior?

Now if you have more than one 'column' bar plot types the chart is missing without any errors.

What are the steps to reproduce?

Add two bar types to the same chart, for example like this:

$series1 = new DataSeries(
DataSeries::TYPE_BARCHART, // plotType - first time
DataSeries::GROUPING_CLUSTERED, // plotGrouping
range(0, count($dataSeriesValues1) - 1), // plotOrder
$dataSeriesLabels1, // plotLabel
$xAxisTickValues, // plotCategory
$dataSeriesValues1 // plotValues
);
...
$series2 = new DataSeries(
DataSeries::TYPE_BARCHART, // plotType - bar again
DataSeries::GROUPING_CLUSTERED, // plotGrouping
range(0, count($dataSeriesValues2) - 1), // plotOrder
$dataSeriesLabels2, // plotLabel
$xAxisTickValues, // plotCategory
$dataSeriesValues2 // plotValues
);

This leads to a blank chart. If you add 'line' type instead (DataSeries::TYPE_LINECHART) it will work. You can have multiple 'line' types (DataSeries::TYPE_LINECHART) in the same chart but you cannot have more than one bar (DataSeries::TYPE_BARCHART).

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

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

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...
  $worksheet = $spreadsheet->getActiveSheet();
      $worksheet->fromArray(
         [
            ['', 'Rainfall (mm)', 'Temperature (°F)', 'Humidity (%)'],
            ['Jan', 78, 52, 61],
            ['Feb', 64, 54, 62],
            ['Mar', 62, 57, 63],
            ['Apr', 21, 62, 59],
            ['May', 11, 75, 60],
            ['Jun', 1, 75, 57],
            ['Jul', 1, 79, 56],
            ['Aug', 1, 79, 59],
            ['Sep', 10, 75, 60],
            ['Oct', 40, 68, 63],
            ['Nov', 69, 62, 64],
            ['Dec', 89, 57, 66],
         ]
      );

      // 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
      $dataSeriesLabels1 = [
         new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', null, 1), // Temperature
      ];
      $dataSeriesLabels2 = [
         new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // Rainfall
      ];
      $dataSeriesLabels3 = [
         new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$D$1', null, 1), // Humidity
      ];

      // 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$13', null, 12), // Jan to Dec
      ];

      // 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
      $dataSeriesValues1 = [
         new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$13', 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 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
      $dataSeriesValues2 = [
         new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$13', null, 12),
      ];

      // Build the dataseries
      $series2 = new DataSeries(
         // DataSeries::TYPE_LINECHART, // plotType - will work with this
         DataSeries::TYPE_BARCHART, // plotType - will not work with this because we have DataSeries::TYPE_BARCHART in $series1
         DataSeries::GROUPING_STANDARD, // plotGrouping
         range(0, count($dataSeriesValues2) - 1), // plotOrder
         $dataSeriesLabels2, // plotLabel
         [], // plotCategory
         $dataSeriesValues2        // plotValues
      );

      // 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
      $dataSeriesValues3 = [
         new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$D$2:$D$13', null, 12),
      ];

      // Build the dataseries
      $series3 = new DataSeries(
         DataSeries::TYPE_AREACHART, // plotType
         DataSeries::GROUPING_STANDARD, // plotGrouping
         range(0, count($dataSeriesValues2) - 1), // plotOrder
         $dataSeriesLabels3, // plotLabel
         [], // plotCategory
         $dataSeriesValues3        // plotValues
      );

      // Set the series in the plot area
      $plotArea = new PlotArea(null, [$series1, $series2, $series3]);
      // Set the chart legend
      $legend = new ChartLegend(ChartLegend::POSITION_RIGHT, null, false);

      $title = new Title('Average Weather Chart for Crete');

      // Create the chart
      $chart = new ExcelChart(
         'chart1', // name
         $title, // title
         $legend, // legend
         $plotArea, // plotArea
         true, // plotVisibleOnly
         DataSeries::EMPTY_AS_GAP, // displayBlanksAs
         null, // xAxisLabel
         null   // yAxisLabel
      );

      // Set the position where the chart should appear in the worksheet
      $chart->setTopLeftPosition('F2');
      $chart->setBottomRightPosition('O16');

      // Add the chart to the worksheet
      $worksheet->addChart($chart);
      $writer = new Xlsx($spreadsheet);
      $writer->setIncludeCharts(true);
      $writer->save('php://output');

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

What features do you think are causing the issue

  • Reader
  • Writer
  • Styles
  • Data Validations
  • Formula Calculations
  • Charts
  • AutoFilter
  • Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

'xlsx' as I know

Which versions of PhpSpreadsheet and PHP are affected?

"php": "^7.3|^8.0",
"phpoffice/phpspreadsheet": "^1.22",

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

No branches or pull requests

2 participants