Scheme colors in Charts

[bridgeplayr]

This is:

- [x] 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)

See also issue #2299 and fix #2828

What is the expected behavior?

PhpSpreadsheet/Chart/DataSeriesValues.php and PhpSpreadsheet/Writer/Xlsx/Chart.php should support "Scheme colors" as well as RGB colors (both string and hex value), for lines, markers, and fill properties. Scheme colors are available in 4 "colorful" palettes and 11 "monochrome" palettes. These built-in palette colors are labeled as 'schemeClr', [string] in xml attributes.
E.g. xml snippet: <a:schemeClr val="accent6">

What is the current behavior?

Phpspreadsheet Chart does not currently support these colors, which are the default (automatic) colors when none is chosen by the user.

What are the steps to reproduce?

33_Chart_create_scatterManual.xlsx
Attached is a manually modified version of the chart produced by '33_Chart_create_scatter2.php' which demonstrates several unsupported properties related to SchemeClr, as well as unsupported line properties demonstrated below:
<c:spPr>
<a:ln w="31750" cap="sq" cmpd="tri"> width, cap type, compound type
<a:solidFill>
<a:schemeClr val="accent1"> palette color
<a:alpha val="60000"/> transparency ?
</a:schemeClr>
</a:solidFill>
<a:prstDash val="sysDash"/> dash type
<a:miter lim="800000"/> miter
<a:headEnd type="arrow" w="med" len="sm"/> arrow-head type, width, size
<a:tailEnd type="triangle" w="med" len="lg"/> arrow-tail type, with, size
</a:ln>
</c:spPr>

Note: Bezier (smooth) line property is the very last attribute of a data series:
...
</c:v>
</c:pt>
</c:numCache>
</c:numRef>
</c:yVal>
<c:smooth val="1"/> Bezier line designation

Marker properties

Markers types and sizes are now supported properties.

Marker fill colors and marker border colors are not supported quite right yet. (even in the cloned version from a week ago).

• Markers can have no fill color, and no border color. (Excel supplies an automatic schemeClr for the marker border in this case: like

<a:schemeClr val="accent1"> for the first dataseries,
<a:schemeClr val="accent2"> for the second, and so on.)

• A marker can have a border color, but no fill color.

e.g.
<a:ln>
<a:solidFill>
<a:srgbClr val="002060"/>
</a:solidFill>
</a:ln>

• A marker can have both a fill color and a border color. (In this case, the marker color must be an array.)

<c:marker>
<c:symbol val="square"/>
<c:size val="7"/>
<c:spPr>
<a:solidFill>
<a:schemeClr val="accent6">
<a:alpha val="97000"/>
</a:schemeClr>
</a:solidFill>
<a:ln>
<a:solidFill>
<a:srgbClr val="0FF000"/>
</a:solidFill>
</a:ln>
</c:spPr>
</c:marker>

I have not been successful in creating php code to implement these new properties with setter/getter methods, but they can probably be implemented in ./Chart/DataSeriesValues.php (& maybe ./Chart/Properties.php) and ./Writer/Xlsx/Chart.php

None of the above properties are propagated to any other files (like ./xl/drawings/drawingsN.xml) as I feared they might. Good news.

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 Calulations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

I have not experimented with any format other than xlsx.

Which versions of PhpSpreadsheet and PHP are affected?

1.23 & 7.3

I didn't forget. I'm just slow (& old).

[oleibman]

As you've noted, we've recently added Scheme colors for Chart fonts. The additional opportunities you've described are on my to-do list, but it may take a while. Thank you for providing a sample spreadsheet to use for testing when the time comes.

[oleibman]

Please test with PR #2906 if you are able. I'm not sure how easy that is since there are prerequisite changes since the last release. At the very least, I am able to copy your sample file accurately.

[bridgeplayr]

Yes. Glad to do it. From: oleibman ***@***.***> Sent: Wednesday, June 22, 2022 5:21 PM To: PHPOffice/PhpSpreadsheet ***@***.***> Cc: bridgeplayr ***@***.***>; Author ***@***.***> Subject: Re: [PHPOffice/PhpSpreadsheet] Scheme colors in Charts (Issue #2863) Please test with PR #2906 <#2906> if you are able. I'm not sure how easy that is since there are prerequisite changes since the last release. At the very least, I am able to copy your sample file accurately. — Reply to this email directly, view it on GitHub <#2863 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYXANOBRICP3OC7RWFIM2WDVQOUWDANCNFSM5XP3DLKA> . You are receiving this because you authored the thread. <https://github.com/notifications/beacon/AYXANOHD4AQ5OEY6JG4TEATVQOUWDA5CNFSM5XP3DLKKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOIVPBLWA.gif> Message ID: ***@***.*** ***@***.***> >

[bridgeplayr]

It took me some time to recall that the "connect-the-dots" property is named "scatterLines", with its getter/setter methods. That's ok, but it confuses me. In my attempted implementation, I added keys 'smooth' and 'connected' with boolean values in the property $lineStyleProperties. I then defined $scatterLine as an object described by the many getter/setter methods dealing with LineStyle/Color/Shadow/Glow/SoftEdge special effects. I'm still figuring out how to describe all of the properties of each DataSeriesValues objects in my proposed chart.

I must modify the methods which I was close to implementing. No worries. I am working hard to do it.

[bridgeplayr]

33_Chart_create_Color_scatter.php.txt

Success. Not the prettiest code you will see. Feel free to clean things up as you see fit.

[oleibman]

Please do not double-post in this thread and the PR. I have adopted several of your suggestions in my latest commit; your code sample is incorporated into the existing 33_chart_create_scatter2. There was a slight mismatch between your code and my changes; previously, you needed to set a marker shape in order to support marker colors, and your code didn't do that. I have changed so that this is no longer necessary; I explicitly set the marker shape for 2 of the 3 series in the updated sample, and leave it unset for the other.

Is "scatterlines" applicable to other types of chart? I can certainly consider making it part of line styles if so. I think I added it after the latest release, so that wouldn't be a breaking change at this point.

[bridgeplayr]

Got it. I don’t see how ‘scatterlines’ can apply to other chart types. Type LINECHART has the possible converse relation between points and line – the points can be hidden or displayed. With type SCATTERCHART, the points are always present, and the line can be hidden or displayed. However, I confess to small experience with any chart type than SCATTERCHARTs. From: oleibman ***@***.***> Sent: Saturday, June 25, 2022 11:46 PM To: PHPOffice/PhpSpreadsheet ***@***.***> Cc: bridgeplayr ***@***.***>; Author ***@***.***> Subject: Re: [PHPOffice/PhpSpreadsheet] Scheme colors in Charts (Issue #2863) Please do not double-post in this thread and the PR. I have adopted several of your suggestions in my latest commit; your code sample is incorporated into the existing 33_chart_create_scatter2. There was a slight mismatch between your code and my changes; previously, you needed to set a marker shape in order to support marker colors, and your code didn't do that. I have changed so that this is no longer necessary; I explicitly set the marker shape for 2 of the 3 series in the updated sample, and leave it unset for the other. Is "scatterlines" applicable to other types of chart? I can certainly consider making it part of line styles if so. I think I added it after the latest release, so that wouldn't be a breaking change at this point. — Reply to this email directly, view it on GitHub <#2863 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AYXANOAXGY4BCCUGRAUNWHDVQ74B5ANCNFSM5XP3DLKA> . You are receiving this because you authored the thread. <https://github.com/notifications/beacon/AYXANOH5HPOJ5QKWNNOTJNTVQ74B5A5CNFSM5XP3DLKKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOIWDFLNI.gif> Message ID: ***@***.*** ***@***.***> >

[bridgeplayr]

FYI, there is a typo in Chart/Properties.php
const LINE_STYLE_JOIN_ROUND = 'bevel'; -->'round'
const LINE_STYLE_JOIN_MITER = 'miter';
const LINE_STYLE_JOIN_BEVEL = 'bevel';

[oleibman]

I might be wrong, but it's deliberate, not a typo. When I tried setting the property to 'round' in Excel, I thought the XML nevertheless specified 'bevel'. If you think otherwise, I will try that experiment again.

[bridgeplayr]

I changed a chart line join property to 'round' in Excel, and the xml also showed 'round'.

[oleibman]

Okay, I will take another look.

[oleibman]

My latest push is probably the one where I'm done tinkering. Have a look. I did change 'round', but I haven't been successful in creating an Excel spreadsheet that uses it. Please upload yours and I'll incorporate some tests for it in my next PR.

[bridgeplayr]

Glad to do it.

[bridgeplayr]

Small changes to samples/Chart/33_Chart_create_scatter2.php:

• LINE_STYLE_DASH_SQUARE_DOT --> ...ROUND_DOT
• added linestyles to $yAxis to prove Writer can correctly handle it.

33_Chart_create_scatter2.1.php.txt
33_Chart_create_Color_scatter2.1.xlsx

[oleibman]

Why is ROUND_DOT a better test than SQUARE_DOT?
You did not include yAxis in the Chart. Even had you done so, it seems close or identical to the result from not including it. If I increase the width to, say, 1.75, there is at least a visual difference.
My comments about width were wrong - size is in points, not pixels.

[bridgeplayr]

Oops. WRONG round. Retrying with JOIN instead of DASH.

In my own version, I included the yAxis, but in the edited version of the released sample, I neglected to add it to the chart. If added, it will work. Yes, its impact is hard to see. But it is in the xml, which is all that counts.

[bridgeplayr]

Results of experiments with 33_Chart_create_scatter2.1.xlsx

• DSV 1: set join= 'bevel' got 'bevel'
• DSV 1: set join= 'round', got 'bevel', [confirming your observation]
• DSV 2: set join= 'miter', got 'miter'
• DSV 2: set join= 'round', got 'miter' ??
• I conclude that Excel ignores join settings of DSVs because other DSV settings override it.
• My guess: In the case of DSV 1, since it is a Bezier (smooth) line, JOIN==='bevel'.
• My guess: in the case of DSV 2, since it is straight line, JOIN==='miter'.

To try something else with LineStyle properties, I added a border around the Title, with these settings which I kept constant:

• width: w=1.5 pts
• dash: 'dashDot'
• color: val='accent2'
• color; alpha=0
• color: type='schemeClr'

Here are the results of changing the JOIN setting of the Title border:

• set join = 'round', got 'round' [So here is a case where 'round' -> 'round']
• set join = 'bevel', got 'bevel'
• set join = 'miter', got 'bevel' ??
• I conclude Excel changes the JOIN setting 'miter' to 'bevel' for a text box. Just because.
• Further, I conclude Excel applies some set of consistency rules for chart and drawing objects which we mere mortals can only guess at.

Bottom line:

• For at least one case, setting join property to 'round' actually produced a join property 'round'. So keep LINE_STYLE_JOIN_ROUND = 'round'.

[oleibman]

I will make one more push tonight. The results of using schemeClr for Axis lines wasn't entirely satisfactory - it's in the Xml, but you can't see it in Excel. This might be another case where Excel is acting on its own - perhaps accent2 used a certain way means one color, but used in another way means a different color. I will use srgbClr for this demonstration.

[bridgeplayr]

Well done. These are big improvements.
Many thanks for enabling me to move forward with my project. (But I have more requests...)