Bug 48294 - setSheetOrder() breaks inter-sheet references
Summary: setSheetOrder() breaks inter-sheet references
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.5-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-11-26 10:41 UTC by Joe
Modified: 2011-07-02 13:28 UTC (History)
0 users

Run this file; a spreadsheet with the problem is dumped to stdout. (903 bytes, text/plain)
2009-11-26 10:41 UTC, Joe

Note You need to log in before you can comment on or make changes to this bug.
Description Joe 2009-11-26 10:41:10 UTC
Created attachment 24625 [details]
Run this file; a spreadsheet with the problem is dumped to stdout.

If you call setSheetOrder() on sheets on a workbook with inter-sheet references (e.g., a formula like ='Sheet 2'!A3 on Sheet 1), the references can be broken. The attached Java file shows how to reproduce the bug.

Tested with POI 3.5-FINAL (poi-bin-3.5-FINAL-20090928.tar.gz) on Debian with this Java:

java version "1.6.0_16"
Java(TM) SE Runtime Environment (build 1.6.0_16-b01)
Java HotSpot(TM) Server VM (build 14.2-b01, mixed mode)

And OpenOffice.org 3.1.1.

Resolution: I avoided the bug by creating all sheets at once in the order in which they would stay in the final file. An easy workaround would be to add a sentence to the documentation of setSheetOrder() warning the user that it may break references.
Comment 1 Antti Koskimäki 2011-06-29 04:55:03 UTC
Still present (3.8b4). Work-around gives me headache, final sheet-order isn't always known early enough.
Comment 2 Yegor Kozlov 2011-07-02 13:28:32 UTC
Fixed in r1142219

setSheetOrder() now updates cell formulas, defined names and formulas in conditional formatting. Same rules are involved in Sheet.shiftRows. 

Formulas in data validations aren't updated, but this will require much more technical efforts. I'm leaving this out for the future.