Reading Excel file using apache poi and storing them into arrays

Question

I'm new to the world of programming. well, im trying to read a excel file (5 rows, 5 cols)using apache-poi library. I have actually two implementation of the same problem. In the first code snippet, i just read my excel files and print them into console.

However now im trying to save the read excel data into an array. So i want to set the array size after getting the excel row and column size dynamically. But to my surprise, when i execute my second code snippet, it seems that "while(cellIterator.hasNext()" iterates continuously even though there are only 5 rows, 5 cols in my input excel file. Please guide me where im going wrong.

Thanks Hussain Code snippet 1 (Working as expected)

public static void main(String args[]) {
    readFile(ConfigReader.readConfigValues("XLS-path"),
            ConfigReader.readConfigValues("SheetName"));
}

public static void readFile(String filePath, String sheetName) {

    try {

        FileInputStream file = new FileInputStream(new File(filePath));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheet(sheetName);

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
                }
            }
            System.out.println("");
        }
        file.close();
        FileOutputStream out = new FileOutputStream(new java.io.File(
                "G:\\test1.xls"));
        workbook.write(out);
        out.close();

    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Code snippet 2 (Not working as expected)

public static void main(String args[]) {
    readFile(ConfigReader.readConfigValues("XLS-path"),
            ConfigReader.readConfigValues("SheetName"));
}

public static void readFile(String filePath, String sheetName) {

    try {

        FileInputStream file = new FileInputStream(new File(filePath));

        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheet(sheetName);

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        String[][] excelArray = null;
        excelArray = new String[getRowCount(rowIterator, excelArray)][];

        file.close();
        FileOutputStream out = new FileOutputStream(new java.io.File(
                "G:\\test1.xls"));
        workbook.write(out);
        out.close();

    } catch (FileNotFoundException e1) {
        e1.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

public static int getRowCount(Iterator<Row> rowIterator,
        String[][] excelArray) {
    int sizeArrayRow = 0;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        excelArray[sizeArrayRow] = new String[getColCount(row)];
        sizeArrayRow++;

    }

    return sizeArrayRow;
}

public static int getColCount(Row row) {

    int sizeArrayCol = 0;
    // For each row, iterate through each columns
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        sizeArrayCol++;
    }
    return sizeArrayCol;
}

Answer 1

 public class ExcelToArrayConverter {
        public String[] excelvalue(String columnWanted,int sheet_no){
            int i=0;
            String[] column_content_array =new String[140];
            try{
                int instindicator=-1;       
                InputStream fileIn = this.getClass().getClassLoader().getResourceAsStream("db.xls");
                POIFSFileSystem fs = new POIFSFileSystem(fileIn);
                HSSFWorkbook filename = new HSSFWorkbook(fs);
                HSSFSheet sheet = filename.getSheetAt(sheet_no);                                                // in the row 0 (which is first row of a work sheet)                                                    // search for column index containing string "Inst_Code"
                Integer columnNo = null;
                Integer rowNo = null;
                List<Cell> cells = new ArrayList<Cell>();
                Row firstRow = sheet.getRow(0);
                for (Cell cell : firstRow) {
                    if (cell.getStringCellValue().equals(columnWanted)) {
                        columnNo = cell.getColumnIndex();
                        rowNo=cell.getRowIndex();
                    }
                }
                if (columnNo != null) {
                    for (Row row : sheet) {
                        Cell c = row.getCell(columnNo);
                        String cell_value=""+c;
                        cell_value=cell_value.trim();
                        try{
                            if((!cell_value.equals(""))&&(!cell_value.equals("null"))&&(!cell_value.equals(columnWanted))){ 
                                column_content_array[i]=cell_value;
                                i++;
                            }}
                        catch(Exception e){
                        }

                    }
                    return column_content_array;
                }}
            catch(Exception ex){
                return column_content_array;
            }
            return column_content_array;

        }}

 This method will convert any specific column of a specific sheet to an array.

Answer 2

To see how Java iterators work, read this: http://www.tutorialspoint.com/java/java_using_iterator.htm In particular, notice the difference between hasNext() and next()

You are checking:

while (cellIterator.hasNext())

but you are never "reading" anything from that iterator, so it stays at its current position and keeps returning true for hasNext. You could push it formward with:

cellIterator.next();

Also, I think the code is messy and difficult to follow. Instead of a String array, consider using a List. That way, you can populate it without knowing its size in advance.