java - 如何使用 POI 测试单元格是否符合 ConditionalFormattingRule 中定义的规则?
问题描述
我尝试使用 POI 将 excel 转换为 HTML,但我的 excel 中有一些条件格式。这些条件格式会影响单元格的背景颜色。我使用以下代码获取所有条件格式。
Sheet sheet = wb.getSheetAt(i);
// get conditional formatting in the sheet
SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
// number of Conditional Formattings
int formattingsNum = formatting.getNumConditionalFormattings();
if (formattingsNum != 0){
// get all conditional formatting
for (int j = 0; j < formattingsNum; j++) {
ConditionalFormatting conditionalFormatting = formatting.getConditionalFormattingAt(j);
}
}
现在我想确定一个单元格是否符合条件格式的规则。我尝试了多种变体,但它们似乎都不起作用。有任何想法吗?
提前致谢。
解决方案
这是一项具有挑战性的任务。一个单元格可能应用了多个条件格式规则。并且影响单元格背景颜色的条件格式规则至少有两种。那是图案格式和色标格式。
Apache poi
提供ConditionalFormattingEvaluator,它有一个方法getConditionalFormattingForCell如果单元格满足规则的标准,则获取单元格的所有条件格式。使用它,我们可以获得单元格的所有匹配条件格式。有了这些,我们必须检查是否有图案格式和/或色标格式。对于图案格式,我们可以获得背景颜色集。但是对于色标格式化,下一个挑战是评估阈值。
示例工作草案:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.formula.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileInputStream;
import java.util.List;
import java.util.ArrayList;
public class ExcelGetConditionalFormattingFromConditionalFormattingEvaluator {
static List<EvaluationConditionalFormatRule> getMatchingConditionalFormattingForCell(Cell cell) {
Sheet sheet = cell.getSheet();
Workbook workbook = sheet.getWorkbook();
WorkbookEvaluatorProvider workbookEvaluatorProvider =
(WorkbookEvaluatorProvider)workbook.getCreationHelper().createFormulaEvaluator();
ConditionalFormattingEvaluator conditionalFormattingEvaluator =
new ConditionalFormattingEvaluator(workbook, workbookEvaluatorProvider);
List<EvaluationConditionalFormatRule> matchingCFRulesForCell =
conditionalFormattingEvaluator.getConditionalFormattingForCell(cell);
return matchingCFRulesForCell;
}
public static void main(String[] args) throws Exception {
//String filePath = "ExcelHavingConditionalFormatting.xls";
String filePath = "ExcelHavingConditionalFormatting.xlsx";
Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
System.out.println(cell.getAddress());
List<EvaluationConditionalFormatRule> matchingCFRules = getMatchingConditionalFormattingForCell(cell);
System.out.println(matchingCFRules);
for (EvaluationConditionalFormatRule evalCFRule : matchingCFRules) {
ConditionalFormattingRule cFRule = evalCFRule.getRule();
if (cFRule.getPatternFormatting() != null) {
PatternFormatting patternFormatting = cFRule.getPatternFormatting();
System.out.println("has pattern formatting: " + patternFormatting);
System.out.println(patternFormatting.getFillBackgroundColorColor());
} else if (cFRule.getColorScaleFormatting() != null) {
System.out.println("has color scale formatting: " + cFRule.getColorScaleFormatting());
}
}
System.out.println();
}
}
workbook.close();
}
}
额外的挑战是当有多个规则将模式格式和/或色标格式应用于单元格时。然后我们还必须检查规则的优先级。ConditionalFormattingEvaluator.getConditionalFormattingForCell也考虑了优先级。所以返回中的顺序List<EvaluationConditionalFormatRule>
代表优先级。