CODEWITHSUNDEEP
javaapache-poi
yaylitzis
yaylitzis

Reputation: 5534

Try to write to a null/blank cell in excel leads to java.lang.NullPointerException

I am trying to print some the elements of arrayList into an excel. I use a new created excel file with no content.

    Row row;
    Cell column;
    int size = records.size(); //records is my ArrayList

    try {
         //Get the excel file.
         FileInputStream file = new FileInputStream(new File("C:\\test\\test.xls"));
         //Get workbook for XLS file.
         HSSFWorkbook workbook = new HSSFWorkbook(file);
         //Get first sheet from the workbook.
         HSSFSheet sheet1 = workbook.getSheetAt(0);

         for(int i=0; i<size; i++){
                 //here I get the exception
                 row = sheet1.getRow(i+1);  
                 column = row.getCell(0);
                 column.setCellValue(records.get(i).getDate()); // this method returns a date..

            }

            file.close();
            FileOutputStream out =  new FileOutputStream(new File("my_path\\test.xls"));                
            workbook.write(out);
            out.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();    
        }

So I replace theses lines with 

row = sheet1.getRow(i+1);  
if(row.getCell(0)==null){
      column = row.createCell(0);
      column.setCellValue(records.get(i).getDate());
 }else{
       row.getCell(0).setCellValue(records.get(i).getDate()); // this method returns a date..
 }

But the exception still occured. Then after I saw this question, I tried this 

    if(row.getCell(0)==null || getCellValue(row.getCell(0)).trim().isEmpty()){
           column = row.createCell(0);
           column.setCellValue(records.get(i).getDate());
    }else{
           row.getCell(0).setCellValue(records.get(i).getDate());
    }

and the method 

private String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return cell.getNumericCellValue() + "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
            return cell.getStringCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
            return cell.getErrorCellValue() + "";
        } 
        else {
            return null;
        }
   }

but I still get an exception..

Upvotes: 1

Views: 16122

Answers (3)

Fajar Rukmo
Fajar Rukmo

Reputation: 78

for my case. i don't use checking cell type but create cell directly because i know my cell is empty. this edit existing excel problem make me confuse all day. but apache POI is recomennded than jxl, just my 2 cent

sheet.createRow(newrow);
sheet.getRow(newrow).createCell(3).setCellValue(npk);
sheet.getRow(newrow).createCell(4).setCellValue(tglawal);
sheet.getRow(newrow).createCell(5).setCellValue(tglakhir);
sheet.getRow(newrow).createCell(6).setCellValue(jamawal);
sheet.getRow(newrow).createCell(7).setCellValue(jamakhir);
sheet.getRow(newrow).createCell(8).setCellValue(tujuan);
sheet.getRow(newrow).createCell(9).setCellValue(nomer);

thanks god for creating people who create stackoverflow and the people who confuse about their code

Upvotes: 0

yaylitzis
yaylitzis

Reputation: 5534

After many days, I found the root of the problem. The problem was when the row was null... with the following

row = sheet1.getRow(i+1); 
if(row == null){
  row = sheet1.createRow(i+1);
}

the program run well.

Upvotes: 4

cbender
cbender

Reputation: 2251

Use .setCellValue(""); instead. If you try setting the value and it is null that means whatever operation POI is performing, it's trying on a null value. You can also just not call the .setCellValue() on the cell.

Upvotes: 1

Related Questions