Index: src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java (revision 1622177) +++ src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java (revision ) @@ -390,7 +390,6 @@ } public void testShiftRows() { - Workbook wb = _testDataProvider.createWorkbook(); Sheet sheet = wb.createSheet(); @@ -403,30 +402,42 @@ PatternFormatting patternFmt = rule1.createPatternFormatting(); patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index); - ConditionalFormattingRule [] cfRules = { rule1, }; + ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule( + ComparisonOperator.BETWEEN, "SUM(A10:A15)", "1+SUM(B16:B30)"); + BorderFormatting borderFmt = rule2.createBorderFormatting(); + borderFmt.setBorderDiagonal((short) 2); + CellRangeAddress [] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5 }; - sheetCF.addConditionalFormatting(regions, cfRules); + sheetCF.addConditionalFormatting(regions, rule1); + sheetCF.addConditionalFormatting(regions, rule2); // This row-shift should destroy the CF region sheet.shiftRows(10, 20, -9); assertEquals(0, sheetCF.getNumConditionalFormattings()); // re-add the CF - sheetCF.addConditionalFormatting(regions, cfRules); + sheetCF.addConditionalFormatting(regions, rule1); + sheetCF.addConditionalFormatting(regions, rule2); // This row shift should only affect the formulas sheet.shiftRows(14, 17, 8); - ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0); - assertEquals("SUM(A10:A23)", cf.getRule(0).getFormula1()); - assertEquals("1+SUM(B24:B30)", cf.getRule(0).getFormula2()); + ConditionalFormatting cf1 = sheetCF.getConditionalFormattingAt(0); + assertEquals("SUM(A10:A23)", cf1.getRule(0).getFormula1()); + assertEquals("1+SUM(B24:B30)", cf1.getRule(0).getFormula2()); + ConditionalFormatting cf2 = sheetCF.getConditionalFormattingAt(1); + assertEquals("SUM(A10:A23)", cf2.getRule(0).getFormula1()); + assertEquals("1+SUM(B24:B30)", cf2.getRule(0).getFormula2()); sheet.shiftRows(0, 8, 21); - cf = sheetCF.getConditionalFormattingAt(0); - assertEquals("SUM(A10:A21)", cf.getRule(0).getFormula1()); - assertEquals("1+SUM(#REF!)", cf.getRule(0).getFormula2()); + cf1 = sheetCF.getConditionalFormattingAt(0); + assertEquals("SUM(A10:A21)", cf1.getRule(0).getFormula1()); + assertEquals("1+SUM(#REF!)", cf1.getRule(0).getFormula2()); + cf2 = sheetCF.getConditionalFormattingAt(1); + assertEquals("SUM(A10:A21)", cf2.getRule(0).getFormula1()); + assertEquals("1+SUM(#REF!)", cf2.getRule(0).getFormula2()); } protected void testRead(String filename){ Index: src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (revision 1622177) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (revision ) @@ -226,12 +226,12 @@ XSSFWorkbook wb = sheet.getWorkbook(); int sheetIndex = wb.getSheetIndex(sheet); - XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); CTWorksheet ctWorksheet = sheet.getCTWorksheet(); - int cfCount = ctWorksheet.sizeOfConditionalFormattingArray(); - for(int j = 0; j< cfCount; j++){ - CTConditionalFormatting cf = ctWorksheet.getConditionalFormattingArray(j); + CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray(); + // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j) + for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) { + CTConditionalFormatting cf = conditionalFormattingArray[j]; ArrayList cellRanges = new ArrayList(); for (Object stRef : cf.getSqref()) { @@ -267,9 +267,9 @@ } for(CTCfRule cfRule : cf.getCfRuleArray()){ - int formulaCount = cfRule.sizeOfFormulaArray(); - for (int i = 0; i < formulaCount; i++) { - String formula = cfRule.getFormulaArray(i); + String[] formulaArray = cfRule.getFormulaArray(); + for (int i = 0; i < formulaArray.length; i++) { + String formula = formulaArray[i]; Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex); if (shifter.adjustFormula(ptgs, sheetIndex)) { String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java IDEA additional info: Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP <+>UTF-8 =================================================================== --- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java (revision 1622177) +++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java (revision ) @@ -131,18 +131,12 @@ if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } - XSSFConditionalFormattingRule[] hfRules; - if(cfRules instanceof XSSFConditionalFormattingRule[]) hfRules = (XSSFConditionalFormattingRule[])cfRules; - else { - hfRules = new XSSFConditionalFormattingRule[cfRules.length]; - System.arraycopy(cfRules, 0, hfRules, 0, hfRules.length); - } + CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions); CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting(); List refs = new ArrayList(); for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString()); cf.setSqref(refs); - int priority = 1; for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()){