KH19 KH19 - 5 months ago 12
Java Question

Read data from excel and Write to List

I want to get data to

List
and display it.But out put display 4 times.
My
excel
file containing
4
data.
I want to get one record. My code is

public static List readDataFromExcel() throws IOException{
String filename = "path";

List sheetData = new ArrayList();

FileInputStream fis = null;
try {

fis = new FileInputStream(filename);

XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);

Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
XSSFRow row = (XSSFRow) rows.next();
Iterator cells = row.cellIterator();

List data = new ArrayList();
while (cells.hasNext()) {
XSSFCell cell = (XSSFCell) cells.next();
String value=" ";
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
value = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
data.add(value);
break;
case Cell.CELL_TYPE_STRING:
value=cell.getStringCellValue();
data.add(value);
break;
case Cell.CELL_TYPE_BLANK:
value = " ".toString();
data.add(value);
break;
case Cell.CELL_TYPE_BOOLEAN:
value = Boolean.valueOf(cell.getBooleanCellValue()).toString();
data.add(value);
break;
}

sheetData.add(data);

}

fis.close();
}
} catch (IOException e) {
e.printStackTrace();
}
return sheetData;
}


Main methods

public static void main(String[] args) throws IOException {

List serverdetailsList = ReadDataFromExcel.readDataFromExcel();
List oneserverdetailsList = new ArrayList();

for (int i = 0; i < serverdetailsList.size(); i++) {

System.out.println(serverdetailsList.get(i));

}

}


Out put image
enter image description here

Answer

I see you are adding the inner ArrayList(data) to the main arrayList (sheetdata) as many as the number of times you find a cell.

The approach should have been

 while (rows.hasNext()) {
            XSSFRow row = (XSSFRow) rows.next();
            Iterator cells = row.cellIterator();

            List data = new ArrayList();       
   while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                String value=" ";
                switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                      value =                BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
                        data.add(value);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        value=cell.getStringCellValue();
                        data.add(value);
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        value = " ".toString();
                        data.add(value);
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        value = Boolean.valueOf(cell.getBooleanCellValue()).toString();
                        data.add(value);
                        break;
                }

                //sheetData.add(data);

            }
       sheetData.add(data);
      fis.close();
      }