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,

enter image description here

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,

enter image description here

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

Finally I gave up POI API and I got the result I wanted using jxl.jar. It works fine now. — Harsha, Dec 16 '11 at 05:30

score 25 · Accepted Answer · answered Dec 16 '11 at 10:42

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)

Tanvir Rahman · Answer 2 · 2018-07-18T08:58:57.040

This is how I did it. Hope this helps. Make sure you import the necessary things. in your project.

            FileInputStream excelFile = new FileInputStream(new File(path));
            XSSFWorkbook wb = new XSSFWorkbook(excelFile);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            Iterator rows = sheet.rowIterator();
            //int count =0;
            DataFormatter df= new DataFormatter();
            while(rows.hasNext()) {
                row = (XSSFRow) rows.next();
                for(int i =1; i< 14; ++i) {
                    cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if(cell==null || cell.getCellTypeEnum()== CellType.BLANK) {
                        //count++;
                        //out.println("okay " + count );
                        if(i==1) {
                        declarationDate.add("");
                        }else if(i==2){
                            poNumber.add("");
                        }
                    }else {
                        String str = df.formatCellValue(cell);
                        if(i==1) {
                        declarationDate.add(str);
                        }else if(i==2) {
                            poNumber.add(str);
                        }
                    }
                }
            }

score 1 · Answer 3 · answered Aug 23 '16 at 20:40

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()
    }

score 0 · Answer 4 · answered Sep 21 '18 at 00:13

Used this, based on previous answers.

    private static NPOI.SS.UserModel.ICell[] GetCells(NPOI.SS.UserModel.IRow wr, int columnCount)
    {
        try
        {
            List<NPOI.SS.UserModel.ICell> values = new List<NPOI.SS.UserModel.ICell>();

            for (int i = 0; i < columnCount; i++) values.Add(wr.GetCell(i));

            return values.ToArray();
        }
        catch (Exception)
        {
            throw;
        }
    }

How to read empty cells of an excel file using POI

4 Answers4