Attachment #31964 for bug #56914

View | Details | Raw Unified | Return to bug 56914
Collapse All | Expand All




    }

    public void testShiftRows() {

        Workbook wb = _testDataProvider.createWorkbook();
        Sheet sheet = wb.createSheet();



        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, 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, rule1);
        sheetCF.addConditionalFormatting(regions, rule2);

        // This row shift should only affect the formulas
        sheet.shiftRows(14, 17, 8);
        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);
        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){




        XSSFWorkbook wb = sheet.getWorkbook();
        int sheetIndex = wb.getSheetIndex(sheet);


        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
        CTWorksheet ctWorksheet = sheet.getCTWorksheet();
        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<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
            for (Object stRef : cf.getSqref()) {

            }

            for(CTCfRule cfRule : cf.getCfRuleArray()){
                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);




        if (cfRules.length > 3) {
            throw new IllegalArgumentException("Number of rules must not exceed 3");
        }

        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<String> refs = new ArrayList<String>();
        for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
        cf.setSqref(refs);


        int priority = 1;
        for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()){

Return to bug 56914

Lines 390-396 Link Here

(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java (-10 / +21 lines)
390	}	390	}
391		391
392	public void testShiftRows() {	392	public void testShiftRows() {
393
394	Workbook wb = _testDataProvider.createWorkbook();	393	Workbook wb = _testDataProvider.createWorkbook();
395	Sheet sheet = wb.createSheet();	394	Sheet sheet = wb.createSheet();
396		395
Lines 403-432 Link Here
403		402
404	PatternFormatting patternFmt = rule1.createPatternFormatting();	403	PatternFormatting patternFmt = rule1.createPatternFormatting();
405	patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);	404	patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);
406	ConditionalFormattingRule [] cfRules = { rule1, };
407		405
		406	ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(
		407	ComparisonOperator.BETWEEN, "SUM(A10:A15)", "1+SUM(B16:B30)");
		408	BorderFormatting borderFmt = rule2.createBorderFormatting();
		409	borderFmt.setBorderDiagonal((short) 2);
		410
408	CellRangeAddress [] regions = {	411	CellRangeAddress [] regions = {
409	new CellRangeAddress(2, 4, 0, 0), // A3:A5	412	new CellRangeAddress(2, 4, 0, 0), // A3:A5
410	};	413	};
411	sheetCF.addConditionalFormatting(regions, cfRules);	414	sheetCF.addConditionalFormatting(regions, rule1);
		415	sheetCF.addConditionalFormatting(regions, rule2);
412		416
413	// This row-shift should destroy the CF region	417	// This row-shift should destroy the CF region
414	sheet.shiftRows(10, 20, -9);	418	sheet.shiftRows(10, 20, -9);
415	assertEquals(0, sheetCF.getNumConditionalFormattings());	419	assertEquals(0, sheetCF.getNumConditionalFormattings());
416		420
417	// re-add the CF	421	// re-add the CF
418	sheetCF.addConditionalFormatting(regions, cfRules);	422	sheetCF.addConditionalFormatting(regions, rule1);
		423	sheetCF.addConditionalFormatting(regions, rule2);
419		424
420	// This row shift should only affect the formulas	425	// This row shift should only affect the formulas
421	sheet.shiftRows(14, 17, 8);	426	sheet.shiftRows(14, 17, 8);
422	ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);	427	ConditionalFormatting cf1 = sheetCF.getConditionalFormattingAt(0);
423	assertEquals("SUM(A10:A23)", cf.getRule(0).getFormula1());	428	assertEquals("SUM(A10:A23)", cf1.getRule(0).getFormula1());
424	assertEquals("1+SUM(B24:B30)", cf.getRule(0).getFormula2());	429	assertEquals("1+SUM(B24:B30)", cf1.getRule(0).getFormula2());
		430	ConditionalFormatting cf2 = sheetCF.getConditionalFormattingAt(1);
		431	assertEquals("SUM(A10:A23)", cf2.getRule(0).getFormula1());
		432	assertEquals("1+SUM(B24:B30)", cf2.getRule(0).getFormula2());
425		433
426	sheet.shiftRows(0, 8, 21);	434	sheet.shiftRows(0, 8, 21);
427	cf = sheetCF.getConditionalFormattingAt(0);	435	cf1 = sheetCF.getConditionalFormattingAt(0);
428	assertEquals("SUM(A10:A21)", cf.getRule(0).getFormula1());	436	assertEquals("SUM(A10:A21)", cf1.getRule(0).getFormula1());
429	assertEquals("1+SUM(#REF!)", cf.getRule(0).getFormula2());	437	assertEquals("1+SUM(#REF!)", cf1.getRule(0).getFormula2());
		438	cf2 = sheetCF.getConditionalFormattingAt(1);
		439	assertEquals("SUM(A10:A21)", cf2.getRule(0).getFormula1());
		440	assertEquals("1+SUM(#REF!)", cf2.getRule(0).getFormula2());
430	}	441	}
431		442
432	protected void testRead(String filename){	443	protected void testRead(String filename){

Lines 226-237 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (-7 / +7 lines)
226	XSSFWorkbook wb = sheet.getWorkbook();	226	XSSFWorkbook wb = sheet.getWorkbook();
227	int sheetIndex = wb.getSheetIndex(sheet);	227	int sheetIndex = wb.getSheetIndex(sheet);
228		228
229
230	XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);	229	XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
231	CTWorksheet ctWorksheet = sheet.getCTWorksheet();	230	CTWorksheet ctWorksheet = sheet.getCTWorksheet();
232	int cfCount = ctWorksheet.sizeOfConditionalFormattingArray();	231	CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
233	for(int j = 0; j< cfCount; j++){	232	// iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
234	CTConditionalFormatting cf = ctWorksheet.getConditionalFormattingArray(j);	233	for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
		234	CTConditionalFormatting cf = conditionalFormattingArray[j];
235		235
236	ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();	236	ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
237	for (Object stRef : cf.getSqref()) {	237	for (Object stRef : cf.getSqref()) {
Lines 267-275 Link Here
267	}	267	}
268		268
269	for(CTCfRule cfRule : cf.getCfRuleArray()){	269	for(CTCfRule cfRule : cf.getCfRuleArray()){
270	int formulaCount = cfRule.sizeOfFormulaArray();	270	String[] formulaArray = cfRule.getFormulaArray();
271	for (int i = 0; i < formulaCount; i++) {	271	for (int i = 0; i < formulaArray.length; i++) {
272	String formula = cfRule.getFormulaArray(i);	272	String formula = formulaArray[i];
273	Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex);	273	Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex);
274	if (shifter.adjustFormula(ptgs, sheetIndex)) {	274	if (shifter.adjustFormula(ptgs, sheetIndex)) {
275	String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);	275	String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);

Lines 131-148 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java (-7 / +1 lines)
131	if (cfRules.length > 3) {	131	if (cfRules.length > 3) {
132	throw new IllegalArgumentException("Number of rules must not exceed 3");	132	throw new IllegalArgumentException("Number of rules must not exceed 3");
133	}	133	}
134	XSSFConditionalFormattingRule[] hfRules;	134
135	if(cfRules instanceof XSSFConditionalFormattingRule[]) hfRules = (XSSFConditionalFormattingRule[])cfRules;
136	else {
137	hfRules = new XSSFConditionalFormattingRule[cfRules.length];
138	System.arraycopy(cfRules, 0, hfRules, 0, hfRules.length);
139	}
140	CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions);	135	CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions);
141	CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting();	136	CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting();
142	List<String> refs = new ArrayList<String>();	137	List<String> refs = new ArrayList<String>();
143	for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());	138	for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
144	cf.setSqref(refs);	139	cf.setSqref(refs);
145
146		140
147	int priority = 1;	141	int priority = 1;
148	for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()){	142	for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()){