Search in sources :

Example 1 with ConditionalFormattingEvaluator

use of org.apache.poi.ss.formula.ConditionalFormattingEvaluator in project poi by apache.

the class ConditionalFormats method evaluateRules.

/**
     * Print out a summary of the conditional formatting rules applied to cells on the given sheet.
     * Only cells with a matching rule are printed, and for those, all matching rules are sumarized.
     * @param wb
     * @param sheetName
     */
static void evaluateRules(Workbook wb, String sheetName) {
    final WorkbookEvaluatorProvider wbEvalProv = (WorkbookEvaluatorProvider) wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEval = new ConditionalFormattingEvaluator(wb, wbEvalProv);
    // if cell values have changed, clear cached format results
    cfEval.clearAllCachedValues();
    final Sheet sheet = wb.getSheet(sheetName);
    for (Row r : sheet) {
        for (Cell c : r) {
            final List<EvaluationConditionalFormatRule> rules = cfEval.getConditionalFormattingForCell(c);
            // check rules list for null, although current implementation will return an empty list, not null, then do what you want with results
            if (rules == null || rules.isEmpty())
                continue;
            final CellReference ref = ConditionalFormattingEvaluator.getRef(c);
            if (rules.isEmpty())
                continue;
            System.out.println("\n" + ref.formatAsString() + " has conditional formatting.");
            for (EvaluationConditionalFormatRule rule : rules) {
                ConditionalFormattingRule cf = rule.getRule();
                StringBuilder b = new StringBuilder();
                b.append("\tRule ").append(rule.getFormattingIndex()).append(": ");
                // check for color scale
                if (cf.getColorScaleFormatting() != null) {
                    b.append("\n\t\tcolor scale (caller must calculate bucket)");
                }
                // check for data bar
                if (cf.getDataBarFormatting() != null) {
                    b.append("\n\t\tdata bar (caller must calculate bucket)");
                }
                // check for icon set
                if (cf.getMultiStateFormatting() != null) {
                    b.append("\n\t\ticon set (caller must calculate icon bucket)");
                }
                // check for fill
                if (cf.getPatternFormatting() != null) {
                    final PatternFormatting fill = cf.getPatternFormatting();
                    b.append("\n\t\tfill pattern ").append(fill.getFillPattern()).append(" color index ").append(fill.getFillBackgroundColor());
                }
                // font stuff
                if (cf.getFontFormatting() != null) {
                    final FontFormatting ff = cf.getFontFormatting();
                    b.append("\n\t\tfont format ").append("color index ").append(ff.getFontColorIndex());
                    if (ff.isBold())
                        b.append(" bold");
                    if (ff.isItalic())
                        b.append(" italic");
                    if (ff.isStruckout())
                        b.append(" strikeout");
                    b.append(" underline index ").append(ff.getUnderlineType());
                }
                System.out.println(b);
            }
        }
    }
}
Also used : PatternFormatting(org.apache.poi.ss.usermodel.PatternFormatting) WorkbookEvaluatorProvider(org.apache.poi.ss.formula.WorkbookEvaluatorProvider) EvaluationConditionalFormatRule(org.apache.poi.ss.formula.EvaluationConditionalFormatRule) Row(org.apache.poi.ss.usermodel.Row) ConditionalFormattingEvaluator(org.apache.poi.ss.formula.ConditionalFormattingEvaluator) CellReference(org.apache.poi.ss.util.CellReference) ConditionalFormattingRule(org.apache.poi.ss.usermodel.ConditionalFormattingRule) FontFormatting(org.apache.poi.ss.usermodel.FontFormatting) Sheet(org.apache.poi.ss.usermodel.Sheet) Cell(org.apache.poi.ss.usermodel.Cell)

Example 2 with ConditionalFormattingEvaluator

use of org.apache.poi.ss.formula.ConditionalFormattingEvaluator in project poi by apache.

the class TestXSSFDataFormat method testConditionalFormattingEvaluation.

@Test
public void testConditionalFormattingEvaluation() throws IOException {
    final Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61060-conditional-number-formatting.xlsx");
    final DataFormatter formatter = new DataFormatter();
    final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEvaluator = new ConditionalFormattingEvaluator(wb, (WorkbookEvaluatorProvider) evaluator);
    CellReference ref = new CellReference("A1");
    Cell cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("0.10", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("0.1", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A3");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("-2.00E+03", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("-2000", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A4");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("100", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    ref = new CellReference("A5");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("$1,000", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("1000", formatter.formatCellValue(cell, evaluator));
    wb.close();
}
Also used : ConditionalFormattingEvaluator(org.apache.poi.ss.formula.ConditionalFormattingEvaluator) CellReference(org.apache.poi.ss.util.CellReference) Cell(org.apache.poi.ss.usermodel.Cell) Workbook(org.apache.poi.ss.usermodel.Workbook) DataFormatter(org.apache.poi.ss.usermodel.DataFormatter) FormulaEvaluator(org.apache.poi.ss.usermodel.FormulaEvaluator) Test(org.junit.Test)

Example 3 with ConditionalFormattingEvaluator

use of org.apache.poi.ss.formula.ConditionalFormattingEvaluator in project poi by apache.

the class ConditionalFormattingEvalTest method openWB.

@Before
public void openWB() {
    wb = XSSFTestDataSamples.openSampleWorkbook("ConditionalFormattingSamples.xlsx");
    formulaEval = new XSSFFormulaEvaluator(wb);
    cfe = new ConditionalFormattingEvaluator(wb, formulaEval);
}
Also used : XSSFFormulaEvaluator(org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator) ConditionalFormattingEvaluator(org.apache.poi.ss.formula.ConditionalFormattingEvaluator) Before(org.junit.Before)

Aggregations

ConditionalFormattingEvaluator (org.apache.poi.ss.formula.ConditionalFormattingEvaluator)3 Cell (org.apache.poi.ss.usermodel.Cell)2 CellReference (org.apache.poi.ss.util.CellReference)2 EvaluationConditionalFormatRule (org.apache.poi.ss.formula.EvaluationConditionalFormatRule)1 WorkbookEvaluatorProvider (org.apache.poi.ss.formula.WorkbookEvaluatorProvider)1 ConditionalFormattingRule (org.apache.poi.ss.usermodel.ConditionalFormattingRule)1 DataFormatter (org.apache.poi.ss.usermodel.DataFormatter)1 FontFormatting (org.apache.poi.ss.usermodel.FontFormatting)1 FormulaEvaluator (org.apache.poi.ss.usermodel.FormulaEvaluator)1 PatternFormatting (org.apache.poi.ss.usermodel.PatternFormatting)1 Row (org.apache.poi.ss.usermodel.Row)1 Sheet (org.apache.poi.ss.usermodel.Sheet)1 Workbook (org.apache.poi.ss.usermodel.Workbook)1 XSSFFormulaEvaluator (org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator)1 Before (org.junit.Before)1 Test (org.junit.Test)1