/** * Ignore errors across a range of cells. * * @param region Range of cells. * @param ignoredErrorTypes Types of error to ignore there. */ public void addIgnoredErrors(CellRangeAddress region, IgnoredErrorType... ignoredErrorTypes) { region.validate(SpreadsheetVersion.EXCEL2007); addIgnoredErrors(region.formatAsString(), ignoredErrorTypes); }
/** * Allows to add a new Conditional Formatting set to the sheet. * * @param regions - list of rectangular regions to apply conditional formatting rules * @param cfRules - set of up to three conditional formatting rules * * @return index of the newly created Conditional Formatting object */ public int addConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule[] cfRules) { if (regions == null) { throw new IllegalArgumentException("regions must not be null"); } for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL97); if (cfRules == null) { throw new IllegalArgumentException("cfRules must not be null"); } if (cfRules.length == 0) { throw new IllegalArgumentException("cfRules must not be empty"); } if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } CFRuleBase[] rules = new CFRuleBase[cfRules.length]; for (int i = 0; i != cfRules.length; i++) { rules[i] = cfRules[i].getCfRuleRecord(); } CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules); return _conditionalFormattingTable.add(cfra); }
public int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) { if (regions == null) { throw new IllegalArgumentException("regions must not be null"); } for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007); if (cfRules == null) { throw new IllegalArgumentException("cfRules must not be null"); } if (cfRules.length == 0) { throw new IllegalArgumentException("cfRules must not be empty"); } if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions); CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting(); List<String> refs = new ArrayList<>(); for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString()); cf.setSqref(refs); int priority = 1; for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()){ priority += c.sizeOfCfRuleArray(); } for(ConditionalFormattingRule rule : cfRules){ XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)rule; xRule.getCTCfRule().setPriority(priority++); cf.addNewCfRule().set(xRule.getCTCfRule()); } return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1; }
/** * adds a merged region of cells (hence those cells form one) * * @param region (rowfrom/colfrom-rowto/colto) to merge * @param validate whether to validate merged region * @return index of this region * @throws IllegalArgumentException if region contains fewer than 2 cells * @throws IllegalStateException if region intersects with an existing merged region * or multi-cell array formula on this sheet */ private int addMergedRegion(CellRangeAddress region, boolean validate) { if (region.getNumberOfCells() < 2) { throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells"); } region.validate(SpreadsheetVersion.EXCEL97); if (validate) { // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); // Throw IllegalStateException if the argument CellRangeAddress intersects with // a merged region already in this sheet validateMergedRegions(region); } return _sheet.addMergedRegion(region.getFirstRow(), region.getFirstColumn(), region.getLastRow(), region.getLastColumn()); }
/** * Adds a merged region of cells (hence those cells form one). * If validate is true, check to make sure adding the merged region to the sheet doesn't create a corrupt workbook * If validate is false, skips the expensive merged region checks, but may produce a corrupt workbook. * * @param region to merge * @param validate whether to validate merged region * @return index of this region * @throws IllegalArgumentException if region contains fewer than 2 cells (this check is inexpensive and is performed regardless of <tt>validate</tt>) * @throws IllegalStateException if region intersects with a multi-cell array formula * @throws IllegalStateException if region intersects with an existing region on this sheet */ private int addMergedRegion(CellRangeAddress region, boolean validate) { if (region.getNumberOfCells() < 2) { throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells"); } region.validate(SpreadsheetVersion.EXCEL2007); if (validate) { // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); // Throw IllegalStateException if the argument CellRangeAddress intersects with // a merged region already in this sheet validateMergedRegions(region); } CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells(); CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell(); ctMergeCell.setRef(region.formatAsString()); return ctMergeCells.sizeOfMergeCellArray(); }
/** * Ignore errors across a range of cells. * * @param region Range of cells. * @param ignoredErrorTypes Types of error to ignore there. */ public void addIgnoredErrors(CellRangeAddress region, IgnoredErrorType... ignoredErrorTypes) { region.validate(SpreadsheetVersion.EXCEL2007); addIgnoredErrors(region.formatAsString(), ignoredErrorTypes); }
/** * Allows to add a new Conditional Formatting set to the sheet. * * @param regions - list of rectangular regions to apply conditional formatting rules * @param cfRules - set of up to three conditional formatting rules * * @return index of the newly created Conditional Formatting object */ public int addConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule[] cfRules) { if (regions == null) { throw new IllegalArgumentException("regions must not be null"); } for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL97); if (cfRules == null) { throw new IllegalArgumentException("cfRules must not be null"); } if (cfRules.length == 0) { throw new IllegalArgumentException("cfRules must not be empty"); } if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } CFRuleRecord[] rules = new CFRuleRecord[cfRules.length]; for (int i = 0; i != cfRules.length; i++) { rules[i] = cfRules[i].getCfRuleRecord(); } CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules); return _conditionalFormattingTable.add(cfra); }
/** * Allows to add a new Conditional Formatting set to the sheet. * * @param regions - list of rectangular regions to apply conditional formatting rules * @param cfRules - set of up to three conditional formatting rules * * @return index of the newly created Conditional Formatting object */ public int addConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule[] cfRules) { if (regions == null) { throw new IllegalArgumentException("regions must not be null"); } for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL97); if (cfRules == null) { throw new IllegalArgumentException("cfRules must not be null"); } if (cfRules.length == 0) { throw new IllegalArgumentException("cfRules must not be empty"); } if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } CFRuleRecord[] rules = new CFRuleRecord[cfRules.length]; for (int i = 0; i != cfRules.length; i++) { rules[i] = cfRules[i].getCfRuleRecord(); } CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules); return _conditionalFormattingTable.add(cfra); }
/** * Allows to add a new Conditional Formatting set to the sheet. * * @param regions - list of rectangular regions to apply conditional formatting rules * @param cfRules - set of up to three conditional formatting rules * * @return index of the newly created Conditional Formatting object */ public int addConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule[] cfRules) { if (regions == null) { throw new IllegalArgumentException("regions must not be null"); } for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL97); if (cfRules == null) { throw new IllegalArgumentException("cfRules must not be null"); } if (cfRules.length == 0) { throw new IllegalArgumentException("cfRules must not be empty"); } if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } CFRuleBase[] rules = new CFRuleBase[cfRules.length]; for (int i = 0; i != cfRules.length; i++) { rules[i] = cfRules[i].getCfRuleRecord(); } CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules); return _conditionalFormattingTable.add(cfra); }
/** * adds a merged region of cells (hence those cells form one) * @param region (rowfrom/colfrom-rowto/colto) to merge * @return index of this region */ public int addMergedRegion(CellRangeAddress region) { region.validate(SpreadsheetVersion.EXCEL97); // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); return _sheet.addMergedRegion( region.getFirstRow(), region.getFirstColumn(), region.getLastRow(), region.getLastColumn()); }
throw new IllegalArgumentException("regions must not be null"); for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007);
public int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) { if (regions == null) { throw new IllegalArgumentException("regions must not be null"); } for(CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007); if (cfRules == null) { throw new IllegalArgumentException("cfRules must not be null"); } if (cfRules.length == 0) { throw new IllegalArgumentException("cfRules must not be empty"); } if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions); CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting(); List<String> refs = new ArrayList<>(); for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString()); cf.setSqref(refs); int priority = 1; for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()){ priority += c.sizeOfCfRuleArray(); } for(ConditionalFormattingRule rule : cfRules){ XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)rule; xRule.getCTCfRule().setPriority(priority++); cf.addNewCfRule().set(xRule.getCTCfRule()); } return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1; }
/** * adds a merged region of cells (hence those cells form one) * * @param region (rowfrom/colfrom-rowto/colto) to merge * @return index of this region */ public int addMergedRegion(CellRangeAddress region) { region.validate(SpreadsheetVersion.EXCEL97); // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); return _sheet.addMergedRegion(region.getFirstRow(), region.getFirstColumn(), region.getLastRow(), region.getLastColumn()); }
/** * Adds a merged region of cells (hence those cells form one). * * @param region (rowfrom/colfrom-rowto/colto) to merge * @return index of this region */ public int addMergedRegion(CellRangeAddress region) { region.validate(SpreadsheetVersion.EXCEL2007); // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells(); CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell(); ctMergeCell.setRef(region.formatAsString()); return ctMergeCells.sizeOfMergeCellArray(); }
/** * adds a merged region of cells (hence those cells form one) * * @param region (rowfrom/colfrom-rowto/colto) to merge * @param validate whether to validate merged region * @return index of this region * @throws IllegalArgumentException if region contains fewer than 2 cells * @throws IllegalStateException if region intersects with an existing merged region * or multi-cell array formula on this sheet */ private int addMergedRegion(CellRangeAddress region, boolean validate) { if (region.getNumberOfCells() < 2) { throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells"); } region.validate(SpreadsheetVersion.EXCEL97); if (validate) { // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); // Throw IllegalStateException if the argument CellRangeAddress intersects with // a merged region already in this sheet validateMergedRegions(region); } return _sheet.addMergedRegion(region.getFirstRow(), region.getFirstColumn(), region.getLastRow(), region.getLastColumn()); }
/** * Adds a merged region of cells (hence those cells form one). * If validate is true, check to make sure adding the merged region to the sheet doesn't create a corrupt workbook * If validate is false, skips the expensive merged region checks, but may produce a corrupt workbook. * * @param region to merge * @param validate whether to validate merged region * @return index of this region * @throws IllegalArgumentException if region contains fewer than 2 cells (this check is inexpensive and is performed regardless of <tt>validate</tt>) * @throws IllegalStateException if region intersects with a multi-cell array formula * @throws IllegalStateException if region intersects with an existing region on this sheet */ private int addMergedRegion(CellRangeAddress region, boolean validate) { if (region.getNumberOfCells() < 2) { throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells"); } region.validate(SpreadsheetVersion.EXCEL2007); if (validate) { // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); // Throw IllegalStateException if the argument CellRangeAddress intersects with // a merged region already in this sheet validateMergedRegions(region); } CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells(); CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell(); ctMergeCell.setRef(region.formatAsString()); return ctMergeCells.sizeOfMergeCellArray(); }