Anuj Shrivastav Anuj Shrivastav - 4 months ago 28
Apache Configuration Question

Updating excel file using Apache POI

I am trying to update an existing excel file using Apache POI. Every time I run my code I receive an error as shown below. I have also tried FileInputStreamNewFile thing.

Exception in thread "main" java.lang.NullPointerException
at com.gma.test.WriteExcelTest.writeXLSXFile(WriteExcelTest.java:26)
at com.gma.test.WriteExcelTest.main(WriteExcelTest.java:44)


Please find the code below. Appreciate your help.

package com.gma.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelTest {

public static void writeXLSXFile(int row, int col) throws IOException {
try {
FileInputStream file = new FileInputStream("C:\\Anuj\\Data\\Data.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Cell cell = null;

//Update the value of cell
cell = sheet.getRow(row).getCell(col);
cell.setCellValue("Pass");

file.close();

FileOutputStream outFile =new FileOutputStream(new File("C:\\Anuj\\Data\\Data.xlsx"));
workbook.write(outFile);
outFile.close();

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

public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
writeXLSXFile(3, 3);
}

}

Answer

If you replace

//Update the value of cell
cell = sheet.getRow(row).getCell(col);
cell.setCellValue("Pass");

With

//Retrieve the row and check for null
HSSFRow sheetrow = sheet.getRow(row);
if(sheetrow == null){
    sheetrow = sheet.createRow(row);
}
//Update the value of cell
cell = sheetrow.getCell(col);
if(cell == null){
    cell = sheetrow.createCell(col);
}
cell.setCellValue("Pass");

It will work!