ashlrem ashlrem - 1 month ago 11
Java Question

Generating 2 excel file Java POI

I have two excel file. excel12.xls and excel_user.xls.
I am comparing cells from that two excel files and finding duplicate which I already did. What I am trying to do is,


  • put all the duplicates from excel_user in excel_1

  • put all without duplicates to excel_2.



However, I am having
java.lang.NullPointerException
on this line.


cell3 = sh4.getRow(z).getCell(0, Row.CREATE_NULL_AS_BLANK);


Here is my code:

HSSFRow row = null;
HSSFRow row2 = null;
HSSFRow row3 = null;
String filename = "C:/Excel/excel12.xls";
String filename2 = "C:/Excel/excel_user.xls";
String filename3 = "C:/Excel/excel_output1.xls";

FileInputStream fis = null;
FileInputStream fis2 = null;
FileInputStream fis3 = null;
FileInputStream fis4 = null;

try{

fis = new FileInputStream(filename);
fis2 = new FileInputStream(filename2);
fis3 = new FileInputStream(filename3);
fis4 = new FileInputStream(filename3);

HSSFWorkbook wb1 = new HSSFWorkbook(fis);
HSSFWorkbook wb2 = new HSSFWorkbook(fis2);
HSSFWorkbook wb3 = new HSSFWorkbook(fis3);
HSSFWorkbook wb4 = new HSSFWorkbook(fis4);

Sheet sh1 = wb1.getSheetAt(0);
Sheet sh2 = wb2.getSheetAt(0);
Sheet sh3 = wb3.getSheetAt(0);
Sheet sh4 = wb4.getSheetAt(0);

Iterator<?> rows = sh1.rowIterator();
Iterator<?> rows2 = sh2.rowIterator();
Iterator<?> rows3 = sh3.rowIterator();

Cell cell = null;
Cell cell2 = null;
Cell cell3 = null;

while(rows.hasNext()){
row = (HSSFRow) rows.next();
}

while(rows2.hasNext()){
row2 = (HSSFRow) rows2.next();
}

while(rows3.hasNext()){
row3 = (HSSFRow) rows3.next();
}

int x = 1;
int y = 1;
int z = 1;
do{
String str = sh1.getRow(x).getCell(0).toString();
cell = sh1.getRow(x).getCell(0);


//check for duplicate account names
for(int i = 1; i < row2.getRowNum()+1; i++){

if(str.equals(String.valueOf(sh2.getRow(i).getCell(0)))){
cell2 = sh3.getRow(y).getCell(0, Row.CREATE_NULL_AS_BLANK);
cell2.setCellValue(formatter.formatCellValue(cell));
y++;

}else{
cell3 = sh4.getRow(z).getCell(0, Row.CREATE_NULL_AS_BLANK);
cell3.setCellValue(formatter.formatCellValue(cell));
z++;
}
}
x++;
}while(x < row.getRowNum()+1);

fis.close();
fis2.close();
fis3.close();
fis4.close();

FileOutputStream outFile =new FileOutputStream(new File("C:/Excel/excel_1.xls"));
FileOutputStream outFile2 =new FileOutputStream(new File("C:/Excel/excel_2.xls"));

wb3.write(outFile);
wb4.write(outFile2);
outFile.close();
outFile2.close();

}catch(Exception e){
e.printStackTrace();
}


Sorry for my code. and thanks in advance for helping!

Answer

You have told POI to create missing cell by passing Row.CREATE_NULL_AS_BLANK to getCell(). However, you did not check that the row itself exists. If the requested row is not present in the file, POI will return null.

Try somehting like this:

Row rowZ = sh4.getRow(z);
if (rowZ==null) {
    rowZ=sh4.createRow(z);
}
cell3 = rowZ.getCell(0, Row.CREATE_NULL_AS_BLANK);
Comments