CODEWITHSUNDEEP
javaexcelapache-poi
ashlrem
ashlrem

Reputation: 127

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,

However, I am having java.lang.NullPointerExceptionon 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!

Upvotes: 0

Views: 40

Answers (1)

Axel
Axel

Reputation: 14169

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);

Upvotes: 2

Related Questions