Download it now!
Bug 68282 - FILESAVE: Pasted Cells Format (Numbers) to blank cells for a whole row not preserved in .xlsx
Summary: FILESAVE: Pasted Cells Format (Numbers) to blank cells for a whole row not pr...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.3.0 release
Hardware: All All
: low major
Assignee: Not Assigned
Whiteboard: BSA
Keywords: filter:xlsx
Depends on:
Blocks: XLSX
  Show dependency treegraph
Reported: 2013-08-19 14:32 UTC by Ryan K
Modified: 2018-12-18 11:40 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

File showing the problem. Try typing a number into the currency column. (4.81 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-08-19 14:32 UTC, Ryan K
File illustrating a partial workaround (5.13 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-08-19 14:33 UTC, Ryan K
File with custom style up to line 25 created with MS Excel 2010 (8.84 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-08-03 00:01 UTC, Bartosz
File after writing by LO 5.1. Styles in empty rows were not preserved (6.97 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-08-03 00:02 UTC, Bartosz

Note You need to log in before you can comment on or make changes to this bug.
Description Ryan K 2013-08-19 14:32:30 UTC
Created attachment 84249 [details]
File showing the problem. Try typing a number into the currency column.

Problem description: Using Formatting of empty rows using the Paste Special command is not preserved across saves in .xlsx documents

Steps to reproduce:
1. Create a new calc document. 
2. Create a row of data in 2 or 3 columns
3. Apply formatting to each column (alignment, number format, whatever)
4. Highlight and copy the row of data
5. Paste the formatting only into all the blank cells below your data, through row 25. (Use CTRL+Shift+v and uncheck everything except "formats").
6. Save as .xlsx
7. Type something into one of the newly formatted rows to confirm that the formatting was applied. DO NOT SAVE.
8. Close the document and reopen it
9. Type something into a formatted row. Notice that the formatting is no longer there. See attached example (test.xlsx)

Current behavior: Formatting of blank rows is lost after save

Expected behavior: Formatting should be preserved.

WORKAROUND: I've noticed that if any content is present in one of the rows that is formatted (even if it is outside the columns that are formatted), then the pasted formatting will be retained UP TO THAT ROW. In the attached example (workaround.xlsx), I have inserted text in cell D21. I pasted formatting into columns A,B, and C through row 25. The formatting is preserved across saves for columns A,B, and C only through row 21.

Operating System: Linux (Other)
Version: release
Comment 1 Ryan K 2013-08-19 14:33:18 UTC
Created attachment 84250 [details]
File illustrating a partial workaround
Comment 2 Joel Madero 2013-09-24 22:32:01 UTC
Thank you for reporting this issue! I have been able to confirm the issue on:
Platform: Ubuntu 13.04 x64
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
As I've been able to confirm this problem on an earlier release I am changing the version number as version is the earliest version that we can confirm the bug, we use comments to say that the bug exists in newer versions as well.

Marking as:

New (confirmed)
Major - loss of data, this is really arguable but I'm trying to be consistent so major it is
Low - you can reapply styles relatively easily but still, should be preserved

Version: Changing to 3.5.0 beta0 as I can confirm pre-bibisect. Version reflects oldest version where we see the problem, I thought this might be a regression but apparently not, probably inherited from OOo

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:

There are also other ways to get involved including with marketing, UX, documentation, and of course developing - 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Comment 3 ign_christian 2014-09-09 03:11:23 UTC
Also confirmed with LO,, - Ubuntu 12.04 x86

All pasted format in empty cells always back to Number|Standard format after filesave.

Tried updating summary for clarity & differentiate with Bug 71897
Comment 4 QA Administrators 2015-10-14 19:57:12 UTC Comment hidden (obsolete)
Comment 5 Ryan K 2015-10-30 18:09:14 UTC
I can still reproduce this bug in LibO x64 (Windows). I followed the exact steps described in the first post.
Comment 6 Bartosz 2016-06-26 22:33:04 UTC
Could you please check if you:
1. Create xlsx document with MS Excel with this example (it should work there
2. Open xlsx document created by MS Exce, and save it as xlsx
3. Check if this bug is reproducible with that scenario

It will be much easier to compare these two files (Original Excel and converted by LibreOffice) and fix that.
Comment 7 Bartosz 2016-08-03 00:00:25 UTC
The solution for this issue will be the same as for
Comment 8 Bartosz 2016-08-03 00:01:51 UTC
Created attachment 126531 [details]
File with custom style up to line 25 created with MS Excel 2010
Comment 9 Bartosz 2016-08-03 00:02:44 UTC
Created attachment 126532 [details]
File after writing by LO 5.1. Styles in empty rows were not preserved
Comment 10 QA Administrators 2018-06-20 02:49:13 UTC Comment hidden (obsolete)
Comment 11 Roman Kuznetsov 2018-12-18 11:40:07 UTC
I can't repro it in 

Build ID: 3c964980da07892a02d5ac721d80558c459532d0
CPU threads: 4; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-12-12_02:07:45
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: threaded