Bug 46738 - FILESAVE Cell background and border color formatting information of empty cells lost after export to xls or xlsx
Summary: FILESAVE Cell background and border color formatting information of empty cel...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other Windows (All)
: high major
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords: preBibisect, regression
Depends on:
Blocks:
 
Reported: 2012-02-28 14:10 UTC by OfficeUser
Modified: 2017-01-20 21:44 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Test.xls (23.00 KB, application/vnd.ms-excel)
2012-02-28 14:10 UTC, OfficeUser
Details
Test kit (6.82 KB, application/x-zip-compressed)
2012-02-28 21:48 UTC, Rainer Bielefeld Retired
Details
test.ods (7.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-02-29 13:26 UTC, OfficeUser
Details
Screenshot On Libreoffice 5.1.3.2, exporting to xls or xlsx is working correctly (26.77 KB, image/png)
2016-06-13 00:41 UTC, Bartosz
Details
test.ods exported to xlsx format by MS Excel 2010 (15.16 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-08-02 21:10 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description OfficeUser 2012-02-28 14:10:09 UTC
Created attachment 57779 [details]
Test.xls

- Please open the attached xls
- Note that yellow background and the black grid go down to row 104
- Save the spreadsheet as xls or xlsx
- Close the spreadsheet
- Open the saved copy of the spreadsheet
- Note that the yellow background and the black grid go down only to row 5 now, which are filled rows

(Since I do not remember such bugs in earlier versions I think it is a regression introduced in LibreOffice 3.5.0.)
Comment 1 Rainer Bielefeld Retired 2012-02-28 21:47:40 UTC
I see the effect with attached document and with "LibreOffice 3.5.1.1 German UI/Locale [Build-ID: 45a2874-aa8c38d-dff3b9c-def3dbd-62463c8] on German WIN7 Home Premium (64bit) , but I can't reproduce the problem with self created documents. There is a difference between my own test documents and reporter's: in my documents with <control+end> I reach the end of area with cells with background (B18, last cell with "real" contents is B8). In reporter's sample with <control+end> I reach D5, what is last cell with "real" contents, not D104 (end of range with background)

With reporter's sample the loss of background is reproducible with all versions I tested back until 3.3.0 portable; I believe it has started with 3.3.0beta.

OOo 3.1.1 and OOo 3.3 correctly export background for all cells.

Might be related to "Bug 39068 - PRINTING only first area with color formatted cells (background, border), same with PDFEXPORT"

@OfficeUser:
I need some additional information:
a) How has your document been created 
b) can co contribute a document.ods created with  3.5.0 from the scratch 
   demonstrating the problem?

Can you find out difference between your test document and my one from test kit?
Comment 2 Rainer Bielefeld Retired 2012-02-28 21:48:39 UTC
Created attachment 57793 [details]
Test kit

With these documents effect is not reproducible
Comment 3 OfficeUser 2012-02-29 13:26:02 UTC
@Rainer:

I have created and attached a new "test.ods" using LibreOfice 3.5.0 rc3 (=final).

Export and import as xls(x) reveals the bug.
Comment 4 OfficeUser 2012-02-29 13:26:39 UTC
Created attachment 57832 [details]
test.ods
Comment 5 Rainer Bielefeld Retired 2012-02-29 21:45:08 UTC
I am able to reproduce the problem with  "LibreOffice 3.5.1.1 German UI/Locale [Build-ID: 45a2874-aa8c38d-dff3b9c-def3dbd-62463c8] on German WIN7 Home Premium (64bit).

1. Start LibO from WIN Start Center, open new CALC document from LibO 
   Atart Center
2. type a "x" to A1 after you clicked cell
3. Typ a "y" to B2 after you clicked cell
4. Type a "z" to C3 after you clicked cell
5. Click A1, select with pushed left mouse button A1:C126
6. Click the 'Borders' icon in 'Formatting' Toolbar, then 
   bottom right "all borders" icon
   > Borders appear for selected A1:C126
7. Click 'Format Cells' icon in 'Formatting' Toolbar
   > Dialog appears
8. Card 'Background', select "Yellow"
   > All cells will get yellow background
9. For test <control+end>
   > cell cursor goes to C3, what indicates that
     reporter's problem will be reproducible
10. save as test.ods
11. save as test.xls and close
12. reopen test.xls
    Expected: borders and yellow background A1:C126
    Actual:  borders and yellow background A1:C3

That will also work using Menu 'Format -> Cells'

I sometimes also had the expected result (visible in step 9), may be with a very little difference in the proceeding before. The different result is predictable, scrolling in step 5 will be much slower. But I was not able to find out what different proceeding makes the difference.

I also see the effect with LibO 3.4.5 and LibO 3.3.0
Works fine with OOo 3.3, in step 9 cell cursor only reaches C3. but all background will be saved in XLS.

@Kohei:
Please set Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug
Comment 6 Markus Mohrhard 2012-03-25 15:43:28 UTC
I'll take that one. Looks like another place where we call ScDocument::ShrinkToUsedArea. Maybe we should provide a method that respects formatting but provides the same semantics.
Comment 7 Markus Mohrhard 2012-03-25 17:59:59 UTC
So after a lot of digging I finally found the problem.

This bug is related to to the fix of https://issues.apache.org/ooo/show_bug.cgi?id=30830

Right now this is by design and I have no perfect solution for this. We might increase the 84 to something bigger and/or move the excel export to another method that does not have this limitation.

@Kohei: Do you have a preference here? The problematic line is in attarray.cxx:1794
Comment 8 Markus Mohrhard 2012-03-26 20:17:26 UTC
Fixed by implementing a own method for the xls/xlsx export.
Comment 9 Not Assigned 2012-03-26 20:22:23 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5d123a0b0e827aba59ddb50ef1b961a529a34a15

export all style information to xls/xlsx, fdo#46738
Comment 10 Not Assigned 2012-03-27 04:34:14 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6c6a4ed070acc0b106e951864fa5d20927f5c1e0&g=libreoffice-3-5

export all style information to xls/xlsx, fdo#46738


It will be available in LibreOffice 3.5.3.
Comment 11 Not Assigned 2012-06-26 02:28:19 UTC
Petr Mladek committed a patch related to this issue.
It has been pushed to "libreoffice-3-5-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6a6cee1af26e68c7a5479f2eff5a33d483ff54ed&g=libreoffice-3-5-5

Revert "export all style information to xls/xlsx, fdo#46738"


It will be available already in LibreOffice 3.5.5.
Comment 12 Markus Mohrhard 2012-06-26 03:58:25 UTC
I'm sorry but I have to reopen this issue. We had to revert it because it showed a major preformance regression in some cases. The patch is correct but our export code is so horribly broken that we needed to decide which is more serious and the performance problem was worse.

I'll check for a solution but might take till 3-7 or later because it may require a serious rework of our cell export.
Comment 13 Not Assigned 2012-06-26 07:44:26 UTC
Petr Mladek committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e5f690b3711b61c05671d46d19439dd1100f4bff&g=libreoffice-3-5

Revert "export all style information to xls/xlsx, fdo#46738"


It will be available in LibreOffice 3.5.6.
Comment 14 Markus Mohrhard 2012-07-06 11:49:47 UTC
Will not be fixed anytime soon. Requires a large refactoring and I'm not sure if I have the time for it.
Comment 15 Not Assigned 2012-07-08 23:37:53 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e9e53a2d961d489440f4addc25af90d3a6b793b

Revert "export all style information to xls/xlsx, fdo#46738"
Comment 16 Not Assigned 2012-07-08 23:51:13 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6c8ec4ad8e816157799cd164d9f9a837efa0f9a7

Revert "Revert "export all style information to xls/xlsx, fdo#46738""
Comment 17 Joel Madero 2014-11-02 16:13:06 UTC
Because this bug isn't assigned to anyone - moving back to NEW.
Comment 18 Buovjaga 2015-04-19 07:08:39 UTC
Could repro with saving attachment 57832 [details] to xls and xlsx.

Win 7 Pro 64-bit Version: 5.0.0.0.alpha0+ (x64)
Build ID: 211c12b9c64facd1c12f637a5229bd6a6feb032a
TinderBox: Win-x86_64@42, Branch:master, Time: 2015-04-18_01:51:17
Locale: fi_FI
Comment 19 Gerhard Schaber 2015-05-18 07:34:35 UTC
I can confirm this. This is really annoying and makes it unusable for me, at least for xlsx format.
Comment 20 Gerhard Schaber 2015-05-22 07:40:31 UTC
With the ODS file format, for example, this issue does not exit. Sorry for my ignorance, but can the ODS code part act as reference for the XLS/XLSX related implementation?
Comment 21 Robinson Tryon (qubit) 2015-12-14 05:51:40 UTC
Migrating Whiteboard tags to Keywords: (preBibisect)
[NinjaEdit]
Comment 22 Bartosz 2016-06-13 00:41:14 UTC
Created attachment 125626 [details]
Screenshot On Libreoffice 5.1.3.2, exporting to xls or xlsx is working correctly

On Libreoffice 5.1.3.2, exporting to xls or xlsx is working correctly.
I tested it on Ubuntu 16.04
Comment 23 Buovjaga 2016-06-13 05:46:41 UTC
Well, I still repro on Windows with attachment 57832 [details], with xls and xlsx formats.

Win 7 Pro 64-bit Version: 5.3.0.0.alpha0+ (x64)
Build ID: f536a83d51443d19dba58157cea28fb67a090e02
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2016-06-13_01:19:21
Locale: fi-FI (fi_FI)
Comment 24 Bartosz 2016-08-02 21:10:50 UTC
Created attachment 126527 [details]
test.ods exported to xlsx format by MS Excel 2010
Comment 25 Bartosz 2016-10-15 17:39:47 UTC
I created new review which partially resolve that issue, without hitting performance:
https://gerrit.libreoffice.org/#/c/29899/
Comment 26 Commit Notification 2016-10-16 11:34:21 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5239cefff56875c7bb45c046977f1724aace0cfb

tdf#46738 Fix exporting .xlsx of coloured empty cells

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 27 Commit Notification 2016-12-07 17:07:56 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=00afb44987ccd10bce740aef53861ec53c75c95f

tdf#104434 tdf#104451 Revert "tdf#46738 Fix exporting .xlsx of coloured em"...

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 28 Eike Rathke 2016-12-07 17:15:12 UTC
That change caused too many other problems. Back to NEW.
Comment 29 Commit Notification 2016-12-07 20:37:29 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=50d746b78f5600a94830143b1bd5facab4d60c25&h=libreoffice-5-3

tdf#104434 tdf#104451 Revert "tdf#46738 Fix exporting .xlsx of coloured em"...

It will be available in 5.3.0.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.