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

export excel file with line chart error xl/drawings/drawing1.xml #2333

Closed
padi-dev-lucvt opened this issue Oct 13, 2021 · 20 comments · Fixed by #3265
Closed

export excel file with line chart error xl/drawings/drawing1.xml #2333

padi-dev-lucvt opened this issue Oct 13, 2021 · 20 comments · Fixed by #3265
Labels

Comments

@padi-dev-lucvt
Copy link

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?

Successfully save and open new .xlsx file with my data and chart

What is the current behavior?

The error that Component "xl/drawings/drawing1.xml" is deleted.
Don't understand why my chart became drawing.

What are the steps to reproduce?

I have a sample template and have corresponding chart data sheets
When I load them so when I fill them with data and export them, they give an error and the chart is not displayed
image
image
image
Sheet DATA_1 to display data for the chart "Comparison chart through experiments - phase A"
The data is still filled out but the chart is not displayed

Here is my code

code
code2

@oleibman
Copy link
Collaborator

There have been a lot of recent changes to Charts. Can you test with release 1.24 to see if they resolve your problem? If it doesn't, is it possible to upload your spreadsheet?

@gotgot1995
Copy link

gotgot1995 commented Sep 1, 2022

@padi-dev-lucvt I have the same error with version 1.19.0. @oleibman I'll try to upgrade to 1.24.1 to see if that solves it.

@gotgot1995
Copy link

gotgot1995 commented Sep 9, 2022

It appears to me that version 1.24.1 does not solve the issue. My server is running PHP 7.3.33 with Apache for Debian. In my case, I'm trying to render a pie chart.

Here is a list of ideas for a new lead:

  • I didn't have this issue before migrating from PhpExcel to PhpSpreadsheet. Maybe Rector didn't do the migration properly regarding graphs.
  • What would be the best practice to render graphs using PhpSpreadsheet nowadays ? I'm still using the old JpGraph package. Could this be the root cause ?

Any help would be appreciated.

@oleibman
Copy link
Collaborator

oleibman commented Sep 9, 2022

We now use mitoteam/jpgraph rather than the old jpgraph package from Composer. Once that package is added to your project via Composer, the only change required is setting the renderer class:

Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);

If this doesn't fix your problem, I might need to see your spreadsheet to investigate further. I will note that there are a handful of chart types for which the rendering doesn't seem to work properly "out of the box", and Pie Charts represent most of those, so I wouldn't be surprised if you still have a problem.

@SSI-johnnypops
Copy link

I've been having the same problem when reading and then writing out an Excel-generated .xlsx file.
After reading through the reader and writer code I was able to isolate the issue to 'Writer/Xlsx/Chart.php' in writePlotGroup(). I was able to successfully write out the file after removing $seriesIndex shenanigans.
Unfortunately, one of the samples: 'Chart/33_Chart_create_composite.php' was broken by the changes. After studying the reader code I was finally able to determine that the sample itself was generating invalid data structures and it looked suspiciously like the $seriesIndex hack was introduced just for this sample!

Attached are a pair of diffs: one for the writer code and one for the sample. The paths in the diffs need fixing but the actual changes are minimal.

I hope this helps someone.

fix-33_Chart_create_composite-sample.txt

fix-writer-xlsx-chart.txt

@oleibman
Copy link
Collaborator

Can you expand on why you think the data structures generated for the sample are invalid? Your solution looks like a change that would break existing code, as it did for the sample in question. I am not averse to making such a change, but I would need a better understanding of why the current sample code is wrong. For the record, $seriesIndex was introduced over 5 years ago; I cannot speak to the reasons for it, but that is a long time to have not shown up a problem.

@oleibman
Copy link
Collaborator

It would also be very helpful if you could upload a spreadsheet which demonstrates this problem.

@SSI-johnnypops
Copy link

I will try and strip out the problematic part. I don't think I can just upload the whole thing.

@SSI-johnnypops
Copy link

Here you go. I believe the data is just test data.
2019_AM_InventoryTemplate_Stripped.xlsx

My test script reads this in like this:
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($template_filename);

and then writes it out like this:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);
$writer->setIncludeCharts(true);
$writer->save($fileNameAndPath);

The resulting file incompletely loads in Excel with the 'recovery' popup and the message:
'Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)'

@SSI-johnnypops
Copy link

The connection with the sample script '33_Chart_create_composite.php' is that they both have a Chart with multiple DataSeries() in a PlotArea(). This is when $seriesIndex becomes involved in writing (it is 0 usually).

@oleibman
Copy link
Collaborator

Thank you for the sample file. I can duplicate your problem, so will take a look at the file, the existing code, and your suggested changes.

@SSI-johnnypops
Copy link

Thanks!

@SSI-johnnypops
Copy link

If it helps, my rough process was inspecting the output .xlsx files for differences and discovering that the 'c:idx' and 'c:order' fields in the 'chartN.xml' files were different and then finding the relevant code in 'Writer/Xlsx/Chart.php'.

The patch for that file that I attached causes the problematic spreadsheet I uploaded to work.

After getting the samples to work on my machine, I discovered only one that was broken by the change (I may have missed some).

After tearing my hair out trying to get the problematic spreadsheet AND the sample to both work, I eventually turned to the 'Reader' part of the code and discovered that the data-structures created by the Reader code and the sample differed and it seemed unlikely that a few tweaks to the Writer code could fix it (I did try various things though).

That's when I started to suspect that maybe the sample 'Chart/33_Chart_create_composite.php' was possibly faulty.

Good luck, Happy New Year!

@oleibman
Copy link
Collaborator

I believe we can get away without breaking existing usage by including the seriesIndex logic if and only if element 0 exists in plotSeriesOrder. This works with your sample file, with 33_Chart_create_composite unchanged, and with 33_Chart_create_composite as you modified it (which seems more sensible than the unchanged version). I need to think about whether it makes sense to grandfather the existing usage (I am strongly inclined to yes). I will probably have a PR ready in a day or two. None of our other samples or tests are affected by this change, since they all specify only element 0 in the plotSeries array which they pass to the PlotArea constructor.

@SSI-johnnypops
Copy link

Awesome! Thanks for looking into this.

oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Dec 29, 2022
Fix PHPOffice#2333. Thanks to @SSI-johnnypops for discovering the problem and formulating most of the solution. The classes involved in setting up composite charts made it relatively easy to code them; however, there can be problems when reading a spreadsheet with such a chart and then saving a copy of it. In  particular, the ordering conventions when reading the chart may not match the expectations when writing. The original attempt to resolve this worked for read/write but broke one of our samples (33_Chart_create_composite), which probably means that it would break other code which is now working in the wild. It has now been refined so that it (hopefully) makes adjustments to the ordering if and only if they are required. The new code works with the failing spreadsheet reported in the issue, with the unchanged sample code, and with an alternate version of the sample which contains what is probably a better model for people to use when coding such a chart.
oleibman added a commit that referenced this issue Jan 1, 2023
Fix #2333. Thanks to @SSI-johnnypops for discovering the problem and formulating most of the solution. The classes involved in setting up composite charts made it relatively easy to code them; however, there can be problems when reading a spreadsheet with such a chart and then saving a copy of it. In  particular, the ordering conventions when reading the chart may not match the expectations when writing. The original attempt to resolve this worked for read/write but broke one of our samples (33_Chart_create_composite), which probably means that it would break other code which is now working in the wild. It has now been refined so that it (hopefully) makes adjustments to the ordering if and only if they are required. The new code works with the failing spreadsheet reported in the issue, with the unchanged sample code, and with an alternate version of the sample which contains what is probably a better model for people to use when coding such a chart.
@nitin-usualsmart
Copy link

nitin-usualsmart commented Nov 1, 2023

Where to find the solution for this issue? I am using the area chart and adding multiple series of it, facing same issue of error while opening the file. I have updated the version to the latest one "phpoffice/phpspreadsheet": "^1.29" still I am unable to get the solution. One thing is sure that on adding the single series in the area chart it's working fine but adding multiple series causes the issue. When opening a file the message seen in the attached images appears.
I am putting here my code for the reference. If you can check and help me where is the issue in it?

foreach ($combinedData['series']['area'] as $index => $data) {
            $type = $data['type'];
            $name = $data['name'];
            $chartValues = $data['data'];
            $xAxisTickValues = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, null, null, count($combinedData['categories']), $combinedData['categories']),
            ];

            $dataSeriesLabels = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, null, null, count($chartValues), [$name]),
            ];
            $dataSeriesValues = [
                new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, null, null, count($chartValues), $chartValues),
            ];

            $series1 = new DataSeries(
                DataSeries::TYPE_AREACHART
                null,
                [0],
                $dataSeriesLabels,
                $xAxisTickValues,
                $dataSeriesValues
            );
            $charts1[] = $series1;
        }
        $plotArea1 = new PlotArea(null, $charts1);
        $legend = new ChartLegend(ChartLegend::POSITION_BOTTOM, null, false);

        $title_area = new Title('%Expense Vs Budget');
        $yAxisLabel_area = new Title('Value %');
        $areaChart = new ChartChart(
            'chart1',
            $title,
            $legend,
            $plotArea1,
            true,
            DataSeries::EMPTY_AS_GAP,
            $title_area,
            $yAxisLabel_area
        );

        $areaChart->setTopLeftPosition('M1');
        $areaChart->setBottomRightPosition('AA25');
       $worksheet->addChart($areaChart);

image
image

@junnysolano
Copy link

Hello everyone,

I also have the same problem as @nitin-usualsmart with version 1.29 but with the pie chart.

@junnysolano
Copy link

Hello again,

I have solved my problem changing the $displayBlanksAs parameter of the Chart class, previously it had 0 now 'gap' (You can leave the value null or use the constant DataSeries::EMPTY_AS_GAP).

$chart = new Chart(
            $name,
            new Title($title),
            new Legend($legendPosition),
            new PlotArea($layout, [$series]),
            true,
            'gap'
        );

@nitin-usualsmart
Copy link

Hi @junnysolano thank you for the help, I tried putting the 'gap' which does not work for me. Also, I tried keeping null over there but facing the same error. Somewhere while googling I found that it's related to the drawing of the shapes while plotting the chart, not sure what to do now as the solution you said is also not working. Should I give you any other information to help me out of this problem? Many thanks in advance.

@junnysolano
Copy link

Hi @nitin-usualsmart, I am not sure of the problem you are presenting, but if you like, share the structure of the array that you are storing in this variable $combinedData to validate how the data is being processed and what is being generated.

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.

6 participants