hi we've been reading xls and xlsx file using apache poi ing our java program, the problem is we are getting null pointer exception with two reasons.. the first 1 is the blank cell which we already solved and the other one is when we are choosing a certain column that doesn't have any record..
our program ask for the path of the excel file then the specific sheet number of the file and the specific column number of the sheet you want to read.. here is the code for reading xls file
public void readXLSFile()throws IOException{
InputStream ExcelFileToRead = new FileInputStream(path);
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
HSSFSheet sheet=wb.getSheetAt(sheetname);
HSSFRow row;
HSSFCell cell;
Iterator rows = sheet.rowIterator();
list1.clear();
while (rows.hasNext())
{
headers.clear();
row=(HSSFRow) rows.next();
// Iterator cells = row.cellIterator();
headers.add("contents");
cnt = cnt+1;
cell = row.getCell(cols);
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
//System.out.println(cell.getStringCellValue()+"(string)");
list.add(cell.getStringCellValue());
d.add(cell.getStringCellValue());
list1.add(new KeyValuePair(cell.getStringCellValue(),""));
}
else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
{
//System.out.println(cell.getNumericCellValue()+"(numeric)");
double num = cell.getNumericCellValue();
String num2 = String.valueOf(num);
list.add(num2);
d.add(num2);
list1.add(new KeyValuePair(num2,""));
}
else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
{
//System.out.println(cell.getBooleanCellValue()+"(boolean)");
String bool = String.valueOf(cell.getBooleanCellValue());
list.add(bool);
d.add(bool);
list1.add(new KeyValuePair(bool,""));
}
else
{
//U Can Handel Boolean, Formula, Errors
}
//System.out.println();
}
arrey = list.toArray(new String[list.size()]);
data.add(d);
// System.out.println(data);
model = new DefaultTableModel();
table_1.setModel(model);
table_1.setModel(model);
model.setColumnIdentifiers(new String[] {"row","contents"});
for (KeyValuePair p : list1){
int nor=table_1.getRowCount();
int n2 = nor +1;
n1 = Integer.toString(n2);
// model.addColumn(new String [] {n1});
model.addRow(new String[] {n1,p.key, p.value});
}
// model.addColumn(new String[] {n1});
}
the variable sheetname is for the excel file's sheet number
HSSFSheet sheet=wb.getSheetAt(sheetname);
and the variable cols is for the specific column you want to read
cell = row.getCell(cols);
we can read the first column of every sheet and also the second column of the second sheet but when i edited my test file the program now can only read the first column of every sheet.. the error is null pointer exception..wish you could help thanks in advance
The issue is that you never test if the cell is null!
if (cell == null)
{
System.out.println("Cell is Empty in Column:" + cols);
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
//code
}
As a general matter, you should be careful while handling Cell.getCellType()
function, since an empty cell could be either null
or be a CELL_TYPE_BLANK
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With