How to read empty cells of an excel file using POI

Question

I have tried to read an excel file using POI and then I wanted to put that data into a JTable.

Here is the excel file,

As you can see, there are two empty cells in the above table, once I read above data into a JTable I got following result,

In my JTable, empty cells has moved to a wrong place, I used following codes to get this result please assist me to achieve the correct result,

private XLSContainer xLSContainer;
    Vector cellVectorHolder;
    private int noOfCells=0;

    public XLSContainer readXLS(XLSFile xLSFile) {
        cellVectorHolder = new Vector();

        try {

            FileInputStream inputStream = new FileInputStream(xLSFile.getFileName());

            POIFSFileSystem pOIFSFileSystem = new POIFSFileSystem(inputStream);

            HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(pOIFSFileSystem);

            HSSFSheet hSSFSheet = hSSFWorkbook.getSheetAt(0);

            Iterator rowIter = hSSFSheet.rowIterator();


            while (rowIter.hasNext()) {
                HSSFRow row = (HSSFRow) rowIter.next();
                if(row.getRowNum()==0){
                    noOfCells = row.getLastCellNum();
                }
                Iterator cellIter = row.cellIterator();

                Vector cellStoreVector = new Vector();

                while (cellIter.hasNext()) {
                    HSSFCell hSSFCell = (HSSFCell) cellIter.next();
                    //System.out.println(hSSFCell.getCellNum());
                    cellStoreVector.addElement(hSSFCell);
                }
                cellVectorHolder.addElement(cellStoreVector);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        feedXLSContainer();
        return xLSContainer;
    }//readXLS

    private void feedXLSContainer() {

        xLSContainer = new XLSContainer();


        for (int i = 0; i < cellVectorHolder.size(); i++) {
            Vector cellStoreVector = (Vector) cellVectorHolder.elementAt(i);
            Vector item = new Vector();
            for (int j = 0; j < cellStoreVector.size(); j++) {
                HSSFCell cell = (HSSFCell) cellStoreVector.elementAt(j);
                item.add(cell.toString());
            }
            if (i == 0) {
                xLSContainer.addHeader(item);
            } else {
                xLSContainer.addRow(item);
            }

        }

    }

What I have done above is put headings and data rows into separate vectors in a class called xLSContainer and then put those vectors into a JTable.

Here is how I've solved it after more google searches :-)

private XLSContainer xLSContainer;

    public XLSContainer readXLS(XLSFile xLSFile) {
        try {

            WorkbookSettings ws = new WorkbookSettings();
            ws.setLocale(new Locale("en", "EN"));
            Workbook workbook = Workbook.getWorkbook(new File(xLSFile.getFileName()), ws);
            Sheet s = workbook.getSheet(0);
            System.out.println("Sheet Content::" + s.getName());
            readDataSheet(s);
            workbook.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return xLSContainer;

    }

    private void readDataSheet(Sheet s) {
        xLSContainer = new XLSContainer();

        int noOfRows = s.getRows();
        int noOfCols = s.getColumns();



        for (int i = 0; i < noOfRows; i++) {
            Vector item = new Vector();
            for (int j = 0; j < noOfCols; j++) {

                if (s.getCell(j, i).getContents() == "") {
                    item.add("");

                } else {
                    item.add(s.getCell(j, i).getContents());

                }
            }

            if (i == 0) {
                xLSContainer.addHeader(item);
            }else{
                xLSContainer.addRow(item);
            }
        }
    }

Answer 1

The Iterators return you the cells the actually exist in the file. If you're trying to replicate their positions, that almost certainly isn't what you want, instead you'll want to check each cell in turn

You'll likely want code something like:

workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
DataFormatter fmt = new DataFormatter();

for(int sn=0; sn<workbook.getNumberOfSheets(); sn++) {
   Sheet sheet = workbook.getSheetAt(sn);
   for (int rn=sheet.getFirstRowNum(); rn<=sheet.getLastRowNum(); rn++) {
      Row row = sheet.getRow(rn);
      if (row == null) {
         // There is no data in this row, handle as needed
      } else {
         // Row "rn" has data
         for (int cn=0; cn<row.getLastCellNum(); cn++) {
            Cell cell = row.getCell(cn);
            if (cell == null) {
              // This cell is empty/blank/un-used, handle as needed
            } else {
               String cellStr = fmt.formatCell(cell);
               // Do something with the value
            }
         }
      }
   }
}

This code will let you get at each cell in turn, and will also correctly format your cells (so that the numbers are formatted to look like they do in Excel)

Answer 2

The following code will get all cell values based on the header size.

public String getRowValueAsString(Row row, 
    int sizeOfHeader, String colSep) {

        StringBuffer sBuf = new StringBuffer();
        for (int i = 0; i < sizeOfHeader; i++) {
            Cell cell = row.getCell(i);
            if(cell == null) {
                sBuf.append(colSep);
                continue;
            }
            switch (cell.getCellType()) {

            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    sBuf.append(format.format(cell.getDateCellValue()) + colSep);
                }
                else{
                    sBuf.append(cell.getNumericCellValue() + colSep);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                    sBuf.append(cell.getStringCellValue() + colSep);
                break;
            case Cell.CELL_TYPE_FORMULA:
                sBuf.append(cell.getCellFormula() + colSep);
                break;
            case Cell.CELL_TYPE_BLANK:
                sBuf.append(" "+colSep);
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                sBuf.append(cell.getBooleanCellValue()+ colSep);
                break;
            case Cell.CELL_TYPE_ERROR:
                sBuf.append(cell.getErrorCellValue() + colSep);
                break;
            default:
                sBuf.append(cell.getStringCellValue() + colSep);
                break;
            }
        }
        return sBuf.toString()
    }