List of usage examples for org.apache.poi.ss.usermodel ConditionalFormatting getNumberOfRules
int getNumberOfRules();
From source file:com.vaadin.addon.spreadsheet.ConditionalFormatter.java
/** * Creates the necessary CSS rules and runs evaluations on all affected * cells./* w w w .j av a2 s. c o m*/ */ 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; } } } }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private String logoutConditionalFormat(ConditionalFormatting cf) { StringBuilder sb = new StringBuilder(); sb.append("Conditional Format:\n Ranges:\n "); CellRangeAddress[] ranges = cf.getFormattingRanges(); if (ranges != null) { for (int r = 0; r < ranges.length; r++) { if (r > 0) { sb.append("\n "); }//from w w w .j a va 2 s. c om sb.append(ranges[r].formatAsString()); } } sb.append("\n Rules:\n "); int nbRules = cf.getNumberOfRules(); for (int r = 0; r < nbRules; r++) { if (r > 0) { sb.append("\n "); } sb.append("#" + r + ":"); sb.append(describeRule(cf.getRule(r))); } return sb.toString(); }
From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java
License:Open Source License
/** * Adds in a Row to the given Sheet//from w w w . java 2 s. c o m */ public Row addRow(Workbook wb, SheetToAdd sheetToAdd, RowToAdd rowToAdd, int rowIndex, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { // Create a new row and copy over style attributes from the row to add Row newRow = sheetToAdd.getSheet().createRow(rowIndex); Row rowToClone = rowToAdd.getRowToClone(); try { CellStyle rowStyle = rowToClone.getRowStyle(); if (rowStyle != null) { newRow.setRowStyle(rowStyle); } } catch (Exception e) { // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the rowStyle. Mysteries of POI } newRow.setHeight(rowToClone.getHeight()); // Iterate across all of the cells in the row, and configure all those that need to be added/cloned List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>(); int totalCells = rowToClone.getPhysicalNumberOfCells(); int cellsFound = 0; for (int cellNum = 0; cellsFound < totalCells; cellNum++) { Cell currentCell = rowToClone.getCell(cellNum); log.debug("Handling cell: " + currentCell); if (currentCell != null) { cellsFound++; } // If we find that the cell that we are on is a repeating cell, then add the appropriate number of cells to clone String repeatingColumnProperty = getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections); if (repeatingColumnProperty != null) { String[] dataSetSpanSplit = repeatingColumnProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData()); int numCellsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug("Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numCellsToRepeat; i++) { Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i)); if (repeatNum == 1 && cell != null && cell != currentCell) { cellsFound++; } Map<String, Object> newReplacements = getReplacementData(rowToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); cellsToAdd.add(new CellToAdd(cell, newReplacements)); log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow); } } cellNum += numCellsToRepeat; } else { cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData())); log.debug("Adding " + currentCell); } } // Now, go through all of the collected cells, and add them back in String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); List<CellRangeAddress> newMergedRegions = new ArrayList<CellRangeAddress>(); for (int i = 0; i < cellsToAdd.size(); i++) { CellToAdd cellToAdd = cellsToAdd.get(i); Cell newCell = newRow.createCell(i); Cell cellToClone = cellToAdd.getCellToClone(); if (cellToClone != null) { Object contents = ExcelUtil.getCellContents(cellToClone); newCell.setCellStyle(cellToClone.getCellStyle()); int numFormattings = sheetToAdd.getSheet().getSheetConditionalFormatting() .getNumConditionalFormattings(); for (int n = 0; n < numFormattings; n++) { ConditionalFormatting f = sheetToAdd.getSheet().getSheetConditionalFormatting() .getConditionalFormattingAt(n); for (CellRangeAddress add : f.getFormattingRanges()) { if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum() && add.getLastRow() == rowToClone.getRowNum()) { if (add.getFirstColumn() == cellToClone.getColumnIndex() && add.getLastColumn() == cellToClone.getColumnIndex()) { ConditionalFormattingRule[] rules = new ConditionalFormattingRule[f .getNumberOfRules()]; for (int j = 0; j < f.getNumberOfRules(); j++) { rules[j] = f.getRule(j); } CellRangeAddress[] cellRange = new CellRangeAddress[1]; cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i); sheetToAdd.getSheet().getSheetConditionalFormatting() .addConditionalFormatting(cellRange, rules); } } } } int numMergedRegions = sheetToAdd.getSheet().getNumMergedRegions(); for (int n = 0; n < numMergedRegions; n++) { CellRangeAddress add = sheetToAdd.getSheet().getMergedRegion(n); int rowNum = rowToClone.getRowNum(); if (add.getFirstRow() == rowNum && add.getLastRow() == rowNum) { if (add.getFirstColumn() == cellToClone.getColumnIndex()) { newMergedRegions .add(new CellRangeAddress(rowNum, rowNum, i, i + add.getNumberOfCells() - 1)); } } } if (ObjectUtil.notNull(contents)) { if (contents instanceof String) { contents = EvaluationUtil.evaluateExpression(contents.toString(), cellToAdd.getReplacementData(), prefix, suffix); } ExcelUtil.setCellContents(newCell, contents); } ExcelUtil.copyFormula(cellToClone, newCell); } } for (CellRangeAddress mergedRegion : newMergedRegions) { sheetToAdd.getSheet().addMergedRegion(mergedRegion); } return newRow; }