Add support for vertical Gridlines

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

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

// 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]

This Chart generator is similar to the one in #2967

33_LineChart_DateAxis_VertGridlines.php.txt

• This issue resolution builds on the modifications suggested in Add support for Date Axis in Line Charts #2967 (I should probably have combined these two issues.)

• Modify Writer/Xlsx/Chart.php : change private function writePlotArea() to support $majorGridlines & $minorGridlines

        if (($chartType !== DataSeries::TYPE_PIECHART) && ($chartType !== DataSeries::TYPE_PIECHART_3D) && ($chartType !== DataSeries::TYPE_DONUTCHART)) {
            if ($chartType === DataSeries::TYPE_BUBBLECHART) {
                $this->writeValueAxis($objWriter, $xAxisLabel, $chartType, $id2, $id1, $catIsMultiLevelSeries, $xAxis, $majorGridlines, $minorGridlines);
            } else {
                // $this->writeCategoryAxis($objWriter, $xAxisLabel, $id1, $id2, $catIsMultiLevelSeries, $xAxis);
                $this->writeCategoryAxis($objWriter, $xAxisLabel, $id1, $id2, $catIsMultiLevelSeries, $xAxis, $majorGridlines, $minorGridlines);
            }

• change argument list for private function writeCategoryAxis(), and add writing xml supporting majorGridlines and minorGridlines.

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

        if ($majorGridlines->getObjectState()) {
          $objWriter->startElement('c:majorGridlines');
          $objWriter->startElement('c:spPr');

          $this->writeLineStyles($objWriter, $majorGridlines);

          $objWriter->startElement('a:effectLst');
          $this->writeGlow($objWriter, $majorGridlines);
          $this->writeShadow($objWriter, $majorGridlines);
          $this->writeSoftEdge($objWriter, $majorGridlines);

          $objWriter->endElement(); //end effectLst
          $objWriter->endElement(); //end spPr
          $objWriter->endElement(); //end majorGridLines
        }

        if ($minorGridlines->getObjectState()) {
            $objWriter->startElement('c:minorGridlines');
            $objWriter->startElement('c:spPr');

            $this->writeLineStyles($objWriter, $minorGridlines);

            $objWriter->startElement('a:effectLst');
            $this->writeGlow($objWriter, $minorGridlines);
            $this->writeShadow($objWriter, $minorGridlines);
            $this->writeSoftEdge($objWriter, $minorGridlines);
            $objWriter->endElement(); //end effectLst

            $objWriter->endElement(); //end spPr
            $objWriter->endElement(); //end minorGridLines
        }

33_LineChart_DateAxis_Gridline.xlsx

[oleibman]

Does #2923, which was merged in time for the 1.24 release, not take care of this?

[bridgeplayr]

I missed it, darn it. Let me check the implementation, and I’ll comment further. (Thanks for the speedy notice!) From: oleibman ***@***.***> Sent: Thursday, July 28, 2022 9:31 PM To: PHPOffice/PhpSpreadsheet ***@***.***> Cc: bridgeplayr ***@***.***>; Author ***@***.***> Subject: Re: [PHPOffice/PhpSpreadsheet] Add support for vertical Gridlines (Issue #2969) Does #2923 <#2923> , which was merged in time for the 1.24 release, not take care of this? — Reply to this email directly, view it on GitHub <#2969 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYXANOGBMVBPXNQMHWM6ZMTVWNNBJANCNFSM547KTTIQ> . You are receiving this because you authored the thread. <https://github.com/notifications/beacon/AYXANOBVNVIHHGA67HV536DVWNNBJA5CNFSM547KTTI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOI52UUUA.gif> Message ID: ***@***.*** ***@***.***> >

[bridgeplayr]

My vertical Gridlines issue #2969 is predicated on adding support for a “date” axis, issue #2967. The “date” axis is available in Excel only for LineCharts - not available on ScatterCharts, which I find maddening. After adding a Date Axis, then needing vertical gridlines becomes clear. Without a Date axis, the x-axis is simply some number of days, and the user is unable to demarcate weeks, or months, or quarters. However, with a Date axis, the user can put in tick marks for days, weeks, months, quarters, years. The two attachments demonstrate the value of tick marks on the “date” axis, which is not possible without it. Now, with visible tick-marks, vertical Gridlines seems like the obvious next step. I have just reviewed your merged PR 2923, and I remember having read it. I chose my path to resolution because you didn’t comprehend the “date” axis feature, which is crucial for me. [I also want to follow up issue #2823 on DateRendering, which is not done correctly in DATEVALUE. I had to invent a new function to correctly render “4/1/2001” and distinguish it from “1/4/2001”. I can do it correctly in pure php functions, but PHPSS’s DATEVALUE implementation has no option to force it to follow the user’s date specification. My implementation comment for issue #2967 includes my function xlDateValue($datestr) which returns the correct numeric date when the date string is formatted as mm/dd/yyyy. This one is pretty high on my bitch-list.] From: oleibman ***@***.***> Sent: Thursday, July 28, 2022 9:31 PM To: PHPOffice/PhpSpreadsheet ***@***.***> Cc: bridgeplayr ***@***.***>; Author ***@***.***> Subject: Re: [PHPOffice/PhpSpreadsheet] Add support for vertical Gridlines (Issue #2969) Does #2923 <#2923> , which was merged in time for the 1.24 release, not take care of this? — Reply to this email directly, view it on GitHub <#2969 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYXANOGBMVBPXNQMHWM6ZMTVWNNBJANCNFSM547KTTIQ> . You are receiving this because you authored the thread. <https://github.com/notifications/beacon/AYXANOBVNVIHHGA67HV536DVWNNBJA5CNFSM547KTTI2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOI52UUUA.gif> Message ID: ***@***.*** ***@***.***> >

[MarkBaker]

[I also want to follow up issue #2823 on DateRendering, which is not done correctly in DATEVALUE. I had to invent a new function to correctly render “4/1/2001” and distinguish it from “1/4/2001”. I can do it correctly in pure php functions, but PHPSS’s DATEVALUE implementation has no option to force it to follow the user’s date specification. My implementation comment for issue #2967 includes my function xlDateValue($datestr) which returns the correct numeric date when the date string is formatted as mm/dd/yyyy. This one is pretty high on my bitch-list.]

MS Excel differentiates 1/4/2001 from 4/1/2001 based on the locale settings: entering '1/4/2001' when locale is set to us would set the internal serialized timestamp value to 36895 (4th January 2001); when the locale is not us, entering '1/4/2001' then the internal serialized timestamp value is 36982 (1st April 2001).
As PhpSpreadsheet doesn't maintain a locale for date/time settings, and uses basic PHP rules for converting a date string to an Excel serialized timestamp (if the separator between day and month is a /, then it assumes US). The internal conversion code does try to correct this if it can recognise that the resulting date would be invalid (e.g. 13/1 couldn't be us, because there is no month 13 in the calendar), but can't disambiguate 1/4 from 4/1, because both could be valid.

Until such time as we provide a locale setting (remembering that this would have to be set by the developer, even if we provided a default, because it isn't maintained in anywhere in an Excel file), then any changes to date conversion from string would be a bc break