List of usage examples for org.apache.poi.ss.usermodel ConditionalFormatting getFormattingRanges
CellRangeAddress[] getFormattingRanges();
From source file:com.vaadin.addon.spreadsheet.ConditionalFormatter.java
/** * 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}.//from www .ja v a2 s. c o m * * @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); } } } } } } }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void find(SheetConditionalFormatting scf, int row, int col) { currentCf = null;// w w w . j a v a 2 s .c o m currentCfIndex = -1; ConditionalFormatting cf = null; int numCF = scf.getNumConditionalFormattings(); for (int i = 0; i < numCF; i++) { cf = scf.getConditionalFormattingAt(i); CellRangeAddress[] crArray = cf.getFormattingRanges(); for (CellRangeAddress cra : crArray) { if (cra.isInRange(row, col)) { if (cra.isFullRowRange() == false) { currentCf = cf; currentCfIndex = i; break; } else { currentCf = null; } } } } }
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 o m 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/* w w w.ja v a 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; }