Bug 56123 - ShiftRows, Bug in POI 3.10 Beta 2: "Could not find 'internal references' EXTERNALBOOK"
Summary: ShiftRows, Bug in POI 3.10 Beta 2: "Could not find 'internal references' EXTE...
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-dev
Hardware: PC Linux
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
: 59059 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-02-09 10:26 UTC by andreas
Modified: 2016-03-11 22:00 UTC (History)
1 user (show)



Attachments
CNB_01_all.xls reproduces the bug (7.50 KB, application/vnd.ms-excel)
2014-02-09 10:26 UTC, andreas
Details

Note You need to log in before you can comment on or make changes to this bug.
Description andreas 2014-02-09 10:26:01 UTC
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
Comment 1 Nick Burch 2014-02-09 23:14:17 UTC
(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
Comment 2 andreas 2014-02-10 08:17:14 UTC
(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
Comment 3 Dominik Stadler 2016-03-11 22:00:04 UTC
*** Bug 59059 has been marked as a duplicate of this bug. ***