Bug 118678 - Subtotals results moved left when saving in ODS format (steps in comment 12)
Summary: Subtotals results moved left when saving in ODS format (steps in comment 12)
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Function-Subtotal
  Show dependency treegraph
 
Reported: 2018-07-10 19:09 UTC by Marcelo
Modified: 2020-08-08 10:20 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Test File Saved in ODS Format (21.61 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-07-10 19:11 UTC, Marcelo
Details
Test File Saved in XLS Format (13.00 KB, application/x-ole-storage)
2018-07-10 19:11 UTC, Marcelo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Marcelo 2018-07-10 19:09:32 UTC
Description:
I'm triying to use subtotals in a Calc document.
I have tried many times until i discovered that: when i save the file in ods format y try to open it, the file don't show the information properly. the colums appears ordered not properly.
Othrwise, if i save the same file in xls format, que file show perfectly.

Steps to Reproduce:
1.I have in a Calc sheet the table:
GRUPO 1	GRUPO 2	GRUPO 3	A	B	C	D	E
GRUPOA	GRUPOC	GRUPOG	1	1	1	1	1
GRUPOA	GRUPOC	GRUPOG	2	2	2	2	2
GRUPOA	GRUPOC	GRUPOG	3	3	3	3	3
GRUPOA	GRUPOC	GRUPOH	4	4	4	4	4
GRUPOA	GRUPOC	GRUPOH	5	5	5	5	5
GRUPOA	GRUPOD	GRUPOH	6	6	6	6	6
GRUPOA	GRUPOD	GRUPOI	7	7	7	7	7
GRUPOA	GRUPOD	GRUPOI	8	8	8	8	8
GRUPOA	GRUPOD	GRUPOI	9	9	9	9	9
GRUPOB	GRUPOD	HRUPOJ	10	10	10	10	10
GRUPOB	GRUPOE	GRUPOK	13	13	13	13	13
GRUPOB	GRUPOE	GRUPOK	14	14	14	14	14
GRUPOB	GRUPOE	GRUPOK	15	15	15	15	15
GRUPOB	GRUPOE	HRUPOJ	11	11	11	11	11
GRUPOB	GRUPOE	HRUPOJ	12	12	12	12	12
GRUPOB	GRUPOF	GRUPOL	16	16	16	16	16
GRUPOB	GRUPOF	GRUPOL	17	17	17	17	17
GRUPOB	GRUPOF	GRUPOL	18	18	18	18	18
GRUPOB	GRUPOF	GRUPOM	19	19	19	19	19
GRUPOB	GRUPOF	GRUPOM	20	20	20	20	20

2.I'm appliying subtotals to group "GRUPO 1", "GRUPO 2" and "GRUPO3" and calculate subtotal (sum) for collumns A, B, C, D and E

3.When the subtotal was applied, save the file with format ODS (Libreoffice Calc) and XLS (Microsoft Excel 97/2003) at the same time.

4. Close both files

5. Open both files, now you can see the diference. The ODS file shows the data not properly, and the XLS shows them fine.

Actual Results:
The data in the columns are incorrectly ordered in the ODS file.

Expected Results:
The data has to show correctly using both extension ODS and XLS.


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Marcelo 2018-07-10 19:11:10 UTC
Created attachment 143434 [details]
Test File Saved in ODS Format
Comment 2 Marcelo 2018-07-10 19:11:58 UTC
Created attachment 143435 [details]
Test File Saved in XLS Format
Comment 3 Marcelo 2018-07-10 19:12:52 UTC Comment hidden (obsolete)
Comment 4 MM 2018-07-10 21:52:44 UTC
Unconfirmed on windows 7 x64 with Version: 6.0.5.2 (x64)
Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
CPU threads: 3; OS: Windows 6.1; UI render: default

When opening the ods file, I see some gaps in the data, which I don't see when I save the xls file to ods (and reload).
Comment 5 Marcelo 2018-07-12 17:05:50 UTC
Apliying Subtotals and saving the file in ODS format (and reload), appears some gaps in the data, which doesn't appears when save in xls file.
Comment 6 Marcelo 2018-07-12 18:06:22 UTC
(In reply to MM from comment #4)
> Unconfirmed on windows 7 x64 with Version: 6.0.5.2 (x64)
> Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
> CPU threads: 3; OS: Windows 6.1; UI render: default
> 
> When opening the ods file, I see some gaps in the data, which I don't see
> when I save the xls file to ods (and reload).

That's correct! those gaps are the problem. I will change the summary... Sorry!
Comment 7 Buovjaga 2018-07-15 17:59:10 UTC Comment hidden (obsolete)
Comment 8 MM 2018-07-16 21:03:09 UTC
(In reply to Buovjaga from comment #7)
> NEW, then, per MM's confirmation.

No, I didn't confirm. I see gaps in the supplied file, but *not* when I save the xls file to ods format.
To keep this report short I only added the test on windows 7, but I also didn't find the gaps when testing on ubuntu 16.04 with v5.3/6.0/6.1/6.2.
Comment 9 Buovjaga 2018-07-18 16:39:37 UTC
Ok, I don't get the gaps when saving from xls to ods either.

Arch Linux 64-bit
Version: 6.2.0.0.alpha0+
Build ID: 860a9daf2b45942a4b10ff22d36aa3fe29be19f4
CPU threads: 8; OS: Linux 4.17; UI render: default; VCL: gtk3; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group threaded
Built on July 14th 2018
Comment 10 Marcelo 2018-07-20 11:45:11 UTC
When i save the supplied xls file into ods file neither i get the gaps.
Please test again following the "steps to reproduce".
Te gaps appears only when start from a new file, apply subtotals, and save the file in ods format...

Versión: 6.0.5.2
Id. de compilación: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
Subprocs. CPU: 4; SO: Windows 6.1; Repres. IU: predet.; 
Configuración regional: es-AR (es_AR); Calc: group
Comment 11 raal 2018-08-01 15:25:10 UTC
please describe how you did subtotals. My steps:
  -select range A1:H21
 - data-Subtotals
     select Group by: Grupo 1
     select A,B,C,D,E and select SUM
     OK
 - data-Subtotals
     select Group by: Grupo 2
     select A,B,C,D,E and select SUM
     OK
 - data-Subtotals
     select Group by: Grupo 2
     select A,B,C,D,E and select SUM
     OK

 - save as .ods

I cannot reproduce with these steps (LO 6.2, windows), but in your file is more subtotals, so please provide step by step description. Thank you.
Comment 12 Marcelo 2018-08-01 16:16:32 UTC
I believe that: The gaps doesn't appear because you are appliying subtotals from the supplied xls file and save it into ods format.
In that case. I neither get the gaps.

I try to explain a more detailed step by step:

 -Open a new, clean libreoffice Calc document.

 -Insert this data:
GRUPO 1	GRUPO 2	GRUPO 3	A	B	C	D	E
GRUPOA	GRUPOC	GRUPOG	1	1	1	1	1
GRUPOA	GRUPOC	GRUPOG	2	2	2	2	2
GRUPOA	GRUPOC	GRUPOG	3	3	3	3	3
GRUPOA	GRUPOC	GRUPOH	4	4	4	4	4
GRUPOA	GRUPOC	GRUPOH	5	5	5	5	5
GRUPOA	GRUPOD	GRUPOH	6	6	6	6	6
GRUPOA	GRUPOD	GRUPOI	7	7	7	7	7
GRUPOA	GRUPOD	GRUPOI	8	8	8	8	8
GRUPOA	GRUPOD	GRUPOI	9	9	9	9	9
GRUPOB	GRUPOD	HRUPOJ	10	10	10	10	10
GRUPOB	GRUPOE	GRUPOK	13	13	13	13	13
GRUPOB	GRUPOE	GRUPOK	14	14	14	14	14
GRUPOB	GRUPOE	GRUPOK	15	15	15	15	15
GRUPOB	GRUPOE	HRUPOJ	11	11	11	11	11
GRUPOB	GRUPOE	HRUPOJ	12	12	12	12	12
GRUPOB	GRUPOF	GRUPOL	16	16	16	16	16
GRUPOB	GRUPOF	GRUPOL	17	17	17	17	17
GRUPOB	GRUPOF	GRUPOL	18	18	18	18	18
GRUPOB	GRUPOF	GRUPOM	19	19	19	19	19
GRUPOB	GRUPOF	GRUPOM	20	20	20	20	20

 - data-Subtotals
     select Group by: Grupo 1
     select A,B,C,D,E and select SUM
     OK
 - data-Subtotals
     select Group by: Grupo 2
     select A,B,C,D,E and select SUM
     OK
 - data-Subtotals
     select Group by: Grupo 2
     select A,B,C,D,E and select SUM
     OK

 - Save in .ods format
 - Close the file.
 - Open the file. Rith here i see the gaps...!!


If i repeat the whole process but i save the file in xls format, the gaps doesn't appears.

Thanks guys! and  sorry for my bad english.
Comment 13 m.a.riosv 2018-08-01 16:43:45 UTC
For me with:
Version: 6.1.1.0.0+ (x64)
Build ID: c20eb29560fa9d9e552c799203156c0742451eba
CPU threads: 4; OS: Windows 10.0; UI render: GL; 
TinderBox: Win-x86_64@42, Branch:libreoffice-6-1, Time: 2018-07-28_12:44:37
Locale: es-ES (es_ES); Calc: CL

With ods the subtotal at reopening shows the subtotals moved to the left, but preserve the options, so applying subtotal again get the right resuls.
But that doesn't happen saving in xls, has the same issue at opening, but the subtotal parameters are clean like as default.
Comment 14 MM 2018-08-06 21:18:30 UTC
With the proper steps I can confirm on windows 7 x64 with Version: 6.0.6.2 (x64)
Build ID: 0c292870b25a325b5ed35f6b45599d2ea4458e77
CPU threads: 3; OS: Windows 6.1; UI render: default

Indeed the results are there, but shifted to the left.

Also confirmed on ubuntu 16.04 x64 with Version: 5.3.7.2
Build ID: 6b8ed514a9f8b44d37a1b96673cbbdd077e24059
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default; VCL: gtk2; Layout Engine: new; 
Locale: en-US (en_US.UTF-8); Calc: single

but unconfirmed with Version: 5.2.6.2
Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); Calc: single
Comment 15 Buovjaga 2018-08-09 12:46:04 UTC
The steps are missing this: you have to use the tabs 1st/2nd/3rd Group when doing the groupings.
I will try to bibisect.
Comment 16 Buovjaga 2018-08-09 14:48:26 UTC
Bibisected with Win 5.3 repo to https://cgit.freedesktop.org/libreoffice/core/commit/?id=7b0aed617f1e57335837cf56ef2d222a96f8270d
Remove old cell styles from calc

The problem matches the disappearance of the bold italic underlined styling of the results.

Adding Cc: to Jaskaran Singh
Comment 17 Olivier MORIN 2019-08-13 09:54:23 UTC
Hello,

Bug confirmed in Version: 6.1.6.3 (x64) under Windows 10.

File saved in .ods, totals moved to left and format is lost...

File save in .xlsx, ererything is OK.

Very annoying...
Comment 18 Olivier MORIN 2019-10-11 09:33:47 UTC Comment hidden (obsolete)
Comment 19 m.a.riosv 2019-10-11 09:54:50 UTC
Also in master
Version: 6.4.0.0.alpha0+ (x64)
Build ID: d744838991594eebe27acc4c7d9fb4579d654853
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2019-09-29_05:12:00
Locale: es-ES (es_ES); UI-Language: en-US Calc: threaded
Comment 20 Olivier MORIN 2020-02-04 15:46:57 UTC
Openoffice does it good...
A sheet with subtotals created with Openoffice 4.1.6 and loaded with Libreoffice 6.3.4.2 is OK, even if resave with LO.
A sheet with subtotals created with Libreoffice 6.3.4.2 and loaded with Openoffice 4.1.6 is bad, even if resave with OO.
Comment 21 Olivier MORIN 2020-08-06 14:06:58 UTC
Still there

Version : 6.4.5.2 (x64)
Build ID : a726b36747cf2001e06b58ad5db1aa3a9a1872d6
Threads CPU : 8; OS : Windows 10.0 Build 18362; UI Render : par défaut; VCL: win; 
Locale : fr-FR (fr_FR); Langue IHM : fr-FR
Calc: threaded
Comment 22 b. 2020-08-08 10:20:22 UTC
still in actual master (7.1 winx64 2020-08-05), 

is the following analysis correct? 

*.xlsx file works by 'direct adressing', see '"D9"' in following fragment, it's the first subtotal which is 'shiftet left' when saving as *.ods, 

<c r="B9" s="1" t="s">
   <v>14</v>
</c>
<c r="C9" s="1"/>
<c r="D9" s="1" t="n">
   <f aca="false">SUBTOTAL(9,$D$2:$D$8)</f>
   <v>15</v>
</c>

while *.ods works with 'spacing cells' for 'whitespace', and would need one more empty cell at the place where i'd add 
   "<table:table-cell/> missing here"
in the following fragment, it's the same part of data as above, 

<table:table-row table:style-name="ro1">
   <table:table-cell/>
   <table:table-cell office:value-type="string" calcext:value-type="string">
      <text:p>GRUPOC Result</text:p>
   </table:table-cell>
   "<table:table-cell/> missing here"
   <table:table-cell table:style-name="ce1" table:formula="of:=SUBTOTAL(9;[.$D$2:.$D$8])" office:value-type="float" office:value="15" calcext:value-type="float">
      <text:p>15</text:p>
   </table:table-cell>