Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Previous pie chart's colors are lost when loading an existing file #2219

Closed
Azzurvif opened this issue Jul 13, 2021 · 1 comment · Fixed by #2879
Closed

Previous pie chart's colors are lost when loading an existing file #2219

Azzurvif opened this issue Jul 13, 2021 · 1 comment · Fixed by #2879
Labels

Comments

@Azzurvif
Copy link

Azzurvif commented Jul 13, 2021

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?

Previous pie charts must keep their style and colors

What is the current behavior?

All previous chart lose their style and colors

What are the steps to reproduce?

  1. Create a spreadsheet with phpspreadsheet
  2. Create a pie chart
  3. Save it
  4. Reload it with a new reader
  5. Add a new pie chart
  6. Save it

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 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Layout;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$filename='bugchart.xlsx';

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray(
    [
        ['', 2010, 2011, 2012],
        ['Q1', 12, 15, 21],
        ['Q2', 56, 73, 86],
        ['Q3', 52, 61, 69],
        ['Q4', 30, 32, 0],
    ]
);

// Custom colors for dataSeries (gray, blue, red, orange)
$colors = [
    'cccccc', '00abb8', 'b8292f', 'eb8500',
];

// 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
$dataSeriesLabels1 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
];
// Set the X-Axis Labels
//     Datatype
//     Cell reference for data
//     Format Code
//     Number of datapoints in series
//     Data values
//     Data Marker
$xAxisTickValues1 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
];
// 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
//     Custom colors
$dataSeriesValues1 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4, [], null, $colors),
];

// Build the dataseries
$series1 = new DataSeries(
    DataSeries::TYPE_PIECHART, // plotType
    null, // plotGrouping (Pie charts don't have any grouping)
    range(0, count($dataSeriesValues1) - 1), // plotOrder
    $dataSeriesLabels1, // plotLabel
    $xAxisTickValues1, // plotCategory
    $dataSeriesValues1          // plotValues
);

// Set up a layout object for the Pie chart
$layout1 = new Layout();
$layout1->setShowVal(true);
$layout1->setShowPercent(true);

// Set the series in the plot area
$plotArea1 = new PlotArea($layout1, [$series1]);
// Set the chart legend
$legend1 = new Legend(Legend::POSITION_RIGHT, null, false);

$title1 = new Title('Test Pie Chart');

// Create the chart
$chart1 = new Chart(
    'chart1', // name
    $title1, // title
    $legend1, // legend
    $plotArea1, // plotArea
    true, // plotVisibleOnly
    DataSeries::EMPTY_AS_GAP, // displayBlanksAs
    null, // xAxisLabel
    null   // yAxisLabel - Pie charts don't have a Y-Axis
);

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

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

// SAVE AND RELOAD
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$writer->save($filename);

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($filename);
$worksheet = $spreadsheet->getActiveSheet();

// 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
$dataSeriesLabels2 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$1', null, 1), // 2011
];
// Set the X-Axis Labels
//     Datatype
//     Cell reference for data
//     Format Code
//     Number of datapoints in series
//     Data values
//     Data Marker
$xAxisTickValues2 = [
    new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$A$2:$A$5', null, 4), // Q1 to Q4
];
// 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
//     Custom colors
$dataSeriesValues2 = [
    $dataSeriesValues2Element = new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$2:$C$5', null, 4),
];
$dataSeriesValues2Element->setFillColor($colors);

// Build the dataseries
$series2 = new DataSeries(
    DataSeries::TYPE_DONUTCHART, // plotType
    null, // plotGrouping (Donut charts don't have any grouping)
    range(0, count($dataSeriesValues2) - 1), // plotOrder
    $dataSeriesLabels2, // plotLabel
    $xAxisTickValues2, // plotCategory
    $dataSeriesValues2        // plotValues
);

// Set up a layout object for the Pie chart
$layout2 = new Layout();
$layout2->setShowVal(true);
$layout2->setShowCatName(true);

// Set the series in the plot area
$plotArea2 = new PlotArea($layout2, [$series2]);

$title2 = new Title('Test Donut Chart');

// Create the chart
$chart2 = new Chart(
    'chart2', // name
    $title2, // title
    null, // legend
    $plotArea2, // plotArea
    true, // plotVisibleOnly
    DataSeries::EMPTY_AS_GAP, // displayBlanksAs
    null, // xAxisLabel
    null   // yAxisLabel - Like Pie charts, Donut charts don't have a Y-Axis
);

// Set the position where the chart should appear in the worksheet
$chart2->setTopLeftPosition('I7');
$chart2->setBottomRightPosition('P20');

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

// Save Excel 2007 file
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
$writer->save($filename);
?>

### Which versions of PhpSpreadsheet and PHP are affected?
1.18.0 (latest atm)
@Azzurvif Azzurvif changed the title Pie chart's colors are lost on previous sheets Previous pie chart's colors are lost when loading an existing file Jul 13, 2021
@oleibman
Copy link
Collaborator

oleibman commented Jun 8, 2022

Thank you for raising this issue, and for the nice sample code you provided. Pie charts store their fill colors in a manner which PhpSpreadsheet is not yet set up to read. So, (a) it is not necessary to add a second chart to demonstrate this problem, and (b) it may apply to other types of charts as well. I am investigating what needs to be done to add the proper support to the Xlsx reader.

oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Jun 10, 2022
Fix PHPOffice#2219. Address some, but not all, issues mentioned in PHPOffice#2863.

For Pie Charts, fill color is stored in XML as part of dPt node, which had been ignored by Reader/Xlsx/Chart. Add support for it, including when specified as schemeClr or prstClr rather than srgbClr. Add support for prstClr in other cases where schemeClr is supported.
oleibman added a commit that referenced this issue Jun 14, 2022
* Expand Chart Support for schemeClr and prstClr

Fix #2219. Address some, but not all, issues mentioned in #2863.

For Pie Charts, fill color is stored in XML as part of dPt node, which had been ignored by Reader/Xlsx/Chart. Add support for it, including when specified as schemeClr or prstClr rather than srgbClr. Add support for prstClr in other cases where schemeClr is supported.

* Update Change Log

Add this PR.
MarkBaker added a commit that referenced this issue Jul 9, 2022
Note that this will be the last 1.x branch release before the 2.x release. We will maintain both branches in parallel for a time; but users are requested to update to version 2.0 once that is fully available.

### Added

- Added `removeComment()` method for Worksheet [PR #2875](https://github.com/PHPOffice/PhpSpreadsheet/pull/2875/files)
- Add point size option for scatter charts [Issue #2298](#2298) [PR #2801](#2801)
- Basic support for Xlsx reading/writing Chart Sheets [PR #2830](#2830)

  Note that a ChartSheet is still only written as a normal Worksheet containing a single chart, not as an actual ChartSheet.

- Added Worksheet visibility in Ods Reader [PR #2851](#2851) and Gnumeric Reader [PR #2853](#2853)
- Added Worksheet visibility in Ods Writer [PR #2850](#2850)
- Allow Csv Reader to treat string as contents of file [Issue #1285](#1285) [PR #2792](#2792)
- Allow Csv Reader to store null string rather than leave cell empty [Issue #2840](#2840) [PR #2842](#2842)
- Provide new Worksheet methods to identify if a row or column is "empty", making allowance for different definitions of "empty":
  - Treat rows/columns containing no cell records as empty (default)
  - Treat cells containing a null value as empty
  - Treat cells containing an empty string as empty

### Changed

- Modify `rangeBoundaries()`, `rangeDimension()` and `getRangeBoundaries()` Coordinate methods to work with row/column ranges as well as with cell ranges and cells [PR #2926](#2926)
- Better enforcement of value modification to match specified datatype when using `setValueExplicit()`
- Relax validation of merge cells to allow merge for a single cell reference [Issue #2776](#2776)
- Memory and speed improvements, particularly for the Cell Collection, and the Writers.

  See [the Discussion section on github](#2821) for details of performance across versions
- Improved performance for removing rows/columns from a worksheet

### Deprecated

- Nothing

### Removed

- Nothing

### Fixed

- Xls Reader resolving absolute named ranges to relative ranges [Issue #2826](#2826) [PR #2827](#2827)
- Null value handling in the Excel Math/Trig PRODUCT() function [Issue #2833](#2833) [PR #2834](#2834)
- Invalid Print Area defined in Xlsx corrupts internal storage of print area [Issue #2848](#2848) [PR #2849](#2849)
- Time interval formatting [Issue #2768](#2768) [PR #2772](#2772)
- Copy from Xls(x) to Html/Pdf loses drawings [PR #2788](#2788)
- Html Reader converting cell containing 0 to null string [Issue #2810](#2810) [PR #2813](#2813)
- Many fixes for Charts, especially, but not limited to, Scatter, Bubble, and Surface charts. [Issue #2762](#2762) [Issue #2299](#2299) [Issue #2700](#2700) [Issue #2817](#2817) [Issue #2763](#2763) [Issue #2219](#2219) [Issue #2863](#2863) [PR #2828](#2828) [PR #2841](#2841) [PR #2846](#2846) [PR #2852](#2852) [PR #2856](#2856) [PR #2865](#2865) [PR #2872](#2872) [PR #2879](#2879) [PR #2898](#2898) [PR #2906](#2906) [PR #2922](#2922) [PR #2923](#2923)
- Adjust both coordinates for two-cell anchors when rows/columns are added/deleted. [Issue #2908](#2908) [PR #2909](#2909)
- Keep calculated string results below 32K. [PR #2921](#2921)
- Filter out illegal Unicode char values FFFE/FFFF. [Issue #2897](#2897) [PR #2910](#2910)
- Better handling of REF errors and propagation of all errors in Calculation engine. [PR #2902](#2902)
- Calculating Engine regexp for Column/Row references when there are multiple quoted worksheet references in the formula [Issue #2874](#2874) [PR #2899](#2899)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

Successfully merging a pull request may close this issue.

3 participants