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;
}

Darius X. · Accepted Answer · 2013-02-19 14:50:11Z

3

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.

edited Feb 19, 2013 at 14:50

answered Feb 18, 2013 at 18:28

Darius X.

2,9374 gold badges28 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Hussain Over a year ago

Hi Darius, sorry i forgot to mention it. Actually i'm using "cellIterator.next()" to iterate through the excel cells which is actually working. But the problem i'm facing is, it continuously iterates even though there are 5 cols in my excel sheet :)

Darius X. Over a year ago

Do you mean that the second snippet of code is different from what you are using? Because, while the first snippet contains "cellIterator.next()" the second snippet does not.

Hussain Over a year ago

Hi darius, even second snippet contains the same code, but its been put into a method.

Darius X. Over a year ago

The method getColCount() does not contain a call to next(), only to hasNext().

Hussain Over a year ago

Your are right. Thats what i'm actually trying to do. Get a row id and then iterate through the cols using hasNext() method. Since in my excel sheet, suppose row-1 has 5 cols, the while loop for row-1 in excel sheet (row-1.hasNext()) should iterate only 5 times and im expecting that to be a correct behavior.However now it iterates indefinitely though the actual column for row-1 is 5

|

Anupam Maiti · Accepted Answer · 2013-10-04 13:10:32Z

 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.

Collectives™ on Stack Overflow

Reading Excel file using apache poi and storing them into arrays

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related