You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2011/07/29 06:47:32 UTC
svn commit: r1152099 [1/2] - in /poi/trunk: src/documentation/content/xdocs/
src/documentation/content/xdocs/spreadsheet/
src/examples/src/org/apache/poi/ss/examples/
src/java/org/apache/poi/hssf/usermodel/
src/java/org/apache/poi/ss/usermodel/ src/oox...
Author: yegor
Date: Fri Jul 29 04:47:25 2011
New Revision: 1152099
URL: http://svn.apache.org/viewvc?rev=1152099&view=rev
Log:
support for conditional formatting in XSSF including docs and examples
Added:
poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java (with props)
poi/trunk/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/FontFormatting.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFontFormatting.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPatternFormatting.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheetConditionalFormatting.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFConditionalFormatting.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestConditionalFormatting.java
poi/trunk/test-data/spreadsheet/WithConditionalFormatting.xls (with props)
poi/trunk/test-data/spreadsheet/WithConditionalFormatting.xlsx (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/IndexedColors.java
poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFConditionalFormatting.java
Modified: poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/spreadsheet/quick-guide.xml Fri Jul 29 04:47:25 2011
@@ -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>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Jul 29 04:47:25 2011
@@ -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>
Added: poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java (added)
+++ poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java Fri Jul 29 04:47:25 2011
@@ -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)");
+ }
+}
Propchange: poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFBorderFormatting.java Fri Jul 29 04:47:25 2011
@@ -27,38 +27,8 @@ import org.apache.poi.hssf.record.cf.Bor
* @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;
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormatting.java Fri Jul 29 04:47:25 2011
@@ -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 HSSFConditionalFormat
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 HSSFConditionalFormat
cfAggregate.addRule(cfRule.getCfRuleRecord());
}
+ public void addRule(ConditionalFormattingRule cfRule){
+ addRule((HSSFConditionalFormattingRule)cfRule);
+ }
+
/**
* @return the Conditional Formatting rule at position idx.
*/
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java Fri Jul 29 04:47:25 2011
@@ -24,6 +24,7 @@ import org.apache.poi.hssf.record.cf.Bor
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;
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFontFormatting.java Fri Jul 29 04:47:25 2011
@@ -26,24 +26,18 @@ import org.apache.poi.hssf.record.cf.Fon
* @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;
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFPatternFormatting.java Fri Jul 29 04:47:25 2011
@@ -26,47 +26,8 @@ import org.apache.poi.hssf.record.cf.Pat
* @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;
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheetConditionalFormatting.java Fri Jul 29 04:47:25 2011
@@ -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.SpreadsheetVers
*
* @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 HSSFSheetConditionalF
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 HSSFSheetConditionalF
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 HSSFSheetConditionalF
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 HSSFSheetConditionalF
});
}
+ public int addConditionalFormatting(CellRangeAddress[] regions,
+ ConditionalFormattingRule rule1,
+ ConditionalFormattingRule rule2)
+ {
+ return addConditionalFormatting(regions,
+ (HSSFConditionalFormattingRule)rule1,
+ (HSSFConditionalFormattingRule)rule2
+ );
+ }
+
/**
* gets Conditional Formatting object at a particular index
*
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/BorderFormatting.java Fri Jul 29 04:47:25 2011
@@ -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);
+}
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ComparisonOperator.java Fri Jul 29 04:47:25 2011
@@ -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;
+}
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormatting.java Fri Jul 29 04:47:25 2011
@@ -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();
+
+
+}
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java Fri Jul 29 04:47:25 2011
@@ -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();
+}
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/FontFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/FontFormatting.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/FontFormatting.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/FontFormatting.java Fri Jul 29 04:47:25 2011
@@ -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();
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/IndexedColors.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/IndexedColors.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/IndexedColors.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/IndexedColors.java Fri Jul 29 04:47:25 2011
@@ -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;
}
}
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/PatternFormatting.java Fri Jul 29 04:47:25 2011
@@ -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);
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Sheet.java Fri Jul 29 04:47:25 2011
@@ -919,5 +919,12 @@ public interface Sheet extends Iterable<
* @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();
+
}
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/SheetConditionalFormatting.java Fri Jul 29 04:47:25 2011
@@ -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);
+}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java Fri Jul 29 04:47:25 2011
@@ -346,6 +346,9 @@ public class StylesTable extends POIXMLD
public CTStylesheet getCTStylesheet() {
return doc.getStyleSheet();
}
+ public int _getDXfsSize() {
+ return dxfs.size();
+ }
/**
@@ -497,14 +500,11 @@ public class StylesTable extends POIXMLD
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();
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java?rev=1152099&r1=1152098&r2=1152099&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java Fri Jul 29 04:47:25 2011
@@ -1196,6 +1196,11 @@ public class SXSSFSheet implements Sheet
{
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().
Added: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java (added)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFBorderFormatting.java Fri Jul 29 04:47:25 2011
@@ -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);
+ }
+
+}
Added: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java?rev=1152099&view=auto
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java (added)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormatting.java Fri Jul 29 04:47:25 2011
@@ -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();
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org