Prevent Writer/Xlsx/Chart.php corrupting Excel scattercharts (& maybe other number type charts)

[bridgeplayr]

This is:

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

produce xlsx Excel charts via Phpspreadsheet methods which Excel can read

What is the current behavior?

Excel refuses to display scattercharts, but will offer to open a spreadsheet after removing all charts. A pop-up window from Excel states something like "We found a problem with some content in 'xxxxx.xlsx'. Do you want us to try to recover as much as
we can? If you trust the source of this workbook, click Yes."
If you click on 'yes', another pop-up box states in the banner "Excel was able to open the file by repairing or removing the unreadable content", and this message in the window, "Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)"
An accompanying logfile 'C:\Users\xxx\AppData\Local\Temp\error101760_nn.xml'
contains (xml delimiters removed)

xml version="1.0" encoding="UTF-8" standalone="yes"
recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
logFileName>error101760_09.xml</logFileName
summary>Errors were detected in file 'H:\Documents\bridge\BridgeDB\code\4934180_Charts.xlsx'</summary
removedParts
removedPart>Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)</removedPart
/removedParts
/recoveryLog

What are the steps to reproduce?

Execute phpspreadsheet/samples/Chart/33_Chart_create_scatter.php and try to open the spreadsheet in Excel.

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?

xlsx scattercharts affected

Which versions of PhpSpreadsheet and PHP are affected?

1.23.0
7.4.29

[bridgeplayr]

Modify Phpspreadsheet/Writer/Xlsx/Chart.php:
I failed in my attempts to insert the code snippets. Attached file contains my code suggestions.
HowToPreventPhpSpreadsheetFromCorruptingCharts-File-WriterXlsxChart.txt

The problem is simple. The x-axis in some charts contains string ("category") data, while the x-axis in other charts (viz. scattercharts and bubblecharts) contains numeric ("value") data. The distinction is important in function writeCategoryAxis(), which must insert characteristic 'c:catAx' for string data, but 'c:valAx' for numeric data. (N.B. you must examine the last parameter, "$yaxis" because this function is called when writing BOTH the xAxis AND the yAxis in function writePlotGroup()). I suggest examining the FORMAT_CODE_type of the axis, but I am at a loss to suggest what to do if the data format is "General". The default of 'c:catAx' is probably ok.

The issue of handling both string and numeric data must also be decided correctly in function writePlotGroup() where the choice of string data vs value data requires inserting either 'c:xVal' or 'c:cat'. This distinction requires additional testing of the X-axis (Category) label's dataseriesvalues datatype :

$CategoryDatatype = $plotSeriesCategory->getDataType();
If the type is numeric then
$this->writePlotSeriesValues($plotSeriesCategory, $objWriter, $groupType, 'num');
otherwise,
$this->writePlotSeriesValues($plotSeriesCategory, $objWriter, $groupType, 'str');

[oleibman]

I don't see a corruption problem with 33_Chart_create_scatter - same release of PhpSpreadsheet and Php as you, Excel 365 Apps for Enterprise (see attached image). We are aware that there are some formatting problems with scatter charts, which might or might not apply to 33*, and which might or might not be addressed by your proposed fix - please let me know if you think either is the case. Also, please elaborate on "failed in my attempt to insert the code snippets"; perhaps we can figure out what you need to do to get your code changes and tests to succeed.

[bridgeplayr]

You are correct. [Blush] Here is a modified version of the 33_Chart_create_scatter which demonstrates the issue. (NOW I see how to insert code.) I changed the data table to demonstrate a trend chart of 3 measurements taken at 4 dates. I changed the output spreadsheet to '33_Chart_create_scatterModified.xlsx'. Each 'metric' measurement (yAxis) now has an accompanying X-axis date - so there are 3 xAxis dataseries instead of only 1. Each data point now has two coordinates - one xAxis value and one yAxis value. (This matches my idea of a scatterchart.)

The chart '33_Chart_create_scatterModified.xlsx' produced in the modified script will be removed by Excel as described in my first post. Adding my proposed code changes to Phpspreadsheet/Writer/Xlsx/Chart.php causes the following entries in the spreadsheet's "xl/charts/chart1.xml" file and makes it acceptable to Excel:

WAS                        IS
<c:xVal>               (same)
<c:strRef>           <c:numRef>
<c:f>Worksheet:$A2:$A5   (same)
</c:f>                  (same)
<c:strCache>      <c:numCache>

(The matching terminating entities also are changed from 'str' to 'num'.)

Each of the 3 xAxis date-datasets are identically mischaracterized as 'str' instead of 'num'.
(Excel still refuses to display xAxis as dates until you manually change axis datatype from General to Date. Grr. Another issue to address... My code change somehow ignores the xAxis FORMAT_CODE_DATE designation. Sigh.)

Here is the modified 33_Chart_create_scatter.php

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
// changed data to simulate a trend chart - Xaxis are dates; Yaxis are 3 meausurements from each date
$worksheet->fromArray(
    [
        ['',              'metric1', 'metric2', 'metric3'],
        ['=DATEVALUE("1/1/2021")', 12.1, 15.1, 21.1],
        ['=DATEVALUE("1/4/2021")', 56.2, 73.2, 86.2],
        ['=DATEVALUE("1/7/2021")', 52.2, 61.2, 69.2],
        ['=DATEVALUE("1/10/2021")', 30.2, 32.2, 0.2],
    ]
);

// Set the Labels for each data series we want to plot
//     Datatype
//     Cell reference for data
//     Format Code
//     Number of datapoints in series
//     Data values
//     Data Marker
$dataSeriesLabels = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1', null, 1), // was 2010
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // was 2011
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$D$1', null, 1), // was 2012
];
// Set the X-Axis Labels
// changed from STRING to NUMBER
// added 2 additional x-axis values associated with each of the 3 metrics
// added FORMATE_CODE_NUMBER
$xAxisTickValues = [
    //new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$A$2:$A$5', Properties::FORMAT_CODE_DATE, 4),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$A$2:$A$5', Properties::FORMAT_CODE_DATE, 4),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$A$2:$A$5', Properties::FORMAT_CODE_DATE, 4),
];
// Set the Data values for each data series we want to plot
//     Datatype
//     Cell reference for data
//     Format Code
//     Number of datapoints in series
//     Data values
//     Data Marker
// added FORMAT_CODE_NUMBER
$dataSeriesValues = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$B$5', Properties::FORMAT_CODE_NUMBER, 4),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', Properties::FORMAT_CODE_NUMBER, 4),
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$D$2:$D$5', Properties::FORMAT_CODE_NUMBER, 4),
];
  // Added so that Xaxis shows dates instead of Excel-equivalent-year1900-numbers
$xAxis = new Axis();
$xAxis->setAxisNumberProperties(Properties::FORMAT_CODE_DATE );

// Build the dataseries
$series = new DataSeries(
    DataSeries::TYPE_SCATTERCHART, // plotType
    null, // plotGrouping (Scatter charts don't have any grouping)
    range(0, count($dataSeriesValues) - 1), // plotOrder
    $dataSeriesLabels, // plotLabel
    $xAxisTickValues, // plotCategory
    $dataSeriesValues, // plotValues
    null, // plotDirection
    null, // smooth line
    //DataSeries::STYLE_LINEMARKER  // plotStyle
    DataSeries::STYLE_MARKER  // plotStyle
);

// Set the series in the plot area
$plotArea = new PlotArea(null, [$series]);
// Set the chart legend
$legend = new ChartLegend(ChartLegend::POSITION_TOPRIGHT, null, false);

$title = new Title('Test Scatter Trend Chart');
$yAxisLabel = new Title('Value ($k)');

// Create the chart
$chart = new Chart(
    'chart1', // name
    $title, // title
    $legend, // legend
    $plotArea, // plotArea
    true, // plotVisibleOnly
    DataSeries::EMPTY_AS_GAP, // displayBlanksAs
    null, // xAxisLabel
    $yAxisLabel,  // yAxisLabel
    // added xAxis for correct date display
    $xAxis, // xAxis
);

// Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('A7');
$chart->setBottomRightPosition('H20');

// Add the chart to the worksheet
$worksheet->addChart($chart);

// Save Excel 2007 file
$filename = '33_Chart_create_scatterModified.xlsx';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$callStartTime = microtime(true);
$writer->save($filename);
`