1

I am reading in a template file that has a series of charts. The charts on sheet "Charts" are based on data sets from a second sheet "RawData". I am trying to change the data set/range that feeds the chart - my assumption is that simply changing the data set will auto expand/shrink the chart.

In excel

From:
   headings: =RawData!$A$1:$H$1
   data      =RawData!$A$2:$H$2

To:
   headings: =RawData!$A$1:$M$1
   data      =RawData!$A$2:$M$2

I can find the chart I need to change, but I can't figure out how to get/set the dataSource of the chart. I think I should be able to extract it with getDataSource() but I can't figure out how to properly use the method:

  foreach ($spreadsheet->getSheetByName("Chart Data")->getChartCollection() as $chart) {
    if ($chart->getTitle()->getCaptionText() == "Chart_12") {
      // "Chart_12" is a manually assigned chart title in the template file
      $chart->getDataSource() ;  <--- error: undefined method
      break ;
    }
  }

getDataSource() is a listed method in the documentation: getDataSource - hopefully someone can assist as this is driving me nuts.

rolinger
  • 2,787
  • 1
  • 31
  • 53
  • see this complete example of phpspreadsheet sample to understand how chart will be generated from dataseries: [Sample chart phpspreadsheet](https://github.com/PHPOffice/PhpSpreadsheet/blob/master/samples/Chart/33_Chart_create_multiple_charts.php) – Haritsinh Gohil Aug 02 '22 at 06:55
  • @HaritsinhGohil - Thanks for the reference. But its about adding a new chart. How could this adapted to change the dataseries of an existing chart? I imagine i can define a whole new dataseries, easy enough, but then how would I assign the dataseries to an existing chart? `$curChart($newDataSeries)` or `$curChart->setDataSource($newDataSeries)`, I am having a hard time understanding the docs on how to do this. – rolinger Aug 02 '22 at 14:50
  • do you already has charts in your excel?, if you are creating it by running then you dont have to load it in existing chart it will redraw the chart a new, you just have to give the range and data series as shown in sample. – Haritsinh Gohil Aug 03 '22 at 06:40
  • @HaritsinhGohil - again thanks. yes, charts already exist. I am just trying to alter the existing charts to expand or shrink the data sets. The example shows how to create a new chart, but not how to replace an existing charts data set...and the docs don't provide any examples I can find. I have isolated the existing `$chart` object, but the command to replace the objects dataset escapes me. – rolinger Aug 03 '22 at 15:13

1 Answers1

0

I think there is no method to alter the existing charts in phpspreadsheet and I think it can not be feasible because new chart can be smaller then existing chart and currently it does not provide any functionality to remove charts.

So what you can do is you can remove the worksheet of charts dynamically so all charts will be removed and after that you can add new worksheet and add new charts on that new sheet.

I have provided sample code below to remove sheet from the excel file and also add new sheet into that file:

<?php

require_once(__DIR__ . '/vendor/autoload.php');

$mySpreadsheet = new PhpOffice\PhpSpreadsheet\Spreadsheet();
    
// delete the default active sheet
$mySpreadsheet->removeSheetByIndex(0);
    
// Create "Sheet 1" as the first worksheet.
// https://phpspreadsheet.readthedocs.io/en/latest/topics/worksheets/adding-a-new-worksheet
$worksheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($mySpreadsheet, "Sheet 1");
$mySpreadsheet->addSheet($worksheet, 0); // add new sheet and then create new charts on it.

In addition to above code you can also use sample chart code provided officially by phpspreadsheet library itself: Sample Chart code by phpspreadsheet

You can make your solution by using both code the code of phpspreadsheet library sample chart and the sample code of add, remove sheet which I have given above.

Haritsinh Gohil
  • 5,818
  • 48
  • 50