excel - 我使用 poi 来获取背景颜色，但它通过不同的颜色得到相同的 argbhex

Question

这是我的测试课：

public class testReadExcel {
public static void readExcel () throws Exception {
String path = "d:\\字体颜色1.xlsx";
File file = new File(path);
InputStream is = new FileInputStream(file);
Workbook wb = new XSSFWorkbook(is);
int numbersheets = wb.getNumberOfSheets();
Sheet sheet = wb.getSheetAt(0);
int cols = sheet.getPhysicalNumberOfRows();
for(int i = 0; i<cols;i++) {
Row row = sheet.getRow(i);
    int cellnumber = row.getPhysicalNumberOfCells();
    for(int j = 0;j<cellnumber;j++) {
        Cell cell = row.getCell(j);
        CellStyle cellstyle1 = ((XSSFCell)cell).getCellStyle();
        XSSFCellStyle cellstyle = (XSSFCellStyle)cellstyle1;    
        XSSFColor b = cellstyle.getFillForegroundXSSFColor();
        XSSFColor d = cellstyle.getFillBackgroundXSSFColor();
            String c =  b.getARGBHex();
            String e =  d.getARGBHex();
        System.out.println("c   "+c);
        System.out.println("e   "+e);
    }
}

}

public static void main(String[] args) throws Exception{
    readExcel();

这是我用的颜色：</p>

一种颜色是#E46D0A，另一种是#F79646。但是当我得到颜色时，它们都变成了#F79646

c   FFF79646

e   null

c   FFF79646

e   null

这是代码的控制台，如何获得正确的颜色？

标签： excelapache-poi

Answer 1

您的问题令人困惑，因为您的屏幕截图显示了 6 个单元格，所有这些单元格都应由您的代码处理。但是您显示的结果仅显示两个单元格的结果。我怀疑这是您屏幕截图中的第一个单元格？如果是这样，那么此输出的唯一原因可能是第二个单元格具有设置了模式格式的附加条件格式。所以它同时具有填充格式的单元格样式和具有模式格式的条件格式。如果是这种情况，则如果满足条件格式的条件，则条件格式的填充格式是可见的。只有当条件格式的条件不满足时，单元格样式的填充格式才会可见。

如果要求始终获得可见的填充颜色，而不管它来自单元格样式还是条件格式，那么这是一项非常昂贵的任务。必须测试每个单元格是否具有条件格式以及是否满足条件。

以下完整代码至少检查每个单元格是否具有具有模式格式的条件格式。如果是这样，它将打印应用于单元格的所有条件格式的所有背景颜色。它不检查条件是否满足。这是任务中昂贵的部分，ToDo 也不是。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ConditionalFormatting;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.util.List;
import java.util.ArrayList;

class ReadExcelCellStyleFillColors {

 static List<PatternFormatting> getConditionalPatternFormatting(Cell cell) {
  List<PatternFormatting> patternFormattings = new ArrayList<PatternFormatting>();
  Sheet sheet = cell.getSheet();
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  for (int i = 0; i < sheetCF.getNumConditionalFormattings(); i++) {
   ConditionalFormatting conditionalFormatting = sheetCF.getConditionalFormattingAt(i);
   CellRangeAddress[] cellRangeAdresses = conditionalFormatting.getFormattingRanges();
   for (CellRangeAddress cellRangeAddress : cellRangeAdresses) {
    if (cellRangeAddress.isInRange(cell)) {
     for (int j = 0; j < conditionalFormatting.getNumberOfRules(); j++) {
      ConditionalFormattingRule cFRule = conditionalFormatting.getRule(j);
      PatternFormatting patternFormatting = cFRule.getPatternFormatting();
      if (patternFormatting != null) patternFormattings.add(patternFormatting);
     }
    }
   }
  }
  return patternFormattings;
 }

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    System.out.println("This is cell " + new CellAddress(cell));

    List<PatternFormatting> patternFormattings = getConditionalPatternFormatting(cell);
    if (patternFormattings.size() > 0) {
     System.out.println("This cell has conditional pattern formattings having background colors:");
     for (PatternFormatting patternFormatting : patternFormattings) {
      Color patternBGColor = patternFormatting.getFillBackgroundColorColor();
      System.out.println(patternBGColor);
      if (patternBGColor instanceof ExtendedColor) {
       ExtendedColor extColor = (ExtendedColor)patternBGColor;
       if (extColor.isThemed()) {
        System.out.println("Theme color with index: " + extColor.getTheme());
       } else {
        System.out.println(extColor.getARGBHex());
       }
      }
     }
    }

    CellStyle cellStyle = cell.getCellStyle();
    Color fillFGColor = cellStyle.getFillForegroundColorColor();
    System.out.println("This cell has fill foreground color:");
    System.out.println(fillFGColor);
    if (fillFGColor instanceof ExtendedColor) {
     ExtendedColor extColor = (ExtendedColor)fillFGColor;
     System.out.println(extColor.getARGBHex());
    }

    System.out.println();
   }
  }
  workbook.close();
 }
}