List of usage examples for org.apache.poi.ss.usermodel ComparisonOperator GT
byte GT
To view the source code for org.apache.poi.ss.usermodel ComparisonOperator GT.
Click Source Link
From source file:com.vaadin.addon.spreadsheet.ConditionalFormatter.java
/** * Checks if the given cell value matches a * {@link ConditionalFormattingRule} of <code>VALUE_IS</code> type. Covers * all cell types and comparison operations. * * @param cell// w ww .j a v a 2 s. c om * Target cell * @param rule * Conditional formatting rule to match against. * @return True if the given cells value matches the given * <code>VALUE_IS</code> rule, false otherwise */ protected boolean matchesValue(Cell cell, ConditionalFormattingRule rule) { boolean isFormulaType = cell.getCellType() == Cell.CELL_TYPE_FORMULA; boolean isFormulaStringType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING; boolean isFormulaBooleanType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_BOOLEAN; boolean isFormulaNumericType = isFormulaType && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC; if (isFormulaType) { try { // make sure we have the latest value for formula cells getFormulaEvaluator().evaluateFormulaCell(cell); } catch (NotImplementedException e) { LOGGER.log(Level.FINEST, e.getMessage(), e); return false; } } // other than numerical types if (cell.getCellType() == Cell.CELL_TYPE_STRING || isFormulaStringType) { // Excel stores conditional formatting strings surrounded with ", so // we must surround the cell value. String cell value from POI is // never null. String quotedStringValue = String.format("\"%s\"", cell.getStringCellValue()); // Excel string comparison ignores case switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return quotedStringValue.equalsIgnoreCase(rule.getFormula1()); case ComparisonOperator.NOT_EQUAL: return !quotedStringValue.equalsIgnoreCase(rule.getFormula1()); } } if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN || isFormulaBooleanType) { // not sure if this is used, since no boolean option exists in // Excel.. Boolean formulaVal = Boolean.parseBoolean(rule.getFormula1()); switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return cell.getBooleanCellValue() == formulaVal; case ComparisonOperator.NOT_EQUAL: return cell.getBooleanCellValue() != formulaVal; } } // numerical types if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || isFormulaNumericType) { double formula1Val = -1; try { formula1Val = Double.valueOf(rule.getFormula1()); } catch (NumberFormatException w) { // non-numeric formatting rules cannot match return false; } switch (rule.getComparisonOperation()) { case ComparisonOperator.EQUAL: return cell.getNumericCellValue() == formula1Val; case ComparisonOperator.NOT_EQUAL: return cell.getNumericCellValue() != formula1Val; case ComparisonOperator.LT: return cell.getNumericCellValue() < formula1Val; case ComparisonOperator.LE: return cell.getNumericCellValue() <= formula1Val; case ComparisonOperator.GT: return cell.getNumericCellValue() > formula1Val; case ComparisonOperator.GE: return cell.getNumericCellValue() >= formula1Val; case ComparisonOperator.BETWEEN: boolean lt = cell.getNumericCellValue() >= formula1Val; boolean gt = cell.getNumericCellValue() <= Double.valueOf(rule.getFormula2()); return lt && gt; case ComparisonOperator.NOT_BETWEEN: lt = cell.getNumericCellValue() <= formula1Val; gt = cell.getNumericCellValue() >= Double.valueOf(rule.getFormula2()); return lt && gt; } } return false; }
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Highlight cells based on their values */// www .ja v a 2 s.c o m static void sameCell(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue(84); sheet.createRow(1).createCell(0).setCellValue(74); sheet.createRow(2).createCell(0).setCellValue(50); sheet.createRow(3).createCell(0).setCellValue(51); sheet.createRow(4).createCell(0).setCellValue(49); sheet.createRow(5).createCell(0).setCellValue(41); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Cell Value Is greater than 70 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); // Condition 2: Cell Value Is less than 50 (Green Fill) ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.GREEN.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") }; sheetCF.addConditionalFormatting(regions, rule1, rule2); sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)"); sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)"); }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private static String describeRuleComparisonOperator(ConditionalFormattingRule rule) { StringBuilder sb = new StringBuilder(); sb.append(" comparison:"); switch (rule.getComparisonOperation()) { case ComparisonOperator.LT: sb.append(rule.getFormula1());/*from w w w . j av a 2 s . com*/ sb.append(" < "); sb.append(rule.getFormula2()); break; case ComparisonOperator.LE: sb.append(rule.getFormula1()); sb.append(" <= "); sb.append(rule.getFormula2()); break; case ComparisonOperator.GT: sb.append(rule.getFormula1()); sb.append(" > "); sb.append(rule.getFormula2()); break; case ComparisonOperator.GE: sb.append(rule.getFormula1()); sb.append(" >= "); sb.append(rule.getFormula2()); break; case ComparisonOperator.EQUAL: sb.append(rule.getFormula1()); sb.append(" = "); sb.append(rule.getFormula2()); break; case ComparisonOperator.NOT_EQUAL: sb.append(rule.getFormula1()); sb.append(" != "); sb.append(rule.getFormula2()); break; case ComparisonOperator.BETWEEN: sb.append(rule.getFormula1()); sb.append(" between "); sb.append(rule.getFormula2()); break; case ComparisonOperator.NOT_BETWEEN: sb.append(rule.getFormula1()); sb.append(" not between "); sb.append(rule.getFormula2()); break; case ComparisonOperator.NO_COMPARISON: sb.append(" none "); break; } return sb.toString(); }
From source file:org.aio.handy.poi.ConditionalFormats.java
License:Apache License
/** * Highlight cells based on their values *//* w w w . j a v a 2 s. c o m*/ static void sameCell(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue(84); sheet.createRow(1).createCell(0).setCellValue(74); sheet.createRow(2).createCell(0).setCellValue(50); sheet.createRow(3).createCell(0).setCellValue(51); sheet.createRow(4).createCell(0).setCellValue(49); sheet.createRow(5).createCell(0).setCellValue(41); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Cell Value Is greater than 70 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); // Condition 2: Cell Value Is less than 50 (Green Fill) ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.GREEN.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") }; sheetCF.addConditionalFormatting(regions, rule1, rule2); sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)"); sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)"); }