CODEWITHSUNDEEP
javaapache-poi
user3595179
user3595179

Reputation: 15

POI excel into 2D array

I am trying to make use of Apache POI to read an excel file and convert it to a 2-dimensional object array. Attached is the code section.

   public class ImportUtil {

    public static DefaultTableModel importFromFile(File f) {
        DefaultTableModel tableModel = null;
        try {
            FileInputStream file = new FileInputStream(f);
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            List<Object> columnNames = new ArrayList<Object>();
            Vector<Object> cellVals = new Vector<Object>();
            Vector<Vector<Object>> tempData = new Vector<Vector<Object>>();
            while(rowIterator.hasNext()){
                //cellVals.clear();
                cellVals.clear();
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                /*
                 * Treat first row in excel file as column names, column names are assumed to be of type String 
                 */
                if(row.getRowNum()==0){
                    while(cellIterator.hasNext()){
                        Cell cell = cellIterator.next();
                        columnNames.add(cell.getStringCellValue());
                    }
                }else{
                    while(cellIterator.hasNext()){
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            if(DateUtil.isCellDateFormatted(cell)){
                                cellVals.add(cell.getDateCellValue());
                            }else{
                                cellVals.add(cell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cellVals.add(cell.getStringCellValue());
                            break;
                        default:
                            System.out.print("Unhanlded Type." + "\t\t\t");
                            break;
                        }
                    }
                    System.out.println(cellVals);
                    tempData.add(cellVals);

                }
            }
            file.close();
            Object[][] modelData = VectorToArray.to2DArray(tempData);
            for(int i=0;i<modelData.length;i++){
            for(int j=0;j<modelData[i].length;j++){
                    System.out.print(modelData[i][j]);
                }
                System.out.println("");
            }
            tableModel = new DefaultTableModel(modelData, columnNames.toArray());
        } catch (IOException e) {
            e.printStackTrace();
        }
        return tableModel;
    }
}

class VectorToArray{
    public static Object[][] to2DArray(Vector<Vector<Object>> v){
        Object[][] out = new Object[v.size()][0];
        for(int i=0;i<out.length;i++){
            out[i] = ((Vector<Object>)v.get(i)).toArray();
        }
        return out;
    }
}

The code compiles, but it doesn't function properly. When I print the cellVals which contains a row of data, it is correct. But this section:

    Object[][] modelData = VectorToArray.to2DArray(tempData);
    for(int i=0;i<modelData.length;i++){
        for(int j=0;j<modelData[i].length;j++){
            System.out.print(modelData[i][j]);
        }
        System.out.println("");
    }

print the last row in the excel sheet. I couldn't figure it out. Any suggestions is appreciated.

Upvotes: 0

Views: 2107

Answers (2)

user3595179
user3595179

Reputation: 15

The problem is solved by declaring cellVals in a different location.

if(row.getRowNum==0){
}
else{
   Vector<Object> cellVals = new Vector<Object>();
}

In solution 1 (incorrect), I declare cellVals first and use cellVals.clear() to clear the contents from previous row. Whereas in solution 2(Seems to be correct), I declare a new cellVals when needed.

Upvotes: 0

A.B
A.B

Reputation: 114

If I'm not mistaken, when you add an element to a list that doesn't "copy" the element, it takes the same reference. So when you do tempData.add(cellVals) and then clear cellVals that will clear the one you just added. That's why you end up with only the last CellVals in your tmpList.

instead of clearing the list try to create a new one and fill it.

Upvotes: 2

Related Questions