How do I create several different sheets in excel using jxl.jar and a while loop?

I am trying to write code that will create multiple sheets based on the amount of array objects I have stored. Currently is breaks at WritableSheet excelSheet = Work.getSheeet(sheetCounter). Giving me an IndexOutOfBoundsException. It will run once but on the second run breaks.

public class DailyFile {
int i = 0; 
static int sheetCounter = 0;

private WritableCellFormat arialBold;
private WritableCellFormat arial;
private String inputFile;

public void setOutputFile(String inputFile) {
    this.inputFile = inputFile;
}

public void write() throws IOException, WriteException {
    File file = new File(inputFile);
    WorkbookSettings wbSettings = new WorkbookSettings();

    wbSettings.setLocale(new Locale("en", "EN"));

    WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
    workbook.createSheet(finalGui.ExperimentCodeList.get(sheetCounter).toString(), sheetCounter);
    WritableSheet excelSheet = workbook.getSheet(sheetCounter);
    createLabel(excelSheet);
    // createContent(excelSheet);

    workbook.write();
    workbook.close();
}

This is where it calls the function to write.

public static void main(String[] args) throws WriteException, IOException {
    DailyFile test = new DailyFile();
    test.setOutputFile("c:/Users/sbutler/Desktop/CERES_Daily-BLIND_TEST.xls");
    while (sheetCounter <= finalGui.ExperimentCodeList.size()){
        System.out.println("I RAN!");
        test.write();
        sheetCounter++;
        System.out.println(sheetCounter);
    }
    System.out.println("Please check the result file under c:/Users/sbutler/Desktop/CERES_Daily-BLIND_TEST.xls ");
}

Here is the stacktrace

Exception in thread "AWT-EventQueue-0" java.lang.IndexOutOfBoundsException: Index: 30, Size: 1
at java.util.ArrayList.rangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at jxl.write.biff.WritableWorkbookImpl.getSheet(WritableWorkbookImpl.java:408)
at gui.DailyFile.write(DailyFile.java:47)
at gui.DailyFile.main(DailyFile.java:293)
at gui.finalGui$4.mouseClicked(finalGui.java:127)
at java.awt.AWTEventMulticaster.mouseClicked(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$400(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Please post a code response as I am struggling with this problem for some time.

Solution

Reason.

Exception in thread "AWT-EventQueue-0" java.lang.IndexOutOfBoundsException: Index: 30, Size: 1
at java.util.ArrayList.rangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at jxl.write.biff.WritableWorkbookImpl.getSheet(WritableWorkbookImpl.java:408)

The first 4 lines of the stack trace says you're trying to access a value to the Index 30 in a size 1 array. It also says where: at the line WritableSheet excelSheet = workbook.getSheet(sheetCounter); of your write() function.

The 2 lines before this are:

WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
workbook.createSheet(finalGui.ExperimentCodeList.get(sheetCounter).toString(), sheetCounter);

It says: create a new workbook and a new sheet inside, for each call of write(). Hence, createSheet(String name, int index) documentation says:

Creates, and returns a worksheet at the specified position with the specified name If the index specified is less than or equal to zero, the new sheet is created at the beginning of the workbook. If the index is greater than the number of sheet, then the sheet is created at the end of the workbook.

The end here will always be at index 0, since the Workbook is always brand new. On the next instruction, when you try to access a sheet of index 30, it raises an exception because the only one you can get is at index 0.

How to fix it?

Here is the logic (I'll let you write the code).

Create pour xls file, create an empty workbook. (Initialization phase)
Loop on your workbook to create sheets, the way you did. (What you really want to do)
Close everything you need to close - at least the workbook. (Leave everything clean and tidy)

A bit of code

The main changes are:

Use of the constructor
Loop moved into write()
New write() parameter (for the loop)
Use the returned value of workbook.createSheet

I can't compile it, but it should look like this.

public class DailyFile {
    int i = 0;

    private WritableCellFormat arialBold;
    private WritableCellFormat arial;
    private String inputFile;

    public DailyFile(String inputFile) {
        this.inputFile = inputFile;
    }

    public void write(int sheetCount) throws IOException, WriteException {
        // Step 1: Initialization
        File file = new File(inputFile);

        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);

        // Step 2: Main logic
        for (int index = 0; index < sheetCount; index++) {
            WritableSheet excelSheet = workbook.createSheet(finalGui.ExperimentCodeList.get(index).toString(), index);
            createLabel(excelSheet);
            // createContent(excelSheet);
        }

        // Step 3: Closing
        workbook.write(); // <-- maybe in the loop?
        workbook.close();
    }

    public static void main(String[] args) throws WriteException, IOException {
        DailyFile test = new DailyFile("c:/Users/sbutler/Desktop/CERES_Daily-BLIND_TEST.xls");
        System.out.println("I RAN!");
        test.write(finalGui.ExperimentCodeList.size());
        System.out.println(sheetCounter);
        System.out.println("Please check the result file under c:/Users/sbutler/Desktop/CERES_Daily-BLIND_TEST.xls ");
    }
}