Nov 24

Once you have opened your Excel file you are probably going to want to access sheets within that file.

The Workbook class contains methods to access sheets by index or by name as shown in the following examples.

int nsheets = workbook.getNumberOfSheets();
for (int i=0; i<nsheets; i++) {
    Sheet sheet = workbook.getSheetAt(i);
				
    // Now you can access the sheet
}

And to access a sheet by name.

Sheet sheet = workbook.getSheet(sheetName);

Stay tuned as we continue to investigate using POI.

written by objects \\ tags: , , ,

Feb 02

There are a couple of ways to approach the problem of converting a spreadsheet column number to a spreadsheet style label. The following shows how our column numbers need to be mapped:

0 -> A
1 -> B
2 -> C

25 -> Z
26 -> AA
27 -> AB

Firstly using recursion:

    public static String getColumnLabel(int columnNumber) {
    	char c = (char) ('A' + (columnNumber % 26));
        if (columnNumber<26) {
         return Character.toString(c);
       } else {
         return getColumnLabel(columnNumber/ 26 - 1)+c;
       }
    }

And secondly using a loop instead of recursion:

    public static String getColumnLabel(int columnNumber) {
        StringBuilder result = new StringBuilder();
        for (; columnNumber >= 0; columnNumber = columnNumber / 26 - 1) {
            result.insert(0, (char)(columnNumber % 26 +'A'));
        }
        return result.toString();
    }

written by objects \\ tags: ,