Add support for Date Axis in Line Charts

[bridgeplayr]

This is:

- [ ] a bug report
- [x] 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?

What is the current behavior?

Supported in Excel; not available in PHPss

What are the steps to reproduce?

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

// add code that show the issue here...

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

Which versions of PhpSpreadsheet and PHP are affected?

1.23 7.3

[bridgeplayr]

Sample spreadsheet creator is derived from samples/Chart/33_Chart_create_scatter.php
33_LineChart_DateAxis.php.txt

• Data remains in worksheet "Data" with 8 datapoints instead of 4
• some dates changed to not end on quarter boundaries
• date format changed to mm/dd/yyyy, to show that PhpSpreadsheet\Calculation\DateTimeExcel does not correctly calculate the date number
• Charts are moved to a separate worksheet
• 2 Charts are created in worksheet "Scatter+Line Chart"
  • ScatterChart showing dates but no tickmarks
  • LineChart showing dates with tickmarks determined by user

Additional functions are required:

function DateRange ($nrows, $Wrkbk) {
  $Datasheet = $Wrkbk->getSheetByName('Data');

  // start the xaxis at the beginning of the quarter of the first date
  $startDateStr = $Datasheet->getCell('B2')->getValue(); // mm/dd/yyyy date string
  $startDate    = date_create_from_format('m/d/Y', $startDateStr); // php date obj

  // get date of first day of the quarter of the start date
  $startMonth   = date_format($startDate ,'n'); // suppress leading zero
  $startYr      = date_format($startDate ,'Y'); 
  $qtr          = intdiv($startMonth, 3)+ (($startMonth % 3 > 0) ? 1:0);
  $qtrStartMonth= 1+ (($qtr-1) * 3);
  $qtrStartStr  = "$qtrStartMonth/1/$startYr";
  $ExcelQtrStartDateVal = xlDateValue($qtrStartStr); // derived from pure php code

  // end the xaxis at the end of the quarter of the last date
  $lastDateStr   = $Datasheet->getCellByColumnAndRow(2, $nrows+1)->getValue();
  $lastDate      = date_create_from_format('m/d/Y', $lastDateStr); 
  $lastMonth     = date_format($lastDate, 'n'); 
  $lastYr        = date_format($lastDate, 'Y'); 
  $qtr          = intdiv($lastMonth, 3) + (($lastMonth % 3 > 0) ? 1:0);
  $qtrEndMonth  = 3+ (($qtr-1) * 3);
  $lastDOM      = cal_days_in_month(CAL_GREGORIAN, $qtrEndMonth,$lastYr);
  $qtrEndStr    = "$qtrEndMonth/$lastDOM/$lastYr";
  $ExcelQtrEndDateVal = xlDateValue($qtrEndStr); // derived from pure php code

  $minMaxDates = ['min' => $ExcelQtrStartDateVal, 'max' => $ExcelQtrEndDateVal ];
  return $minMaxDates;
}

function xlDateValue($datestr) {
  // must invent this function since phpSpreadsheet's DATEVALUE implementation 
  // insists that 1/10/2020 is Oct 1 instead of Jan 10, proud that it ignores 
  // any date formatting that the user might impose on the date string.
  $dayOne       = date_create_from_format('m/d/Y', "1/1/1900");
  $dateObj      = date_create_from_format('m/d/Y', $datestr); 
  $daysAfterOne = date_diff($dayOne, $dateObj);
  $excelDateVal = $daysAfterOne->format("%a");
  // no explanation for requirement to add 2; just accept it
  return 2+$excelDateVal; // according to php as opposed to phpss
}

• Add to Chart/Properties.php

    const
        TIME_UNIT_DAYS = 'days';
    const TIME_UNIT_MONTHS = 'months';
    const TIME_UNIT_YEARS = 'years';

• Modify Chart/Axis.php - add majorTimeUnit, minorTimeUnit, baseTimeUnit, and auto

  private $axisOptions = [
  'minimum' => null,
  'maximum' => null,
  'majorTimeUnit' => self::TIME_UNIT_YEARS,
  'minorTimeUnit' => self::TIME_UNIT_MONTHS,
  'baseTimeUnit' => self::TIME_UNIT_DAYS,
  'major_unit' => null,
  'minor_unit' => null,
  'orientation' => self::ORIENTATION_NORMAL,
  'minor_tick_mark' => self::TICK_MARK_NONE,
  'major_tick_mark' => self::TICK_MARK_NONE,
  'axis_labels' => self::AXIS_LABELS_NEXT_TO,
  'horizontal_crosses' => self::HORIZONTAL_CROSSES_AUTOZERO,
  'horizontal_crosses_value' => null,
  'auto' => null,
  ];

  /**

  • Set Axis Options Properties.
    */
    public function setAxisOptionsProperties(
    string $axisLabels,
    ?string $horizontalCrossesValue = null,
    ?string $horizontalCrosses = null,
    ?string $axisOrientation = null,
    ?string $majorTmt = null,
    ?string $minorTmt = null,
    ?string $minimum = null,
    ?string $maximum = null,
    ?string $majorUnit = null,
    ?string $minorUnit = null,
    ?string $majorTimeUnit = null,
    ?string $minorTimeUnit = null,
    ?string $baseTimeUnit = null,
    ?string $auto = '1'
    ): void {
    $this->axisOptions['axis_labels'] = $axisLabels;
    $this->setAxisOption('horizontal_crosses_value', $horizontalCrossesValue);
    $this->setAxisOption('horizontal_crosses', $horizontalCrosses);
    $this->setAxisOption('orientation', $axisOrientation);
    $this->setAxisOption('major_tick_mark', $majorTmt);
    $this->setAxisOption('minor_tick_mark', $minorTmt);
    $this->setAxisOption('minimum', $minimum);
    $this->setAxisOption('maximum', $maximum);
    $this->setAxisOption('major_unit', $majorUnit);
    $this->setAxisOption('minor_unit', $minorUnit);
    $this->setAxisOption('majorTimeUnit', $majorTimeUnit);
    $this->setAxisOption('minorTimeUnit', $minorTimeUnit);
    $this->setAxisOption('baseTimeUnit', $baseTimeUnit);
    $this->setAxisOption('auto', $auto); // allow Excel to determine axis type
    }

• Add 'dateAx' -- LineCharts only; not available in ScatterCharts

    public function setAxisType(string $type): self
    {
        if ($type === 'catAx' || $type === 'valAx' || $type === 'dateAx') {
            $this->axisType = $type;
        } else {
            $this->axisType = '';
        }

        return $this;
    }

• modify Writer/Xlsx/Chart.php

• modify writeCategoryAxis()
  

    private function writeCategoryAxis(XMLWriter $objWriter, ?Title $xAxisLabel, $id1, $id2, $isMultiLevelSeries, Axis $yAxis, GridLines $majorGridlines, GridLines $minorGridlines): void
    {
        // N.B. writeCategoryAxis may be invoked with $xAxis substituted for the last parameter($yAxis) for ScatterChart, etc
        // In that case, xAxis may contain values like the yAxis, or it may be a date axis (LINECHART).
        $AxisType = $yAxis->getAxisType() ;
        if ($AxisType !== '') {
            $objWriter->startElement('c:' . $AxisType);
        } elseif ($yAxis->getAxisIsNumericFormat()) {
            $objWriter->startElement('c:valAx');
        } else {
            $objWriter->startElement('c:catAx');
        }

• below line $objWriter->startElement('c:auto'); -- modify value of 'auto'

        // $objWriter->writeAttribute('val', 1); // LineChart with dateAx wants '0', but we don't pass in $Chart to test; could test for axisType == 'dateAx'
`        $objWriter->writeAttribute('val', (int) $yAxis->getAxisOptionsProperty('auto'));`
        $objWriter->endElement();

        $objWriter->startElement('c:lblAlgn');
        $objWriter->writeAttribute('val', 'ctr');
        $objWriter->endElement();

        $objWriter->startElement('c:lblOffset');
        $objWriter->writeAttribute('val', 100);
        $objWriter->endElement();

        if (!empty($yAxis->getAxisOptionsProperty('baseTimeUnit')) && $AxisType == 'dateAx') {
            $objWriter->startElement('c:baseTimeUnit');
            $objWriter->writeAttribute('val', $yAxis->getAxisOptionsProperty('baseTimeUnit')) ;
            $objWriter->endElement();
        }

        if (!empty($yAxis->getAxisOptionsProperty('majorTimeUnit')) && $AxisType == 'dateAx') {
            $objWriter->startElement('c:majorTimeUnit');
            $objWriter->writeAttribute('val', $yAxis->getAxisOptionsProperty('majorTimeUnit')) ;
            $objWriter->endElement();
        }

        if (!empty($yAxis->getAxisOptionsProperty('minorbaseTimeUnit')) && $AxisType == 'dateAx') {
            $objWriter->startElement('c:minorTimeUnit');
            $objWriter->writeAttribute('val', $yAxis->getAxisOptionsProperty('minorTimeUnit')) ;
            $objWriter->endElement();
        }

        if ($isMultiLevelSeries) {
            $objWriter->startElement('c:noMultiLvlLbl');
            $objWriter->writeAttribute('val', 0);
            $objWriter->endElement();
        }
        $objWriter->endElement(); // c:$AxisType
    }

• modify method writePlotGroup()
• add TYPE_LINECHART qualifier to $nofill assignment

$nofill = $groupType == DataSeries::TYPE_LINECHART || $groupType == DataSeries::TYPE_STOCKCHART || ($groupType === DataSeries::TYPE_SCATTERCHART && !$plotSeriesValues->getScatterLines());
33_LineChart_DateAxis.xlsx