PHPOffice / PhpSpreadsheet

A pure PHP library for reading and writing spreadsheet files
https://phpspreadsheet.readthedocs.io
MIT License
13.34k stars 3.46k forks source link

Chart: Y axis is no longer displayed after load and save #3977

Open homersimpsons opened 7 months ago

homersimpsons commented 7 months ago

This is:

What is the expected behavior?

PhpSpreadsheet should let the Y Axis displayed

What is the current behavior?

PhpSpreadsheet hides the Y axis

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

use PhpOffice\PhpSpreadsheet\Reader\IReader;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Writer\IWriter;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;

require_once "vendor/autoload.php";

$reader = new XlsxReader();
$spreadsheet = $reader->load("example.xlsx", IReader::LOAD_WITH_CHARTS);

$writer = new XlsxWriter($spreadsheet);
$writer->save("example_updated.xlsx", IWriter::SAVE_WITH_CHARTS);

File: example.xlsx

What features do you think are causing the issue

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

I'm not sure about this

Which versions of PhpSpreadsheet and PHP are affected?

Latest PhpSpreadsheet (2.0.0) with PHP 8.1.2-1ubuntu2.14

Note that this was working until PhpSpreadsheet 1.24.1, and it is broken in PhpSpreadsheet 1.25.0. The diff between those 2 versions is rather huge: https://github.com/PHPOffice/PhpSpreadsheet/compare/1.24.1...1.25.0

Some possible candidates for the regression in the above list of commits:

Analysis

I did some analysis before getting to this result. I did attempt 2 ways to get a concise reproduction. The issue may come from the mix of bar and line chart together.

1. Down from failing case

After the following simplification it is still not working:

I tried to reduce it further but ultimately came to #3975

I can provide the non-minified example privately. Note that the base file was certainly generated on Excel but I did edit it with LibreOffice to reduce it.

2. Up from blank case

I did try to generate a failing test case from scratch but could not get a result:

For instance, the following file works: sheet_with_chart.xlsx

Both axis hidden

While reducing this I was able to get a file where both axis would not be displayed. Note that re-enablig them in LibreOffice works. I do not know if I should open a new issue for this, the cause may be the same.

Here is the failing file: example_hide_both_axis.xlsx

Thank you for this awesome library!

oleibman commented 6 months ago

Thank you for the sample files. The problem you are seeing is that your combination chart uses secondary axes, and PhpSpreadsheet does not yet support those. I believe that rearranging your chart so that the scatter chart comes "before" the stacked column chart would work for you. That's not much of a solution even when it works, and it won't work all the time. It seems difficult (to me) to add secondary axes, but I will take another look when time allows.