From b43fbeffe623e4885f500bb3768b4360563b2441 Mon Sep 17 00:00:00 2001 From: Dominik Stadler Date: Mon, 18 Jul 2016 10:32:55 +0000 Subject: [PATCH] 52122: Excel does not handle conditional formatting based on formula correctly unless recalculation is forced git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1753199 13f79535-47bb-0310-9956-ffa450edef68 --- .../record/aggregates/CFRecordsAggregate.java | 12 +- .../aggregates/TestCFRecordsAggregate.java | 20 +++- .../TestHSSFConditionalFormatting.java | 105 ++++++++++++++++-- 3 files changed, 124 insertions(+), 13 deletions(-) diff --git a/src/java/org/apache/poi/hssf/record/aggregates/CFRecordsAggregate.java b/src/java/org/apache/poi/hssf/record/aggregates/CFRecordsAggregate.java index b0112306ff..40fd288652 100644 --- a/src/java/org/apache/poi/hssf/record/aggregates/CFRecordsAggregate.java +++ b/src/java/org/apache/poi/hssf/record/aggregates/CFRecordsAggregate.java @@ -83,10 +83,18 @@ public final class CFRecordsAggregate extends RecordAggregate { this(createHeader(regions, rules), rules); } private static CFHeaderBase createHeader(CellRangeAddress[] regions, CFRuleBase[] rules) { + final CFHeaderBase header; if (rules.length == 0 || rules[0] instanceof CFRuleRecord) { - return new CFHeaderRecord(regions, rules.length); + header = new CFHeaderRecord(regions, rules.length); + } else { + header = new CFHeader12Record(regions, rules.length); } - return new CFHeader12Record(regions, rules.length); + + // set the "needs recalculate" by default to avoid Excel handling conditional formatting incorrectly + // see bug 52122 for details + header.setNeedRecalculation(true); + + return header; } /** diff --git a/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java b/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java index c58f401bc4..ee04ed01a1 100644 --- a/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java +++ b/src/testcases/org/apache/poi/hssf/record/aggregates/TestCFRecordsAggregate.java @@ -64,8 +64,7 @@ public final class TestCFRecordsAggregate extends TestCase { recs.add(rule1); recs.add(rule2); recs.add(rule3); - CFRecordsAggregate record; - record = CFRecordsAggregate.createCFAggregate(new RecordStream(recs, 0)); + CFRecordsAggregate record = CFRecordsAggregate.createCFAggregate(new RecordStream(recs, 0)); // Serialize byte [] serializedRecord = new byte[record.getRecordSize()]; @@ -81,12 +80,16 @@ public final class TestCFRecordsAggregate extends TestCase { header = (CFHeaderRecord)recs.get(0); rule1 = (CFRuleRecord)recs.get(1); + assertNotNull(rule1); rule2 = (CFRuleRecord)recs.get(2); + assertNotNull(rule2); rule3 = (CFRuleRecord)recs.get(3); + assertNotNull(rule3); cellRanges = header.getCellRanges(); assertEquals(2, cellRanges.length); assertEquals(3, header.getNumberOfConditionalFormats()); + assertFalse(header.getNeedRecalculation()); record = CFRecordsAggregate.createCFAggregate(new RecordStream(recs, 0)); @@ -97,12 +100,16 @@ public final class TestCFRecordsAggregate extends TestCase { header = record.getHeader(); rule1 = record.getRule(0); + assertNotNull(rule1); rule2 = record.getRule(1); + assertNotNull(rule2); rule3 = record.getRule(2); + assertNotNull(rule3); cellRanges = header.getCellRanges(); assertEquals(2, cellRanges.length); assertEquals(3, header.getNumberOfConditionalFormats()); + assertFalse(header.getNeedRecalculation()); } /** @@ -144,15 +151,20 @@ public final class TestCFRecordsAggregate extends TestCase { try { new CFRecordsAggregate(cellRanges, rules); fail("Shouldn't be able to mix between types"); - } catch (IllegalArgumentException e) {} + } catch (IllegalArgumentException e) { + // expected here + } rules = new CFRuleBase[] { CFRuleRecord.create(sheet, "7") }; CFRecordsAggregate agg = new CFRecordsAggregate(cellRanges, rules); + assertTrue(agg.getHeader().getNeedRecalculation()); try { agg.addRule(CFRule12Record.create(sheet, "7")); fail("Shouldn't be able to mix between types"); - } catch (IllegalArgumentException e) {} + } catch (IllegalArgumentException e) { + // expected here + } } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java index 2aee6f54fa..ede6a6df1e 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java @@ -19,16 +19,14 @@ package org.apache.poi.hssf.usermodel; import static org.junit.Assert.*; -import java.io.FileNotFoundException; + import java.io.IOException; import org.apache.poi.hssf.HSSFITestDataProvider; +import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.util.HSSFColor; -import org.apache.poi.ss.usermodel.BaseTestConditionalFormatting; -import org.apache.poi.ss.usermodel.Color; -import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.SheetConditionalFormatting; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddress; import org.junit.Test; /** @@ -116,10 +114,103 @@ public final class TestHSSFConditionalFormatting extends BaseTestConditionalForm } } - private void writeTemp53691(Workbook wb, String suffix) throws FileNotFoundException, IOException { + private void writeTemp53691(Workbook wb, @SuppressWarnings("UnusedParameters") String suffix) throws IOException { // assert that we can write/read it in memory Workbook wbBack = HSSFITestDataProvider.instance.writeOutAndReadBack(wb); assertNotNull(wbBack); wbBack.close(); } + + + @SuppressWarnings("deprecation") + @Test + public void test52122() throws Exception { + Workbook workbook = new HSSFWorkbook(); + Sheet sheet = workbook.createSheet("Conditional Formatting Test"); + sheet.setColumnWidth(0, 256 * 10); + sheet.setColumnWidth(1, 256 * 10); + sheet.setColumnWidth(2, 256 * 10); + + // Create some content. + // row 0 + Row row = sheet.createRow(0); + + Cell cell0 = row.createCell(0); + cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell0.setCellValue(100); + + Cell cell1 = row.createCell(1); + cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell1.setCellValue(120); + + Cell cell2 = row.createCell(2); + cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell2.setCellValue(130); + + // row 1 + row = sheet.createRow(1); + + cell0 = row.createCell(0); + cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell0.setCellValue(200); + + cell1 = row.createCell(1); + cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell1.setCellValue(220); + + cell2 = row.createCell(2); + cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell2.setCellValue(230); + + // row 2 + row = sheet.createRow(2); + + cell0 = row.createCell(0); + cell0.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell0.setCellValue(300); + + cell1 = row.createCell(1); + cell1.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell1.setCellValue(320); + + cell2 = row.createCell(2); + cell2.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC); + cell2.setCellValue(330); + + // Create conditional formatting, CELL1 should be yellow if CELL0 is not blank. + SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting(); + + ConditionalFormattingRule rule = formatting.createConditionalFormattingRule("$A$1>75"); + + PatternFormatting pattern = rule.createPatternFormatting(); + pattern.setFillBackgroundColor(IndexedColors.BLUE.index); + pattern.setFillPattern(PatternFormatting.SOLID_FOREGROUND); + + CellRangeAddress[] range = {CellRangeAddress.valueOf("B2:C2")}; + CellRangeAddress[] range2 = {CellRangeAddress.valueOf("B1:C1")}; + + formatting.addConditionalFormatting(range, rule); + formatting.addConditionalFormatting(range2, rule); + + // Write file. + /*FileOutputStream fos = new FileOutputStream("c:\\temp\\52122_conditional-sheet.xls"); + try { + workbook.write(fos); + } finally { + fos.close(); + }*/ + + Workbook wbBack = HSSFTestDataSamples.writeOutAndReadBack((HSSFWorkbook)workbook); + Sheet sheetBack = wbBack.getSheetAt(0); + final SheetConditionalFormatting sheetConditionalFormattingBack = sheetBack.getSheetConditionalFormatting(); + assertNotNull(sheetConditionalFormattingBack); + final ConditionalFormatting formattingBack = sheetConditionalFormattingBack.getConditionalFormattingAt(0); + assertNotNull(formattingBack); + final ConditionalFormattingRule ruleBack = formattingBack.getRule(0); + assertNotNull(ruleBack); + final PatternFormatting patternFormattingBack1 = ruleBack.getPatternFormatting(); + assertNotNull(patternFormattingBack1); + assertEquals(IndexedColors.BLUE.index, patternFormattingBack1.getFillBackgroundColor()); + assertEquals(PatternFormatting.SOLID_FOREGROUND, patternFormattingBack1.getFillPattern()); + } } -- 2.39.5