Harsha Harsha - 3 months ago 15
Java Question

How to read empty cells of an excell file using POI

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

JTable
.

Here is the excell 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);
}
}
}

Answer

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)