How can i make my code make an excel output that highlights the cells with differences

Question

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Execute {

    public static void main(String[] args) {
        try {
            // get input excel files
            FileInputStream excellFile1 = new FileInputStream(new File(
                    "C:\\Users\\Leeny\\Desktop\\Teacher.xlsx"));

            FileInputStream excellFile2 = new FileInputStream(new File(
                    "C:\\Users\\Leeny\\Desktop\\Teacher1.xlsx"));

            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
            XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);

            // Get first/desired sheet from the workbook
            XSSFSheet sheet1 = workbook1.getSheetAt(0);
            XSSFSheet sheet2 = workbook2.getSheetAt(0);

            // returns result of CompareTwoSheets
            if(compareTwoSheets(sheet1, sheet2)) {

                System.out.println("\n\nThe two excel sheets are Equal");
            }

            else {
                System.out.println("\n\nThe two excel sheets are Not Equal");
            }

            //close files
            excellFile1.close();
            excellFile2.close();

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

    }


    // Compare Two Sheets
    public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {

        int firstRow1 = sheet1.getFirstRowNum();
        int lastRow1 = sheet1.getLastRowNum();
        boolean equalSheets = true;

        for(int i=firstRow1; i < lastRow1; i++) {

            System.out.println("\n\nComparing Row "+i);

            XSSFRow row1 = sheet1.getRow(i);
            XSSFRow row2 = sheet2.getRow(i);

            if(!compareTwoRows(row1, row2)) {

                equalSheets = false;
                System.out.println("Row "+i+" - Not Equal");
            }

            else {
                System.out.println("Row "+i+" - Equal");
            }
        }
        return equalSheets;
    }

    // Compare Two Rows
    public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {

        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;
                System.out.println("       Cell "+i+" - NOt Equal");

            }

            else {
                System.out.println("       Cell "+i+" - Equal");
            }
        }
        return equalRows;
    }

    // Compare Two Cells
    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;
        CellType type1 = cell1.getCellType();
        CellType type2 = cell2.getCellType();

        if (type1 == type2) {

            if (cell1.getCellStyle().equals(cell2.getCellStyle())) {

                // Compare cells based on its type
                switch (cell1.getCellType()) {

                    case FORMULA:
                        if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                            equalCells = true;
                        }
                        break;

                    case NUMERIC:
                        if (cell1.getNumericCellValue() == cell2
                                .getNumericCellValue()) {
                            equalCells = true;
                        }
                        break;

                    case STRING:
                        if (cell1.getStringCellValue().equals(cell2
                                .getStringCellValue())) {
                            equalCells = true;
                        }
                        break;

                    case BOOLEAN:
                        if (cell1.getBooleanCellValue() == cell2
                                .getBooleanCellValue()) {
                            equalCells = true;
                        }
                        break;

                    case ERROR:
                        if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                            equalCells = true;
                        }
                        break;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }
        return equalCells;
    }
}

Answer 1

I will answer this question giving the best case answer in my opinion.

As I see in your code, you already know which cells are equal and which are not. You even do printing those results using System.out.println(" Cell "+i+" - NOt Equal");. So the only additional thing is to set a special fill color to those cells.

Apache POI provides CellUtil which has a method CellUtil.setCellStyleProperties.

This method attempts to find an existing CellStyle that matches the cell's current style plus styles properties in properties. A new style is created if the workbook does not contain a matching style. It modifies the cell style of cell without affecting other cells that use the same style. This is necessary because Excel has an upper limit on the number of styles that it supports.

Additional it does not influence the other styling options of that cell. This are font, number format, alignment, ... These remain unchanged.

So one could have following method:

void setCellColor(Cell cell, IndexedColors color) {
  Map<String, Object> properties = new HashMap<String, Object>();
  properties.put(CellUtil.FILL_FOREGROUND_COLOR, color.getIndex());
  properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
  CellUtil.setCellStyleProperties(cell, properties);
}

This gets a Cell and one of the IndexedColors and sets this color as the fill color to the cell.

In your code this then could get called like so:

...
if(!compareTwoCells(cell1, cell2)) {
  equalRows = false;
  System.out.println("       Cell "+i+" - NOt Equal");
  setCellColor(cell1, IndexedColors.YELLOW );
  setCellColor(cell2, IndexedColors.YELLOW );
}
...