Change charts title by PhpOffice\PhpSpreadsheet in template

Question

I try to change chart title in .xlsx template

I open template, change cels ,write to output.

Q: How to change chart title....

$reader = IOFactory::createReader( 'Xlsx' );
$reader->setIncludeCharts( true );
$spreadsheet = $reader->load( storage_path( 'app/template.xlsx' ) );
$spreadsheet->getActiveSheet()->setCellValue( 'B3', 'Blabla' );

//create new from template
$writer = new Xlsx( $spreadsheet );
$writer->setPreCalculateFormulas( true );
$writer->setIncludeCharts( true );

##how to change title in all/first charts in template?

Answer 1

I finally found the method. There are a few things to understand.

1. chartIndex - In an excel file, an id assigned to the chart based on the total number of charts starting from the first left most chart on a sheetA incrementing to the last right most chart on SheetC. This index is the entire collection of all charts on all sheets starting with chart1 and ending with chartX. If you have ten charts chart1,chart2,...chart10 and delete chart5, you will then have a new index of chart1,chart2....chart9, where the orig chart6-10 are all redindexed as chart5-9. In phpspreadsheet, this index appears to be represented with $chart->getName() ;, There is no corresponding setName() that would allow you to change/alter the index.
2. chartName - In an excel file, if you highlight a chart, in the upper left corner of the cells, above the A column, is a "chart name" drop down. You can name your charts for organization purposes. In excel, this field can be edited. In phpspreadsheet, I have yet to have find what object can get/set this field.
3. chartTitle - the in chart name of a chart, IE: "Annual Revenue Projections". In phpspreadsheet, this field is represented with $chart->getTitle->get/setCaptionText

So in order to find the chart you are looking to modify, you either must already know the chart index (ie: getName()) OR the chart visual title (ie: getCaptionText() ).

Because of adding deleting moving charts, I found it easier, in the excel template, to add visual titles to your charts then search for those titles/captionText in your code. This way you don't have to have worry about tracking which chart is which index. Use the getCaptionText to then get the getName() index.

  foreach ($spreadsheet->getSheetByName("SheetName")->getChartCollection() as $chart) {
    // if you know TITLE of the chart
    // TITLE is the visible title in the chart
    // Easy to know if you set the titles yourself in the template chart
    // A template chart TITLE cannot be a cell reference, will cause an error on file import
    if ($chart->getTitle()->getCaptionText() == "Chart_12") {
      $curIndex = $chart->getName() ; // = "chart3"
      $curTitle = $chart->getTitle()->getCaptionText() ;  // "Chart_12"
      $chart->getTitle()->setCaption("Quarterly Revenue Chart") ;
      break ;
    }

    // if you know the NAME (chart id) the chart
    // NAME is like the hidden chart index
    if ($chart->getName() == "chart12") {
      $chart->getTitle()->setCaption("New Chart Title") ;
      break ;
    }
  }