| /* ==================================================================== |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to You under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| ==================================================================== */ |
| |
| package org.apache.poi.ss.tests.usermodel; |
| |
| |
| import static org.junit.jupiter.api.Assertions.assertEquals; |
| import static org.junit.jupiter.api.Assertions.assertFalse; |
| import static org.junit.jupiter.api.Assertions.assertNotEquals; |
| |
| import java.util.Date; |
| import java.util.List; |
| |
| import org.apache.poi.ss.formula.ConditionalFormattingEvaluator; |
| import org.apache.poi.ss.formula.EvaluationConditionalFormatRule; |
| import org.apache.poi.ss.usermodel.Cell; |
| import org.apache.poi.ss.usermodel.Color; |
| import org.apache.poi.ss.usermodel.Sheet; |
| import org.apache.poi.ss.util.CellReference; |
| import org.apache.poi.util.IOUtils; |
| import org.apache.poi.xssf.XSSFTestDataSamples; |
| import org.apache.poi.xssf.usermodel.XSSFColor; |
| import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator; |
| import org.apache.poi.xssf.usermodel.XSSFWorkbook; |
| import org.junit.jupiter.api.AfterEach; |
| import org.junit.jupiter.api.BeforeEach; |
| import org.junit.jupiter.api.Test; |
| |
| class TestConditionalFormattingEval { |
| |
| private XSSFWorkbook wb; |
| private Sheet sheet; |
| private XSSFFormulaEvaluator formulaEval; |
| private ConditionalFormattingEvaluator cfe; |
| private CellReference ref; |
| private List<EvaluationConditionalFormatRule> rules; |
| |
| @BeforeEach |
| void openWB() { |
| wb = XSSFTestDataSamples.openSampleWorkbook("ConditionalFormattingSamples.xlsx"); |
| formulaEval = new XSSFFormulaEvaluator(wb); |
| cfe = new ConditionalFormattingEvaluator(wb, formulaEval); |
| } |
| |
| @AfterEach |
| void closeWB() { |
| formulaEval = null; |
| cfe = null; |
| ref = null; |
| rules = null; |
| IOUtils.closeQuietly(wb); |
| } |
| |
| @Test |
| void testFormattingEvaluation() { |
| sheet = wb.getSheet("Products1"); |
| |
| getRulesFor(12, 1); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| assertEquals("FFFFEB9C", getColor(rules.get(0).getRule().getPatternFormatting().getFillBackgroundColorColor()), "wrong bg color for " + ref); |
| assertFalse(rules.get(0).getRule().getFontFormatting().isItalic(), "should not be italic " + ref); |
| |
| getRulesFor(16, 3); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| assertEquals(0.7999816888943144d, getTint(rules.get(0).getRule().getPatternFormatting().getFillBackgroundColorColor()), 0.000000000000001, "wrong bg color for " + ref); |
| |
| getRulesFor(12, 3); |
| assertEquals(0, rules.size(), "wrong # of rules for " + ref); |
| |
| sheet = wb.getSheet("Products2"); |
| |
| getRulesFor(15,1); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| assertEquals("FFFFEB9C", getColor(rules.get(0).getRule().getPatternFormatting().getFillBackgroundColorColor()), "wrong bg color for " + ref); |
| |
| getRulesFor(20,3); |
| assertEquals(0, rules.size(), "wrong # of rules for " + ref); |
| |
| // now change a cell value that's an input for the rules |
| Cell cell = sheet.getRow(1).getCell(6); |
| cell.setCellValue("Dairy"); |
| formulaEval.notifyUpdateCell(cell); |
| cell = sheet.getRow(4).getCell(6); |
| cell.setCellValue(500); |
| formulaEval.notifyUpdateCell(cell); |
| // need to throw away all evaluations, since we don't know how value changes may have affected format formulas |
| cfe.clearAllCachedValues(); |
| |
| // test that the conditional validation evaluations changed |
| getRulesFor(15,1); |
| assertEquals(0, rules.size(), "wrong # of rules for " + ref); |
| |
| getRulesFor(20,3); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| assertEquals(0.7999816888943144d, getTint(rules.get(0).getRule().getPatternFormatting().getFillBackgroundColorColor()), 0.000000000000001, "wrong bg color for " + ref); |
| |
| getRulesFor(20,1); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| assertEquals("FFFFEB9C", getColor(rules.get(0).getRule().getPatternFormatting().getFillBackgroundColorColor()), "wrong bg color for " + ref); |
| |
| sheet = wb.getSheet("Book tour"); |
| |
| getRulesFor(8,2); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| |
| sheet = wb.getSheet("Compare to totals"); |
| getRulesFor(3, 2); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| assertEquals("FFFF0000", getColor(rules.get(0).getRule().getFontFormatting().getFontColor()), "wrong fg color for " + ref); |
| getRulesFor(3, 3); |
| assertEquals(0, rules.size(), "wrong # of rules for " + ref); |
| getRulesFor(15, 4); |
| assertEquals(0, rules.size(), "wrong # of rules for " + ref); |
| getRulesFor(16, 1); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| assertEquals("FFFF0000", getColor(rules.get(0).getRule().getFontFormatting().getFontColor()), "wrong fg color for " + ref); |
| |
| sheet = wb.getSheet("Products3"); |
| sheet.getRow(8).getCell(0).setCellValue(new Date()); |
| getRulesFor(8, 0); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| getRulesFor(8, 3); |
| assertEquals(1, rules.size(), "wrong # of rules for " + ref); |
| |
| sheet = wb.getSheet("Customers2"); |
| getRulesFor(3, 0); |
| assertEquals(0, rules.size(), "wrong # of rules for " + ref); |
| } |
| |
| @Test |
| void testFormattingOnUndefinedCell() { |
| wb = XSSFTestDataSamples.openSampleWorkbook("conditional_formatting_with_formula_on_second_sheet.xlsx"); |
| formulaEval = new XSSFFormulaEvaluator(wb); |
| cfe = new ConditionalFormattingEvaluator(wb, formulaEval); |
| |
| sheet = wb.getSheet("Sales Plan"); |
| getRulesFor(9,2); |
| assertNotEquals(0, rules.size(), "No rules for " + ref); |
| assertEquals("FFFFFF00", getColor(rules.get(0).getRule().getPatternFormatting().getFillBackgroundColorColor()), "wrong bg color for " + ref); |
| } |
| |
| @Test |
| void testRepeatedEval() { |
| wb = XSSFTestDataSamples.openSampleWorkbook("test_conditional_formatting.xlsx"); |
| formulaEval = new XSSFFormulaEvaluator(wb); |
| cfe = new ConditionalFormattingEvaluator(wb, formulaEval); |
| |
| sheet = wb.getSheetAt(0); |
| assertEquals(0, getRulesFor(2, 1).size(), "no rules should apply"); |
| |
| assertEquals(0, getRulesFor(2, 1).size(), "no rules should apply"); |
| |
| } |
| |
| @Test |
| void testCellValueIsWrongType() { |
| wb = XSSFTestDataSamples.openSampleWorkbook("conditional_formatting_cell_is.xlsx"); |
| formulaEval = new XSSFFormulaEvaluator(wb); |
| cfe = new ConditionalFormattingEvaluator(wb, formulaEval); |
| |
| sheet = wb.getSheetAt(1); |
| |
| assertEquals(1, getRulesFor(3, 1).size(), "wrong # of matching rules"); |
| } |
| |
| @Test |
| void testRangeCondition() { |
| wb = XSSFTestDataSamples.openSampleWorkbook("conditional_formatting_multiple_ranges.xlsx"); |
| formulaEval = new XSSFFormulaEvaluator(wb); |
| cfe = new ConditionalFormattingEvaluator(wb, formulaEval); |
| |
| sheet = wb.getSheetAt(0); |
| |
| assertEquals(0, getRulesFor(0, 0).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(1, 0).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(2, 0).size(), "wrong # of matching rules"); |
| assertEquals(1, getRulesFor(3, 0).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(0, 1).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(1, 1).size(), "wrong # of matching rules"); |
| assertEquals(1, getRulesFor(2, 1).size(), "wrong # of matching rules"); |
| assertEquals(1, getRulesFor(3, 1).size(), "wrong # of matching rules"); |
| assertEquals(1, getRulesFor(0, 3).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(1, 3).size(), "wrong # of matching rules"); |
| assertEquals(1, getRulesFor(2, 3).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(0, 6).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(3, 6).size(), "wrong # of matching rules"); |
| assertEquals(0, getRulesFor(2, 6).size(), "wrong # of matching rules"); |
| } |
| |
| private List<EvaluationConditionalFormatRule> getRulesFor(int row, int col) { |
| ref = new CellReference(sheet.getSheetName(), row, col, false, false); |
| return rules = cfe.getConditionalFormattingForCell(ref); |
| } |
| |
| private String getColor(Color color) { |
| final XSSFColor c = XSSFColor.toXSSFColor(color); |
| return c.getARGBHex(); |
| } |
| |
| private double getTint(Color color) { |
| final XSSFColor c = XSSFColor.toXSSFColor(color); |
| return c.getTint(); |
| } |
| } |