时间戳

首页 > 解决方案 > 比较2个excel文件大数据写入结果与Highlight cellstyle sxssf JAVA

java - 比较2个excel文件大数据写入结果与Highlight cellstyle sxssf JAVA

问题描述

如何比较每个单元格超过 40,000 行的 2 个 excel 工作表内容大数据，并在新结果文件下以红色突出显示不相等的单元格。

**发布回复前请阅读代码**

打包页面对象；

import java.awt.Color;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.FileSystem;
import java.nio.file.FileSystems;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Reporter;

import property.IHomePage;
import utility.SeleniumUtils;

public class Excelcom2try extends SeleniumUtils implements IHomePage {
    public static FileOutputStream opstr = null;
    XSSFCellStyle cellStyleRed = null;
    SXSSFWorkbook sxssfWorkbook = null;
    SXSSFSheet sheet = null;
    SXSSFRow row3edit = null;
    @SuppressWarnings("resource")
public void compare() {
    try {
        // Create new file for Result 
        XSSFWorkbook workbook = new XSSFWorkbook();
        FileOutputStream fos = new FileOutputStream(new File("\\\\sd-cd6b\\Selenium\\comparisonfile\\ResultFile.xlsx"));
        workbook.write(fos);
        workbook.close();
        Thread.sleep(2000);
        // get input for 2 compare excel files
        FileInputStream excellFile1 = new FileInputStream(new File("\\\\sd-cd6b\\Selenium\\comparisonfile\\UAT_Rel.xlsx"));
        FileInputStream excellFile2 = new FileInputStream(new File("\\\\sd-cd6b\\Selenium\\comparisonfile\\Prod_Rel.xlsx"));
        // Copy file 2 for result to highlight not equal cell
        FileSystem system = FileSystems.getDefault();
        Path original = system.getPath("\\\\sd-cd6b\\Selenium\\comparisonfile\\Prod_Rel.xlsx");
        Path target = system.getPath("\\\\sd-cd6b\\Selenium\\comparisonfile\\ResultFile.xlsx");

        try {
            // Throws an exception if the original file is not found.
            Files.copy(original, target, StandardCopyOption.REPLACE_EXISTING);
            Reporter.log("Successfully Copy File 2 for result to highlight not equal cell");
            Add_Log.info("Successfully Copy File 2 for result to highlight not equal cell");
        } catch (IOException ex) {
            Reporter.log("Unable to Copy File 2 ");
            Add_Log.info("Unable to Copy File 2 ");
        }
        Thread.sleep(2000);
        FileInputStream excelledit3 = new FileInputStream(new File("\\\\sd-cd6b\\Selenium\\comparisonfile\\ResultFile.xlsx"));
        // Create Workbook for 2 compare excel files
        XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
        XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);
        // Temp workbook 
        XSSFWorkbook workbook3new = new XSSFWorkbook();
        //XSSF cellStyleRed as  SXSSFWorkbook cannot have cellstyle  color
        cellStyleRed = workbook3new.createCellStyle();
        cellStyleRed.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyleRed.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // Get first/desired sheet from the workbook to compare both excel sheets
        XSSFSheet sheet1 = workbook1.getSheetAt(0);
        XSSFSheet sheet2 = workbook2.getSheetAt(0);
        //XSSFWorkbook workbook3new temp convert to SXSSFWorkbook
        // keep 100 rows in memory, exceeding rows will be flushed to disk
        sxssfWorkbook = new SXSSFWorkbook(workbook3new, 100);
        sheet = sxssfWorkbook.createSheet();
        // Compare sheets
        if (compareTwoSheets(sheet1, sheet2, sheet)) {

            Reporter.log("\\n\\nThe two excel sheets are Equal");
            Add_Log.info("\\n\\nThe two excel sheets are Equal");
        } else {
            Reporter.log("\\n\\nThe two excel sheets are Not Equal");
            Add_Log.info("\\n\\nThe two excel sheets are Not Equal");

        }

        // close files
        excellFile1.close();
        excellFile2.close();
        excelledit3.close();
        opstr.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    Reporter.log("Successfully Close All files");
    Add_Log.info("Successfully Close All files");
}

// Compare Two Sheets
public boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2, SXSSFSheet sheet) throws IOException {
    int firstRow1 = sheet1.getFirstRowNum();
    int lastRow1 = sheet1.getLastRowNum();
    boolean equalSheets = true;
    for (int i = firstRow1; i <= lastRow1; i++) {

        Reporter.log("\n\nComparing Row " + i);
        Add_Log.info("\n\nComparing Row " + i);
        XSSFRow row1 = sheet1.getRow(i);
        XSSFRow row2 = sheet2.getRow(i);

        row3edit = sheet.getRow(i);
        if (!compareTwoRows(row1, row2, row3edit)) {
            equalSheets = false;
            // Write if not equal
// Get error here java.lang.NullPointerException for row3edit.setRowStyle(cellStyleRed);
            //if disable test is completed Successfully without writing result file 
            row3edit.setRowStyle(cellStyleRed);
            Reporter.log("Row " + i + " - Not Equal");
            Add_Log.info("Row " + i + " - Not Equal");
            // break;
        } else {
            Reporter.log("Row " + i + " - Equal");
            Add_Log.info("Row " + i + " - Equal");
        }
    }
    // Write if not equal 
    opstr = new FileOutputStream("\\\\sd-cd6b\\Selenium\\comparisonfile\\ResultFile.xlsx");
    sxssfWorkbook.write(opstr);

    opstr.close();

    return equalSheets;
}

// Compare Two Rows
public boolean compareTwoRows(XSSFRow row1, XSSFRow row2, SXSSFRow row3edit) throws IOException {
    if ((row1 == null) && (row2 == null)) {
        return true;
    } else if ((row1 == null) || (row2 == null)) {
        return false;
    }

    int firstCell1 = row1.getFirstCellNum();
    int lastCell1 = row1.getLastCellNum();
    boolean equalRows = true;

    // Compare all cells in a row

    for (int i = firstCell1; i <= lastCell1; i++) {
        XSSFCell cell1 = row1.getCell(i);
        XSSFCell cell2 = row2.getCell(i);
        if (!compareTwoCells(cell1, cell2)) {
            equalRows = false;
            Reporter.log("       Cell " + i + " - NOt Equal " + cell1 + "  ===  " + cell2);
            Add_Log.info("       Cell " + i + " - NOt Equal " + cell1 + "  ===  " + cell2);
            break;
        } else {
            Reporter.log("       Cell " + i + " - Equal " + cell1 + "  ===  " + cell2);
            Add_Log.info("       Cell " + i + " - Equal " + cell1 + "  ===  " + cell2);
        }
    }
    return equalRows;
}

// Compare Two Cells
@SuppressWarnings("deprecation")
public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
    if ((cell1 == null) && (cell2 == null)) {
        return true;
    } else if ((cell1 == null) || (cell2 == null)) {
        return false;
    }

    boolean equalCells = false;
    int type1 = cell1.getCellType();
    int type2 = cell2.getCellType();
    if (type2 == type1) {
        if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
            // Compare cells based on its type
            switch (cell1.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                    equalCells = true;
                } else {
                }
                break;

            case HSSFCell.CELL_TYPE_NUMERIC:
                if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) {
                    equalCells = true;
                } else {
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                } else {
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    equalCells = true;

                } else {
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) {
                    equalCells = true;
                } else {
                }
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                    equalCells = true;
                } else {
                }
                break;
            default:
                if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                    equalCells = true;
                } else {
                }
                break;
            }
        } else {
            return false;
        }
    } else {
        return false;
    }
    return equalCells;
}

}

标签： javanullpointerexceptionapache-poi

解决方案

推荐阅读