Blank rows is skipped while reading data from the excel sheet

Question

i try to read the data from excel and store in vector list. But i'm having two issue when applied the following code in my system, hope can get some guidance.

1) When i run the system it will read all the data from the excel file but it skipped those blank data. How can i make it as empty string rather than skipped.

Result : from this Excel file you can see that between value 145 and 2DHatchback is blank data, but system will skipped it, how can i make it as empty string " "?. [PAS, 04/01/2015, ALFA ROMEO, 145, 2D HATCHBACK, 5 SP MANUAL, 1598.42, GZ095G, 02, 01, 02, MULTI POINT F/INJ, ITALY, 9400, 7800]

2) When i tried to deleted few rows of data in excel, my vector result still will keep the deleted data but remain as empty.

Result: from this Excel file if i manually delete row 11 and row 12, when i system.out.println the vector result it will show as [, , ] , [, , ] for deleted rows.

Readexcel.java

public Vector getexcel(String filename)
    {  
        String filetype =  filename.substring(filename.lastIndexOf(".")+1);
        Vector cellVectorHolder = new Vector();

        try
        {
            Workbook workBook = WorkbookFactory.create(new FileInputStream(filename));
            Sheet sheet = workBook.getSheetAt(0);
            Iterator rowIter = sheet.rowIterator();
             if(filetype.equals("xlsx"))
            {
                while(rowIter.hasNext())
                {
                    XSSFRow row = (XSSFRow) rowIter.next();
                    Iterator cellIter = row.cellIterator();
                    Vector cellStoreVector=new Vector();

                 if(row.getRowNum()>1)
                    {
                        while(cellIter.hasNext())
                        {
                            XSSFCell cell = (XSSFCell) cellIter.next();
                         Integer cellType = cell.getCellType();
                         String cellTypeDesc    = "";
                         String cellValue       = "";

                              switch (cellType) 
                              {
                              case 0:
                                  cellTypeDesc = "NUMERIC";
                                  String doubleValue = cell.getRawValue();

                                  if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                      if (HSSFDateUtil.isValidExcelDate(Double.parseDouble(doubleValue))) {
                                          Date date = HSSFDateUtil.getJavaDate(Double.parseDouble(doubleValue));

                                          DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                                          cellValue = df.format(date);   
                                      }
                                  } else {

                                      cellValue = String.valueOf(doubleValue);
                                  }
                                  break;
                              case 1:
                                  cellTypeDesc = "STRING";
                                  cellValue = cell.getStringCellValue();

                                  break;   
                              case 3:
                                  cellTypeDesc = "BLANK";
                                  cellValue = "";
                                  break;
                              }

                            cellStoreVector.addElement(cellValue);
                        }
                    }
                    cellVectorHolder.addElement(cellStoreVector);
                }
            }
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        return cellVectorHolder;
    }

`Vector` is so Java 1.0.. (see http://stackoverflow.com/q/1386275/461499) — Rob Audenaerde, Jun 24 '15 at 09:53
ikr, but this is not the main issue for current topic. The system was implemented since 10 years ago, that's why i have to use old method to code it . — user3835327, Jun 24 '15 at 09:58

score 0 · Answer 1 · answered Jun 24 '15 at 10:03

Excel stores empty fields, so you need to do manual checking

You should check for rows containing only empty cells. Just write a check before you add it to the cellStoreVector

Also, if the rowNumber increases by more than 1, you could add empty row-vectors yourself. So store the previous rowNumber and compare to the current rowNumber

Blank rows is skipped while reading data from the excel sheet

Readexcel.java

1 Answers1