java - 在写入工作簿 WorkBook.write(out) Apache POI 时获取 java.lang.NullPointerException

Question

我收到 java.lang.NullPointerException在写入输出流时:

workbook.write(new FileOutputStream("test1.xslx"));

异常(exception)是:

Exception:
   java.lang.NullPointerException
      at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:201)
      at ExcelCompare.WriteOutputIntoExcel.addRow(WriteExcel.java:124)
      at ExcelCompare.CompareExcel.main(MainClassExcelCompare.java:113)

这里我有两个类: CompareExcel类和 WriteOutputIntoExcel
我想比较两张excel表 Excel1.xslx和 Excel2.xslx并将结果放回 Result.xslx .

我不想把所有东西都放在 Result.xslx ，我只想将不匹配的行放在 Excel1 中和 Excel2.
这里是 Main类(class)

package ExcelCompare;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CompareExcel {

    public static void main(String[] args) {

        try {
            int temp;

            File excel1 = new File("C://Users/ckothakapax076037/Desktop/Demo1.xlsx");
            FileInputStream fis1 = new FileInputStream(excel1);
            XSSFWorkbook book1 = new XSSFWorkbook(fis1);
            XSSFSheet sheet1 = book1.getSheetAt(0);

            File excel2 = new File("C://Users/ckothakapax076037/Desktop/Demo2.xlsx");
            FileInputStream fis2 = new FileInputStream(excel2);
            XSSFWorkbook book2 = new XSSFWorkbook(fis2);
            XSSFSheet sheet2 = book2.getSheetAt(0);

            WriteExcel obj1 = new WriteExcel();
            obj1.setOutputFile("C://Users/ckothakapax076037/Desktop/Result.xlsx");

            //Get iterator to all the rows in current sheet
            Iterator<Row> itr1 = sheet1.iterator();
            Iterator<Row> itr2 = sheet2.iterator();

            // Iterating through all cells row by row
            while (itr1.hasNext() && itr2.hasNext()) {

                temp = 0;
                int j = 0;
                Row row1 = itr1.next();
                Row row2 = itr2.next();

                //Get iterator to all cells of current row
                Iterator<Cell> cellIterator1 = row1.cellIterator();
                Iterator<Cell> cellIterator2 = row2.cellIterator();

                CellStyle style = book1.createCellStyle();
                style = book1.createCellStyle();
                style.setFillForegroundColor(IndexedColors.RED.getIndex());
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);

                while (cellIterator1.hasNext() && cellIterator2.hasNext()) {
                    Cell cell1 = cellIterator1.next();
                    Cell cell2 = cellIterator2.next();

                    switch (cell1.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell1.getStringCellValue() + "\t");
                            System.out.print(cell2.getStringCellValue() + "\t");

                            if (!cell1.getStringCellValue().equalsIgnoreCase(cell2.getStringCellValue())) {
                                temp++;
                                cell1.setCellStyle(style);
                            }
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell1.getNumericCellValue() + "\t");
                            System.out.print(cell2.getNumericCellValue() + "\t");

                            if (cell1.getNumericCellValue() != cell2.getNumericCellValue()) {
                                temp++;
                                cell1.setCellStyle(style);
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(cell1.getBooleanCellValue() + "\t");
                            System.out.print(cell2.getBooleanCellValue() + "\t");
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            System.out.print(cell1.getNumericCellValue() + "\t");
                            System.out.print(cell2.getNumericCellValue() + "\t");

                            if (cell2.getStringCellValue() != " ") {
                                temp++;
                                cell1.setCellStyle(style);
                            }
                            break;
                        default:
                    }
                    j++;
                }

                System.out.print("\n");
                System.out.print("Flag value:" + temp);
                System.out.print("\n");

                if (temp >= 1) {
                    obj1.addRow(row1.cellIterator(), row2.cellIterator());
                }
            }

            book1.close();
            fis1.close();
            book2.close();
            fis2.close();
            obj1.closerActivity();

        } catch (FileNotFoundException fe) {
            fe.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
    }
}

现在我有 WriteOutputIntoExcel将结果放回 Result.xslx 的类

package ExcelCompare;

import java.io.FileOutputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteOutputIntoExcel {

    private static String OutputFile;
    private static XSSFWorkbook myWorkBook = new XSSFWorkbook();
    private static XSSFSheet mySheet = myWorkBook.createSheet("Report");

    public static int i = 0;

    public void setOutputFile(String OutputFile1) {
        OutputFile = OutputFile1;
    }

    public void addRow(Iterator<Cell> cellIterator1, Iterator<Cell> cellIterator2) {

        try {
            XSSFRow row = mySheet.createRow(i++);
            CellStyle style = myWorkBook.createCellStyle();
            style = myWorkBook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.RED.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            FileOutputStream out = new FileOutputStream(OutputFile);

            System.out.print("Writing result from Sheet1");
            System.out.print("\n");

            while (cellIterator1.hasNext()) {

                int j = 0;
                Cell cell1 = cellIterator1.next();

                switch (cell1.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell1.getStringCellValue() + "\t");
                        row.createCell(j).setCellValue(cell1.getStringCellValue());
                        row.createCell(j).setCellStyle(style);

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell1.getNumericCellValue() + "\t");
                        row.createCell(j).setCellValue(cell1.getNumericCellValue());
                        row.createCell(j).setCellStyle(style);
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        System.out.print(cell1.getStringCellValue() + "\t");
                        row.createCell(j).setCellValue(cell1.getStringCellValue());
                        row.createCell(j).setCellStyle(style);
                        break;
                    default:
                        System.out.print(cell1.getStringCellValue() + "\t");
                        row.createCell(j).setCellValue(cell1.getStringCellValue());
                        row.createCell(j).setCellStyle(style);
                }
                j++;
            }

            System.out.print("\n");
            System.out.print("Writing result from Sheet2");
            System.out.print("\n");

            while (cellIterator2.hasNext()) {
                int j = 0;
                Cell cell2 = cellIterator2.next();

                switch (cell2.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell2.getStringCellValue() + "\t");
                        row.createCell(j).setCellValue(cell2.getStringCellValue());

                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell2.getNumericCellValue() + "\t");
                        row.createCell(j).setCellValue(cell2.getNumericCellValue());

                        break;
                    case Cell.CELL_TYPE_BLANK:
                        System.out.print(cell2.getStringCellValue() + "\t");
                        row.createCell(j).setCellValue(cell2.getStringCellValue());

                        break;
                    default:
                        System.out.print(cell2.getStringCellValue() + "\t");
                        row.createCell(j).setCellValue(cell2.getStringCellValue());
                }
                j++;
            }

            System.out.print("\n");
            myWorkBook.write(out);
            out.close();
            myWorkBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void closerActivity() {
        try {
            System.out.println(" Hi i am in close");

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

我还想突出显示 Excel1 中与 Excel2 不同的单元格。 .

请帮助我实现这一目标。

Answer 1

解决此类问题的最佳方法是将库的源代码(在本例中为 POI)附加到 IDE，以便您可以快速查找库中第 201 行发生的情况。请注意，您需要确保您的库 lib 版本与 src 版本相同，否则您可能会看错行。

好的，您对 addRow 的参数之一很可能是 null (迭代器)。所以首先检查(println)。