如何使用Apache POI改变源excel表单与其他excel表单对比后的单元格颜色

Question

我一直在尝试更改从源excel文件中的精确匹配字符串的单元格颜色，当与另一个excel文件相比较时，无法做到与所有建议的各种示例。我将每个单元格条目视为字符串，并将该字符串与另一个excel工作表的行进行比较，如果找到匹配项，则希望将源字符串单元格颜色高亮显示为绿色。这里是我编写的代码，用于比较两张excel表格(Book1和Book2)，如果有人能指导改变Book1中精确匹配条件的单元格颜色，需要帮助。或者，如果需要创建一个新的excel文件，其中重定向的内容来自Book1完全匹配条件？

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

    }

}

}

}

Answer 1

您必须设置cellstyle，然后对其应用所需的颜色。

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