java - 生成2个excel文件Java POI

Question

我有两个 Excel 文件。 excel12.xls 和 excel_user.xls。我正在比较这两个 Excel 文件中的单元格，并找到我已经做过的重复项。我想做的是，

• 将 excel_user 中的所有重复项放入 excel_1
• 将所有不重复的内容放入 excel_2。

但是，我在这一行遇到了java.lang.NullPointerException。

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

这是我的代码:

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

对我的代码感到抱歉。提前感谢您的帮助!

Answer 1

您已通过将 Row.CREATE_NULL_AS_BLANK 传递给 getCell() 来告诉 POI 创建缺失的单元格。但是，您没有检查该行本身是否存在。如果文件中不存在请求的行，POI 将返回 null。

尝试这样的事情:

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