Add Trendline for Xlsx Charts - Revised complete solution and samples included

[bridgeplayr]

1. Create new class PhpSpeadSheet/Chart/TrendLine.php

TrendLine.php.txt
(remove ".txt")

2. Modify PhpSpeadSheet/Chart/DataSeriesValues.php

• add
  use PhpOffice\PhpSpreadsheet\Chart\TrendLine;

• add to the list of properties (note original suggestion of scalar $trendLine is replaced by array $trendLines[] because Excel supports up to 3 trend lines for each DataSeriesValues dataset

    /** @var array of Trendlines*/
    private $trendLines = [];

• add getter/setter methods

    public function setTrendLines($trendLines): self
    {
        $this->trendLines = $trendLines;

        return $this;
    }

    public function getTrendLines($indx = null)
    {
        if (is_numeric($indx)) {
            return $this->trendLines[$indx]; // return one trendLine
        } else { 
            return $this->trendLines; // return the array if no indx provided
      }

        return $this;

    }

3. Modify PhpSpeadSheet/Writer/Xlsx/Chart.php

• add
  use PhpOffice\PhpSpreadsheet\Chart\TrendLine;

• following existing code

//    Formatting for the points (markers)
    ...
            if (($groupType === DataSeries::TYPE_BARCHART) || ($groupType === DataSeries::TYPE_BARCHART_3D) || ($groupType === DataSeries::TYPE_BUBBLECHART)) {
                $objWriter->startElement('c:invertIfNegative');
                $objWriter->writeAttribute('val', 0);
                $objWriter->endElement();
            }

• add

            // Trendlines
            $trendLines = $plotSeriesValues->getTrendLines(); // returns array
            if ($trendLines) {
                $trendLineCount = count($trendLines);
                for ($i=0; $i<$trendLineCount; $i++) {
                    $trendLine = $plotSeriesValues->getTrendLines($i);

                    $trendLineType  = $trendLine->getTrendLineType();
                    $order          = $trendLine->getOrder(); 
                    $period         = $trendLine->getPeriod(); 
                    $dispRSqr       = $trendLine->getDispRSqr(); 
                    $dispEq         = $trendLine->getDispEq(); 
                    $trendLineColor = $trendLine->getLineColor(); // ChartColor
                    $trendLineWidth = $trendLine->getLineStyleProperty('width');
            
                    $objWriter->startElement('c:trendline'); // N.B. lowercase 'ell'
                    $objWriter->startElement('c:spPr');

                    if ($trendLineColor == null)
                    {   // use dataSeriesValues line color as a backup if $trendLineColor is null
                        $dsvLineColor = $plotSeriesValues->getLineColor();
                        if ($dsvLineColor) { 
                            $trendLine->getLineColor()
                                      ->setColorProperties($dsvLineColor['value'],$dsvLineColor['alpha'],$dsvLineColor['type']);
                        }
                    } // otherwise, hope Excel does the right thing
            
                    $this->writeLineStyles($objWriter, $trendLine, false); // suppress noFill
            
                    $objWriter->endElement(); // spPr
            
                    $objWriter->startElement('c:trendlineType'); // N.B lowercase 'ell'
                    $objWriter->writeAttribute('val', $trendLineType);
                    $objWriter->endElement(); // trendlineType
                    if ($trendLineType == 'poly')
                    {   $objWriter->startElement('c:order');
                        $objWriter->writeAttribute('val', $order);
                        $objWriter->endElement(); // order
                    }
                    if ($trendLineType == 'movingAvg') 
                    {
                        $objWriter->startElement('c:period');
                        $objWriter->writeAttribute('val', $period);
                        $objWriter->endElement(); // period
                    }
                    $objWriter->startElement('c:dispRSqr');
                    $objWriter->writeAttribute('val', (int) $dispRSqr);
                    $objWriter->endElement();
                    $objWriter->startElement('c:dispEq');
                    $objWriter->writeAttribute('val', (int) $dispEq);
                    $objWriter->endElement();  
                    if ($groupType === DataSeries::TYPE_SCATTERCHART || $groupType === DataSeries::TYPE_LINECHART )
                    {
                        $objWriter->startElement('c:trendlineLbl');
                        $objWriter->startElement('c:numFmt');
                        $objWriter->writeAttribute('formatCode', (string) 'General');
                        $objWriter->writeAttribute('sourceLinked', (int) 0);
                        $objWriter->endElement();  // numFmt
                        $objWriter->endElement();  // trendlineLbl
                    }
            
                    $objWriter->endElement(); // trendline
                }
            }

4. Add chart creator code samples\Chart\33_Chart_create_scatter_trendline.php
   33_scatter_w_trendlines.php.txt (remove .txt and edit file to match PHPSS file naming and saving conventions)

• I added more datapoints to the data table.
• I added a separate worksheet for the charts.
• I created two charts; the first with 3 DSVs and no trend lines; the second with only 1 DSV, but with 3 different Trend Lines to demonstrate different 'fits'. R squared and equations are displayed.
• I add Marker, Marker Color and Marker Size in each instantiation of the DataSeriesValues to reduce the method calls, if only slightly.

5. Add template Xlsx chart produced by above sample:
   33_Chart_create_scatter_trendlines.xlsx