Bug 71394 - Text box position imported incorrectly after saving as xlsx
Summary: Text box position imported incorrectly after saving as xlsx
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All Linux (All)
: high major
Assignee: Not Assigned
Keywords: bibisected, bisected, regression
Depends on:
Reported: 2013-11-08 16:18 UTC by Aurosutru
Modified: 2017-11-13 17:48 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:

Left side of picture shows onscreen alignment of green boxes. Right side shows vertical shift of green boxes and vertical shift of text within boxes. (136.78 KB, image/jpeg)
2013-11-08 16:18 UTC, Aurosutru
File converted to XLSX showing vertical shift in text box (5.64 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-09 03:00 UTC, Aurosutru
Aligned ODS of sample file (11.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-01-10 11:12 UTC, Matthew Francis
Sample .ods requested by Foss - sorry this is so late (12.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-01-11 02:57 UTC, Aurosutru

Note You need to log in before you can comment on or make changes to this bug.
Description Aurosutru 2013-11-08 16:18:31 UTC
Created attachment 88897 [details]
Left side of picture shows onscreen alignment of green boxes.  Right side shows vertical shift of green boxes and vertical shift of text within boxes.

When using drawing tools to create text boxes in spreadsheets two problems arise:

1. The vertical position of the boxes shifts when printing and when viewing page (print) preview compared to normal onscreen view.  Alignment with the cells becomes more and more off as one scrolls down the spreadsheet. By the time page two is reached the boxes are off nearly half a cell vertically.  

2. The text within the text boxes is also off in its vertical alignment relative to the boxes and cannot be corrected.

Trying to anchor the boxes to the cells or other locations doesn't help.

Please see attachment which shows the problem clearly, especially with the green text box vertical placement shift and text vertical shift within the boxes.
Comment 1 Joel Madero 2013-11-09 00:23:06 UTC
Please attach an actual document (not just a picture of it)

Marking as NEEDINFO

Once you attach the document, mark as UNCONFIRMED and we'll take a look at it.

Also, do you happen to know if this ever worked (in previous releases)?
Comment 2 Aurosutru 2013-11-09 03:00:04 UTC
Created attachment 88921 [details]
File converted to XLSX showing vertical shift in text box


Here is a sample file that shows the problem.  Further investigation reveals that the culprit is the conversion from ods to xlsx and back.  Each conversion to xlsx produces a vertical rise of several pixels for all text boxes.

Thanks for your help with this.
Comment 3 Aurosutru 2013-11-09 03:02:08 UTC
Same problem occurs on another computer with earlier versions of Ubuntu and LibreOffice.
Comment 4 Joel Madero 2013-11-09 03:54:09 UTC
you mind updating the version field to the earlier version that you're seeing the problem on?
Comment 5 Aurosutru 2013-11-09 10:05:15 UTC
OK, an older version of LibreOffice running in Ubuntu 12.04 on a 32-bit machine shows the same problem.  

The example file attachment above shows a problem with text boxes shifting vertically that is similar to the original problem I reported.  There are also small, maybe one pixel vertical shifts that occur even in native .ods format when, for example, cell border types are changed.  I mention this just to alert that there may be a fundamental problem in the accounting of vertical spacing for text boxes.
Comment 6 Joel Madero 2013-11-15 02:37:25 UTC
Wow - so this one is an interesting one. What I can confirm is the problem but it's tricky because this is a read problem not a write problem. If I open the xlsx with a really old version of LibreOffice the placement is right. That being said, I'm trying to bibisect now
Comment 7 Joel Madero 2013-11-15 02:50:38 UTC
Okay - I'm going to mark this as new but I really don't know when it was introduced. I thought that I had bibisected it correctly but you say that you see the problem with (I thought it looked okay in 3.6). 

What I did:
Opened your document
Saved as ODS and aligned the text frame
Saved as xlsx

Opened with different versions of LibreOffice - I see shifts in anything post 3.7alpha (ie. 4.0alpha) but 3.6 seems to be better (perhaps not perfect but better). But I'll take your word for it that it's broken in 3.5.7 and leave it at that.

I'm going to attach the bibisect just because there is definitely something in there that has changed the way text frames are imported with xlsx files.

New (confirmed)
Major (for users it seems like there is data loss even though there isn't, warrants major I believe)
High (at least quasi regression, someone has played with the code and made it worse or better between 3.5 and 3.7)

regression - despite not exactly matching up with reporter, I can see a regression in my bibisect

 29eabec9444105ba81c1ba85b5e491919bf1c8d3 is the first bad commit
commit 29eabec9444105ba81c1ba85b5e491919bf1c8d3
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Sun Dec 9 16:52:12 2012 +0000

    commit 2a360b68475d6fff5b6618feddb0b52f3a4a2373
    Author:     Noel Grandin <noel@peralex.com>
    AuthorDate: Tue Jun 12 15:41:27 2012 +0200
    Commit:     Michael Stahl <mstahl@redhat.com>
    CommitDate: Tue Jun 12 23:25:10 2012 +0200
        Convert SV_DECL_PTRARR(SvBaseLinks) to std::vector
        Change-Id: I9197dc4fd7ed32f030de8121913265ec78c83585

:100644 100644 f7eccba8d9d5410eeab71c5acf79c4d2e6b8449c f3050e05432fb8b9624ca76ff6025d190f12ef05 M	autogen.log
:100644 100644 596c69d5a502161bf6c9fa82c7809db0bd2d2717 6b47b1d0f80f893139e6896a1d3b9ec414080790 M	ccache.log
:100644 100644 6a19e107e2479b08da8537ab509cec001af5cf5e be4045365079f31ab24b4d2e4309b743675bd4fe M	commitmsg
:100644 100644 7a8dafbae307c7a593dfef2ac43f747867e41bd8 a67d8e92e07894b300e4f1300d8048a0b39ee4b6 M	dev-install.log
:100644 100644 060ccd79dce129d52524f3c72521506ca6e3fd1d 6dabafb2814fbb1ce5b1a232487d948f60281875 M	make.log
:040000 040000 d31d0ae92b309453f8f3761ec68db0771b43ac07 885fe6e2bcbf9d30789fb4ded5edc3eac7f9c149 M	opt

# bad: [5b4b36d87517a6ea96ff8c84c46b12f462fc9a1a] source-hash-8450a99c744e9005f19173e4df35d65640bcf5c4
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [16b0b88cbd4ef0f51816e97277e40c5cf78f7bf9] source-hash-099198a4224778fe6e43f5dc13b5b9b1b4dc828c
git bisect good 16b0b88cbd4ef0f51816e97277e40c5cf78f7bf9
# bad: [f28b8f9a6c47fa59bf98fffe937a2f2db7a2445a] source-hash-a581d31b227623e09d2970a91214fda398f98eda
git bisect bad f28b8f9a6c47fa59bf98fffe937a2f2db7a2445a
# bad: [5bf3b624cdeb593e55402f44c730209f12813961] source-hash-4b4ca8030285bd66526ff5bb2b6ea5a75a6c6bc7
git bisect bad 5bf3b624cdeb593e55402f44c730209f12813961
# bad: [fbd64ab02c3b611eb2161132a98d2a24ccf109ad] source-hash-77987eacff20dec40caf29aae61d262239d441e9
git bisect bad fbd64ab02c3b611eb2161132a98d2a24ccf109ad
# good: [b8013cdf546a6319d5cd43746b74e35f177a5544] source-hash-699e7d9e4081942bb0ad73e9be73f90a26d0c2f7
git bisect good b8013cdf546a6319d5cd43746b74e35f177a5544
# bad: [778045e259d6c6cdd39e55feea1646e95eab8537] source-hash-6aeeca56daa9065f607cc7056e7d86d237c84a99
git bisect bad 778045e259d6c6cdd39e55feea1646e95eab8537
# bad: [5d495e9d278412d3719fe3d18b429d2b34831241] source-hash-4f5c523b97542bdbfe69fb7695bcb9699c66f89f
git bisect bad 5d495e9d278412d3719fe3d18b429d2b34831241
# bad: [29eabec9444105ba81c1ba85b5e491919bf1c8d3] source-hash-2a360b68475d6fff5b6618feddb0b52f3a4a2373
git bisect bad 29eabec9444105ba81c1ba85b5e491919bf1c8d3
Comment 8 retired 2014-12-28 09:28:07 UTC
OS > All since when I open the test xlsx file the textbox is off in alignment.

To really reproduce, could you please provide a ods speadsheet file which has a correct textbox. This then can be exported as xlsx to reproduce the faulte behavior.

Setting to NEEDINFO until the requested information has been provided.

After that please set the bug back to "NEW". Thanks.
Comment 9 Matthew Francis 2015-01-10 11:12:01 UTC
Created attachment 112053 [details]
Aligned ODS of sample file

Not much to it, all you need is to align the box to the cell grid as tightly as possible
Comment 10 Matthew Francis 2015-01-10 11:19:38 UTC
It's not clear if this is the only issue with respect to the (unattached) file shown in attachment 88897 [details], but one commit which changes the position on load is the below.

Adding Cc: to noel.power@gmail.com; Any chance you could take a look at this? The positional accuracy of round-tripping the text box in the attached file through xlsx seems to have deteriorated after this commit. Thanks

commit d59a707f6e9308c2b89ebf4e71693175c37b7ed6
Author: Noel Power <noel.power@novell.com>
Date:   Mon Jun 4 14:49:31 2012 +0100

    remove anchor to cell shape (re)position hack
    this change manaully reverts c07e9299bb2c8d5477669568b12708bd2ff12684 & 4908c16a73b397f83c327789f113658c1ea4d904
    Change-Id: Ia8656631f84709430c8e94e8d9dfa961e81d5746
Comment 11 Aurosutru 2015-01-11 02:57:12 UTC
Created attachment 112082 [details]
Sample .ods requested by Foss - sorry this is so late

Each time the file is saved as a .xlsx the text box moves upward a bit, making a big change after multiple saves.
Comment 12 Robinson Tryon (qubit) 2015-12-09 18:20:50 UTC Comment hidden (obsolete)
Comment 13 Timur 2017-11-13 17:48:25 UTC
Attachment 112082 [details] repro 5.0, correctly saves vertical position of text boxes in 5.1 and 6.0+. Looks like WFM.
Please test yourself and if I'm wrong, feel free to set back to New. 
Text position in textbox is another issue.