Created attachment 31297 [details] CNB_01_all.xls reproduces the bug Dear All, the method shift rows always fails for me: Exception in thread "main" java.lang.RuntimeException: Could not find 'internal references' EXTERNALBOOK at org.apache.poi.hssf.model.LinkTable.checkExternSheet(LinkTable.java:433) at org.apache.poi.hssf.model.InternalWorkbook.checkExternSheet(InternalWorkbook.java:1796) at org.apache.poi.hssf.usermodel.HSSFSheet.shiftRows(HSSFSheet.java:1494) at org.apache.poi.hssf.usermodel.HSSFSheet.shiftRows(HSSFSheet.java:1343) The code is extremely simple: import java.io.*; import org.apache.poi.hssf.usermodel.*; /** * * @author are */ public class ExcelToolsBug { public static void main(String[] args) throws FileNotFoundException, IOException { String fileName="CNB_01_all.xls"; int rowOffset = 11; String sheetName = "MBR302"; File file = new File(fileName); InputStream inputStream = new FileInputStream(file); final HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheet(sheetName); for (int r = 0; r < 100; r++) { sheet.shiftRows(rowOffset+1, sheet.getLastRowNum(), 1, true, true); } } } I attached the Excel-File, which reproduces the problem. It was created with LibreOffice Version: 4.1.4.2 Build ID: Gentoo official package. I also tried to open/save that file with Excel and the same problem occurred afterwards. Please can anyone help me on that? I do not understand unfortunately the purpose and the functionality of the method "checkExternSheet". Thank you and best regards Andreas
(In reply to andreas from comment #0) > Please can > anyone help me on that? I do not understand unfortunately the purpose > and the functionality of the method "checkExternSheet". The comment for the code which calls checkExternSheet is: // Update any formulas on this sheet that point to // rows which have been moved At first glance, it looks like the table that maps between sheet names and indexes for formulas has ended up confused, and/or that there's a bug in our logic for handling that on some edge case. A few things you could try: * Step into LinkTable in a debugger, and see what it does know about, and if the ids and names match what the normal names are * Try deleting a sheet in Excel, saving, and seeing if that fixes it * Try adding a sheet in in Excel, saving, and seeing that fixes it
(In reply to Nick Burch from comment #1) > The comment for the code which calls checkExternSheet is: > > // Update any formulas on this sheet that point to > // rows which have been moved Yes, clear. But what is the exact purpose of the failing function? Please note, there is no reference to any other worksheet or workbook. All the formula refer to the single worksheet only. > At first glance, it looks like the table that maps between sheet names and > indexes for formulas has ended up confused, and/or that there's a bug in our > logic for handling that on some edge case. > A few things you could try: > * Step into LinkTable in a debugger, and see what it does know about, and > if the ids and names match what the normal names are Seems to be very likely. Unfortunately I am on travel an can not pull the full maven-repository at the moment. > * Try deleting a sheet in Excel, saving, and seeing if that fixes it > * Try adding a sheet in in Excel, saving, and seeing that fixes it I tried this with various combinations a thousand times in order to understand what is going on. I've created new workbooks from scratch, I copied existing worksheets over, I copied ranges, I inserted sheets etc etc. The only hint I can give: 1) one single sheet seems to work but (mostly), but simple reformatting can sometimes break it (e.g. change width of several columns at once) 2) as soon as you add another worksheet it breaks the new worksheet, but also the first (formerly working) one
*** Bug 59059 has been marked as a duplicate of this bug. ***