Bug Hunting Session
Bug 83746 - FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is truncated to 1-983040
Summary: FILEOPEN: When opening an .xls with a 1-1M VLOOKUP range, the range is trunca...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.5.2 release
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:5.2.0 target:5.1.4
Keywords:
: 93878 (view as bug list)
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
 
Reported: 2014-09-11 03:38 UTC by wiwiroon-free
Modified: 2017-07-25 03:41 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
テスト用ダミーデータ (134.41 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-09-15 00:56 UTC, wiwiroon-free
Details
Minimal sample .xls (5.50 KB, application/vnd.ms-excel)
2014-09-15 16:33 UTC, Matthew Francis
Details
sample file using the provided steps (6.50 KB, application/vnd.ms-excel)
2014-09-15 16:50 UTC, Yousuf Philips (jay) (retired)
Details
Expanded example (as ODS) (9.83 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-09-16 13:30 UTC, Matthew Francis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description wiwiroon-free 2014-09-11 03:38:17 UTC
Display "REF!" after Counting, Save and RE:Open in fx:VLOOKUP

Problem description: 
*I'm Japanese and well not speak English, then, I write Japanese ,sorry.
まず、ページシートにページ番号と型番だけが入力されたリストがあります。次に集計シートのB列には型番があり、C列には金額が入っています。集計シートのA列にはB列の型番をページシートで調べるVLOOKUPが設定してあり、ページシートを探して同じ型番のものがあれば、その横にあるページ番号を表示するようにしています。この状態で、ページを元にして合計の集計を行います。ここまではうまく行っています。

ところが、集計後のページを保存して再度開きなおすと、集計シートのA列が全てREF表示になってしまいます。
=VLOOKUP(C2,$ページ.#REF!$#REF!:#REF!$#REF!,2)
こういう式が入ってしまいます。閉じる前までは
=VLOOKUP(C2,$ページ.A$1:B$200,2)
といった式が入っていました。

Steps to reproduce:
1. VLOOKUPで作ったページ番号列を元に集計を取る
2. 保存して閉じて再び開く

手順で言うとこれしかしていませんが、何度やっても再現します。
日本語では受け付けてもらえないかもしれませんが、大変困っていますので、ダメもとで投げてみたいと思います。よろしくお願いいたします。

Current behavior:

Expected behavior:

             
Operating System: Windows 7
Version: 4.2.5.2 release
Comment 1 Matthew Francis 2014-09-11 04:48:29 UTC
バグレポートをありがとうございました。

直ちにはバグを再現できませんでした。お手数かけてすみませんが、問題のファイルを添付していただけませんか。機密データが入った場合には、余計なシートや数字を除去した形でも大丈夫ですが、元の状態でのバグが見えたら助かります。

うまく再現できたら英語での再現手順を書かせていただきます。
よろしくお願いします。

(In English)
Thank you for the bug report.
I couldn't immediately reproduce the bug. Could you possibly attach the problematic file? If it contains confidential data, you can remove all unnecessary sheets and figures, but it would be helpful to see the bug in its original form.

Once the bug has been reproduced, I will write reproduction instructions in English.
Thank you.
Comment 2 wiwiroon-free 2014-09-15 00:56:05 UTC
Created attachment 106291 [details]
テスト用ダミーデータ
Comment 3 wiwiroon-free 2014-09-15 01:09:42 UTC
ご返答ありがとうございます。
再現しようとしてしていたときに、ひとつ条件が抜けていたことがわかりました。
「xls形式で保存したとき」限定のようです。

ファイルを添付しましたが、こちらで
 1:小計を取ってods形式で保存する
 2:そのままxls形式で保存する
という2パターンでは問題ありませんでしたが、
 3:小計を取ってxls形式で保存する
としたときに、再び開きなおすとREF表示になることがわかりました。

さらに、再現させない手順も判明しました。
ダミーデータのリストシートのページ列には現在、
=VLOOKUP(B781,ページ.$A$1:$B$1048576,2)
といった数式が入っていますが、
この「$B$1048576」のところの数字を「$B$900」のように小さくすれば、
xls形式で開いても問題なく表示されるようです。

おそらくExcelとCalcを交互に使っていたことが原因のようにも思います。
Excel側のVLOOKUP関数の場合、範囲指定は
=VLOOKUP(B781,A:B,2)
という書き方になりますが、このA:BがCalcへ移ったときに、
=VLOOKUP(C34,$ページ.A$1:B$983040,2)
のように、勝手に数値が入力されていました。

xls形式の保存は保障外だったかもしれませんが、
ひとまず以上を報告させていただきます。
よろしくお願いいたします。
Comment 4 Matthew Francis 2014-09-15 02:28:48 UTC
テストデータをありがとうございます。

新しいファイルでVLOOKUPのターゲットを$C$1:$D$1048576にして旧型xlsとして保存みたら、また開くと確かにターゲットが$C$1:D$983040になってしまいました。
(数値的にはこれは1048576-65536 (1M-64K)であることには関係がありそう)

今も#REF!表示は再現できていませんが、以上の問題が原因ならば解決できるはずと思います。

代わりにExcelを使ってこのようなファイルを開いたらちゃんとまだ$D$1048576になっているし、Excelを使ってxlsとして保存して再び開いても問題ないので、これはおそらくLibreOfficeのxlsセーブ機能の問題のようです。

回避策としては、Excelファイルが必要なときにはできればOffice Open XML Spreadsheet (.xlsx)を使った方が効果的だと思います。


(Summary in English)

When a formula such as =VLOOKUP(B1,$C$1:$D$983040,2) is saved as an old style .xls (not .xlsx) and reloaded, the target is corrupted to $C$1:$D$983040

Reproduced on OSX 10.9.4/LO 4.3.1.2

Steps to reproduce:
1. In cell A1 of a new spreadsheet, enter the formula =VLOOKUP(B1,$C$1:$D$1048576,2)
2. Save the file as .xls (Microsoft Excel 97/2000/XP/2003)
3. Close and reopen the file, and check the target range of the VLOOKUP

Excel shows the correct result when opening such a file, and has no problem saving/reloading it itself, so this is probably an issue in the import filter for .xls.
The fact that 983040=1048576-65536 is probably relevant.

The obvious workaround is to use an .xslx for file interchange when possible.
Comment 5 wiwiroon-free 2014-09-15 03:02:15 UTC
ご返答ありがとうございます。ひとまず回避策ができたため、私の中では喫緊の問題ではなくなりました……申し訳ありません。xls形式で保存していたのも、Excelファイルが必要なのではなく、以前から使っていたデータがxls形式だったから使っていた、というだけでしたので、今後はods形式で作業を行いたいと思います。

お忙しい中ご対応いただき、ありがとうございました。
Comment 6 Matthew Francis 2014-09-15 16:33:30 UTC
Created attachment 106331 [details]
Minimal sample .xls

Should contain "=VLOOKUP(B1,$C$1:$D$1048576,2)" at A1
Comment 7 Yousuf Philips (jay) (retired) 2014-09-15 16:50:59 UTC
Created attachment 106334 [details]
sample file using the provided steps

Opening Matthew's sample xls, A1 is '=VLOOKUP(B1,$C$1:$D$983040,2)' in 4.2.6 and 4.3.3. But when i followed his instructions in creating the attached file in 4.2.6, reopening results in '=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)'.
Comment 8 Matthew Francis 2014-09-16 13:30:33 UTC
Created attachment 106373 [details]
Expanded example (as ODS)

This expanded ODS example contains the formulae
=VLOOKUP(B1,$C$1:$D$1048576,2)
=VLOOKUP(B1,$C$1:$D$983040,2)
=VLOOKUP(B1,$C$1:$D$983039,2)
=VLOOKUP(B1,$C$1:$D$65537,2)
=VLOOKUP(B1,$C$1:$D$65536,2)
=VLOOKUP(B1,$C$1:$D$65535,2)
=VLOOKUP(B1,$C$1:$D$100,2)

After saving as .xls, closing and reloading, the result in OSX/4.3.1.2 is
=VLOOKUP(B1,$C$1:$D$983040,2)
=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)
=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)
=VLOOKUP(B1,$#REF!$#REF!:$#REF!$#REF!,2)
=VLOOKUP(B1,$C$1:$D$983040,2)
=VLOOKUP(B1,$C$1:$D$65535,2)
=VLOOKUP(B1,$C$1:$D$100,2)

Based on this, it looks to me as if the handling of any row range >= 65536 is broken, sometimes resulting in an incorrect range and sometimes a #REF!

The original reporter did also mention something about seeing #REF!, but I couldn't reproduce that initially - however, having now done so, it looks like it's still part of the same bug.
Comment 9 raal 2014-09-20 06:08:46 UTC
Format .xls handles only 65 356 rows
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx
Comment 10 tommy27 2016-04-16 07:24:26 UTC Comment hidden (obsolete)
Comment 11 Eike Rathke 2016-05-06 16:51:44 UTC
We can't do anything about the 65536 row limit of .xls, but the 983040=1048576-65536 looks odd and likely should be handled differently..
Comment 12 Eike Rathke 2016-05-19 17:33:42 UTC
*** Bug 93878 has been marked as a duplicate of this bug. ***
Comment 13 Commit Notification 2016-05-19 22:02:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6821ad076c276b997c44520fd700817566a718c5

Resolves: tdf#83746 wrapAddress() didn't do what it was supposed to do

It will be available in 5.2.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 14 Eike Rathke 2016-05-19 22:22:23 UTC
Pending review https://gerrit.libreoffice.org/25182 for 5-1
Comment 15 Commit Notification 2016-05-20 21:56:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=01a3831f9dc1b9f64775f3382180d27a3b51f131&h=libreoffice-5-1

Resolves: tdf#83746 wrapAddress() didn't do what it was supposed to do

It will be available in 5.1.4.

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 16 Óvári 2016-05-21 10:34:03 UTC
(In reply to Commit Notification from comment #13)
> Affected users are encouraged to test the fix and report feedback.

This may be related to:
Bug 99980 FILEOPEN: XLS and XLXS filters with absolute & relative referencing of entire column (and maybe row)

Thank you