diff options
33 files changed, 3097 insertions, 278 deletions
diff --git a/src/documentation/content/xdocs/spreadsheet/quick-guide.xml b/src/documentation/content/xdocs/spreadsheet/quick-guide.xml index 0e2d6a05b3..d467468097 100644 --- a/src/documentation/content/xdocs/spreadsheet/quick-guide.xml +++ b/src/documentation/content/xdocs/spreadsheet/quick-guide.xml @@ -729,7 +729,7 @@ Examples: sheet1.addMergedRegion( region ); // Set the border and border colors. - final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED; + final short borderMediumDashed = CellStyle.BORDER_MEDIUM_DASHED; RegionUtil.setBorderBottom( borderMediumDashed, region, sheet1, wb ); RegionUtil.setBorderTop( borderMediumDashed, @@ -748,7 +748,7 @@ Examples: style.setIndention((short)4); CellUtil.createCell(row, 8, "This is the value of the cell", style); Cell cell2 = CellUtil.createCell( row2, 8, "This is the value of the cell"); - CellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER); + CellUtil.setAlignment(cell2, wb, CellStyle.ALIGN_CENTER); // Write out the workbook FileOutputStream fileOut = new FileOutputStream( "workbook.xls" ); @@ -1677,39 +1677,43 @@ Examples: </source> </section> <anchor id="ConditionalFormatting"/> - <section><title>Conditional Formatting (HSSF Only)</title> + <section><title>Conditional Formatting</title> <source> - HSSFWorkbook workbook = new HSSFWorkbook(); - HSSFSheet sheet = workbook.createSheet(); - String formula = "7"; + Workbook workbook = new HSSFWorkbook(); // or new XSSFWorkbook(); + Sheet sheet = workbook.createSheet(); - HSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); + SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); - HSSFConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(formula); - HSSFFontFormatting fontFmt = rule1.createFontFormatting(); + ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0"); + FontFormatting fontFmt = rule1.createFontFormatting(); fontFmt.setFontStyle(true, false); + fontFmt.setFontColorIndex(IndexedColors.DARK_RED.index); + + BorderFormatting bordFmt = rule1.createBorderFormatting(); + bordFmt.setBorderBottom(BorderFormatting.BORDER_THIN); + bordFmt.setBorderTop(BorderFormatting.BORDER_THICK); + bordFmt.setBorderLeft(BorderFormatting.BORDER_DASHED); + bordFmt.setBorderRight(BorderFormatting.BORDER_DOTTED); - HSSFBorderFormatting bordFmt = rule1.createBorderFormatting(); - bordFmt.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); - bordFmt.setBorderTop(HSSFBorderFormatting.BORDER_THICK); - bordFmt.setBorderLeft(HSSFBorderFormatting.BORDER_DASHED); - bordFmt.setBorderRight(HSSFBorderFormatting.BORDER_DOTTED); - - HSSFPatternFormatting patternFmt = rule1.createPatternFormatting(); - patternFmt.setFillBackgroundColor(HSSFColor.YELLOW.index); + PatternFormatting patternFmt = rule1.createPatternFormatting(); + patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index); - HSSFConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "1", "2"); - HSSFConditionalFormattingRule [] cfRules = + ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "-10", "10"); + ConditionalFormattingRule [] cfRules = { rule1, rule2 }; - CellRangeAddress [] regions = { - new CellRangeAddress(2, 4, 0, 0), // A3:A5 + CellRangeAddress[] regions = { + CellRangeAddress.valueOf("A3:A5") }; sheetCF.addConditionalFormatting(regions, cfRules); </source> + <p> See more examples on Excel conditional formatting in + <link href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java">ConditionalFormats.java</link> + </p> + </section> </body> </document> diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 510e67e842..4bf487c3e2 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ <changes> <release version="3.8-beta4" date="2011-??-??"> + <action dev="poi-developers" type="add">Support for conditional formatting in XSSF</action> <action dev="poi-developers" type="add">Support isRightToLeft and setRightToLeft on the common spreadsheet Sheet interface, as per existing HSSF support</action> <action dev="poi-developers" type="fix">50209 - Fixed evaluation of Subtotals to ignore nested subtotals</action> <action dev="poi-developers" type="fix">44431 - HWPFDocument.write destroys fields</action> diff --git a/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java b/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java new file mode 100755 index 0000000000..d0ce7674ee --- /dev/null +++ b/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java @@ -0,0 +1,349 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.examples;
+
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+
+import java.io.FileOutputStream;
+import java.io.IOException;
+
+/**
+ * Excel Conditional Formatting -- Examples
+ *
+ * <p>
+ * Based on the code snippets from http://www.contextures.com/xlcondformat03.html
+ * </p>
+ *
+ * @author Yegor Kozlov
+ */
+public class ConditionalFormats {
+
+ public static void main(String[] args) throws IOException {
+ Workbook wb;
+
+ if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
+ else wb = new XSSFWorkbook();
+
+ sameCell(wb.createSheet("Same Cell"));
+ multiCell(wb.createSheet("MultiCell"));
+ errors(wb.createSheet("Errors"));
+ hideDupplicates(wb.createSheet("Hide Dups"));
+ formatDuplicates(wb.createSheet("Duplicates"));
+ inList(wb.createSheet("In List"));
+ expiry(wb.createSheet("Expiry"));
+ shadeAlt(wb.createSheet("Shade Alt"));
+ shadeBands(wb.createSheet("Shade Bands"));
+
+ // Write the output to a file
+ String file = "cf-poi.xls";
+ if(wb instanceof XSSFWorkbook) file += "x";
+ FileOutputStream out = new FileOutputStream(file);
+ wb.write(out);
+ out.close();
+
+ }
+
+ /**
+ * Highlight cells based on their values
+ */
+ static void sameCell(Sheet sheet) {
+ sheet.createRow(0).createCell(0).setCellValue(84);
+ sheet.createRow(1).createCell(0).setCellValue(74);
+ sheet.createRow(2).createCell(0).setCellValue(50);
+ sheet.createRow(3).createCell(0).setCellValue(51);
+ sheet.createRow(4).createCell(0).setCellValue(49);
+ sheet.createRow(5).createCell(0).setCellValue(41);
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Cell Value Is greater than 70 (Blue Fill)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70");
+ PatternFormatting fill1 = rule1.createPatternFormatting();
+ fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
+ fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
+
+ // Condition 2: Cell Value Is less than 50 (Green Fill)
+ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50");
+ PatternFormatting fill2 = rule2.createPatternFormatting();
+ fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
+ fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A1:A6")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1, rule2);
+
+ sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)");
+ sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)");
+ }
+
+ /**
+ * Highlight multiple cells based on a formula
+ */
+ static void multiCell(Sheet sheet) {
+ // header row
+ Row row0 = sheet.createRow(0);
+ row0.createCell(0).setCellValue("Units");
+ row0.createCell(1).setCellValue("Cost");
+ row0.createCell(2).setCellValue("Total");
+
+ Row row1 = sheet.createRow(1);
+ row1.createCell(0).setCellValue(71);
+ row1.createCell(1).setCellValue(29);
+ row1.createCell(2).setCellValue(2059);
+
+ Row row2 = sheet.createRow(2);
+ row2.createCell(0).setCellValue(85);
+ row2.createCell(1).setCellValue(29);
+ row2.createCell(2).setCellValue(2059);
+
+ Row row3 = sheet.createRow(3);
+ row3.createCell(0).setCellValue(71);
+ row3.createCell(1).setCellValue(29);
+ row3.createCell(2).setCellValue(2059);
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Formula Is =$B2>75 (Blue Fill)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75");
+ PatternFormatting fill1 = rule1.createPatternFormatting();
+ fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
+ fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A2:C4")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75 (Blue Fill)");
+ }
+
+ /**
+ * Use Excel conditional formatting to check for errors,
+ * and change the font colour to match the cell colour.
+ * In this example, if formula result is #DIV/0! then it will have white font colour.
+ */
+ static void errors(Sheet sheet) {
+ sheet.createRow(0).createCell(0).setCellValue(84);
+ sheet.createRow(1).createCell(0).setCellValue(0);
+ sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
+ sheet.createRow(3).createCell(0).setCellValue(0);
+ sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
+ sheet.createRow(5).createCell(0).setCellValue(41);
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Formula Is =ISERROR(C2) (White Font)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
+ FontFormatting font = rule1.createFontFormatting();
+ font.setFontColorIndex(IndexedColors.WHITE.index);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A1:A6")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.getRow(2).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is =ISERROR(C2) (White Font)");
+ sheet.getRow(4).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is =ISERROR(C2) (White Font)");
+ }
+
+ /**
+ * Use Excel conditional formatting to hide the duplicate values,
+ * and make the list easier to read. In this example, when the table is sorted by Region,
+ * the second (and subsequent) occurences of each region name will have white font colour.
+ */
+ static void hideDupplicates(Sheet sheet) {
+ sheet.createRow(0).createCell(0).setCellValue("City");
+ sheet.createRow(1).createCell(0).setCellValue("Boston");
+ sheet.createRow(2).createCell(0).setCellValue("Boston");
+ sheet.createRow(3).createCell(0).setCellValue("Chicago");
+ sheet.createRow(4).createCell(0).setCellValue("Chicago");
+ sheet.createRow(5).createCell(0).setCellValue("New York");
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Formula Is =A2=A1 (White Font)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
+ FontFormatting font = rule1.createFontFormatting();
+ font.setFontColorIndex(IndexedColors.WHITE.index);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A2:A6")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.getRow(1).createCell(1).setCellValue("<== the second (and subsequent) " +
+ "occurences of each region name will have white font colour. " +
+ "Condition: Formula Is =A2=A1 (White Font)");
+ }
+
+ /**
+ * Use Excel conditional formatting to highlight duplicate entries in a column.
+ */
+ static void formatDuplicates(Sheet sheet) {
+ sheet.createRow(0).createCell(0).setCellValue("Code");
+ sheet.createRow(1).createCell(0).setCellValue(4);
+ sheet.createRow(2).createCell(0).setCellValue(3);
+ sheet.createRow(3).createCell(0).setCellValue(6);
+ sheet.createRow(4).createCell(0).setCellValue(3);
+ sheet.createRow(5).createCell(0).setCellValue(5);
+ sheet.createRow(6).createCell(0).setCellValue(8);
+ sheet.createRow(7).createCell(0).setCellValue(0);
+ sheet.createRow(8).createCell(0).setCellValue(2);
+ sheet.createRow(9).createCell(0).setCellValue(8);
+ sheet.createRow(10).createCell(0).setCellValue(6);
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Formula Is =A2=A1 (White Font)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
+ FontFormatting font = rule1.createFontFormatting();
+ font.setFontStyle(false, true);
+ font.setFontColorIndex(IndexedColors.BLUE.index);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A2:A11")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted. " +
+ "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)");
+ }
+
+ /**
+ * Use Excel conditional formatting to highlight items that are in a list on the worksheet.
+ */
+ static void inList(Sheet sheet) {
+ sheet.createRow(0).createCell(0).setCellValue("Codes");
+ sheet.createRow(1).createCell(0).setCellValue("AA");
+ sheet.createRow(2).createCell(0).setCellValue("BB");
+ sheet.createRow(3).createCell(0).setCellValue("GG");
+ sheet.createRow(4).createCell(0).setCellValue("AA");
+ sheet.createRow(5).createCell(0).setCellValue("FF");
+ sheet.createRow(6).createCell(0).setCellValue("XX");
+ sheet.createRow(7).createCell(0).setCellValue("CC");
+
+ sheet.getRow(0).createCell(2).setCellValue("Valid");
+ sheet.getRow(1).createCell(2).setCellValue("AA");
+ sheet.getRow(2).createCell(2).setCellValue("BB");
+ sheet.getRow(3).createCell(2).setCellValue("CC");
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Formula Is =A2=A1 (White Font)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
+ PatternFormatting fill1 = rule1.createPatternFormatting();
+ fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
+ fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A2:A8")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.getRow(2).createCell(3).setCellValue("<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
+ }
+
+ /**
+ * Use Excel conditional formatting to highlight payments that are due in the next thirty days.
+ * In this example, Due dates are entered in cells A2:A4.
+ */
+ static void expiry(Sheet sheet) {
+ CellStyle style = sheet.getWorkbook().createCellStyle();
+ style.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm"));
+
+ sheet.createRow(0).createCell(0).setCellValue("Date");
+ sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
+ sheet.createRow(2).createCell(0).setCellFormula("A2+1");
+ sheet.createRow(3).createCell(0).setCellFormula("A3+1");
+
+ for(int rownum = 1; rownum <= 3; rownum++) sheet.getRow(rownum).getCell(0).setCellStyle(style);
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Formula Is =A2=A1 (White Font)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
+ FontFormatting font = rule1.createFontFormatting();
+ font.setFontStyle(false, true);
+ font.setFontColorIndex(IndexedColors.BLUE.index);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A2:A4")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
+ }
+
+ /**
+ * Use Excel conditional formatting to shade alternating rows on the worksheet
+ */
+ static void shadeAlt(Sheet sheet) {
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ // Condition 1: Formula Is =A2=A1 (White Font)
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
+ PatternFormatting fill1 = rule1.createPatternFormatting();
+ fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
+ fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A1:Z100")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
+ sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)");
+ }
+
+ /**
+ * You can use Excel conditional formatting to shade bands of rows on the worksheet.
+ * In this example, 3 rows are shaded light grey, and 3 are left with no shading.
+ * In the MOD function, the total number of rows in the set of banded rows (6) is entered.
+ */
+ static void shadeBands(Sheet sheet) {
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),6)<3");
+ PatternFormatting fill1 = rule1.createPatternFormatting();
+ fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
+ fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
+
+ CellRangeAddress[] regions = {
+ CellRangeAddress.valueOf("A1:Z100")
+ };
+
+ sheetCF.addConditionalFormatting(regions, rule1);
+
+ sheet.createRow(0).createCell(1).setCellValue("Shade Bands of Rows");
+ sheet.createRow(1).createCell(1).setCellValue("Condition: Formula Is =MOD(ROW(),6)<2 (Light Grey Fill)");
+ }
+}
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java index 178e1675ef..0bd2b40e0a 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java @@ -27,38 +27,8 @@ import org.apache.poi.hssf.record.cf.BorderFormatting; * @author Dmitriy Kumshayev * */ -public final class HSSFBorderFormatting +public final class HSSFBorderFormatting implements org.apache.poi.ss.usermodel.BorderFormatting { - /** No border */ - public final static short BORDER_NONE = BorderFormatting.BORDER_NONE; - /** Thin border */ - public final static short BORDER_THIN = BorderFormatting.BORDER_THIN; - /** Medium border */ - public final static short BORDER_MEDIUM = BorderFormatting.BORDER_MEDIUM; - /** dash border */ - public final static short BORDER_DASHED = BorderFormatting.BORDER_DASHED; - /** dot border */ - public final static short BORDER_HAIR = BorderFormatting.BORDER_HAIR; - /** Thick border */ - public final static short BORDER_THICK = BorderFormatting.BORDER_THICK; - /** double-line border */ - public final static short BORDER_DOUBLE = BorderFormatting.BORDER_DOUBLE; - /** hair-line border */ - public final static short BORDER_DOTTED = BorderFormatting.BORDER_DOTTED; - /** Medium dashed border */ - public final static short BORDER_MEDIUM_DASHED = BorderFormatting.BORDER_MEDIUM_DASHED; - /** dash-dot border */ - public final static short BORDER_DASH_DOT = BorderFormatting.BORDER_DASH_DOT; - /** medium dash-dot border */ - public final static short BORDER_MEDIUM_DASH_DOT = BorderFormatting.BORDER_MEDIUM_DASH_DOT; - /** dash-dot-dot border */ - public final static short BORDER_DASH_DOT_DOT = BorderFormatting.BORDER_DASH_DOT_DOT; - /** medium dash-dot-dot border */ - public final static short BORDER_MEDIUM_DASH_DOT_DOT = BorderFormatting.BORDER_MEDIUM_DASH_DOT_DOT; - /** slanted dash-dot border */ - public final static short BORDER_SLANTED_DASH_DOT = BorderFormatting.BORDER_SLANTED_DASH_DOT; - - private final CFRuleRecord cfRuleRecord; private final BorderFormatting borderFormatting; diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java index 1d843c6848..80fb4ef99f 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java @@ -18,6 +18,8 @@ package org.apache.poi.hssf.usermodel; import org.apache.poi.hssf.record.CFRuleRecord; import org.apache.poi.hssf.record.aggregates.CFRecordsAggregate; +import org.apache.poi.ss.usermodel.ConditionalFormatting; +import org.apache.poi.ss.usermodel.ConditionalFormattingRule; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.Region; @@ -74,7 +76,7 @@ import org.apache.poi.ss.util.Region; * * @author Dmitriy Kumshayev */ -public final class HSSFConditionalFormatting +public final class HSSFConditionalFormatting implements ConditionalFormatting { private final HSSFWorkbook _workbook; private final CFRecordsAggregate cfAggregate; @@ -122,6 +124,10 @@ public final class HSSFConditionalFormatting cfAggregate.setRule(idx, cfRule.getCfRuleRecord()); } + public void setRule(int idx, ConditionalFormattingRule cfRule){ + setRule(idx, (HSSFConditionalFormattingRule)cfRule); + } + /** * add a Conditional Formatting rule. * Excel allows to create up to 3 Conditional Formatting rules. @@ -132,6 +138,10 @@ public final class HSSFConditionalFormatting cfAggregate.addRule(cfRule.getCfRuleRecord()); } + public void addRule(ConditionalFormattingRule cfRule){ + addRule((HSSFConditionalFormattingRule)cfRule); + } + /** * @return the Conditional Formatting rule at position idx. */ diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java index 82f7884d6f..53f5423eb7 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java @@ -24,6 +24,7 @@ import org.apache.poi.hssf.record.cf.BorderFormatting; import org.apache.poi.hssf.record.cf.FontFormatting; import org.apache.poi.hssf.record.cf.PatternFormatting; import org.apache.poi.ss.formula.ptg.Ptg; +import org.apache.poi.ss.usermodel.ConditionalFormattingRule; /** * @@ -33,7 +34,7 @@ import org.apache.poi.ss.formula.ptg.Ptg; * * @author Dmitriy Kumshayev */ -public final class HSSFConditionalFormattingRule +public final class HSSFConditionalFormattingRule implements ConditionalFormattingRule { private static final byte CELL_COMPARISON = CFRuleRecord.CONDITION_TYPE_CELL_VALUE_IS; diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java index f55fe64b57..da1ddae6fa 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java @@ -26,24 +26,18 @@ import org.apache.poi.hssf.record.cf.FontFormatting; * @author Dmitriy Kumshayev * */ -public final class HSSFFontFormatting +public final class HSSFFontFormatting implements org.apache.poi.ss.usermodel.FontFormatting { - /** Escapement type - None */ - public final static short SS_NONE = FontFormatting.SS_NONE; - /** Escapement type - Superscript */ - public final static short SS_SUPER = FontFormatting.SS_SUPER; - /** Escapement type - Subscript */ - public final static short SS_SUB = FontFormatting.SS_SUB; - - /** Underline type - None */ + + /** Underline type - None */ public final static byte U_NONE = FontFormatting.U_NONE; - /** Underline type - Single */ + /** Underline type - Single */ public final static byte U_SINGLE = FontFormatting.U_SINGLE; - /** Underline type - Double */ + /** Underline type - Double */ public final static byte U_DOUBLE = FontFormatting.U_DOUBLE; - /** Underline type - Single Accounting */ + /** Underline type - Single Accounting */ public final static byte U_SINGLE_ACCOUNTING = FontFormatting.U_SINGLE_ACCOUNTING; - /** Underline type - Double Accounting */ + /** Underline type - Double Accounting */ public final static byte U_DOUBLE_ACCOUNTING = FontFormatting.U_DOUBLE_ACCOUNTING; private final FontFormatting fontFormatting; diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java index f0d31aad2b..d97d03cfb8 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java @@ -26,47 +26,8 @@ import org.apache.poi.hssf.record.cf.PatternFormatting; * @author Dmitriy Kumshayev * */ -public class HSSFPatternFormatting +public class HSSFPatternFormatting implements org.apache.poi.ss.usermodel.PatternFormatting { - /** No background */ - public final static short NO_FILL = PatternFormatting.NO_FILL; - /** Solidly filled */ - public final static short SOLID_FOREGROUND = PatternFormatting.SOLID_FOREGROUND; - /** Small fine dots */ - public final static short FINE_DOTS = PatternFormatting.FINE_DOTS; - /** Wide dots */ - public final static short ALT_BARS = PatternFormatting.ALT_BARS; - /** Sparse dots */ - public final static short SPARSE_DOTS = PatternFormatting.SPARSE_DOTS; - /** Thick horizontal bands */ - public final static short THICK_HORZ_BANDS = PatternFormatting.THICK_HORZ_BANDS; - /** Thick vertical bands */ - public final static short THICK_VERT_BANDS = PatternFormatting.THICK_VERT_BANDS; - /** Thick backward facing diagonals */ - public final static short THICK_BACKWARD_DIAG = PatternFormatting.THICK_BACKWARD_DIAG; - /** Thick forward facing diagonals */ - public final static short THICK_FORWARD_DIAG = PatternFormatting.THICK_FORWARD_DIAG; - /** Large spots */ - public final static short BIG_SPOTS = PatternFormatting.BIG_SPOTS; - /** Brick-like layout */ - public final static short BRICKS = PatternFormatting.BRICKS; - /** Thin horizontal bands */ - public final static short THIN_HORZ_BANDS = PatternFormatting.THIN_HORZ_BANDS; - /** Thin vertical bands */ - public final static short THIN_VERT_BANDS = PatternFormatting.THIN_VERT_BANDS; - /** Thin backward diagonal */ - public final static short THIN_BACKWARD_DIAG = PatternFormatting.THIN_BACKWARD_DIAG; - /** Thin forward diagonal */ - public final static short THIN_FORWARD_DIAG = PatternFormatting.THIN_FORWARD_DIAG; - /** Squares */ - public final static short SQUARES = PatternFormatting.SQUARES; - /** Diamonds */ - public final static short DIAMONDS = PatternFormatting.DIAMONDS; - /** Less Dots */ - public final static short LESS_DOTS = PatternFormatting.LESS_DOTS; - /** Least Dots */ - public final static short LEAST_DOTS = PatternFormatting.LEAST_DOTS; - private final CFRuleRecord cfRuleRecord; private final PatternFormatting patternFormatting; diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java index ab539c22de..b75f0a620f 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java @@ -20,6 +20,9 @@ package org.apache.poi.hssf.usermodel; import org.apache.poi.hssf.record.CFRuleRecord; import org.apache.poi.hssf.record.aggregates.CFRecordsAggregate; import org.apache.poi.hssf.record.aggregates.ConditionalFormattingTable; +import org.apache.poi.ss.usermodel.ConditionalFormatting; +import org.apache.poi.ss.usermodel.ConditionalFormattingRule; +import org.apache.poi.ss.usermodel.SheetConditionalFormatting; import org.apache.poi.ss.util.Region; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.SpreadsheetVersion; @@ -29,7 +32,7 @@ import org.apache.poi.ss.SpreadsheetVersion; * * @author Dmitriy Kumshayev */ -public final class HSSFSheetConditionalFormatting { +public final class HSSFSheetConditionalFormatting implements SheetConditionalFormatting { private final HSSFSheet _sheet; private final ConditionalFormattingTable _conditionalFormattingTable; @@ -72,6 +75,15 @@ public final class HSSFSheetConditionalFormatting { return new HSSFConditionalFormattingRule(wb, rr); } + public HSSFConditionalFormattingRule createConditionalFormattingRule( + byte comparisonOperation, + String formula1) { + + HSSFWorkbook wb = _sheet.getWorkbook(); + CFRuleRecord rr = CFRuleRecord.create(_sheet, comparisonOperation, formula1, null); + return new HSSFConditionalFormattingRule(wb, rr); + } + /** * A factory method allowing to create a conditional formatting rule with a formula.<br> * @@ -102,6 +114,11 @@ public final class HSSFSheetConditionalFormatting { return _conditionalFormattingTable.add(cfraClone); } + + public int addConditionalFormatting( ConditionalFormatting cf ) { + return addConditionalFormatting((HSSFConditionalFormatting)cf); + } + /** * @deprecated use <tt>CellRangeAddress</tt> instead of <tt>Region</tt> */ @@ -140,16 +157,32 @@ public final class HSSFSheetConditionalFormatting { return _conditionalFormattingTable.add(cfra); } + public int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) { + HSSFConditionalFormattingRule[] hfRules; + if(cfRules instanceof HSSFConditionalFormattingRule[]) hfRules = (HSSFConditionalFormattingRule[])cfRules; + else { + hfRules = new HSSFConditionalFormattingRule[cfRules.length]; + System.arraycopy(cfRules, 0, hfRules, 0, hfRules.length); + } + return addConditionalFormatting(regions, hfRules); + } + public int addConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule rule1) { return addConditionalFormatting(regions, - new HSSFConditionalFormattingRule[] + rule1 == null ? null : new HSSFConditionalFormattingRule[] { rule1 }); } + public int addConditionalFormatting(CellRangeAddress[] regions, + ConditionalFormattingRule rule1) + { + return addConditionalFormatting(regions, (HSSFConditionalFormattingRule)rule1); + } + public int addConditionalFormatting(CellRangeAddress[] regions, HSSFConditionalFormattingRule rule1, HSSFConditionalFormattingRule rule2) @@ -161,6 +194,16 @@ public final class HSSFSheetConditionalFormatting { }); } + public int addConditionalFormatting(CellRangeAddress[] regions, + ConditionalFormattingRule rule1, + ConditionalFormattingRule rule2) + { + return addConditionalFormatting(regions, + (HSSFConditionalFormattingRule)rule1, + (HSSFConditionalFormattingRule)rule2 + ); + } + /** * gets Conditional Formatting object at a particular index * diff --git a/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java b/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java new file mode 100644 index 0000000000..c15dc269cb --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java @@ -0,0 +1,115 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+/**
+ * @author Dmitriy Kumshayev
+ * @author Yegor Kozlov
+ */
+public interface BorderFormatting {
+ /** No border */
+ final static short BORDER_NONE = 0x0;
+ /** Thin border */
+ final static short BORDER_THIN = 0x1;
+ /** Medium border */
+ final static short BORDER_MEDIUM = 0x2;
+ /** dash border */
+ final static short BORDER_DASHED = 0x3;
+ /** dot border */
+ final static short BORDER_HAIR = 0x4;
+ /** Thick border */
+ final static short BORDER_THICK = 0x5;
+ /** double-line border */
+ final static short BORDER_DOUBLE = 0x6;
+ /** hair-line border */
+ final static short BORDER_DOTTED = 0x7;
+ /** Medium dashed border */
+ final static short BORDER_MEDIUM_DASHED = 0x8;
+ /** dash-dot border */
+ final static short BORDER_DASH_DOT = 0x9;
+ /** medium dash-dot border */
+ final static short BORDER_MEDIUM_DASH_DOT = 0xA;
+ /** dash-dot-dot border */
+ final static short BORDER_DASH_DOT_DOT = 0xB;
+ /** medium dash-dot-dot border */
+ final static short BORDER_MEDIUM_DASH_DOT_DOT = 0xC;
+ /** slanted dash-dot border */
+ final static short BORDER_SLANTED_DASH_DOT = 0xD;
+
+ short getBorderBottom();
+
+ short getBorderDiagonal();
+
+ short getBorderLeft();
+
+ short getBorderRight();
+
+ short getBorderTop();
+
+ short getBottomBorderColor();
+
+ short getDiagonalBorderColor();
+
+ short getLeftBorderColor();
+
+ short getRightBorderColor();
+
+ short getTopBorderColor();
+
+ void setBorderBottom(short border);
+
+ /**
+ * Set diagonal border.
+ *
+ * @param border MUST be a BORDER_* constant
+ */
+ void setBorderDiagonal(short border);
+
+ /**
+ * Set left border.
+ *
+ * @param border MUST be a BORDER_* constant
+ */
+ void setBorderLeft(short border);
+
+ /**
+ * Set right border.
+ *
+ * @param border MUST be a BORDER_* constant
+ */
+ void setBorderRight(short border);
+
+ /**
+ * Set top border.
+ *
+ * @param border MUST be a BORDER_* constant
+ */
+ void setBorderTop(short border);
+
+ void setBottomBorderColor(short color);
+
+ void setDiagonalBorderColor(short color);
+
+ void setLeftBorderColor(short color);
+
+ void setRightBorderColor(short color);
+
+ void setTopBorderColor(short color);
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java b/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java new file mode 100644 index 0000000000..7e29cbf4c9 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java @@ -0,0 +1,73 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+/**
+ * The conditional format operators used for "Highlight Cells That Contain..." rules.
+ * <p>
+ * For example, "highlight cells that begin with "M2" and contain "Mountain Gear".
+ * </p>
+ *
+ * @author Dmitriy Kumshayev
+ * @author Yegor Kozlov
+ */
+public final class ComparisonOperator {
+ public static final byte NO_COMPARISON = 0;
+
+ /**
+ * 'Between' operator
+ */
+ public static final byte BETWEEN = 1;
+
+ /**
+ * 'Not between' operator
+ */
+ public static final byte NOT_BETWEEN = 2;
+
+ /**
+ * 'Equal to' operator
+ */
+ public static final byte EQUAL = 3;
+
+ /**
+ * 'Not equal to' operator
+ */
+ public static final byte NOT_EQUAL = 4;
+
+ /**
+ * 'Greater than' operator
+ */
+ public static final byte GT = 5;
+
+ /**
+ * 'Less than' operator
+ */
+ public static final byte LT = 6;
+
+ /**
+ * 'Greater than or equal to' operator
+ */
+ public static final byte GE = 7;
+
+ /**
+ * 'Less than or equal to' operator
+ */
+ public static final byte LE = 8;
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java b/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java new file mode 100644 index 0000000000..8b117ce31b --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java @@ -0,0 +1,115 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+/**
+ * The ConditionalFormatting class encapsulates all settings of Conditional Formatting.
+ *
+ * The class can be used
+ *
+ * <UL>
+ * <LI>
+ * to make a copy ConditionalFormatting settings.
+ * </LI>
+ *
+ *
+ * For example:
+ * <PRE>
+ * ConditionalFormatting cf = sheet.getConditionalFormattingAt(index);
+ * newSheet.addConditionalFormatting(cf);
+ * </PRE>
+ *
+ * <LI>
+ * or to modify existing Conditional Formatting settings (formatting regions and/or rules).
+ * </LI>
+ * </UL>
+ *
+ * Use {@link org.apache.poi.hssf.usermodel.Sheet#getSheetConditionalFormatting()} to get access to an instance of this class.
+ * <P>
+ * To create a new Conditional Formatting set use the following approach:
+ *
+ * <PRE>
+ *
+ * // Define a Conditional Formatting rule, which triggers formatting
+ * // when cell's value is greater or equal than 100.0 and
+ * // applies patternFormatting defined below.
+ * ConditionalFormattingRule rule = sheet.createConditionalFormattingRule(
+ * ComparisonOperator.GE,
+ * "100.0", // 1st formula
+ * null // 2nd formula is not used for comparison operator GE
+ * );
+ *
+ * // Create pattern with red background
+ * PatternFormatting patternFmt = rule.cretePatternFormatting();
+ * patternFormatting.setFillBackgroundColor(IndexedColor.RED.getIndex());
+ *
+ * // Define a region containing first column
+ * Region [] regions =
+ * {
+ * new Region(1,(short)1,-1,(short)1)
+ * };
+ *
+ * // Apply Conditional Formatting rule defined above to the regions
+ * sheet.addConditionalFormatting(regions, rule);
+ * </PRE>
+ *
+ * @author Dmitriy Kumshayev
+ * @author Yegor Kozlov
+ */
+public interface ConditionalFormatting {
+
+ /**
+ * @return array of <tt>CellRangeAddress</tt>s. Never <code>null</code>
+ */
+ CellRangeAddress[] getFormattingRanges();
+
+ /**
+ * Replaces an existing Conditional Formatting rule at position idx.
+ * Excel allows to create up to 3 Conditional Formatting rules.
+ * This method can be useful to modify existing Conditional Formatting rules.
+ *
+ * @param idx position of the rule. Should be between 0 and 2.
+ * @param cfRule - Conditional Formatting rule
+ */
+ void setRule(int idx, ConditionalFormattingRule cfRule);
+
+ /**
+ * Add a Conditional Formatting rule.
+ * Excel allows to create up to 3 Conditional Formatting rules.
+ *
+ * @param cfRule - Conditional Formatting rule
+ */
+ void addRule(ConditionalFormattingRule cfRule);
+
+ /**
+ * @return the Conditional Formatting rule at position idx.
+ */
+ ConditionalFormattingRule getRule(int idx);
+
+ /**
+ * @return number of Conditional Formatting rules.
+ */
+ int getNumberOfRules();
+
+
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java b/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java new file mode 100644 index 0000000000..1c63f19257 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java @@ -0,0 +1,125 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+/**
+ * Represents a description of a conditional formatting rule
+ *
+ * @author Dmitriy Kumshayev
+ * @author Yegor Kozlov
+ */
+public interface ConditionalFormattingRule {
+ /**
+ * This conditional formatting rule compares a cell value
+ * to a formula calculated result, using an operator
+ */
+ public static final byte CONDITION_TYPE_CELL_VALUE_IS = 1;
+
+ /**
+ * This conditional formatting rule contains a formula to evaluate.
+ * When the formula result is true, the cell is highlighted.
+ */
+ public static final byte CONDITION_TYPE_FORMULA = 2;
+
+ /**
+ * Create a new border formatting structure if it does not exist,
+ * otherwise just return existing object.
+ *
+ * @return - border formatting object, never returns <code>null</code>.
+ */
+ BorderFormatting createBorderFormatting();
+
+ /**
+ * @return - border formatting object if defined, <code>null</code> otherwise
+ */
+ BorderFormatting getBorderFormatting();
+
+ /**
+ * Create a new font formatting structure if it does not exist,
+ * otherwise just return existing object.
+ *
+ * @return - font formatting object, never returns <code>null</code>.
+ */
+ FontFormatting createFontFormatting();
+
+ /**
+ * @return - font formatting object if defined, <code>null</code> otherwise
+ */
+ FontFormatting getFontFormatting();
+
+ /**
+ * Create a new pattern formatting structure if it does not exist,
+ * otherwise just return existing object.
+ *
+ * @return - pattern formatting object, never returns <code>null</code>.
+ */
+ PatternFormatting createPatternFormatting();
+
+ /**
+ * @return - pattern formatting object if defined, <code>null</code> otherwise
+ */
+ PatternFormatting getPatternFormatting();
+
+ /**
+ * Type of conditional formatting rule.
+ * <p>
+ * MUST be either {@link #CONDITION_TYPE_CELL_VALUE_IS} or {@link #CONDITION_TYPE_FORMULA}
+ * </p>
+ *
+ * @return the type of condition
+ */
+ byte getConditionType();
+
+ /**
+ * The comparison function used when the type of conditional formatting is set to
+ * {@link #CONDITION_TYPE_CELL_VALUE_IS}
+ * <p>
+ * MUST be a constant from {@link ComparisonOperator}
+ * </p>
+ *
+ * @return the conditional format operator
+ */
+ byte getComparisonOperation();
+
+ /**
+ * The formula used to evaluate the first operand for the conditional formatting rule.
+ * <p>
+ * If the condition type is {@link #CONDITION_TYPE_CELL_VALUE_IS},
+ * this field is the first operand of the comparison.
+ * If type is {@link #CONDITION_TYPE_FORMULA}, this formula is used
+ * to determine if the conditional formatting is applied.
+ * </p>
+ * <p>
+ * If comparison type is {@link #CONDITION_TYPE_FORMULA} the formula MUST be a Boolean function
+ * </p>
+ *
+ * @return the first formula
+ */
+ String getFormula1();
+
+ /**
+ * The formula used to evaluate the second operand of the comparison when
+ * comparison type is {@link #CONDITION_TYPE_CELL_VALUE_IS} and operator
+ * is either {@link ComparisonOperator#BETWEEN} or {@link ComparisonOperator#NOT_BETWEEN}
+ *
+ * @return the second formula
+ */
+ String getFormula2();
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/FontFormatting.java b/src/java/org/apache/poi/ss/usermodel/FontFormatting.java new file mode 100644 index 0000000000..2298d79635 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/FontFormatting.java @@ -0,0 +1,143 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+/**
+ * High level representation for Font Formatting component
+ * of Conditional Formatting settings
+ *
+ * @author Dmitriy Kumshayev
+ * @author Yegor Kozlov
+ */
+public interface FontFormatting {
+ /** Escapement type - None */
+ public final static short SS_NONE = 0;
+ /** Escapement type - Superscript */
+ public final static short SS_SUPER = 1;
+ /** Escapement type - Subscript */
+ public final static short SS_SUB = 2;
+
+ /** Underline type - None */
+ public final static byte U_NONE = 0;
+ /** Underline type - Single */
+ public final static byte U_SINGLE = 1;
+ /** Underline type - Double */
+ public final static byte U_DOUBLE = 2;
+ /** Underline type - Single Accounting */
+ public final static byte U_SINGLE_ACCOUNTING = 0x21;
+ /** Underline type - Double Accounting */
+ public final static byte U_DOUBLE_ACCOUNTING = 0x22;
+
+ /**
+ * get the type of super or subscript for the font
+ *
+ * @return super or subscript option
+ * @see #SS_NONE
+ * @see #SS_SUPER
+ * @see #SS_SUB
+ */
+ short getEscapementType();
+
+ /**
+ * set the escapement type for the font
+ *
+ * @param escapementType super or subscript option
+ * @see #SS_NONE
+ * @see #SS_SUPER
+ * @see #SS_SUB
+ */
+ void setEscapementType(short escapementType);
+
+ /**
+ * @return font color index
+ */
+ short getFontColorIndex();
+
+
+ /**
+ * @param color font color index
+ */
+ void setFontColorIndex(short color);
+
+ /**
+ * gets the height of the font in 1/20th point units
+ *
+ * @return fontheight (in points/20); or -1 if not modified
+ */
+ int getFontHeight();
+
+ /**
+ * Sets the height of the font in 1/20th point units
+ *
+ * @param height the height in twips (in points/20)
+ */
+ void setFontHeight(int height);
+
+ /**
+ * get the type of underlining for the font
+ *
+ * @return font underlining type
+ *
+ * @see #U_NONE
+ * @see #U_SINGLE
+ * @see #U_DOUBLE
+ * @see #U_SINGLE_ACCOUNTING
+ * @see #U_DOUBLE_ACCOUNTING
+ */
+ short getUnderlineType();
+
+ /**
+ * set the type of underlining type for the font
+ *
+ * @param underlineType super or subscript option
+ *
+ * @see #U_NONE
+ * @see #U_SINGLE
+ * @see #U_DOUBLE
+ * @see #U_SINGLE_ACCOUNTING
+ * @see #U_DOUBLE_ACCOUNTING
+ */
+ void setUnderlineType(short underlineType);
+
+ /**
+ * get whether the font weight is set to bold or not
+ *
+ * @return bold - whether the font is bold or not
+ */
+ boolean isBold();
+
+ /**
+ * @return true if font style was set to <i>italic</i>
+ */
+ boolean isItalic();
+
+ /**
+ * set font style options.
+ *
+ * @param italic - if true, set posture style to italic, otherwise to normal
+ * @param bold if true, set font weight to bold, otherwise to normal
+ */
+ void setFontStyle(boolean italic, boolean bold);
+
+ /**
+ * set font style options to default values (non-italic, non-bold)
+ */
+ void resetFontStyle();
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/IndexedColors.java b/src/java/org/apache/poi/ss/usermodel/IndexedColors.java index 07237471aa..61d7f6818c 100644 --- a/src/java/org/apache/poi/ss/usermodel/IndexedColors.java +++ b/src/java/org/apache/poi/ss/usermodel/IndexedColors.java @@ -80,10 +80,10 @@ public enum IndexedColors { GREY_80_PERCENT(63), AUTOMATIC(64); - private int index; + public final short index; IndexedColors(int idx){ - index = idx; + index = (short)idx; } /** @@ -92,6 +92,6 @@ public enum IndexedColors { * @return index of this color */ public short getIndex(){ - return (short)index; + return index; } } diff --git a/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java b/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java new file mode 100644 index 0000000000..2739c96af3 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java @@ -0,0 +1,76 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+/**
+ * @author Yegor Kozlov
+ */
+public interface PatternFormatting {
+ /** No background */
+ public final static short NO_FILL = 0 ;
+ /** Solidly filled */
+ public final static short SOLID_FOREGROUND = 1 ;
+ /** Small fine dots */
+ public final static short FINE_DOTS = 2 ;
+ /** Wide dots */
+ public final static short ALT_BARS = 3 ;
+ /** Sparse dots */
+ public final static short SPARSE_DOTS = 4 ;
+ /** Thick horizontal bands */
+ public final static short THICK_HORZ_BANDS = 5 ;
+ /** Thick vertical bands */
+ public final static short THICK_VERT_BANDS = 6 ;
+ /** Thick backward facing diagonals */
+ public final static short THICK_BACKWARD_DIAG = 7 ;
+ /** Thick forward facing diagonals */
+ public final static short THICK_FORWARD_DIAG = 8 ;
+ /** Large spots */
+ public final static short BIG_SPOTS = 9 ;
+ /** Brick-like layout */
+ public final static short BRICKS = 10 ;
+ /** Thin horizontal bands */
+ public final static short THIN_HORZ_BANDS = 11 ;
+ /** Thin vertical bands */
+ public final static short THIN_VERT_BANDS = 12 ;
+ /** Thin backward diagonal */
+ public final static short THIN_BACKWARD_DIAG = 13 ;
+ /** Thin forward diagonal */
+ public final static short THIN_FORWARD_DIAG = 14 ;
+ /** Squares */
+ public final static short SQUARES = 15 ;
+ /** Diamonds */
+ public final static short DIAMONDS = 16 ;
+ /** Less Dots */
+ public final static short LESS_DOTS = 17 ;
+ /** Least Dots */
+ public final static short LEAST_DOTS = 18 ;
+
+ short getFillBackgroundColor();
+
+ short getFillForegroundColor();
+
+ short getFillPattern();
+
+ void setFillBackgroundColor(short bg);
+
+ void setFillForegroundColor(short fg);
+
+ void setFillPattern(short fp);
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java index b68f0fdd72..5be4f1f5e6 100644 --- a/src/java/org/apache/poi/ss/usermodel/Sheet.java +++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java @@ -919,5 +919,12 @@ public interface Sheet extends Iterable<Row> { * @param range the range of cells to filter */ AutoFilter setAutoFilter(CellRangeAddress range); - + + /** + * The 'Conditional Formatting' facet for this <tt>Sheet</tt> + * + * @return conditional formatting rule for this sheet + */ + SheetConditionalFormatting getSheetConditionalFormatting(); + } diff --git a/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java b/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java new file mode 100644 index 0000000000..37387c8da1 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java @@ -0,0 +1,163 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+import org.apache.poi.ss.util.CellRangeAddress;
+
+/**
+ * The 'Conditional Formatting' facet of <tt>Sheet</tt>
+ *
+ * @author Dmitriy Kumshayev
+ * @author Yegor Kozlov
+ * @since 3.8
+ */
+public interface SheetConditionalFormatting {
+
+ /**
+ * Add a new Conditional Formatting to the sheet.
+ *
+ * @param regions - list of rectangular regions to apply conditional formatting rules
+ * @param rule - the rule to apply
+ *
+ * @return index of the newly created Conditional Formatting object
+ */
+ int addConditionalFormatting(CellRangeAddress[] regions,
+ ConditionalFormattingRule rule);
+
+ /**
+ * Add a new Conditional Formatting consisting of two rules.
+ *
+ * @param regions - list of rectangular regions to apply conditional formatting rules
+ * @param rule1 - the first rule
+ * @param rule1 - the second rule
+ *
+ * @return index of the newly created Conditional Formatting object
+ */
+ int addConditionalFormatting(CellRangeAddress[] regions,
+ ConditionalFormattingRule rule1,
+ ConditionalFormattingRule rule2);
+
+ /**
+ * 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
+ */
+ int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules);
+
+ /**
+ * Adds a copy of a ConditionalFormatting object to the sheet
+ * <p>
+ * This method could be used to copy ConditionalFormatting object
+ * from one sheet to another. For example:
+ * </p>
+ * <pre>
+ * ConditionalFormatting cf = sheet.getConditionalFormattingAt(index);
+ * newSheet.addConditionalFormatting(cf);
+ * </pre>
+ *
+ * @param cf the Conditional Formatting to clone
+ * @return index of the new Conditional Formatting object
+ */
+ int addConditionalFormatting(ConditionalFormatting cf);
+
+ /**
+ * A factory method allowing to create a conditional formatting rule
+ * with a cell comparison operator
+ * <p>
+ * The created conditional formatting rule compares a cell value
+ * to a formula calculated result, using the specified operator.
+ * The type of the created condition is {@link ConditionalFormattingRule#CONDITION_TYPE_CELL_VALUE_IS}
+ * </p>
+ *
+ * @param comparisonOperation - MUST be a constant value from
+ * <tt>{@link ComparisonOperator}</tt>: <p>
+ * <ul>
+ * <li>BETWEEN</li>
+ * <li>NOT_BETWEEN</li>
+ * <li>EQUAL</li>
+ * <li>NOT_EQUAL</li>
+ * <li>GT</li>
+ * <li>LT</li>
+ * <li>GE</li>
+ * <li>LE</li>
+ * </ul>
+ * </p>
+ * @param formula1 - formula for the valued, compared with the cell
+ * @param formula2 - second formula (only used with
+ * {@link ComparisonOperator#BETWEEN}) and {@link ComparisonOperator#NOT_BETWEEN} operations)
+ */
+ ConditionalFormattingRule createConditionalFormattingRule(
+ byte comparisonOperation,
+ String formula1,
+ String formula2);
+
+ /**
+ * Create a conditional formatting rule that compares a cell value
+ * to a formula calculated result, using an operator *
+ * <p>
+ * The type of the created condition is {@link ConditionalFormattingRule#CONDITION_TYPE_CELL_VALUE_IS}
+ * </p>
+ *
+ * @param comparisonOperation MUST be a constant value from
+ * <tt>{@link ComparisonOperator}</tt> except BETWEEN and NOT_BETWEEN
+ *
+ * @param formula the formula to determine if the conditional formatting is applied
+ */
+ ConditionalFormattingRule createConditionalFormattingRule(
+ byte comparisonOperation,
+ String formula);
+
+ /**
+ * Create a conditional formatting rule based on a Boolean formula.
+ * When the formula result is true, the cell is highlighted.
+ *
+ * <p>
+ * The type of the created format condition is {@link ConditionalFormattingRule#CONDITION_TYPE_FORMULA}
+ * </p>
+ * @param formula the formula to evaluate. MUST be a Boolean function.
+ */
+ ConditionalFormattingRule createConditionalFormattingRule(String formula);
+
+ /**
+ * Gets Conditional Formatting object at a particular index
+ *
+ * @param index 0-based index of the Conditional Formatting object to fetch
+ * @return Conditional Formatting object or <code>null</code> if not found
+ * @throws IllegalArgumentException if the index is outside of the allowable range (0 ... numberOfFormats-1)
+ */
+ ConditionalFormatting getConditionalFormattingAt(int index);
+
+ /**
+ *
+ * @return the number of conditional formats in this sheet
+ */
+ int getNumConditionalFormattings();
+
+ /**
+ * Removes a Conditional Formatting object by index
+ *
+ * @param index 0-based index of the Conditional Formatting object to remove
+ * @throws IllegalArgumentException if the index is outside of the allowable range (0 ... numberOfFormats-1)
+ */
+ void removeConditionalFormatting(int index);
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java index 3a2e87903c..50dca1811c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java @@ -346,6 +346,9 @@ public class StylesTable extends POIXMLDocumentPart { public CTStylesheet getCTStylesheet() { return doc.getStyleSheet(); } + public int _getDXfsSize() { + return dxfs.size(); + } /** @@ -497,14 +500,11 @@ public class StylesTable extends POIXMLDocumentPart { return xssfFont; } - protected CTDxf getDxf(int idx) { - if (dxfs.size()==0) { - return CTDxf.Factory.newInstance(); - } + public CTDxf getDxfAt(int idx) { return dxfs.get(idx); } - protected int putDxf(CTDxf dxf) { + public int putDxf(CTDxf dxf) { this.dxfs.add(dxf); return this.dxfs.size(); } diff --git a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java index e8e000b4ef..05dc3ec12e 100644 --- a/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java @@ -1196,6 +1196,11 @@ public class SXSSFSheet implements Sheet, Cloneable { return _sh.setAutoFilter(range); } + + public SheetConditionalFormatting getSheetConditionalFormatting(){ + return _sh.getSheetConditionalFormatting(); + } + //end of interface implementation /** * Specifies how many rows can be accessed at most via getRow(). diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java new file mode 100644 index 0000000000..6505841ad6 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java @@ -0,0 +1,150 @@ +package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.BorderFormatting;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STBorderStyle;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorderPr;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class XSSFBorderFormatting implements BorderFormatting {
+ CTBorder _border;
+
+ /*package*/ XSSFBorderFormatting(CTBorder border){
+ _border = border;
+ }
+
+ public short getBorderBottom(){
+ STBorderStyle.Enum ptrn = _border.isSetBottom() ? _border.getBottom().getStyle() : null;
+ return ptrn == null ? BORDER_NONE : (short)(ptrn.intValue() - 1);
+ }
+
+ public short getBorderDiagonal(){
+ STBorderStyle.Enum ptrn = _border.isSetDiagonal() ? _border.getDiagonal().getStyle() : null;
+ return ptrn == null ? BORDER_NONE : (short)(ptrn.intValue() - 1);
+ }
+
+ public short getBorderLeft(){
+ STBorderStyle.Enum ptrn = _border.isSetLeft() ? _border.getLeft().getStyle() : null;
+ return ptrn == null ? BORDER_NONE : (short)(ptrn.intValue() - 1);
+ }
+
+ public short getBorderRight(){
+ STBorderStyle.Enum ptrn = _border.isSetRight() ? _border.getRight().getStyle() : null;
+ return ptrn == null ? BORDER_NONE : (short)(ptrn.intValue() - 1);
+ }
+
+ public short getBorderTop(){
+ STBorderStyle.Enum ptrn = _border.isSetTop() ? _border.getTop().getStyle() : null;
+ return ptrn == null ? BORDER_NONE : (short)(ptrn.intValue() - 1);
+ }
+
+ public short getBottomBorderColor(){
+ if(!_border.isSetBottom()) return 0;
+
+ CTBorderPr pr = _border.getBottom();
+ return (short)pr.getColor().getIndexed();
+ }
+
+ public short getDiagonalBorderColor(){
+ if(!_border.isSetDiagonal()) return 0;
+
+ CTBorderPr pr = _border.getDiagonal();
+ return (short)pr.getColor().getIndexed();
+ }
+
+ public short getLeftBorderColor(){
+ if(!_border.isSetLeft()) return 0;
+
+ CTBorderPr pr = _border.getLeft();
+ return (short)pr.getColor().getIndexed();
+ }
+
+ public short getRightBorderColor(){
+ if(!_border.isSetRight()) return 0;
+
+ CTBorderPr pr = _border.getRight();
+ return (short)pr.getColor().getIndexed();
+ }
+
+ public short getTopBorderColor(){
+ if(!_border.isSetTop()) return 0;
+
+ CTBorderPr pr = _border.getTop();
+ return (short)pr.getColor().getIndexed();
+ }
+
+ public void setBorderBottom(short border){
+ CTBorderPr pr = _border.isSetBottom() ? _border.getBottom() : _border.addNewBottom();
+ if(border == BORDER_NONE) _border.unsetBottom();
+ else pr.setStyle(STBorderStyle.Enum.forInt(border + 1));
+ }
+
+ public void setBorderDiagonal(short border){
+ CTBorderPr pr = _border.isSetDiagonal() ? _border.getDiagonal() : _border.addNewDiagonal();
+ if(border == BORDER_NONE) _border.unsetDiagonal();
+ else pr.setStyle(STBorderStyle.Enum.forInt(border + 1));
+ }
+
+ public void setBorderLeft(short border){
+ CTBorderPr pr = _border.isSetLeft() ? _border.getLeft() : _border.addNewLeft();
+ if(border == BORDER_NONE) _border.unsetLeft();
+ else pr.setStyle(STBorderStyle.Enum.forInt(border + 1));
+ }
+
+ public void setBorderRight(short border){
+ CTBorderPr pr = _border.isSetRight() ? _border.getRight() : _border.addNewRight();
+ if(border == BORDER_NONE) _border.unsetRight();
+ else pr.setStyle(STBorderStyle.Enum.forInt(border + 1));
+ }
+
+ public void setBorderTop(short border){
+ CTBorderPr pr = _border.isSetTop() ? _border.getTop() : _border.addNewTop();
+ if(border == BORDER_NONE) _border.unsetTop();
+ else pr.setStyle(STBorderStyle.Enum.forInt(border + 1));
+ }
+
+ public void setBottomBorderColor(short color){
+ CTBorderPr pr = _border.isSetBottom() ? _border.getBottom() : _border.addNewBottom();
+
+ CTColor ctColor = CTColor.Factory.newInstance();
+ ctColor.setIndexed(color);
+ pr.setColor(ctColor);
+ }
+
+ public void setDiagonalBorderColor(short color){
+ CTBorderPr pr = _border.isSetDiagonal() ? _border.getDiagonal() : _border.addNewDiagonal();
+
+ CTColor ctColor = CTColor.Factory.newInstance();
+ ctColor.setIndexed(color);
+ pr.setColor(ctColor);
+ }
+
+ public void setLeftBorderColor(short color){
+ CTBorderPr pr = _border.isSetLeft() ? _border.getLeft() : _border.addNewLeft();
+
+ CTColor ctColor = CTColor.Factory.newInstance();
+ ctColor.setIndexed(color);
+ pr.setColor(ctColor);
+ }
+
+ public void setRightBorderColor(short color){
+ CTBorderPr pr = _border.isSetRight() ? _border.getRight() : _border.addNewRight();
+
+ CTColor ctColor = CTColor.Factory.newInstance();
+ ctColor.setIndexed(color);
+ pr.setColor(ctColor);
+ }
+
+ public void setTopBorderColor(short color){
+ CTBorderPr pr = _border.isSetTop() ? _border.getTop() : _border.addNewTop();
+
+ CTColor ctColor = CTColor.Factory.newInstance();
+ ctColor.setIndexed(color);
+ pr.setColor(ctColor);
+ }
+
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java new file mode 100644 index 0000000000..127c03783d --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java @@ -0,0 +1,101 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.ConditionalFormatting;
+import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
+
+import java.util.ArrayList;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class XSSFConditionalFormatting implements ConditionalFormatting {
+ private final CTConditionalFormatting _cf;
+ private final XSSFSheet _sh;
+
+ /*package*/ XSSFConditionalFormatting(XSSFSheet sh){
+ _cf = CTConditionalFormatting.Factory.newInstance();
+ _sh = sh;
+ }
+
+ /*package*/ XSSFConditionalFormatting(XSSFSheet sh, CTConditionalFormatting cf){
+ _cf = cf;
+ _sh = sh;
+ }
+
+ /*package*/ CTConditionalFormatting getCTConditionalFormatting(){
+ return _cf;
+ }
+
+ /**
+ * @return array of <tt>CellRangeAddress</tt>s. Never <code>null</code>
+ */
+ public CellRangeAddress[] getFormattingRanges(){
+ ArrayList<CellRangeAddress> lst = new ArrayList<CellRangeAddress>();
+ for (Object stRef : _cf.getSqref()) {
+ String[] regions = stRef.toString().split(" ");
+ for (int i = 0; i < regions.length; i++) {
+ lst.add(CellRangeAddress.valueOf(regions[i]));
+ }
+ }
+ return lst.toArray(new CellRangeAddress[lst.size()]);
+ }
+
+ /**
+ * Replaces an existing Conditional Formatting rule at position idx.
+ * Excel allows to create up to 3 Conditional Formatting rules.
+ * This method can be useful to modify existing Conditional Formatting rules.
+ *
+ * @param idx position of the rule. Should be between 0 and 2.
+ * @param cfRule - Conditional Formatting rule
+ */
+ public void setRule(int idx, ConditionalFormattingRule cfRule){
+ XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule;
+ _cf.getCfRuleArray(idx).set(xRule.getCTCfRule());
+ }
+
+ /**
+ * Add a Conditional Formatting rule.
+ * Excel allows to create up to 3 Conditional Formatting rules.
+ *
+ * @param cfRule - Conditional Formatting rule
+ */
+ public void addRule(ConditionalFormattingRule cfRule){
+ XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule;
+ _cf.addNewCfRule().set(xRule.getCTCfRule());
+ }
+
+ /**
+ * @return the Conditional Formatting rule at position idx.
+ */
+ public XSSFConditionalFormattingRule getRule(int idx){
+ return new XSSFConditionalFormattingRule(_sh, _cf.getCfRuleArray(idx));
+ }
+
+ /**
+ * @return number of Conditional Formatting rules.
+ */
+ public int getNumberOfRules(){
+ return _cf.sizeOfCfRuleArray();
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java new file mode 100644 index 0000000000..e816a841f4 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java @@ -0,0 +1,226 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.xssf.usermodel.XSSFFontFormatting;
+import org.apache.poi.xssf.model.StylesTable;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCfType;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STConditionalFormattingOperator;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxfs;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxf;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTStylesheet;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFill;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class XSSFConditionalFormattingRule implements ConditionalFormattingRule {
+ private final CTCfRule _cfRule;
+ private XSSFSheet _sh;
+
+ /*package*/ XSSFConditionalFormattingRule(XSSFSheet sh){
+ _cfRule = CTCfRule.Factory.newInstance();
+ _sh = sh;
+ }
+
+ /*package*/ XSSFConditionalFormattingRule(XSSFSheet sh, CTCfRule cfRule){
+ _cfRule = cfRule;
+ _sh = sh;
+ }
+
+ /*package*/ CTCfRule getCTCfRule(){
+ return _cfRule;
+ }
+
+ /*package*/ CTDxf getDxf(boolean create){
+ StylesTable styles = _sh.getWorkbook().getStylesSource();
+ CTDxf dxf = null;
+ if(styles._getDXfsSize() > 0 && _cfRule.isSetDxfId()){
+ int dxfId = (int)_cfRule.getDxfId();
+ dxf = styles.getDxfAt(dxfId);
+ }
+ if(create && dxf == null) {
+ dxf = CTDxf.Factory.newInstance();
+ int dxfId = styles.putDxf(dxf);
+ _cfRule.setDxfId(dxfId - 1);
+ }
+ return dxf;
+ }
+
+ /**
+ * Create a new border formatting structure if it does not exist,
+ * otherwise just return existing object.
+ *
+ * @return - border formatting object, never returns <code>null</code>.
+ */
+ public XSSFBorderFormatting createBorderFormatting(){
+ CTDxf dxf = getDxf(true);
+ CTBorder border;
+ if(!dxf.isSetBorder()) {
+ border = dxf.addNewBorder();
+ } else {
+ border = dxf.getBorder();
+ }
+
+ return new XSSFBorderFormatting(border);
+ }
+
+ /**
+ * @return - border formatting object if defined, <code>null</code> otherwise
+ */
+ public XSSFBorderFormatting getBorderFormatting(){
+ CTDxf dxf = getDxf(false);
+ if(dxf == null || !dxf.isSetBorder()) return null;
+
+ return new XSSFBorderFormatting(dxf.getBorder());
+ }
+
+ /**
+ * Create a new font formatting structure if it does not exist,
+ * otherwise just return existing object.
+ *
+ * @return - font formatting object, never returns <code>null</code>.
+ */
+ public XSSFFontFormatting createFontFormatting(){
+ CTDxf dxf = getDxf(true);
+ CTFont font;
+ if(!dxf.isSetFont()) {
+ font = dxf.addNewFont();
+ } else {
+ font = dxf.getFont();
+ }
+
+ return new XSSFFontFormatting(font);
+ }
+
+ /**
+ * @return - font formatting object if defined, <code>null</code> otherwise
+ */
+ public XSSFFontFormatting getFontFormatting(){
+ CTDxf dxf = getDxf(false);
+ if(dxf == null || !dxf.isSetFont()) return null;
+
+ return new XSSFFontFormatting(dxf.getFont());
+ }
+
+ /**
+ * Create a new pattern formatting structure if it does not exist,
+ * otherwise just return existing object.
+ *
+ * @return - pattern formatting object, never returns <code>null</code>.
+ */
+ public XSSFPatternFormatting createPatternFormatting(){
+ CTDxf dxf = getDxf(true);
+ CTFill fill;
+ if(!dxf.isSetFill()) {
+ fill = dxf.addNewFill();
+ } else {
+ fill = dxf.getFill();
+ }
+
+ return new XSSFPatternFormatting(fill);
+ }
+
+ /**
+ * @return - pattern formatting object if defined, <code>null</code> otherwise
+ */
+ public XSSFPatternFormatting getPatternFormatting(){
+ CTDxf dxf = getDxf(false);
+ if(dxf == null || !dxf.isSetFill()) return null;
+
+ return new XSSFPatternFormatting(dxf.getFill());
+ }
+
+ /**
+ * Type of conditional formatting rule.
+ * <p>
+ * MUST be either {@link ConditionalFormattingRule#CONDITION_TYPE_CELL_VALUE_IS}
+ * or {@link ConditionalFormattingRule#CONDITION_TYPE_FORMULA}
+ * </p>
+ *
+ * @return the type of condition
+ */
+ public byte getConditionType(){
+ switch (_cfRule.getType().intValue()){
+ case STCfType.INT_EXPRESSION: return ConditionalFormattingRule.CONDITION_TYPE_FORMULA;
+ case STCfType.INT_CELL_IS: return ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS;
+ }
+ return 0;
+ }
+
+ /**
+ * The comparison function used when the type of conditional formatting is set to
+ * {@link ConditionalFormattingRule#CONDITION_TYPE_CELL_VALUE_IS}
+ * <p>
+ * MUST be a constant from {@link org.apache.poi.ss.usermodel.ComparisonOperator}
+ * </p>
+ *
+ * @return the conditional format operator
+ */
+ public byte getComparisonOperation(){
+ STConditionalFormattingOperator.Enum op = _cfRule.getOperator();
+ if(op == null) return ComparisonOperator.NO_COMPARISON;
+
+ switch(op.intValue()){
+ case STConditionalFormattingOperator.INT_LESS_THAN: return ComparisonOperator.LT;
+ case STConditionalFormattingOperator.INT_LESS_THAN_OR_EQUAL: return ComparisonOperator.LE;
+ case STConditionalFormattingOperator.INT_GREATER_THAN: return ComparisonOperator.GT;
+ case STConditionalFormattingOperator.INT_GREATER_THAN_OR_EQUAL: return ComparisonOperator.GE;
+ case STConditionalFormattingOperator.INT_EQUAL: return ComparisonOperator.EQUAL;
+ case STConditionalFormattingOperator.INT_NOT_EQUAL: return ComparisonOperator.NOT_EQUAL;
+ case STConditionalFormattingOperator.INT_BETWEEN: return ComparisonOperator.BETWEEN;
+ case STConditionalFormattingOperator.INT_NOT_BETWEEN: return ComparisonOperator.NOT_BETWEEN;
+ }
+ return ComparisonOperator.NO_COMPARISON;
+ }
+
+ /**
+ * The formula used to evaluate the first operand for the conditional formatting rule.
+ * <p>
+ * If the condition type is {@link ConditionalFormattingRule#CONDITION_TYPE_CELL_VALUE_IS},
+ * this field is the first operand of the comparison.
+ * If type is {@link ConditionalFormattingRule#CONDITION_TYPE_FORMULA}, this formula is used
+ * to determine if the conditional formatting is applied.
+ * </p>
+ * <p>
+ * If comparison type is {@link ConditionalFormattingRule#CONDITION_TYPE_FORMULA} the formula MUST be a Boolean function
+ * </p>
+ *
+ * @return the first formula
+ */
+ public String getFormula1(){
+ return _cfRule.sizeOfFormulaArray() > 0 ? _cfRule.getFormulaArray(0) : null;
+ }
+
+ /**
+ * The formula used to evaluate the second operand of the comparison when
+ * comparison type is {@link ConditionalFormattingRule#CONDITION_TYPE_CELL_VALUE_IS} and operator
+ * is either {@link org.apache.poi.ss.usermodel.ComparisonOperator#BETWEEN} or {@link org.apache.poi.ss.usermodel.ComparisonOperator#NOT_BETWEEN}
+ *
+ * @return the second formula
+ */
+ public String getFormula2(){
+ return _cfRule.sizeOfFormulaArray() == 2 ? _cfRule.getFormulaArray(1) : null;
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFontFormatting.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFontFormatting.java new file mode 100644 index 0000000000..4c9cb54f95 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFontFormatting.java @@ -0,0 +1,212 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.hssf.util.HSSFColor;
+import org.apache.poi.POIXMLException;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTUnderlineProperty;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STUnderlineValues;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFontSize;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTVerticalAlignFontProperty;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STVerticalAlignRun;
+
+import java.util.Map;
+import java.util.Iterator;
+import java.awt.*;
+import java.awt.Font;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class XSSFFontFormatting implements FontFormatting {
+ CTFont _font;
+
+ /*package*/ XSSFFontFormatting(CTFont font){
+ _font = font;
+ }
+
+ /**
+ * get the type of super or subscript for the font
+ *
+ * @return super or subscript option
+ * @see #SS_NONE
+ * @see #SS_SUPER
+ * @see #SS_SUB
+ */
+ public short getEscapementType(){
+ if(_font.sizeOfVertAlignArray() == 0) return SS_NONE;
+
+ CTVerticalAlignFontProperty prop = _font.getVertAlignArray(0);
+ return (short)(prop.getVal().intValue() - 1);
+ }
+
+ /**
+ * set the escapement type for the font
+ *
+ * @param escapementType super or subscript option
+ * @see #SS_NONE
+ * @see #SS_SUPER
+ * @see #SS_SUB
+ */
+ public void setEscapementType(short escapementType){
+ _font.setVertAlignArray(null);
+ if(escapementType != SS_NONE){
+ _font.addNewVertAlign().setVal(STVerticalAlignRun.Enum.forInt(escapementType + 1));
+ }
+ }
+
+ /**
+ * @return font color index
+ */
+ public short getFontColorIndex(){
+ if(_font.sizeOfColorArray() == 0) return -1;
+
+ int idx = 0;
+ CTColor color = _font.getColorArray(0);
+ if(color.isSetIndexed()) idx = (int)color.getIndexed();
+ return (short)idx;
+ }
+
+
+ /**
+ * @param color font color index
+ */
+ public void setFontColorIndex(short color){
+ _font.setColorArray(null);
+ if(color != -1){
+ _font.addNewColor().setIndexed(color);
+ }
+ }
+
+ /**
+ *
+ * @return xssf color wrapper or null if color info is missing
+ */
+ public XSSFColor getXSSFColor(){
+ if(_font.sizeOfColorArray() == 0) return null;
+
+ return new XSSFColor(_font.getColorArray(0));
+ }
+
+ /**
+ * gets the height of the font in 1/20th point units
+ *
+ * @return fontheight (in points/20); or -1 if not modified
+ */
+ public int getFontHeight(){
+ if(_font.sizeOfSzArray() == 0) return -1;
+
+ CTFontSize sz = _font.getSzArray(0);
+ return (short)(20*sz.getVal());
+ }
+
+ /**
+ * Sets the height of the font in 1/20th point units
+ *
+ * @param height the height in twips (in points/20)
+ */
+ public void setFontHeight(int height){
+ _font.setSzArray(null);
+ if(height != -1){
+ _font.addNewSz().setVal((double)height / 20);
+ }
+ }
+
+ /**
+ * get the type of underlining for the font
+ *
+ * @return font underlining type
+ *
+ * @see #U_NONE
+ * @see #U_SINGLE
+ * @see #U_DOUBLE
+ * @see #U_SINGLE_ACCOUNTING
+ * @see #U_DOUBLE_ACCOUNTING
+ */
+ public short getUnderlineType(){
+ if(_font.sizeOfUArray() == 0) return U_NONE;
+ CTUnderlineProperty u = _font.getUArray(0);
+ switch(u.getVal().intValue()){
+ case STUnderlineValues.INT_SINGLE: return U_SINGLE;
+ case STUnderlineValues.INT_DOUBLE: return U_DOUBLE;
+ case STUnderlineValues.INT_SINGLE_ACCOUNTING: return U_SINGLE_ACCOUNTING;
+ case STUnderlineValues.INT_DOUBLE_ACCOUNTING: return U_DOUBLE_ACCOUNTING;
+ default: return U_NONE;
+ }
+ }
+
+ /**
+ * set the type of underlining type for the font
+ *
+ * @param underlineType super or subscript option
+ *
+ * @see #U_NONE
+ * @see #U_SINGLE
+ * @see #U_DOUBLE
+ * @see #U_SINGLE_ACCOUNTING
+ * @see #U_DOUBLE_ACCOUNTING
+ */
+ public void setUnderlineType(short underlineType){
+ _font.setUArray(null);
+ if(underlineType != U_NONE){
+ FontUnderline fenum = FontUnderline.valueOf(underlineType);
+ STUnderlineValues.Enum val = STUnderlineValues.Enum.forInt(fenum.getValue());
+ _font.addNewU().setVal(val);
+ }
+ }
+
+ /**
+ * get whether the font weight is set to bold or not
+ *
+ * @return bold - whether the font is bold or not
+ */
+ public boolean isBold(){
+ return _font.sizeOfBArray() == 1 && _font.getBArray(0).getVal();
+ }
+
+ /**
+ * @return true if font style was set to <i>italic</i>
+ */
+ public boolean isItalic(){
+ return _font.sizeOfIArray() == 1 && _font.getIArray(0).getVal();
+ }
+
+ /**
+ * set font style options.
+ *
+ * @param italic - if true, set posture style to italic, otherwise to normal
+ * @param bold if true, set font weight to bold, otherwise to normal
+ */
+ public void setFontStyle(boolean italic, boolean bold){
+ _font.setIArray(null);
+ _font.setBArray(null);
+ if(italic) _font.addNewI().setVal(true);
+ if(bold) _font.addNewB().setVal(true);
+ }
+
+ /**
+ * set font style options to default values (non-italic, non-bold)
+ */
+ public void resetFontStyle(){
+ _font.set(CTFont.Factory.newInstance());
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPatternFormatting.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPatternFormatting.java new file mode 100644 index 0000000000..f3d1329063 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPatternFormatting.java @@ -0,0 +1,75 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.PatternFormatting;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFill;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPatternFill;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPatternType;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class XSSFPatternFormatting implements PatternFormatting {
+ CTFill _fill;
+
+ XSSFPatternFormatting(CTFill fill){
+ _fill = fill;
+ }
+
+ public short getFillBackgroundColor(){
+ if(!_fill.isSetPatternFill()) return 0;
+
+ return (short)_fill.getPatternFill().getBgColor().getIndexed();
+ }
+
+ public short getFillForegroundColor(){
+ if(!_fill.isSetPatternFill() || ! _fill.getPatternFill().isSetFgColor())
+ return 0;
+
+ return (short)_fill.getPatternFill().getFgColor().getIndexed();
+ }
+
+ public short getFillPattern(){
+ if(!_fill.isSetPatternFill() || !_fill.getPatternFill().isSetPatternType()) return NO_FILL;
+
+ return (short)(_fill.getPatternFill().getPatternType().intValue() - 1);
+ }
+
+ public void setFillBackgroundColor(short bg){
+ CTPatternFill ptrn = _fill.isSetPatternFill() ? _fill.getPatternFill() : _fill.addNewPatternFill();
+ CTColor bgColor = CTColor.Factory.newInstance();
+ bgColor.setIndexed(bg);
+ ptrn.setBgColor(bgColor);
+ }
+
+ public void setFillForegroundColor(short fg){
+ CTPatternFill ptrn = _fill.isSetPatternFill() ? _fill.getPatternFill() : _fill.addNewPatternFill();
+ CTColor fgColor = CTColor.Factory.newInstance();
+ fgColor.setIndexed(fg);
+ ptrn.setFgColor(fgColor);
+ }
+
+ public void setFillPattern(short fp){
+ CTPatternFill ptrn = _fill.isSetPatternFill() ? _fill.getPatternFill() : _fill.addNewPatternFill();
+ if(fp == NO_FILL) ptrn.unsetPatternType();
+ else ptrn.setPatternType(STPatternType.Enum.forInt(fp + 1));
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index 281f773495..f1c573ebb8 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -49,7 +49,7 @@ import org.apache.poi.ss.usermodel.Footer; import org.apache.poi.ss.usermodel.Header; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.util.CellRangeAddress; +simport org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.SSCellRange; @@ -2391,6 +2391,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { rowShifter.updateNamedRanges(shifter); rowShifter.updateFormulas(shifter); rowShifter.shiftMerged(startRow, endRow, n); + rowShifter.updateConditionalFormatting(shifter); //rebuild the _rows map TreeMap<Integer, XSSFRow> map = new TreeMap<Integer, XSSFRow>(); @@ -3209,4 +3210,8 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { ); return tableList; } + + public XSSFSheetConditionalFormatting getSheetConditionalFormatting(){ + return new XSSFSheetConditionalFormatting(this); + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java new file mode 100644 index 0000000000..3a0e71de91 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java @@ -0,0 +1,236 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.ConditionalFormatting;
+import org.apache.poi.ss.usermodel.ConditionalFormattingRule;
+import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
+import org.apache.poi.ss.usermodel.ComparisonOperator;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.hssf.record.cf.CellRangeUtil;
+import org.apache.xmlbeans.XmlObject;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCfType;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STConditionalFormattingOperator;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
+
+import java.util.List;
+import java.util.Arrays;
+import java.util.ArrayList;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class XSSFSheetConditionalFormatting implements SheetConditionalFormatting {
+ private final XSSFSheet _sheet;
+
+ /* package */ XSSFSheetConditionalFormatting(XSSFSheet sheet) {
+ _sheet = sheet;
+ }
+
+ /**
+ * A factory method allowing to create a conditional formatting rule
+ * with a cell comparison operator<p/>
+ * TODO - formulas containing cell references are currently not parsed properly
+ *
+ * @param comparisonOperation - a constant value from
+ * <tt>{@link org.apache.poi.hssf.record.CFRuleRecord.ComparisonOperator}</tt>: <p>
+ * <ul>
+ * <li>BETWEEN</li>
+ * <li>NOT_BETWEEN</li>
+ * <li>EQUAL</li>
+ * <li>NOT_EQUAL</li>
+ * <li>GT</li>
+ * <li>LT</li>
+ * <li>GE</li>
+ * <li>LE</li>
+ * </ul>
+ * </p>
+ * @param formula1 - formula for the valued, compared with the cell
+ * @param formula2 - second formula (only used with
+ * {@link org.apache.poi.ss.usermodel.ComparisonOperator#BETWEEN}) and
+ * {@link org.apache.poi.ss.usermodel.ComparisonOperator#NOT_BETWEEN} operations)
+ */
+ public XSSFConditionalFormattingRule createConditionalFormattingRule(
+ byte comparisonOperation,
+ String formula1,
+ String formula2) {
+
+ XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
+ CTCfRule cfRule = rule.getCTCfRule();
+ cfRule.addFormula(formula1);
+ if(formula2 != null) cfRule.addFormula(formula2);
+ cfRule.setType(STCfType.CELL_IS);
+ STConditionalFormattingOperator.Enum operator;
+ switch (comparisonOperation){
+ case ComparisonOperator.BETWEEN: operator = STConditionalFormattingOperator.BETWEEN; break;
+ case ComparisonOperator.NOT_BETWEEN: operator = STConditionalFormattingOperator.NOT_BETWEEN; break;
+ case ComparisonOperator.LT: operator = STConditionalFormattingOperator.LESS_THAN; break;
+ case ComparisonOperator.LE: operator = STConditionalFormattingOperator.LESS_THAN_OR_EQUAL; break;
+ case ComparisonOperator.GT: operator = STConditionalFormattingOperator.GREATER_THAN; break;
+ case ComparisonOperator.GE: operator = STConditionalFormattingOperator.GREATER_THAN_OR_EQUAL; break;
+ case ComparisonOperator.EQUAL: operator = STConditionalFormattingOperator.EQUAL; break;
+ case ComparisonOperator.NOT_EQUAL: operator = STConditionalFormattingOperator.NOT_EQUAL; break;
+ default: throw new IllegalArgumentException("Unknown comparison operator: " + comparisonOperation);
+ }
+ cfRule.setOperator(operator);
+
+ return rule;
+ }
+
+ public XSSFConditionalFormattingRule createConditionalFormattingRule(
+ byte comparisonOperation,
+ String formula) {
+
+ return createConditionalFormattingRule(comparisonOperation, formula, null);
+ }
+
+ /**
+ * A factory method allowing to create a conditional formatting rule with a formula.<br>
+ *
+ * @param formula - formula for the valued, compared with the cell
+ */
+ public XSSFConditionalFormattingRule createConditionalFormattingRule(String formula) {
+ XSSFConditionalFormattingRule rule = new XSSFConditionalFormattingRule(_sheet);
+ CTCfRule cfRule = rule.getCTCfRule();
+ cfRule.addFormula(formula);
+ cfRule.setType(STCfType.EXPRESSION);
+ return rule;
+ }
+
+ 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");
+ }
+ 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<String> refs = new ArrayList<String>();
+ for(CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
+ cf.setSqref(refs);
+
+
+ int priority = 1;
+ for(CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingList()){
+ 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;
+ }
+
+ public int addConditionalFormatting(CellRangeAddress[] regions,
+ ConditionalFormattingRule rule1)
+ {
+ return addConditionalFormatting(regions,
+ rule1 == null ? null : new XSSFConditionalFormattingRule[] {
+ (XSSFConditionalFormattingRule)rule1
+ });
+ }
+
+ public int addConditionalFormatting(CellRangeAddress[] regions,
+ ConditionalFormattingRule rule1, ConditionalFormattingRule rule2)
+ {
+ return addConditionalFormatting(regions,
+ rule1 == null ? null : new XSSFConditionalFormattingRule[] {
+ (XSSFConditionalFormattingRule)rule1,
+ (XSSFConditionalFormattingRule)rule2
+ });
+ }
+
+ /**
+ * Adds a copy of HSSFConditionalFormatting object to the sheet
+ * <p>This method could be used to copy HSSFConditionalFormatting object
+ * from one sheet to another. For example:
+ * <pre>
+ * HSSFConditionalFormatting cf = sheet.getConditionalFormattingAt(index);
+ * newSheet.addConditionalFormatting(cf);
+ * </pre>
+ *
+ * @param cf HSSFConditionalFormatting object
+ * @return index of the new Conditional Formatting object
+ */
+ public int addConditionalFormatting( ConditionalFormatting cf ) {
+ XSSFConditionalFormatting xcf = (XSSFConditionalFormatting)cf;
+ CTWorksheet sh = _sheet.getCTWorksheet();
+ sh.addNewConditionalFormatting().set(xcf.getCTConditionalFormatting().copy());
+ return sh.sizeOfConditionalFormattingArray() - 1;
+ }
+
+ /**
+ * gets Conditional Formatting object at a particular index
+ *
+ * @param index
+ * of the Conditional Formatting object to fetch
+ * @return Conditional Formatting object
+ */
+ public XSSFConditionalFormatting getConditionalFormattingAt(int index) {
+ checkIndex(index);
+ CTConditionalFormatting cf = _sheet.getCTWorksheet().getConditionalFormattingArray(index);
+ return new XSSFConditionalFormatting(_sheet, cf);
+ }
+
+ /**
+ * @return number of Conditional Formatting objects of the sheet
+ */
+ public int getNumConditionalFormattings() {
+ return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray();
+ }
+
+ /**
+ * removes a Conditional Formatting object by index
+ * @param index of a Conditional Formatting object to remove
+ */
+ public void removeConditionalFormatting(int index) {
+ checkIndex(index);
+ _sheet.getCTWorksheet().getConditionalFormattingList().remove(index);
+ }
+
+ private void checkIndex(int index) {
+ int cnt = getNumConditionalFormattings();
+ if (index < 0 || index >= cnt) {
+ throw new IllegalArgumentException("Specified CF index " + index
+ + " is outside the allowable range (0.." + (cnt - 1) + ")");
+ }
+ }
+
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java index 166dcc369c..40de77bcd4 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java @@ -26,8 +26,12 @@ import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.formula.FormulaShifter; import org.apache.poi.ss.formula.ptg.Ptg; +import org.apache.poi.ss.formula.ptg.AreaPtg; +import org.apache.poi.ss.formula.ptg.AreaErrPtg; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule; import java.util.List; import java.util.ArrayList; @@ -187,4 +191,81 @@ public final class XSSFRowShifter { return shiftedFmla; } + public void updateConditionalFormatting(FormulaShifter shifter) { + XSSFWorkbook wb = sheet.getWorkbook(); + int sheetIndex = wb.getSheetIndex(sheet); + + + XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); + List<CTConditionalFormatting> cfList = sheet.getCTWorksheet().getConditionalFormattingList(); + for(int j = 0; j< cfList.size(); j++){ + CTConditionalFormatting cf = cfList.get(j); + + ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>(); + for (Object stRef : cf.getSqref()) { + String[] regions = stRef.toString().split(" "); + for (int i = 0; i < regions.length; i++) { + cellRanges.add(CellRangeAddress.valueOf(regions[i])); + } + } + + boolean changed = false; + List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>(); + for (int i = 0; i < cellRanges.size(); i++) { + CellRangeAddress craOld = cellRanges.get(i); + CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex); + if (craNew == null) { + changed = true; + continue; + } + temp.add(craNew); + if (craNew != craOld) { + changed = true; + } + } + + if (changed) { + int nRanges = temp.size(); + if (nRanges == 0) { + cfList.remove(j); + continue; + } + List<String> refs = new ArrayList<String>(); + for(CellRangeAddress a : temp) refs.add(a.formatAsString()); + cf.setSqref(refs); + } + + for(CTCfRule cfRule : cf.getCfRuleList()){ + List<String> formulas = cfRule.getFormulaList(); + for (int i = 0; i < formulas.size(); i++) { + String formula = formulas.get(i); + Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex); + if (shifter.adjustFormula(ptgs, sheetIndex)) { + String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); + formulas.set(i, shiftedFmla); + } + } + } + } + } + + private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) { + // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here + AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false); + Ptg[] ptgs = { aptg, }; + + if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) { + return cra; + } + Ptg ptg0 = ptgs[0]; + if (ptg0 instanceof AreaPtg) { + AreaPtg bptg = (AreaPtg) ptg0; + return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn()); + } + if (ptg0 instanceof AreaErrPtg) { + return null; + } + throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")"); + } + } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFConditionalFormatting.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFConditionalFormatting.java new file mode 100644 index 0000000000..325c5342dd --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFConditionalFormatting.java @@ -0,0 +1,35 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.ss.usermodel.BaseTestConditionalFormatting;
+import org.apache.poi.xssf.XSSFITestDataProvider;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class TestXSSFConditionalFormatting extends BaseTestConditionalFormatting {
+ public TestXSSFConditionalFormatting(){
+ super(XSSFITestDataProvider.instance);
+ }
+
+ public void testRead(){
+ testRead("WithConditionalFormatting.xlsx");
+ }
+}
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java index f4fa306cc2..5495199770 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java @@ -20,171 +20,22 @@ package org.apache.poi.hssf.usermodel; import junit.framework.AssertionFailedError; import junit.framework.TestCase; +import org.apache.poi.hssf.HSSFITestDataProvider; import org.apache.poi.hssf.record.CFRuleRecord.ComparisonOperator; import org.apache.poi.hssf.util.HSSFColor; +import org.apache.poi.ss.usermodel.BaseTestConditionalFormatting; import org.apache.poi.ss.util.CellRangeAddress; /** * * @author Dmitriy Kumshayev */ -public final class TestHSSFConditionalFormatting extends TestCase { - public void testCreateCF() { - HSSFWorkbook workbook = new HSSFWorkbook(); - HSSFSheet sheet = workbook.createSheet(); - String formula = "7"; - - HSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); - - HSSFConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(formula); - HSSFFontFormatting fontFmt = rule1.createFontFormatting(); - fontFmt.setFontStyle(true, false); - - HSSFBorderFormatting bordFmt = rule1.createBorderFormatting(); - bordFmt.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); - bordFmt.setBorderTop(HSSFBorderFormatting.BORDER_THICK); - bordFmt.setBorderLeft(HSSFBorderFormatting.BORDER_DASHED); - bordFmt.setBorderRight(HSSFBorderFormatting.BORDER_DOTTED); - - HSSFPatternFormatting patternFmt = rule1.createPatternFormatting(); - patternFmt.setFillBackgroundColor(HSSFColor.YELLOW.index); - - - HSSFConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "1", "2"); - HSSFConditionalFormattingRule [] cfRules = - { - rule1, rule2 - }; - - short col = 1; - CellRangeAddress [] regions = { - new CellRangeAddress(0, 65535, col, col) - }; - - sheetCF.addConditionalFormatting(regions, cfRules); - sheetCF.addConditionalFormatting(regions, cfRules); - - // Verification - assertEquals(2, sheetCF.getNumConditionalFormattings()); - sheetCF.removeConditionalFormatting(1); - assertEquals(1, sheetCF.getNumConditionalFormattings()); - HSSFConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0); - assertNotNull(cf); - - regions = cf.getFormattingRanges(); - assertNotNull(regions); - assertEquals(1, regions.length); - CellRangeAddress r = regions[0]; - assertEquals(1, r.getFirstColumn()); - assertEquals(1, r.getLastColumn()); - assertEquals(0, r.getFirstRow()); - assertEquals(65535, r.getLastRow()); - - assertEquals(2, cf.getNumberOfRules()); - - rule1 = cf.getRule(0); - assertEquals("7",rule1.getFormula1()); - assertNull(rule1.getFormula2()); - - HSSFFontFormatting r1fp = rule1.getFontFormatting(); - assertNotNull(r1fp); - - assertTrue(r1fp.isItalic()); - assertFalse(r1fp.isBold()); - - HSSFBorderFormatting r1bf = rule1.getBorderFormatting(); - assertNotNull(r1bf); - assertEquals(HSSFBorderFormatting.BORDER_THIN, r1bf.getBorderBottom()); - assertEquals(HSSFBorderFormatting.BORDER_THICK,r1bf.getBorderTop()); - assertEquals(HSSFBorderFormatting.BORDER_DASHED,r1bf.getBorderLeft()); - assertEquals(HSSFBorderFormatting.BORDER_DOTTED,r1bf.getBorderRight()); - - HSSFPatternFormatting r1pf = rule1.getPatternFormatting(); - assertNotNull(r1pf); - assertEquals(HSSFColor.YELLOW.index,r1pf.getFillBackgroundColor()); - - rule2 = cf.getRule(1); - assertEquals("2",rule2.getFormula2()); - assertEquals("1",rule2.getFormula1()); - } - - public void testClone() { - - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet sheet = wb.createSheet(); - String formula = "7"; - - HSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); - - HSSFConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(formula); - HSSFFontFormatting fontFmt = rule1.createFontFormatting(); - fontFmt.setFontStyle(true, false); - - HSSFPatternFormatting patternFmt = rule1.createPatternFormatting(); - patternFmt.setFillBackgroundColor(HSSFColor.YELLOW.index); - - - HSSFConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "1", "2"); - HSSFConditionalFormattingRule [] cfRules = - { - rule1, rule2 - }; - - short col = 1; - CellRangeAddress [] regions = { - new CellRangeAddress(0, 65535, col, col) - }; - - sheetCF.addConditionalFormatting(regions, cfRules); - - try { - wb.cloneSheet(0); - } catch (RuntimeException e) { - if (e.getMessage().indexOf("needs to define a clone method") > 0) { - throw new AssertionFailedError("Indentified bug 45682"); - } - throw e; - } - assertEquals(2, wb.getNumberOfSheets()); - } - - public void testShiftRows() { - - HSSFWorkbook wb = new HSSFWorkbook(); - HSSFSheet sheet = wb.createSheet(); - - HSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); - - HSSFConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule( - ComparisonOperator.BETWEEN, "sum(A10:A15)", "1+sum(B16:B30)"); - HSSFFontFormatting fontFmt = rule1.createFontFormatting(); - fontFmt.setFontStyle(true, false); - - HSSFPatternFormatting patternFmt = rule1.createPatternFormatting(); - patternFmt.setFillBackgroundColor(HSSFColor.YELLOW.index); - HSSFConditionalFormattingRule [] cfRules = { rule1, }; - - CellRangeAddress [] regions = { - new CellRangeAddress(2, 4, 0, 0), // A3:A5 - }; - sheetCF.addConditionalFormatting(regions, cfRules); - - // 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); - - // This row shift should only affect the formulas - sheet.shiftRows(14, 17, 8); - HSSFConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0); - assertEquals("SUM(A10:A23)", cf.getRule(0).getFormula1()); - assertEquals("1+SUM(B24:B30)", cf.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()); - } +public final class TestHSSFConditionalFormatting extends BaseTestConditionalFormatting { + public TestHSSFConditionalFormatting(){ + super(HSSFITestDataProvider.instance); + } + + public void testRead(){ + testRead("WithConditionalFormatting.xls"); + } } diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java new file mode 100644 index 0000000000..7ddb3f02ce --- /dev/null +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java @@ -0,0 +1,692 @@ +/*
+ * ====================================================================
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * ====================================================================
+ */
+
+package org.apache.poi.ss.usermodel;
+
+import junit.framework.TestCase;
+import org.apache.poi.ss.ITestDataProvider;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+/**
+ * @author Dmitriy Kumshayev
+ * @author Yegor Kozlov
+ */
+public abstract class BaseTestConditionalFormatting extends TestCase {
+ private final ITestDataProvider _testDataProvider;
+
+ public BaseTestConditionalFormatting(ITestDataProvider testDataProvider){
+ _testDataProvider = testDataProvider;
+ }
+
+ public void testBasic() {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sh = wb.createSheet();
+ SheetConditionalFormatting sheetCF = sh.getSheetConditionalFormatting();
+
+ assertEquals(0, sheetCF.getNumConditionalFormattings());
+ try {
+ assertNull(sheetCF.getConditionalFormattingAt(0));
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("Specified CF index 0 is outside the allowable range"));
+ }
+
+ try {
+ sheetCF.removeConditionalFormatting(0);
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("Specified CF index 0 is outside the allowable range"));
+ }
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("1");
+ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("2");
+ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("3");
+ ConditionalFormattingRule rule4 = sheetCF.createConditionalFormattingRule("4");
+ try {
+ sheetCF.addConditionalFormatting(null, rule1);
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("regions must not be null"));
+ }
+ try {
+ sheetCF.addConditionalFormatting(
+ new CellRangeAddress[]{ CellRangeAddress.valueOf("A1:A3") },
+ (ConditionalFormattingRule)null);
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("cfRules must not be null"));
+ }
+
+ try {
+ sheetCF.addConditionalFormatting(
+ new CellRangeAddress[]{ CellRangeAddress.valueOf("A1:A3") },
+ new ConditionalFormattingRule[0]);
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("cfRules must not be empty"));
+ }
+
+ try {
+ sheetCF.addConditionalFormatting(
+ new CellRangeAddress[]{ CellRangeAddress.valueOf("A1:A3") },
+ new ConditionalFormattingRule[]{rule1, rule2, rule3, rule4});
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("Number of rules must not exceed 3"));
+ }
+ }
+
+ /**
+ * Test format conditions based on a boolean formula
+ */
+ public void testBooleanFormulaConditions() {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sh = wb.createSheet();
+ SheetConditionalFormatting sheetCF = sh.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("SUM(A1:A5)>10");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_FORMULA, rule1.getConditionType());
+ assertEquals("SUM(A1:A5)>10", rule1.getFormula1());
+ int formatIndex1 = sheetCF.addConditionalFormatting(
+ new CellRangeAddress[]{
+ CellRangeAddress.valueOf("B1"),
+ CellRangeAddress.valueOf("C3"),
+ }, rule1);
+ assertEquals(0, formatIndex1);
+ assertEquals(1, sheetCF.getNumConditionalFormattings());
+ CellRangeAddress[] ranges1 = sheetCF.getConditionalFormattingAt(formatIndex1).getFormattingRanges();
+ assertEquals(2, ranges1.length);
+ assertEquals("B1", ranges1[0].formatAsString());
+ assertEquals("C3", ranges1[1].formatAsString());
+
+ // adjacent address are merged
+ int formatIndex2 = sheetCF.addConditionalFormatting(
+ new CellRangeAddress[]{
+ CellRangeAddress.valueOf("B1"),
+ CellRangeAddress.valueOf("B2"),
+ CellRangeAddress.valueOf("B3"),
+ }, rule1);
+ assertEquals(1, formatIndex2);
+ assertEquals(2, sheetCF.getNumConditionalFormattings());
+ CellRangeAddress[] ranges2 = sheetCF.getConditionalFormattingAt(formatIndex2).getFormattingRanges();
+ assertEquals(1, ranges2.length);
+ assertEquals("B1:B3", ranges2[0].formatAsString());
+ }
+
+ public void testSingleFormulaConditions() {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sh = wb.createSheet();
+ SheetConditionalFormatting sheetCF = sh.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.EQUAL, "SUM(A1:A5)+10");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule1.getConditionType());
+ assertEquals("SUM(A1:A5)+10", rule1.getFormula1());
+ assertEquals(ComparisonOperator.EQUAL, rule1.getComparisonOperation());
+
+ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.NOT_EQUAL, "15");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule2.getConditionType());
+ assertEquals("15", rule2.getFormula1());
+ assertEquals(ComparisonOperator.NOT_EQUAL, rule2.getComparisonOperation());
+
+ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.NOT_EQUAL, "15");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule3.getConditionType());
+ assertEquals("15", rule3.getFormula1());
+ assertEquals(ComparisonOperator.NOT_EQUAL, rule3.getComparisonOperation());
+
+ ConditionalFormattingRule rule4 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.GT, "0");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule4.getConditionType());
+ assertEquals("0", rule4.getFormula1());
+ assertEquals(ComparisonOperator.GT, rule4.getComparisonOperation());
+
+ ConditionalFormattingRule rule5 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.LT, "0");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule5.getConditionType());
+ assertEquals("0", rule5.getFormula1());
+ assertEquals(ComparisonOperator.LT, rule5.getComparisonOperation());
+
+ ConditionalFormattingRule rule6 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.GE, "0");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule6.getConditionType());
+ assertEquals("0", rule6.getFormula1());
+ assertEquals(ComparisonOperator.GE, rule6.getComparisonOperation());
+
+ ConditionalFormattingRule rule7 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.LE, "0");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule7.getConditionType());
+ assertEquals("0", rule7.getFormula1());
+ assertEquals(ComparisonOperator.LE, rule7.getComparisonOperation());
+
+ ConditionalFormattingRule rule8 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.BETWEEN, "0", "5");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule8.getConditionType());
+ assertEquals("0", rule8.getFormula1());
+ assertEquals("5", rule8.getFormula2());
+ assertEquals(ComparisonOperator.BETWEEN, rule8.getComparisonOperation());
+
+ ConditionalFormattingRule rule9 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.NOT_BETWEEN, "0", "5");
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule9.getConditionType());
+ assertEquals("0", rule9.getFormula1());
+ assertEquals("5", rule9.getFormula2());
+ assertEquals(ComparisonOperator.NOT_BETWEEN, rule9.getComparisonOperation());
+ }
+
+ public void testCopy() {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet1 = wb.createSheet();
+ Sheet sheet2 = wb.createSheet();
+ SheetConditionalFormatting sheet1CF = sheet1.getSheetConditionalFormatting();
+ SheetConditionalFormatting sheet2CF = sheet2.getSheetConditionalFormatting();
+ assertEquals(0, sheet1CF.getNumConditionalFormattings());
+ assertEquals(0, sheet2CF.getNumConditionalFormattings());
+
+ ConditionalFormattingRule rule1 = sheet1CF.createConditionalFormattingRule(
+ ComparisonOperator.EQUAL, "SUM(A1:A5)+10");
+
+ ConditionalFormattingRule rule2 = sheet1CF.createConditionalFormattingRule(
+ ComparisonOperator.NOT_EQUAL, "15");
+
+ // adjacent address are merged
+ int formatIndex = sheet1CF.addConditionalFormatting(
+ new CellRangeAddress[]{
+ CellRangeAddress.valueOf("A1:A5"),
+ CellRangeAddress.valueOf("C1:C5")
+ }, rule1, rule2);
+ assertEquals(0, formatIndex);
+ assertEquals(1, sheet1CF.getNumConditionalFormattings());
+
+ assertEquals(0, sheet2CF.getNumConditionalFormattings());
+ sheet2CF.addConditionalFormatting(sheet1CF.getConditionalFormattingAt(formatIndex));
+ assertEquals(1, sheet2CF.getNumConditionalFormattings());
+
+ ConditionalFormatting sheet2cf = sheet2CF.getConditionalFormattingAt(0);
+ assertEquals(2, sheet2cf.getNumberOfRules());
+ assertEquals("SUM(A1:A5)+10", sheet2cf.getRule(0).getFormula1());
+ assertEquals(ComparisonOperator.EQUAL, sheet2cf.getRule(0).getComparisonOperation());
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, sheet2cf.getRule(0).getConditionType());
+ assertEquals("15", sheet2cf.getRule(1).getFormula1());
+ assertEquals(ComparisonOperator.NOT_EQUAL, sheet2cf.getRule(1).getComparisonOperation());
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, sheet2cf.getRule(1).getConditionType());
+ }
+
+ public void testRemove() {
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet1 = wb.createSheet();
+ SheetConditionalFormatting sheetCF = sheet1.getSheetConditionalFormatting();
+ assertEquals(0, sheetCF.getNumConditionalFormattings());
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.EQUAL, "SUM(A1:A5)");
+
+ // adjacent address are merged
+ int formatIndex = sheetCF.addConditionalFormatting(
+ new CellRangeAddress[]{
+ CellRangeAddress.valueOf("A1:A5")
+ }, rule1);
+ assertEquals(0, formatIndex);
+ assertEquals(1, sheetCF.getNumConditionalFormattings());
+ sheetCF.removeConditionalFormatting(0);
+ assertEquals(0, sheetCF.getNumConditionalFormattings());
+ try {
+ assertNull(sheetCF.getConditionalFormattingAt(0));
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("Specified CF index 0 is outside the allowable range"));
+ }
+
+ formatIndex = sheetCF.addConditionalFormatting(
+ new CellRangeAddress[]{
+ CellRangeAddress.valueOf("A1:A5")
+ }, rule1);
+ assertEquals(0, formatIndex);
+ assertEquals(1, sheetCF.getNumConditionalFormattings());
+ sheetCF.removeConditionalFormatting(0);
+ assertEquals(0, sheetCF.getNumConditionalFormattings());
+ try {
+ assertNull(sheetCF.getConditionalFormattingAt(0));
+ fail("expected exception");
+ } catch (IllegalArgumentException e) {
+ assertTrue(e.getMessage().startsWith("Specified CF index 0 is outside the allowable range"));
+ }
+ }
+
+ public void testCreateCF() {
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+ String formula = "7";
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(formula);
+ FontFormatting fontFmt = rule1.createFontFormatting();
+ fontFmt.setFontStyle(true, false);
+
+ BorderFormatting bordFmt = rule1.createBorderFormatting();
+ bordFmt.setBorderBottom(BorderFormatting.BORDER_THIN);
+ bordFmt.setBorderTop(BorderFormatting.BORDER_THICK);
+ bordFmt.setBorderLeft(BorderFormatting.BORDER_DASHED);
+ bordFmt.setBorderRight(BorderFormatting.BORDER_DOTTED);
+
+ PatternFormatting patternFmt = rule1.createPatternFormatting();
+ patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);
+
+
+ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "1", "2");
+ ConditionalFormattingRule [] cfRules =
+ {
+ rule1, rule2
+ };
+
+ short col = 1;
+ CellRangeAddress [] regions = {
+ new CellRangeAddress(0, 65535, col, col)
+ };
+
+ sheetCF.addConditionalFormatting(regions, cfRules);
+ sheetCF.addConditionalFormatting(regions, cfRules);
+
+ // Verification
+ assertEquals(2, sheetCF.getNumConditionalFormattings());
+ sheetCF.removeConditionalFormatting(1);
+ assertEquals(1, sheetCF.getNumConditionalFormattings());
+ ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
+ assertNotNull(cf);
+
+ regions = cf.getFormattingRanges();
+ assertNotNull(regions);
+ assertEquals(1, regions.length);
+ CellRangeAddress r = regions[0];
+ assertEquals(1, r.getFirstColumn());
+ assertEquals(1, r.getLastColumn());
+ assertEquals(0, r.getFirstRow());
+ assertEquals(65535, r.getLastRow());
+
+ assertEquals(2, cf.getNumberOfRules());
+
+ rule1 = cf.getRule(0);
+ assertEquals("7",rule1.getFormula1());
+ assertNull(rule1.getFormula2());
+
+ FontFormatting r1fp = rule1.getFontFormatting();
+ assertNotNull(r1fp);
+
+ assertTrue(r1fp.isItalic());
+ assertFalse(r1fp.isBold());
+
+ BorderFormatting r1bf = rule1.getBorderFormatting();
+ assertNotNull(r1bf);
+ assertEquals(BorderFormatting.BORDER_THIN, r1bf.getBorderBottom());
+ assertEquals(BorderFormatting.BORDER_THICK,r1bf.getBorderTop());
+ assertEquals(BorderFormatting.BORDER_DASHED,r1bf.getBorderLeft());
+ assertEquals(BorderFormatting.BORDER_DOTTED,r1bf.getBorderRight());
+
+ PatternFormatting r1pf = rule1.getPatternFormatting();
+ assertNotNull(r1pf);
+// assertEquals(IndexedColors.YELLOW.index,r1pf.getFillBackgroundColor());
+
+ rule2 = cf.getRule(1);
+ assertEquals("2",rule2.getFormula2());
+ assertEquals("1",rule2.getFormula1());
+ }
+
+ public void testClone() {
+
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet();
+ String formula = "7";
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(formula);
+ FontFormatting fontFmt = rule1.createFontFormatting();
+ fontFmt.setFontStyle(true, false);
+
+ PatternFormatting patternFmt = rule1.createPatternFormatting();
+ patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);
+
+
+ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "1", "2");
+ ConditionalFormattingRule [] cfRules =
+ {
+ rule1, rule2
+ };
+
+ short col = 1;
+ CellRangeAddress [] regions = {
+ new CellRangeAddress(0, 65535, col, col)
+ };
+
+ sheetCF.addConditionalFormatting(regions, cfRules);
+
+ try {
+ wb.cloneSheet(0);
+ } catch (RuntimeException e) {
+ if (e.getMessage().indexOf("needs to define a clone method") > 0) {
+ fail("Indentified bug 45682");
+ }
+ throw e;
+ }
+ assertEquals(2, wb.getNumberOfSheets());
+ }
+
+ public void testShiftRows() {
+
+ Workbook wb = _testDataProvider.createWorkbook();
+ Sheet sheet = wb.createSheet();
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(
+ ComparisonOperator.BETWEEN, "SUM(A10:A15)", "1+SUM(B16:B30)");
+ FontFormatting fontFmt = rule1.createFontFormatting();
+ fontFmt.setFontStyle(true, false);
+
+ PatternFormatting patternFmt = rule1.createPatternFormatting();
+ patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);
+ ConditionalFormattingRule [] cfRules = { rule1, };
+
+ CellRangeAddress [] regions = {
+ new CellRangeAddress(2, 4, 0, 0), // A3:A5
+ };
+ sheetCF.addConditionalFormatting(regions, cfRules);
+
+ // 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);
+
+ // 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());
+
+ 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());
+ }
+
+ protected void testRead(String filename){
+ Workbook wb = _testDataProvider.openSampleWorkbook(filename);
+ Sheet sh = wb.getSheet("CF");
+ SheetConditionalFormatting sheetCF = sh.getSheetConditionalFormatting();
+ assertEquals(3, sheetCF.getNumConditionalFormattings());
+
+ ConditionalFormatting cf1 = sheetCF.getConditionalFormattingAt(0);
+ assertEquals(2, cf1.getNumberOfRules());
+
+ CellRangeAddress[] regions1 = cf1.getFormattingRanges();
+ assertEquals(1, regions1.length);
+ assertEquals("A1:A8", regions1[0].formatAsString());
+
+ // CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
+ ConditionalFormattingRule rule1 = cf1.getRule(0);
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule1.getConditionType());
+ assertEquals(ComparisonOperator.GT, rule1.getComparisonOperation());
+ assertEquals("3", rule1.getFormula1());
+ assertNull(rule1.getFormula2());
+ // fills and borders are not set
+ assertNull(rule1.getPatternFormatting());
+ assertNull(rule1.getBorderFormatting());
+
+ FontFormatting fmt1 = rule1.getFontFormatting();
+// assertEquals(IndexedColors.GREEN.index, fmt1.getFontColorIndex());
+ assertTrue(fmt1.isBold());
+ assertFalse(fmt1.isItalic());
+
+ ConditionalFormattingRule rule2 = cf1.getRule(1);
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule2.getConditionType());
+ assertEquals(ComparisonOperator.LT, rule2.getComparisonOperation());
+ assertEquals("-3", rule2.getFormula1());
+ assertNull(rule2.getFormula2());
+ assertNull(rule2.getPatternFormatting());
+ assertNull(rule2.getBorderFormatting());
+
+ FontFormatting fmt2 = rule2.getFontFormatting();
+// assertEquals(IndexedColors.RED.index, fmt2.getFontColorIndex());
+ assertTrue(fmt2.isBold());
+ assertTrue(fmt2.isItalic());
+
+
+ ConditionalFormatting cf2 = sheetCF.getConditionalFormattingAt(1);
+ assertEquals(1, cf2.getNumberOfRules());
+ CellRangeAddress[] regions2 = cf2.getFormattingRanges();
+ assertEquals(1, regions2.length);
+ assertEquals("B9", regions2[0].formatAsString());
+
+ ConditionalFormattingRule rule3 = cf2.getRule(0);
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_FORMULA, rule3.getConditionType());
+ assertEquals(ComparisonOperator.NO_COMPARISON, rule3.getComparisonOperation());
+ assertEquals("$A$8>5", rule3.getFormula1());
+ assertNull(rule3.getFormula2());
+
+ FontFormatting fmt3 = rule3.getFontFormatting();
+// assertEquals(IndexedColors.RED.index, fmt3.getFontColorIndex());
+ assertTrue(fmt3.isBold());
+ assertTrue(fmt3.isItalic());
+
+ PatternFormatting fmt4 = rule3.getPatternFormatting();
+// assertEquals(IndexedColors.LIGHT_CORNFLOWER_BLUE.index, fmt4.getFillBackgroundColor());
+// assertEquals(IndexedColors.AUTOMATIC.index, fmt4.getFillForegroundColor());
+ assertEquals(PatternFormatting.NO_FILL, fmt4.getFillPattern());
+ // borders are not set
+ assertNull(rule3.getBorderFormatting());
+
+ ConditionalFormatting cf3 = sheetCF.getConditionalFormattingAt(2);
+ CellRangeAddress[] regions3 = cf3.getFormattingRanges();
+ assertEquals(1, regions3.length);
+ assertEquals("B1:B7", regions3[0].formatAsString());
+ assertEquals(2, cf3.getNumberOfRules());
+
+ ConditionalFormattingRule rule4 = cf3.getRule(0);
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule4.getConditionType());
+ assertEquals(ComparisonOperator.LE, rule4.getComparisonOperation());
+ assertEquals("\"AAA\"", rule4.getFormula1());
+ assertNull(rule4.getFormula2());
+
+ ConditionalFormattingRule rule5 = cf3.getRule(1);
+ assertEquals(ConditionalFormattingRule.CONDITION_TYPE_CELL_VALUE_IS, rule5.getConditionType());
+ assertEquals(ComparisonOperator.BETWEEN, rule5.getComparisonOperation());
+ assertEquals("\"A\"", rule5.getFormula1());
+ assertEquals("\"AAA\"", rule5.getFormula2());
+ }
+
+
+ public void testCreateFontFormatting() {
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
+ FontFormatting fontFmt = rule1.createFontFormatting();
+ assertFalse(fontFmt.isItalic());
+ assertFalse(fontFmt.isBold());
+ fontFmt.setFontStyle(true, true);
+ assertTrue(fontFmt.isItalic());
+ assertTrue(fontFmt.isBold());
+
+ assertEquals(-1, fontFmt.getFontHeight()); // not modified
+ fontFmt.setFontHeight(200);
+ assertEquals(200, fontFmt.getFontHeight());
+ fontFmt.setFontHeight(100);
+ assertEquals(100, fontFmt.getFontHeight());
+
+ assertEquals(FontFormatting.SS_NONE, fontFmt.getEscapementType());
+ fontFmt.setEscapementType(FontFormatting.SS_SUB);
+ assertEquals(FontFormatting.SS_SUB, fontFmt.getEscapementType());
+ fontFmt.setEscapementType(FontFormatting.SS_NONE);
+ assertEquals(FontFormatting.SS_NONE, fontFmt.getEscapementType());
+ fontFmt.setEscapementType(FontFormatting.SS_SUPER);
+ assertEquals(FontFormatting.SS_SUPER, fontFmt.getEscapementType());
+
+ assertEquals(FontFormatting.U_NONE, fontFmt.getUnderlineType());
+ fontFmt.setUnderlineType(FontFormatting.U_SINGLE);
+ assertEquals(FontFormatting.U_SINGLE, fontFmt.getUnderlineType());
+ fontFmt.setUnderlineType(FontFormatting.U_NONE);
+ assertEquals(FontFormatting.U_NONE, fontFmt.getUnderlineType());
+ fontFmt.setUnderlineType(FontFormatting.U_DOUBLE);
+ assertEquals(FontFormatting.U_DOUBLE, fontFmt.getUnderlineType());
+
+ assertEquals(-1, fontFmt.getFontColorIndex());
+ fontFmt.setFontColorIndex(IndexedColors.RED.index);
+ assertEquals(IndexedColors.RED.index, fontFmt.getFontColorIndex());
+ fontFmt.setFontColorIndex(IndexedColors.AUTOMATIC.index);
+ assertEquals(IndexedColors.AUTOMATIC.index, fontFmt.getFontColorIndex());
+ fontFmt.setFontColorIndex(IndexedColors.BLUE.index);
+ assertEquals(IndexedColors.BLUE.index, fontFmt.getFontColorIndex());
+
+ ConditionalFormattingRule [] cfRules = { rule1 };
+
+ CellRangeAddress [] regions = { CellRangeAddress.valueOf("A1:A5") };
+
+ sheetCF.addConditionalFormatting(regions, cfRules);
+
+ // Verification
+ ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
+ assertNotNull(cf);
+
+ assertEquals(1, cf.getNumberOfRules());
+
+ FontFormatting r1fp = cf.getRule(0).getFontFormatting();
+ assertNotNull(r1fp);
+
+ assertTrue(r1fp.isItalic());
+ assertTrue(r1fp.isBold());
+ assertEquals(FontFormatting.SS_SUPER, r1fp.getEscapementType());
+ assertEquals(FontFormatting.U_DOUBLE, r1fp.getUnderlineType());
+ assertEquals(IndexedColors.BLUE.index, r1fp.getFontColorIndex());
+
+ }
+
+ public void testCreatePatternFormatting() {
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
+ PatternFormatting patternFmt = rule1.createPatternFormatting();
+
+ assertEquals(0, patternFmt.getFillBackgroundColor());
+ patternFmt.setFillBackgroundColor(IndexedColors.RED.index);
+ assertEquals(IndexedColors.RED.index, patternFmt.getFillBackgroundColor());
+
+ assertEquals(0, patternFmt.getFillForegroundColor());
+ patternFmt.setFillForegroundColor(IndexedColors.BLUE.index);
+ assertEquals(IndexedColors.BLUE.index, patternFmt.getFillForegroundColor());
+
+ assertEquals(PatternFormatting.NO_FILL, patternFmt.getFillPattern());
+ patternFmt.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
+ assertEquals(PatternFormatting.SOLID_FOREGROUND, patternFmt.getFillPattern());
+ patternFmt.setFillPattern(PatternFormatting.NO_FILL);
+ assertEquals(PatternFormatting.NO_FILL, patternFmt.getFillPattern());
+ patternFmt.setFillPattern(PatternFormatting.BRICKS);
+ assertEquals(PatternFormatting.BRICKS, patternFmt.getFillPattern());
+
+ ConditionalFormattingRule [] cfRules = { rule1 };
+
+ CellRangeAddress [] regions = { CellRangeAddress.valueOf("A1:A5") };
+
+ sheetCF.addConditionalFormatting(regions, cfRules);
+
+ // Verification
+ ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
+ assertNotNull(cf);
+
+ assertEquals(1, cf.getNumberOfRules());
+
+ PatternFormatting r1fp = cf.getRule(0).getPatternFormatting();
+ assertNotNull(r1fp);
+
+ assertEquals(IndexedColors.RED.index, r1fp.getFillBackgroundColor());
+ assertEquals(IndexedColors.BLUE.index, r1fp.getFillForegroundColor());
+ assertEquals(PatternFormatting.BRICKS, r1fp.getFillPattern());
+ }
+
+ public void testCreateBorderFormatting() {
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
+
+ ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
+ BorderFormatting borderFmt = rule1.createBorderFormatting();
+
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderBottom());
+ borderFmt.setBorderBottom(BorderFormatting.BORDER_DOTTED);
+ assertEquals(BorderFormatting.BORDER_DOTTED, borderFmt.getBorderBottom());
+ borderFmt.setBorderBottom(BorderFormatting.BORDER_NONE);
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderBottom());
+ borderFmt.setBorderBottom(BorderFormatting.BORDER_THICK);
+ assertEquals(BorderFormatting.BORDER_THICK, borderFmt.getBorderBottom());
+
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderTop());
+ borderFmt.setBorderTop(BorderFormatting.BORDER_DOTTED);
+ assertEquals(BorderFormatting.BORDER_DOTTED, borderFmt.getBorderTop());
+ borderFmt.setBorderTop(BorderFormatting.BORDER_NONE);
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderTop());
+ borderFmt.setBorderTop(BorderFormatting.BORDER_THICK);
+ assertEquals(BorderFormatting.BORDER_THICK, borderFmt.getBorderTop());
+
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderLeft());
+ borderFmt.setBorderLeft(BorderFormatting.BORDER_DOTTED);
+ assertEquals(BorderFormatting.BORDER_DOTTED, borderFmt.getBorderLeft());
+ borderFmt.setBorderLeft(BorderFormatting.BORDER_NONE);
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderLeft());
+ borderFmt.setBorderLeft(BorderFormatting.BORDER_THIN);
+ assertEquals(BorderFormatting.BORDER_THIN, borderFmt.getBorderLeft());
+
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderRight());
+ borderFmt.setBorderRight(BorderFormatting.BORDER_DOTTED);
+ assertEquals(BorderFormatting.BORDER_DOTTED, borderFmt.getBorderRight());
+ borderFmt.setBorderRight(BorderFormatting.BORDER_NONE);
+ assertEquals(BorderFormatting.BORDER_NONE, borderFmt.getBorderRight());
+ borderFmt.setBorderRight(BorderFormatting.BORDER_HAIR);
+ assertEquals(BorderFormatting.BORDER_HAIR, borderFmt.getBorderRight());
+
+ ConditionalFormattingRule [] cfRules = { rule1 };
+
+ CellRangeAddress [] regions = { CellRangeAddress.valueOf("A1:A5") };
+
+ sheetCF.addConditionalFormatting(regions, cfRules);
+
+ // Verification
+ ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
+ assertNotNull(cf);
+
+ assertEquals(1, cf.getNumberOfRules());
+
+ BorderFormatting r1fp = cf.getRule(0).getBorderFormatting();
+ assertNotNull(r1fp);
+ assertEquals(BorderFormatting.BORDER_THICK, r1fp.getBorderBottom());
+ assertEquals(BorderFormatting.BORDER_THICK, r1fp.getBorderTop());
+ assertEquals(BorderFormatting.BORDER_THIN, r1fp.getBorderLeft());
+ assertEquals(BorderFormatting.BORDER_HAIR, r1fp.getBorderRight());
+
+ }
+}
diff --git a/test-data/spreadsheet/WithConditionalFormatting.xls b/test-data/spreadsheet/WithConditionalFormatting.xls Binary files differnew file mode 100755 index 0000000000..b47782aa34 --- /dev/null +++ b/test-data/spreadsheet/WithConditionalFormatting.xls diff --git a/test-data/spreadsheet/WithConditionalFormatting.xlsx b/test-data/spreadsheet/WithConditionalFormatting.xlsx Binary files differnew file mode 100755 index 0000000000..f2d2923a02 --- /dev/null +++ b/test-data/spreadsheet/WithConditionalFormatting.xlsx |