Q:

ArrayIndexOutOfBoundException with jxl api when trying to copy content from one sheet to another

Whenever I am trying to use template to create xls using jxl API running into weird ArrayIndexOutOfBoundException.
Here is the snippet of code I am trying to use
private static void readWorkSheet()throws Exception{
            File file = new File("C:\\reports\\");
            Map template = new HashMap();
            File[] listFiles = file.listFiles();
            for(File file1:listFiles){
                if(file1.getName().endsWith(".xls")){
                    System.out.println(" ==> "+file1.getName());
                    Workbook workbookTemplate = Workbook.getWorkbook(file1);
                    template.put(file1.getName(), workbookTemplate);
                }

            }
            System.out.println("template "+template);
            Workbook templateWorkBook = (Workbook)template.get("TestReport.xls");
            Sheet readSheet = templateWorkBook.getSheet("Sheet1");

            WritableWorkbook copy = Workbook.createWorkbook(new File("c://myfile_copy2.xls"));

            WritableSheet sheet = copy.createSheet("Test", 0);
            for (int i = 0; i < readSheet.getRows(); i++) {
                for (int j = 0; j < readSheet.getColumns(); j++) {
                    Cell readCell = readSheet.getCell(j, i);
                    CellFormat readFormat = readCell.getCellFormat();
                    if(readFormat != null && readCell.getContents() != null && readCell.getContents() != ""){
                        WritableCell newCell = new Label(i,j,readCell.getContents());
                        WritableCellFormat newFormat = new WritableCellFormat(readFormat);
                        newCell.setCellFormat(newFormat);
                        System.out.println("details of cell ["+i+", "+j+"]"+" Name = "+readCell.getContents());
                        System.out.println("details of newCell ["+i+", "+j+"]"+" Name = "+newCell.getContents());
                        sheet.addCell(newCell);
                    }
                }
        }
            copy.write();
            copy.close(); 
        }

Not sure what I am missing in this !!!
Exception I am running into
  Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 56
    at jxl.biff.IndexMapping.getNewIndex(IndexMapping.java:68)
    at jxl.biff.XFRecord.rationalize(XFRecord.java:1667)
    at jxl.biff.FormattingRecords.rationalize(FormattingRecords.java:443)
    at jxl.write.biff.WritableWorkbookImpl.rationalize(WritableWorkbookImpl.java:1023)
    at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:701)
    at com.jxl.test.JXLTest.readWorkSheet(JXLTest.java:83)
    at com.jxl.test.JXLTest.main(JXLTest.java:30)

A:

I face the same problem. But it occurs only if I try to create more than one excel file by the same class. Means if i create two files by one junit test it occurs. If I do this by two separate unit test classes everything works fine. I am just searching for a kind of strong reference in the jxl code.
Until then I found a Workaround:
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setRationalization(false);

The problem occurs due to the FormattingRecords.rationalize() method. This setting above switches of the rationalization process. DateFormats and NumberFormats and so on will work BUT you will loose the WritableFonts for the cell format by doing this.
Stay tuned - maybe I will find more.
... I found more:
The main problem is that the jxl.biff.Fonts class is not reinitialized correctly and only contains the 4 default FontRecord entities. I dug a bit deeper and found that in the initialize method of the XFRecord class there is an if statement:
if (!font.isInitialized())
{
   fonts.addFont(font);
}

For the very first time a font is not initialized and will be added here successfully. For all other iterations the font is initialized and won't be added. No idea who holds the XFRecords and why it isn't refreshed correctly. But I found a working workaround:
@SuppressWarnings("unchecked")
private void adjustUsedFonts(WritableWorkbook workbook) throws NoSuchFieldException, IllegalAccessException {
    WritableWorkbookImpl workbookImpl = (WritableWorkbookImpl) workbook;
    Field fontsField = WritableWorkbookImpl.class.getDeclaredField("fonts");
    fontsField.setAccessible(true);
    Fonts fonts = (Fonts) fontsField.get(workbookImpl);
    Field fontsListField = Fonts.class.getDeclaredField("fonts");
    fontsListField.setAccessible(true);
    ArrayList<FontRecord> fontRecords = (ArrayList<FontRecord>) fontsListField.get(fonts);
    // just to get to know which fonts are available
    for (FontRecord fontRecord : fontRecords) {
        logger.info("found font: name={}; size={}", fontRecord.getName(), fontRecord.getPointSize());
    }
    // DON'T DO THIS HARDCODED LIKE THIS 
    // BUT CHECK IF YOUR FONTS ARE AVAILABLE AND ADD IF NOT
    if (fontRecords.size() == 4) {
        logger.info("only 4 fonts available - add necessary ones");
        fontRecords.add(tableDefaultFont);
    }
}

I put this method in front of calling the workbook.write method. And it works all fonts are available.
I know this is a hack and no good solution. This is why I will open an issue at their Ticket-Queue (the hack is only for those who cannot wait until the fix is available).
I used this version of jxl and it seemed to be the latest:
groupId: net.sourceforge.jexcelapi
artifactId: jxl
version: 2.6.12
ADDITION:
If you have more than one own font you will also need to add it in the right order by using its fontIndex. Otherwise fonts will be mixed up. I wrote a helpful method to find out the right index:
    private void determineFontIndizesOfOwnFonts(WritableWorkbook workbook) throws NoSuchFieldException, IllegalAccessException {
    WritableWorkbookImpl workbookImpl = (WritableWorkbookImpl) workbook;
    Field frField = WritableWorkbookImpl.class.getDeclaredField("formatRecords");
    frField.setAccessible(true);
    FormattingRecords formRecords = (FormattingRecords) frField.get(workbookImpl);
    Field xfRecordListField = FormattingRecords.class.getDeclaredField("xfRecords");
    xfRecordListField.setAccessible(true);
    ArrayList<XFRecord> xfRecords = (ArrayList<XFRecord>) xfRecordListField.get(formRecords);
    logger.debug("amount of xfRecords: {}", xfRecords.size());
    for (XFRecord curXfRecord : xfRecords) {
        Font curFont = curXfRecord.getFont();
        if (curFont.equals(tableHeaderFont)) {
            logger.debug("font 'tableHeaderFont' info: idx={}, initialized={}, font[{}, {}px]", curXfRecord.getFontIndex(), curXfRecord.isInitialized(),
                    curFont.getName(), curFont.getPointSize());
            if (!fontIdxToName.containsKey(curXfRecord.getFontIndex())) {
                fontIdxToName.put(curXfRecord.getFontIndex(), tableHeaderFont);
            }
        }
        if (curFont.equals(tableContentFont)) {
            logger.debug("font 'tableContentFont' info: idx={}, initialized={}, font[{}, {}px]", curXfRecord.getFontIndex(), curXfRecord.isInitialized(),
                    curFont.getName(), curFont.getPointSize());
            if (!fontIdxToName.containsKey(curXfRecord.getFontIndex())) {
                fontIdxToName.put(curXfRecord.getFontIndex(), tableContentFont);
            }
        }
        if (curFont.equals(tableImportantOrFooterFont)) {
            logger.debug("font 'tableImportantOrFooterFont' info: idx={}, initialized={}, font[{}, {}px]", curXfRecord.getFontIndex(),
                    curXfRecord.isInitialized(), curFont.getName(), curFont.getPointSize());
            if (!fontIdxToName.containsKey(curXfRecord.getFontIndex())) {
                fontIdxToName.put(curXfRecord.getFontIndex(), tableImportantOrFooterFont);
            }
        }
    }
}

then afterwards just add it in the right order see other method.

