Bug 160310 - FILEOPEN import xlsx with Pivot Table and Chart, errors
Summary: FILEOPEN import xlsx with Pivot Table and Chart, errors
Status: RESOLVED DUPLICATE of bug 126702
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
24.2.1.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: Pivot-Chart Pivot-Table-XLSX
  Show dependency treegraph
 
Reported: 2024-03-22 07:14 UTC by Paul Thompson
Modified: 2024-07-05 11:06 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Error message (30.94 KB, image/png)
2024-03-22 07:15 UTC, Paul Thompson
Details
This file was opened and saved through Calc (16.01 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2024-03-24 00:42 UTC, Paul Thompson
Details
This file has not been opened in Calc (24.90 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2024-03-24 00:43 UTC, Paul Thompson
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Paul Thompson 2024-03-22 07:14:06 UTC
Description:
If I edit a spreadsheet in Calc, save it and then edit it in MS Excel, Excel reports "We found a problem with some content in '<File Name>'. Do you want us to try to recover as much as we can?"

The error occurs because I use Calc and send spreadsheets to others who use MS Excel.

Steps to Reproduce:
1. Edit a file in Calc and save it
2. Open the file in MS Excel
3.

Actual Results:
The above error is displayed in MS Excel. The error is then corrected by MS Excel and Excel reports "Repaired records. Drawing from /xl/drawings/drawing1.xml pare (Drawing shape)"

Expected Results:
MS Excel should be able to open the file without error.


Reproducible: Always


User Profile Reset: No

Additional Info:
XML Error file
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error047120_01.xml</logFileName><summary>Errors were detected in file 'https://d.docs.live.net/3eec8a413510c938/Documents/Apartments - Sydney/2023-2024/Rent Income - Test.xlsx'</summary><repairedRecords><repairedRecord>Repaired Records: Drawing from /xl/drawings/drawing1.xml part (Drawing shape)</repairedRecord><repairedRecord>Repaired Records: Drawing from /xl/drawings/drawing2.xml part (Drawing shape)</repairedRecord><repairedRecord>Repaired Records: Drawing from /xl/drawings/drawing3.xml part (Drawing shape)</repairedRecord></repairedRecords></recoveryLog>
Comment 1 Paul Thompson 2024-03-22 07:15:31 UTC
Created attachment 193236 [details]
Error message

The error message
Comment 2 ady 2024-03-22 10:21:48 UTC Comment hidden (obsolete)
Comment 3 Paul Thompson 2024-03-22 22:25:45 UTC Comment hidden (obsolete)
Comment 4 ady 2024-03-23 12:40:35 UTC Comment hidden (obsolete)
Comment 5 Paul Thompson 2024-03-24 00:42:46 UTC
Created attachment 193268 [details]
This file was opened and saved through Calc

This file was created in Excel then opened and saved without alteration in Calc. Compare it to Excel.xlsx, which was created in Excel and not opened in Calc.
Comment 6 Paul Thompson 2024-03-24 00:43:49 UTC
Created attachment 193269 [details]
This file has not been opened in Calc
Comment 7 Paul Thompson 2024-03-24 00:59:51 UTC
(In reply to ady from comment #4)
> I understand the difficulty in testing under those conditions.
> 
> It is still not completely clear yet whether the problem only occurs with 1
> particular specific file, or with more than 1 file, or with all (XLSX) files.
> 
> Additionally, in order to move forward this report, most probably we need
> such problematic XLSX file to be attached to this report.
> 
> Attachments are publicly available, so any private or sensitive information
> in the file should be eliminated beforehand. Modifying the file _could_
> potentially eliminate the source of the problem, making it useless for the
> purpose of replication.
> 
> Anyway, see
> <https://wiki.documentfoundation.org/QA/Bugzilla/
> Sanitizing_Files_Before_Submission>

Hi,

After spending Sunday afternoon sending emails to each other across the dining table, we have found that the problem is coming from embedded charts. My spreadsheet uses bar charts, but it could be coming from other charts as well.

I've attached 2 files. Excel was created in Excel then copied to Calc. Calc was opened in Calc and saved without alteration.

While creating a non-confidential spreadsheet for you to examine, we identified another problem which is much more serious. If you create a chart from a pivot table in Excel, then open the spreadsheet in Calc, Calc destroys the chart and the pivot table. See the attached Excel.xlsx and Calc.xlsx.

I hope the above information is helpful. If you need more, email me and I'll do my best to provide it.

Paul
Comment 8 ady 2024-03-24 11:58:56 UTC
So, the issue is about importing XLSX files that contain a Pivot Table and its Chart. Neither are correctly imported (opened) in Calc, and then re-saving (exporting) the file will generate an error in the file (according to Excel).

I am changing the Summary accordingly.

Maybe the worksheet not being the same as where the original data is located might have some influence?

I checked with the attachments in comments 5,6.
Comment 9 Paul Thompson 2024-03-24 22:46:16 UTC
Not quite, there are now two issues.

The original issue I tracked down to a bar chart in an xlsx file. If you open a spreadsheet in Calc that was created in Excel and contains a bar chart, when you save it in Calc and reopen it in Excel, Excel reports an error in an image file (the bar chart).

The second issue, which I discovered while creating a non-confidential spreadsheet, is that if you create a pivot table and then a bar chart from the pivot table in Excel, when you open the file in Calc, the pivot table has disappeared along with the chart.

I suggest you open the sample Excel file (Excel.xlsx) in Excel, examine the pivot table and close Excel without saving. Then open the same sample Excel file in Calc and examine the pivot table.

FYI Excel is Excel 2021 vs. 2402 and calc is vs. 24.2.1
Comment 10 ady 2024-03-25 11:14:43 UTC
(In reply to Paul Thompson from comment #9)
> Not quite, there are now two issues.
> 
> The original issue I tracked down to a bar chart in an xlsx file. If you
> open a spreadsheet in Calc that was created in Excel and contains a bar
> chart, when you save it in Calc and reopen it in Excel, Excel reports an
> error in an image file (the bar chart).


That's the _export_ (to xlsx) issue I mentioned.


> 
> The second issue, which I discovered while creating a non-confidential
> spreadsheet, is that if you create a pivot table and then a bar chart from
> the pivot table in Excel, when you open the file in Calc, the pivot table
> has disappeared along with the chart.

That's the _import_ (from xlsx) issue I mentioned.


FWIW, using GSheets I can also see the difference between the attached files, although there was no error message while opening the file saved by Calc.


For someone that has access to both Excel and Calc, it could be interesting to test whether the problem also occurs when the pivot table and its chart are created in the same worksheet as the original data (instead of having them in another worksheet, separated from the data).

I'm not even sure that having these in separated worksheets is (correctly) supported.


Someone (else) would need to replicate the problem from the start (and compare it with the above attachments):

1. Using Excel, create an xlsx file with a similar Pivot Table and Chart.
2. Open it in Calc > check how it is imported.
3. Save as a new xlsx file and close Calc.
4. Using Excel, open the new xlsx saved by Calc > check how it was exported (i.e. is there some error message in Excel about the file?).

By generating a new file from scratch, and comparing the results with those in comments 5,6, the whole procedure is replicated. This would eliminate some particular setting or format in the specific documents and/or in the particular system in which those attachments were created.

Since there seem to be 2 issues, once these problems are replicated by someone else, there might be a need to open a second bug report; one for the import and the other for the export.

But, before investing more resources, the first question (which its answer IDK) is:

Is this case supported by Calc at all?
Comment 11 Timur 2024-07-05 11:06:11 UTC
Seems duplicate.

*** This bug has been marked as a duplicate of bug 126702 ***