[patch] Patch for Conditional Formatting

diff.txt (text/plain), 11.71 KB, created by Dmitriy Kumshayev on 2008-03-18 21:56:32 UTC

(hide)

Description: Patch for Conditional Formatting

Filename:

MIME Type:

Creator: Dmitriy Kumshayev

Created: 2008-03-18 21:56:32 UTC

Size: 11.71 KB

patch

obsolete

>Index: src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java >=================================================================== >--- src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (revision 638692) >+++ src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (working copy) >@@ -27,6 +27,7 @@ > import org.apache.poi.hssf.model.Sheet; > import org.apache.poi.hssf.model.Workbook; > import org.apache.poi.hssf.record.*; >+import org.apache.poi.hssf.record.aggregates.CFRecordsAggregate; > import org.apache.poi.hssf.record.formula.Ptg; > import org.apache.poi.hssf.record.formula.ReferencePtg; > import org.apache.poi.hssf.util.HSSFCellRangeAddress; >@@ -1830,4 +1831,161 @@ > return null; > } > >+ >+ /** >+ * A factory method allowing to create a conditional formatting rule >+ * with a cell comparison operator and >+ * formatting rules such as font format, border format and pattern format >+ * >+ * @param comparisonOperation - one of the following values: <p> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_BETWEEN}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_BETWEEN}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_EQUAL}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_EQUAL}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_GT}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_LT}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_GE}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_LE}</li> >+ * </p> >+ * @param formula1 - formula for the valued, compared with the cell >+ * @param formula2 - second formula (only used with >+ * {@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_BETWEEN}) and >+ * {@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_BETWEEN} operations) >+ * @param fontFmt - font formatting rules >+ * @param bordFmt - border formatting rules >+ * @param patternFmt - pattern formatting rules >+ * @return >+ * >+ */ >+ public HSSFConditionalFormattingRule createConditionalFormattingRule( >+ byte comparisonOperation, >+ String formula1, >+ String formula2, >+ HSSFFontFormatting fontFmt, >+ HSSFBorderFormatting bordFmt, >+ HSSFPatternFormatting patternFmt) >+ { >+ HSSFConditionalFormattingRule cf = new HSSFConditionalFormattingRule(workbook); >+ cf.setFontFormatting(fontFmt); >+ cf.setBorderFormatting(bordFmt); >+ cf.setPatternFormatting(patternFmt); >+ cf.setCellComparisonCondition(comparisonOperation, formula1, formula2); >+ return cf; >+ } >+ >+ /** >+ * A factory method allowing to create a conditional formatting rule with a formula >+ * and formatting rules such as font format, border format and pattern format. <br> >+ * >+ * The formatting rules are applied by Excel when the value of the formula not equal to 0. >+ * >+ * @param comparisonOperation - one of the following values: <p> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_BETWEEN}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_BETWEEN}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_EQUAL}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_EQUAL}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_GT}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_LT}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_GE}</li> >+ * <li>{@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_LE}</li> >+ * </p> >+ * @param formula1 - formula for the valued, compared with the cell >+ * @param formula2 - second formula (only used with >+ * {@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_BETWEEN}) and >+ * {@link HSSFConditionalFormattingRule#COMPARISON_OPERATOR_NOT_BETWEEN} operations) >+ * @param fontFmt - font formatting rules >+ * @param bordFmt - border formatting rules >+ * @param patternFmt - pattern formatting rules >+ * @return >+ * >+ */ >+ public HSSFConditionalFormattingRule createConditionalFormattingRule( >+ String formula, >+ HSSFFontFormatting fontFmt, >+ HSSFBorderFormatting bordFmt, >+ HSSFPatternFormatting patternFmt) >+ { >+ HSSFConditionalFormattingRule cf = new HSSFConditionalFormattingRule(workbook); >+ cf.setFontFormatting(fontFmt); >+ cf.setBorderFormatting(bordFmt); >+ cf.setPatternFormatting(patternFmt); >+ cf.setFormulaCondition(formula); >+ return cf; >+ } >+ >+ /** >+ * 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( HSSFConditionalFormatting cf ) >+ { >+ HSSFConditionalFormatting cfClone = new HSSFConditionalFormatting(this,cf.cfAggregate.cloneCFAggregate()); >+ cfClone.sheet=this; >+ return sheet.addConditionalFormatting(cfClone.cfAggregate); >+ } >+ >+ /** >+ * Allows to 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 >+ */ >+ >+ public int addConditionalFormatting( Region [] regions, HSSFConditionalFormattingRule [] cfRules ) >+ { >+ HSSFConditionalFormatting cf = new HSSFConditionalFormatting(this); >+ cf.setFormattingRegions(regions); >+ if( cfRules != null ) >+ { >+ for( int i=0; i!= cfRules.length; i++ ) >+ { >+ cf.addConditionalFormat(cfRules[i]); >+ } >+ } >+ return sheet.addConditionalFormatting(cf.cfAggregate); >+ } >+ >+ /** >+ * gets Conditional Formatting object at a particular index >+ * @param index of the Conditional Formatting object to fetch >+ * @return Conditional Formatting object >+ */ >+ >+ public HSSFConditionalFormatting getConditionalFormattingAt(int index) >+ { >+ CFRecordsAggregate cf = sheet.getCFRecordsAggregateAt(index); >+ if( cf != null ) >+ { >+ return new HSSFConditionalFormatting(this,cf); >+ } >+ return null; >+ } >+ >+ /** >+ * @return number of Conditional Formatting objects of the sheet >+ */ >+ public int getNumConditionalFormattings() >+ { >+ return sheet.getNumConditionalFormattings(); >+ } >+ >+ /** >+ * removes a Conditional Formatting object by index >+ * @param index of a Conditional Formatting object to remove >+ */ >+ public void removeConditionalFormatting(int index) >+ { >+ sheet.removeConditionalFormatting(index); >+ } >+ > } >Index: src/java/org/apache/poi/hssf/model/Sheet.java >=================================================================== >--- src/java/org/apache/poi/hssf/model/Sheet.java (revision 638692) >+++ src/java/org/apache/poi/hssf/model/Sheet.java (working copy) >@@ -24,6 +24,7 @@ > import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; > import org.apache.poi.hssf.record.aggregates.RowRecordsAggregate; > import org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate; >+import org.apache.poi.hssf.record.aggregates.CFRecordsAggregate; > import org.apache.poi.hssf.record.formula.Ptg; > import org.apache.poi.hssf.util.PaneInformation; > >@@ -96,6 +97,7 @@ > protected ObjectProtectRecord objprotect = null; > protected ScenarioProtectRecord scenprotect = null; > protected PasswordRecord password = null; >+ protected List condFormatting = new ArrayList();; > > /** Add an UncalcedRecord if not true indicating formulas have not been calculated */ > protected boolean uncalced = false; >@@ -184,6 +186,17 @@ > retval.merged = ( MergeCellsRecord ) rec; > retval.numMergedRegions += retval.merged.getNumAreas(); > } >+ else if ( rec.getSid() == CFHeaderRecord.sid ) >+ { >+ CFRecordsAggregate cfAgg = CFRecordsAggregate.createCFAggregate(recs, k); >+ retval.condFormatting.add(cfAgg); >+ rec = cfAgg; >+ } >+ else if ( rec.getSid() == CFRuleRecord.sid ) >+ { >+ // Skip it since it is processed by CFRecordsAggregate >+ rec = null; >+ } > else if (rec.getSid() == ColumnInfoRecord.sid) > { > ColumnInfoRecord col = (ColumnInfoRecord)rec; >@@ -604,7 +617,67 @@ > { > return numMergedRegions; > } >+ // Find correct position to add new CF record >+ private int findConditionalFormattingPosition() >+ { >+ // This is default. >+ // If the algorithm does not find the right position, >+ // this one will be used (this is a position before EOF record) >+ int index = records.size()-2; >+ >+ for( int i=index; i>=0; i-- ) >+ { >+ Record rec = (Record)records.get(i); >+ short sid = rec.getSid(); >+ >+ // CFRecordsAggregate records already exist, just add to the end >+ if (rec instanceof CFRecordsAggregate) { return i+1; } >+ >+ if( sid == (short)0x00ef ) { return i+1; }// PHONETICPR >+ if( sid == (short)0x015f ) { return i+1; }// LABELRANGES >+ if( sid == MergeCellsRecord.sid ) { return i+1; } >+ if( sid == (short)0x0099 ) { return i+1; }// STANDARDWIDTH >+ if( sid == SelectionRecord.sid ) { return i+1; } >+ if( sid == PaneRecord.sid ) { return i+1; } >+ if( sid == SCLRecord.sid ) { return i+1; } >+ if( sid == WindowTwoRecord.sid ) { return i+1; } >+ } >+ >+ return index; >+ } > >+ public int addConditionalFormatting(CFRecordsAggregate cfAggregate) >+ { >+ int index = findConditionalFormattingPosition(); >+ records.add(index, cfAggregate); >+ condFormatting.add(cfAggregate); >+ return condFormatting.size()-1; >+ } >+ >+ public void removeConditionalFormatting(int index) >+ { >+ if (index >= 0 && index <= condFormatting.size()-1 ) >+ { >+ CFRecordsAggregate cfAggregate = getCFRecordsAggregateAt(index); >+ records.remove(cfAggregate); >+ condFormatting.remove(index); >+ } >+ } >+ >+ public CFRecordsAggregate getCFRecordsAggregateAt(int index) >+ { >+ if (index >= 0 && index <= condFormatting.size()-1 ) >+ { >+ return (CFRecordsAggregate) condFormatting.get(index); >+ } >+ return null; >+ } >+ >+ public int getNumConditionalFormattings() >+ { >+ return condFormatting.size(); >+ } >+ > /** > * Returns the number of low level binary records in this sheet. This adjusts things for the so called > * AgregateRecords. >Index: src/java/org/apache/poi/hssf/record/RecordFactory.java >=================================================================== >--- src/java/org/apache/poi/hssf/record/RecordFactory.java (revision 638692) >+++ src/java/org/apache/poi/hssf/record/RecordFactory.java (working copy) >@@ -82,6 +82,8 @@ > SupBookRecord.class, > CRNCountRecord.class, > CRNRecord.class, >+ CFHeaderRecord.class, >+ CFRuleRecord.class, > }; > } > private static Map recordsMap = recordsToMap(records); View Attachment As Diff View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]