Java tutorial
package com.vaadin.addon.spreadsheet; /* * #%L * Vaadin Spreadsheet * %% * Copyright (C) 2013 - 2015 Vaadin Ltd * %% * This program is available under Commercial Vaadin Add-On License 3.0 * (CVALv3). * * See the file license.html distributed with this software for more * information about licensing. * * You should have received a copy of the CVALv3 along with this program. * If not, see <http://vaadin.com/license/cval-3>. * #L% */ import java.io.Serializable; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.poi.hssf.usermodel.HSSFSheetConditionalFormatting; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.formula.FormulaParser; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.WorkbookEvaluatorUtil; import org.apache.poi.ss.formula.eval.BoolEval; import org.apache.poi.ss.formula.eval.NotImplementedException; import org.apache.poi.ss.formula.eval.ValueEval; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.RefPtgBase; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.ComparisonOperator; import org.apache.poi.ss.usermodel.ConditionType; import org.apache.poi.ss.usermodel.ConditionalFormatting; import org.apache.poi.ss.usermodel.ConditionalFormattingRule; import org.apache.poi.ss.usermodel.FontFormatting; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.PatternFormatting; import org.apache.poi.ss.usermodel.SheetConditionalFormatting; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFBorderFormatting; import org.apache.poi.xssf.usermodel.XSSFConditionalFormatting; import org.apache.poi.xssf.usermodel.XSSFConditionalFormattingRule; import org.apache.poi.xssf.usermodel.XSSFFontFormatting; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBooleanProperty; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont; import com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.BorderStyle; /** * ConditionalFormatter is an utility class of Spreadsheet, which handles all * processing regarding Conditional Formatting rules. * <p> * Rules are parsed into CSS rules with individual class names. Class names for * each cell can then be fetched from this class. * <p> * For now, only XSSF formatting rules are supported because of bugs in POI. * * @author Thomas Mattsson / Vaadin Ltd. */ @SuppressWarnings("serial") public class ConditionalFormatter implements Serializable { private static final Logger LOGGER = Logger.getLogger(ConditionalFormatter.class.getName()); /* * Slight hack. This style is used when a CF rule defines 'no border', in * which case the border should be empty. However, since we use cell DIV * borders for the grid structure, empty borders are in fact grey. So, if * one rule says red, and the next says no border, then we need to know what * 'no border' means in CSS. Of course, if the default CSS changes, this * needs to change too. */ private static String BORDER_STYLE_DEFAULT = "1pt solid #d6d6d6;"; private Spreadsheet spreadsheet; /** * Cache of styles for each cell. One cell may have several styles. */ private Map<String, Set<Integer>> cellToIndex = new HashMap<String, Set<Integer>>(); private Map<ConditionalFormatting, Integer> topBorders = new HashMap<ConditionalFormatting, Integer>(); private Map<ConditionalFormatting, Integer> leftBorders = new HashMap<ConditionalFormatting, Integer>(); protected ColorConverter colorConverter; /** * Constructs a new ConditionalFormatter targeting the given Spreadsheet. * * @param spreadsheet * Target spreadsheet */ public ConditionalFormatter(Spreadsheet spreadsheet) { this.spreadsheet = spreadsheet; final Workbook workbook = spreadsheet.getWorkbook(); if (workbook instanceof HSSFWorkbook) { colorConverter = new HSSFColorConverter((HSSFWorkbook) workbook); } else { colorConverter = new XSSFColorConverter((XSSFWorkbook) workbook); } } /** * Each cell can have multiple matching rules, hence a collection. Order * doesn't matter here, CSS is applied in correct order on the client side. * * @param cell * Target cell * @return indexes of the rules that match this Cell (to be used in class * names) */ public Set<Integer> getCellFormattingIndex(Cell cell) { Set<Integer> index = cellToIndex.get(SpreadsheetUtil.toKey(cell)); return index; } /** * Creates the necessary CSS rules and runs evaluations on all affected * cells. */ public void createConditionalFormatterRules() { // make sure old styles are cleared if (cellToIndex != null) { for (String key : cellToIndex.keySet()) { int col = SpreadsheetUtil.getColumnIndexFromKey(key) - 1; int row = SpreadsheetUtil.getRowFromKey(key) - 1; Cell cell = spreadsheet.getCell(row, col); if (cell != null) { spreadsheet.markCellAsUpdated(cell, true); } } } cellToIndex.clear(); topBorders.clear(); leftBorders.clear(); spreadsheet.getState().conditionalFormattingStyles = new HashMap<Integer, String>(); SheetConditionalFormatting cfs = spreadsheet.getActiveSheet().getSheetConditionalFormatting(); if (cfs instanceof HSSFSheetConditionalFormatting) { // disable formatting for HSSF, since formulas are read incorrectly // and we would return incorrect results. return; } for (int i = 0; i < cfs.getNumConditionalFormattings(); i++) { ConditionalFormatting cf = cfs.getConditionalFormattingAt(i); List<XSSFConditionalFormattingRule> cfRuleList = getOrderedRuleList(cf); // rules are listen bottom up, but we want top down so that we can // stop when we need to. Rule indexes follow original order, because // that is the order CSS is applied on client side. for (int ruleIndex = cf.getNumberOfRules() - 1; ruleIndex >= 0; ruleIndex--) { ConditionalFormattingRule rule = cfRuleList.get(ruleIndex); // first formatting object gets 0-999, second 1000-1999... // should be enough. int cssIndex = i * 1000000 + ruleIndex * 1000; // build style // TODO: some of this code will override all old values on each // iteration. POI API will return the default value for nulls, // which is not what we want. StringBuilder css = new StringBuilder(); FontFormatting fontFormatting = rule.getFontFormatting(); if (fontFormatting != null) { String fontColorCSS = colorConverter.getFontColorCSS(rule); if (fontColorCSS != null) { css.append("color:" + fontColorCSS); } // we can't have both underline and line-through in the same // DIV element, so use the first one that matches. // HSSF might return 255 for 'none'... if (fontFormatting.getUnderlineType() != FontFormatting.U_NONE && fontFormatting.getUnderlineType() != 255) { css.append("text-decoration: underline;"); } if (hasStrikeThrough(fontFormatting)) { css.append("text-decoration: line-through;"); } if (fontFormatting.getFontHeight() != -1) { // POI returns height in 1/20th points, convert int fontHeight = fontFormatting.getFontHeight() / 20; css.append("font-size:" + fontHeight + "pt;"); } // excel has a setting for bold italic, otherwise bold // overrides // italic and vice versa if (fontFormatting.isItalic() && fontFormatting.isBold()) { css.append("font-style: italic;"); css.append("font-weight: bold;"); } else if (fontFormatting.isItalic()) { css.append("font-style: italic;"); css.append("font-weight: initial;"); } else if (fontFormatting.isBold()) { css.append("font-style: normal;"); css.append("font-weight: bold;"); } } PatternFormatting patternFormatting = rule.getPatternFormatting(); if (patternFormatting != null) { String colorCSS = colorConverter.getBackgroundColorCSS(rule); if (colorCSS != null) { css.append("background-color:" + colorCSS); } } cssIndex = addBorderFormatting(cf, rule, css, cssIndex); spreadsheet.getState().conditionalFormattingStyles.put(cssIndex, css.toString()); // check actual cells runCellMatcher(cf, rule, cssIndex); // stop here if defined in rules if (stopHere(rule)) { break; } } } } /** * Get the common {@link FormulaEvaluator} instance from {@link Spreadsheet} */ protected FormulaEvaluator getFormulaEvaluator() { return spreadsheet.getFormulaEvaluator(); } /** * Excel uses a field called 'priority' to re-order rules. Just calling * {@link XSSFConditionalFormatting#getRule(int)} will result in wrong * order. So, instead, get the list and reorder it according to the priority * field. * * @return The list of conditional formatting rules in reverse order (same * order Excel processes them). */ private List<XSSFConditionalFormattingRule> getOrderedRuleList(ConditionalFormatting cf) { // get the list XSSFConditionalFormatting xcf = (XSSFConditionalFormatting) cf; List<XSSFConditionalFormattingRule> rules = new ArrayList<XSSFConditionalFormattingRule>(); for (int i = 0; i < xcf.getNumberOfRules(); i++) { rules.add(xcf.getRule(i)); } // reorder with hidden field Collections.sort(rules, new Comparator<XSSFConditionalFormattingRule>() { @Override public int compare(XSSFConditionalFormattingRule o1, XSSFConditionalFormattingRule o2) { CTCfRule object = (CTCfRule) getFieldValWithReflection(o1, "_cfRule"); CTCfRule object2 = (CTCfRule) getFieldValWithReflection(o2, "_cfRule"); if (object != null && object2 != null) { // reverse order return object2.getPriority() - object.getPriority(); } return 0; } }); return rules; } /** * @return the new cssIndex */ private int addBorderFormatting(ConditionalFormatting cf, ConditionalFormattingRule rule, StringBuilder css, int cssIndex) { if (!(rule instanceof XSSFConditionalFormattingRule)) { // HSSF not supported return cssIndex; } XSSFBorderFormatting borderFormatting = (XSSFBorderFormatting) rule.getBorderFormatting(); if (borderFormatting != null) { BorderStyle borderLeft = SpreadsheetStyleFactory.BORDER.get(borderFormatting.getBorderLeft()); BorderStyle borderRight = SpreadsheetStyleFactory.BORDER.get(borderFormatting.getBorderRight()); BorderStyle borderTop = SpreadsheetStyleFactory.BORDER.get(borderFormatting.getBorderTop()); BorderStyle borderBottom = SpreadsheetStyleFactory.BORDER.get(borderFormatting.getBorderBottom()); // In Excel, we can set a border to 'none', which overrides previous // rules. Default is 'not set', in which case we add no CSS. boolean isLeftSet = isBorderSet(borderFormatting, BorderSide.LEFT); boolean isTopSet = isBorderSet(borderFormatting, BorderSide.TOP); boolean isRightSet = isBorderSet(borderFormatting, BorderSide.RIGHT); boolean isBottomSet = isBorderSet(borderFormatting, BorderSide.BOTTOM); if (isRightSet) { css.append("border-right:"); if (borderRight != BorderStyle.NONE) { css.append(borderRight.getBorderAttributeValue()); css.append(colorConverter.getBorderColorCSS(BorderSide.RIGHT, "border-right-color", borderFormatting)); } else { css.append(BORDER_STYLE_DEFAULT); } } if (isBottomSet) { css.append("border-bottom:"); if (borderBottom != BorderStyle.NONE) { css.append(borderBottom.getBorderAttributeValue()); css.append(colorConverter.getBorderColorCSS(BorderSide.BOTTOM, "border-bottom-color", borderFormatting)); } else { css.append(BORDER_STYLE_DEFAULT); } } // top and left borders might be applied to another cell, so store // them with a different index if (isTopSet) { // bottom border for cell above final StringBuilder sb2 = new StringBuilder("border-bottom:"); if (borderTop != BorderStyle.NONE) { sb2.append(borderTop.getBorderAttributeValue()); sb2.append(colorConverter.getBorderColorCSS(BorderSide.TOP, "border-bottom-color", borderFormatting)); spreadsheet.getState().conditionalFormattingStyles.put(cssIndex, sb2.toString()); topBorders.put(cf, cssIndex++); } else { css.append(BORDER_STYLE_DEFAULT); } } if (isLeftSet) { // right border for cell to the left final StringBuilder sb2 = new StringBuilder("border-right:"); if (borderLeft != BorderStyle.NONE) { sb2.append(borderLeft.getBorderAttributeValue()); sb2.append(colorConverter.getBorderColorCSS(BorderSide.LEFT, "border-right-color", borderFormatting)); spreadsheet.getState().conditionalFormattingStyles.put(cssIndex, sb2.toString()); leftBorders.put(cf, cssIndex++); } else { css.append(BORDER_STYLE_DEFAULT); } } } return cssIndex; } /** * Checks if this rule has 'stop if true' defined. */ private boolean stopHere(ConditionalFormattingRule rule) { if (rule instanceof XSSFConditionalFormattingRule) { // No POI API for this particular data, but it is present in XML. CTCfRule ctRule = (CTCfRule) getFieldValWithReflection(rule, "_cfRule"); if (ctRule != null) { return ctRule.getStopIfTrue(); } } return false; } /** * Helper for the very common case of having to get underlying XML data. */ private Object getFieldValWithReflection(Object owner, String fieldName) { Field f = null; Object val = null; try { f = owner.getClass().getDeclaredField(fieldName); f.setAccessible(true); val = f.get(owner); return val; } catch (NoSuchFieldException e) { LOGGER.log(Level.SEVERE, "Incompatible POI implementation, unable to parse conditional formatting rule", e); } catch (SecurityException e) { LOGGER.log(Level.SEVERE, "Incompatible POI implementation, unable to parse conditional formatting rule", e); } catch (IllegalArgumentException e) { LOGGER.log(Level.SEVERE, "Incompatible POI implementation, unable to parse conditional formatting rule", e); } catch (IllegalAccessException e) { LOGGER.log(Level.SEVERE, "Incompatible POI implementation, unable to parse conditional formatting rule", e); } finally { if (f != null) { f.setAccessible(false); } } return null; } /** * @param i * 0 - left, 1 - top, 2 - right, 3 - bottom */ private boolean isBorderSet(XSSFBorderFormatting borderFormatting, BorderSide b) { CTBorder ctBorder = (CTBorder) getFieldValWithReflection(borderFormatting, "_border"); if (ctBorder == null) { return false; } switch (b) { case LEFT: return ctBorder.isSetLeft(); case TOP: return ctBorder.isSetTop(); case RIGHT: return ctBorder.isSetRight(); case BOTTOM: return ctBorder.isSetBottom(); } return false; } /** * Checks if this formatting has strike-through enabled or not. */ private boolean hasStrikeThrough(FontFormatting fontFormatting) { if (fontFormatting instanceof XSSFFontFormatting) { // No POI API for this particular data, but it is present in XML. CTFont font = (CTFont) getFieldValWithReflection(fontFormatting, "_font"); if (font == null) { return false; } List<CTBooleanProperty> strikeList = font.getStrikeList(); if (strikeList != null) { for (CTBooleanProperty p : strikeList) { if (p.getVal()) { return true; } } } } return false; } /** * Goes through the cells specified in the given formatting, and checks if * each rule matches. Style ids from resulting matches are put in * {@link #cellToIndex}. * * @param cf * {@link ConditionalFormatting} that specifies the affected * cells * @param rule * The rule to be evaluated * @param classNameIndex * The index of the class name that was generated for this rule, * to be added to {@link #cellToIndex} */ protected void runCellMatcher(ConditionalFormatting cf, ConditionalFormattingRule rule, int classNameIndex) { final int firstColumn = cf.getFormattingRanges()[0].getFirstColumn(); final int firstRow = cf.getFormattingRanges()[0].getFirstRow(); for (CellRangeAddress cra : cf.getFormattingRanges()) { for (int row = cra.getFirstRow(); row <= cra.getLastRow(); row++) { for (int col = cra.getFirstColumn(); col <= cra.getLastColumn(); col++) { Cell cell = spreadsheet.getCell(row, col); if (cell != null && matches(cell, rule, col - firstColumn, row - firstRow)) { Set<Integer> list = cellToIndex.get(SpreadsheetUtil.toKey(cell)); if (list == null) { list = new HashSet<Integer>(); cellToIndex.put(SpreadsheetUtil.toKey(cell), list); } list.add(classNameIndex); // if the rule contains borders, we need to add styles // to other cells too if (leftBorders.containsKey(cf)) { int ruleIndex = leftBorders.get(cf); // left border for col 0 isn't rendered if (col != 0) { Cell cellToLeft = spreadsheet.getCell(row, col - 1); if (cellToLeft == null) { cellToLeft = spreadsheet.createCell(row, col - 1, ""); } list = cellToIndex.get(SpreadsheetUtil.toKey(cellToLeft)); if (list == null) { list = new HashSet<Integer>(); cellToIndex.put(SpreadsheetUtil.toKey(cellToLeft), list); } list.add(ruleIndex); } } if (topBorders.containsKey(cf)) { int ruleIndex = topBorders.get(cf); // top border for row 0 isn't rendered if (row != 0) { Cell cellOnTop = spreadsheet.getCell(row - 1, col); if (cellOnTop == null) { cellOnTop = spreadsheet.createCell(row - 1, col, ""); } list = cellToIndex.get(SpreadsheetUtil.toKey(cellOnTop)); if (list == null) { list = new HashSet<Integer>(); cellToIndex.put(SpreadsheetUtil.toKey(cellOnTop), list); } list.add(ruleIndex); } } } } } } } /** * Checks if the given cell value matches the given conditional formatting * rule. * * @param cell * Target cell * @param rule * Conditional formatting rule to check against * @return Whether the given rule evaluates to <code>true</code> for the * given cell. */ protected boolean matches(Cell cell, ConditionalFormattingRule rule, int deltaColumn, int deltaRow) { /* * Formula type is the default for most rules in modern excel files. * * There are a couple of issues with this. * * 1. the condition type seems to be '0' in all xlsx files, which is an * illegal value according to the API. The formula is still correct, and * can be accessed. * * 2. in xls-files the type is correct, but the formula is not: it * references the wrong cell. * * 3. the formula is a String. POIs FormulaEvaluation only takes Cell * arguments. So, to use it, we need to copy the formula to an existing * cell temporarily, and run the eval. */ if (rule.getConditionType().equals(ConditionType.CELL_VALUE_IS)) { return matchesValue(cell, rule); } else { return matchesFormula(rule, deltaColumn, deltaRow); } } /** * Checks if the formula in the given rule evaluates to <code>true</code>. * <p> * * NOTE: Does not support HSSF files currently. * * @param rule * Conditional formatting rule to get the formula from * @return True if the formula in the given rule is of boolean formula type * and evaluates to <code>true</code>, false otherwise */ protected boolean matchesFormula(ConditionalFormattingRule rule, int deltaColumn, int deltaRow) { if (!(rule instanceof XSSFConditionalFormattingRule)) { // TODO Does not support HSSF files for now, since HSSF does not // read cell references in the file correctly.Since HSSF formulas // are read completely wrong, that boolean formula above is useless. return false; } String booleanFormula = rule.getFormula1(); if (booleanFormula == null || booleanFormula.isEmpty()) { return false; } // Parse formula and use deltas to get relative cell references to work // (#18702) Ptg[] ptgs = FormulaParser.parse(booleanFormula, WorkbookEvaluatorUtil.getEvaluationWorkbook(spreadsheet), FormulaType.CELL, spreadsheet.getActiveSheetIndex()); for (Ptg ptg : ptgs) { // base class for cell reference "things" if (ptg instanceof RefPtgBase) { RefPtgBase ref = (RefPtgBase) ptg; // re-calculate cell references if (ref.isColRelative()) { ref.setColumn(ref.getColumn() + deltaColumn); } if (ref.isRowRelative()) { ref.setRow(ref.getRow() + deltaRow); } } } ValueEval eval; try { eval = WorkbookEvaluatorUtil.evaluate(spreadsheet, ptgs); } catch (NotImplementedException e) { LOGGER.log(Level.FINEST, e.getMessage(), e); return false; } if (eval instanceof BoolEval) { return eval == null ? false : ((BoolEval) eval).getBooleanValue(); } else { return false; } } /** * 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 * 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; } }