CODEWITHSUNDEEP
javaexcelapache-poi
cloudakmaluddin
cloudakmaluddin

Reputation: 11

How to change cell color of source excel sheet after comparison with another excel using Apache POI

I have been trying to change the cell color of exact match string from source excel file when compared with another excel file and unable to do it with all the various examples suggested. I am treating each cell entry as string and comparing that string with row of another excel sheet and if a match is found then want to highlight the source string cell color as GREEN. Here is the code which I have written so far for comparing two excel sheets(Book1 and Book2) and need help if someone can guide in changing the cell color of exact match condition in Book1. Or if there is a need to create a new excel file with redirected contents from Book1 exact match conditions?

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;


import org.apache.poi.ss.usermodel.CellType;
import java.io.FileInputStream;
import java.io.IOException;


public class ExcelCompare {
public static void main(String[] srgs) throws IOException {

    FileInputStream fileInputStream1 = new 
    FileInputStream("C:\\Stuff\\JavaProject\\Book1.xlsx");
    XSSFWorkbook workbook1 = new XSSFWorkbook(fileInputStream1);
    XSSFSheet worksheet1 = workbook1.getSheet("Sheet1");
    int rowCount1= worksheet1.getPhysicalNumberOfRows();

    FileInputStream fileInputStream2 = new 
    FileInputStream("C:\\Stuff\\JavaProject\\Book2.xlsx");
    XSSFWorkbook workbook2 = new XSSFWorkbook(fileInputStream2);
    XSSFSheet worksheet2 = workbook2.getSheet("Sheet1");
    int rowCount2= worksheet2.getPhysicalNumberOfRows();

    System.out.println("Row count 1=" + rowCount1 + "  Row count 2 = " + rowCount2);


for (int i = 1; i < rowCount1; i++) {
    XSSFRow row1 = worksheet1.getRow(i);
//------------------------------ comapring Name --------------------------
    String namestr1 = "";
    XSSFCell name1 = row1.getCell(0);
    if (name1 != null) 
    {
        name1.setCellType(CellType.STRING);
        namestr1 = name1.getStringCellValue();
    }

    int j=1;
    int notNullRows=0;
    int rowCount2WithNulls = rowCount2;
    while(j<rowCount2WithNulls && notNullRows <= rowCount2 )
    {
        XSSFRow row2 = worksheet2.getRow(j);
        String namestr2 = "";
        j++;
        if (row2 != null)
        {
            notNullRows++;
            XSSFCell name2 = row2.getCell(0);
            if (name2 != null) {
                name2.setCellType(CellType.STRING);
                namestr2 = name2.getStringCellValue();
            }
        }
        else
        {
            rowCount2WithNulls++;
        }

         if(namestr1.equals(namestr2))
         {
             System.out.println("[Processing] :"+"NAME " + namestr1 + "=> Book 1 name = " + namestr1+ " Book 2 name = " + namestr2);
         }

    }

}

}

}

Upvotes: 1

Views: 492

Answers (1)

Harish
Harish

Reputation: 726

You must set a cellstyle and then apply a desired color to it. 

if (namestr1.equals(namestr2)) {
    System.out.println("[Processing] :" + "NAME " + namestr1 + "=> Book 1 name = " + namestr1 + " Book 2 name = " + 

    // add color
    XSSFCellStyle style = workbook1.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    assert name1 != null;
    name1.setCellStyle(style);
    FileOutputStream fos = new FileOutputStream(BOOK1);
    workbook1.write(fos);
    fos.close();
}

Upvotes: 0

Related Questions