diff options
9 files changed, 280 insertions, 20 deletions
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java index aa46315091..71920a9ff8 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java @@ -33,6 +33,7 @@ import org.apache.poi.ss.usermodel.ConditionFilterData; import org.apache.poi.ss.usermodel.ConditionFilterType; import org.apache.poi.ss.usermodel.ConditionType; import org.apache.poi.ss.usermodel.ConditionalFormattingRule; +import org.apache.poi.ss.usermodel.ExcelNumberFormat; /** * @@ -91,6 +92,14 @@ public final class HSSFConditionalFormattingRule implements ConditionalFormattin } return (CFRule12Record)cfRuleRecord; } + + /** + * Always null for HSSF records, until someone figures out where to find it + * @see org.apache.poi.ss.usermodel.ConditionalFormattingRule#getNumberFormat() + */ + public ExcelNumberFormat getNumberFormat() { + return null; + } private HSSFFontFormatting getFontFormatting(boolean create) { FontFormatting fontFormatting = cfRuleRecord.getFontFormatting(); diff --git a/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java b/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java index 1bab3e9cae..477aaaf64a 100644 --- a/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationConditionalFormatRule.java @@ -41,6 +41,7 @@ import org.apache.poi.ss.usermodel.ConditionFilterType; import org.apache.poi.ss.usermodel.ConditionType; import org.apache.poi.ss.usermodel.ConditionalFormatting; import org.apache.poi.ss.usermodel.ConditionalFormattingRule; +import org.apache.poi.ss.usermodel.ExcelNumberFormat; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; @@ -60,6 +61,9 @@ import org.apache.poi.ss.util.CellRangeAddress; * create whatever style objects they need, caching those at the application level. * Thus this class only caches values needed for evaluation, not display. */ +/** + * + */ public class EvaluationConditionalFormatRule implements Comparable<EvaluationConditionalFormatRule> { private final WorkbookEvaluator workbookEvaluator; @@ -82,6 +86,8 @@ public class EvaluationConditionalFormatRule implements Comparable<EvaluationCon private final String formula2; private final OperatorEnum operator; private final ConditionType type; + // cached for performance, to avoid reading the XMLBean every time a conditinally formatted cell is rendered + private final ExcelNumberFormat numberFormat; /** * @@ -108,11 +114,15 @@ public class EvaluationConditionalFormatRule implements Comparable<EvaluationCon this.regions = regions; formula1 = rule.getFormula1(); formula2 = rule.getFormula2(); + numberFormat = rule.getNumberFormat(); operator = OperatorEnum.values()[rule.getComparisonOperation()]; type = rule.getConditionType(); } + /** + * @return sheet + */ public Sheet getSheet() { return sheet; } @@ -124,17 +134,30 @@ public class EvaluationConditionalFormatRule implements Comparable<EvaluationCon return formatting; } + /** + * @return conditional formatting index + */ public int getFormattingIndex() { return formattingIndex; } /** + * @return Excel number format string to apply to matching cells, or null to keep the cell default + */ + public ExcelNumberFormat getNumberFormat() { + return numberFormat; + } + + /** * @return the rule */ public ConditionalFormattingRule getRule() { return rule; } + /** + * @return rule index + */ public int getRuleIndex() { return ruleIndex; } diff --git a/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java b/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java index da3896bd05..78b16ad87e 100644 --- a/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java +++ b/src/java/org/apache/poi/ss/usermodel/ConditionalFormattingRule.java @@ -78,6 +78,12 @@ public interface ConditionalFormattingRule { ColorScaleFormatting getColorScaleFormatting(); /** + * + * @return number format defined for this rule, or null if the cell default should be used + */ + ExcelNumberFormat getNumberFormat(); + + /** * Type of conditional formatting rule. * * @return the type of condition diff --git a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java index 4f4fae988d..bc976cc4ef 100644 --- a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java +++ b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java @@ -43,6 +43,7 @@ import java.util.regex.Pattern; import org.apache.poi.ss.format.CellFormat; import org.apache.poi.ss.format.CellFormatResult; +import org.apache.poi.ss.formula.ConditionalFormattingEvaluator; import org.apache.poi.ss.util.DateFormatConverter; import org.apache.poi.ss.util.NumberToTextConverter; import org.apache.poi.util.LocaleUtil; @@ -292,12 +293,20 @@ public class DataFormatter implements Observer { * @return A Format for the format String */ private Format getFormat(Cell cell) { - if ( cell.getCellStyle() == null) { + return getFormat(cell, null); + } + + private Format getFormat(Cell cell, ConditionalFormattingEvaluator cfEvaluator) { + if (cell == null) return null; + + ExcelNumberFormat numFmt = ExcelNumberFormat.from(cell, cfEvaluator); + + if ( numFmt == null) { return null; } - int formatIndex = cell.getCellStyle().getDataFormat(); - String formatStr = cell.getCellStyle().getDataFormatString(); + int formatIndex = numFmt.getIdx(); + String formatStr = numFmt.getFormat(); if(formatStr == null || formatStr.trim().length() == 0) { return null; } @@ -748,12 +757,17 @@ public class DataFormatter implements Observer { * Returns the formatted value of an Excel date as a <tt>String</tt> based * on the cell's <code>DataFormat</code>. i.e. "Thursday, January 02, 2003" * , "01/02/2003" , "02-Jan" , etc. + * <p/> + * If any conditional format rules apply, the highest priority with a number format is used. + * If no rules contain a number format, or no rules apply, the cell's style format is used. + * If the style does not have a format, the default date format is applied. * - * @param cell The cell - * @return a formatted date string + * @param cell + * @param cfEvaluator ConditionalFormattingEvaluator (if available) + * @return */ - private String getFormattedDateString(Cell cell) { - Format dateFormat = getFormat(cell); + private String getFormattedDateString(Cell cell, ConditionalFormattingEvaluator cfEvaluator) { + Format dateFormat = getFormat(cell, cfEvaluator); if(dateFormat instanceof ExcelStyleDateFormatter) { // Hint about the raw excel value ((ExcelStyleDateFormatter)dateFormat).setDateToBeFormatted( @@ -769,13 +783,17 @@ public class DataFormatter implements Observer { * based on the cell's <code>DataFormat</code>. Supported formats include * currency, percents, decimals, phone number, SSN, etc.: * "61.54%", "$100.00", "(800) 555-1234". - * + * <p/> + * Format comes from either the highest priority conditional format rule with a + * specified format, or from the cell style. + * * @param cell The cell + * @param cfEvaluator if available, or null * @return a formatted number string */ - private String getFormattedNumberString(Cell cell) { + private String getFormattedNumberString(Cell cell, ConditionalFormattingEvaluator cfEvaluator) { - Format numberFormat = getFormat(cell); + Format numberFormat = getFormat(cell, cfEvaluator); double d = cell.getNumericCellValue(); if (numberFormat == null) { return String.valueOf(d); @@ -863,7 +881,7 @@ public class DataFormatter implements Observer { /** * <p> * Returns the formatted value of a cell as a <tt>String</tt> regardless - * of the cell type. If the Excel format pattern cannot be parsed then the + * of the cell type. If the Excel number format pattern cannot be parsed then the * cell value will be formatted using a default format. * </p> * <p>When passed a null or blank cell, this method will return an empty @@ -878,6 +896,37 @@ public class DataFormatter implements Observer { * @return a string value of the cell */ public String formatCellValue(Cell cell, FormulaEvaluator evaluator) { + return formatCellValue(cell, evaluator, null); + } + + /** + * <p> + * Returns the formatted value of a cell as a <tt>String</tt> regardless + * of the cell type. If the Excel number format pattern cannot be parsed then the + * cell value will be formatted using a default format. + * </p> + * <p>When passed a null or blank cell, this method will return an empty + * String (""). Formula cells will be evaluated using the given + * {@link FormulaEvaluator} if the evaluator is non-null. If the + * evaluator is null, then the formula String will be returned. The caller + * is responsible for setting the currentRow on the evaluator + *</p> + * <p> + * When a ConditionalFormattingEvaluator is present, it is checked first to see + * if there is a number format to apply. If multiple rules apply, the last one is used. + * If no ConditionalFormattingEvaluator is present, no rules apply, or the applied + * rules do not define a format, the cell's style format is used. + * </p> + * <p> + * The two evaluators should be from the same context, to avoid inconsistencies in cached values. + *</p> + * + * @param cell The cell (can be null) + * @param evaluator The FormulaEvaluator (can be null) + * @param cfEvaluator ConditionalFormattingEvaluator (can be null) + * @return a string value of the cell + */ + public String formatCellValue(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator) { localeChangedObservable.checkForLocaleChange(); if (cell == null) { @@ -894,10 +943,10 @@ public class DataFormatter implements Observer { switch (cellType) { case NUMERIC : - if (DateUtil.isCellDateFormatted(cell)) { - return getFormattedDateString(cell); + if (DateUtil.isCellDateFormatted(cell, cfEvaluator)) { + return getFormattedDateString(cell, cfEvaluator); } - return getFormattedNumberString(cell); + return getFormattedNumberString(cell, cfEvaluator); case STRING : return cell.getRichStringCellValue().getString(); diff --git a/src/java/org/apache/poi/ss/usermodel/DateUtil.java b/src/java/org/apache/poi/ss/usermodel/DateUtil.java index 79d94c2f8d..1ba0e133ff 100644 --- a/src/java/org/apache/poi/ss/usermodel/DateUtil.java +++ b/src/java/org/apache/poi/ss/usermodel/DateUtil.java @@ -19,10 +19,14 @@ package org.apache.poi.ss.usermodel; import java.util.Calendar; +import java.util.Collections; import java.util.Date; +import java.util.List; import java.util.TimeZone; import java.util.regex.Pattern; +import org.apache.poi.ss.formula.ConditionalFormattingEvaluator; +import org.apache.poi.ss.formula.EvaluationConditionalFormatRule; import org.apache.poi.util.LocaleUtil; /** @@ -356,12 +360,33 @@ public class DateUtil { * date formatting characters (ymd-/), which covers most * non US date formats. * + * @param numFmt The number format index and string expression, or null if not specified + * @return true if it is a valid date format, false if not or null + * @see #isInternalDateFormat(int) + */ + public static boolean isADateFormat(ExcelNumberFormat numFmt) { + + if (numFmt == null) return false; + + return isADateFormat(numFmt.getIdx(), numFmt.getFormat()); + } + + /** + * Given a format ID and its format String, will check to see if the + * format represents a date format or not. + * Firstly, it will check to see if the format ID corresponds to an + * internal excel date format (eg most US date formats) + * If not, it will check to see if the format string only contains + * date formatting characters (ymd-/), which covers most + * non US date formats. + * * @param formatIndex The index of the format, eg from ExtendedFormatRecord.getFormatIndex * @param formatString The format string, eg from FormatRecord.getFormatString + * @return true if it is a valid date format, false if not or null * @see #isInternalDateFormat(int) */ - public static boolean isADateFormat(int formatIndex, String formatString) { + // First up, is this an internal date format? if(isInternalDateFormat(formatIndex)) { cache(formatString, formatIndex, true); @@ -492,23 +517,40 @@ public class DateUtil { * Check if a cell contains a date * Since dates are stored internally in Excel as double values * we infer it is a date if it is formatted as such. + * @param cell + * @return true if it looks like a date * @see #isADateFormat(int, String) * @see #isInternalDateFormat(int) */ public static boolean isCellDateFormatted(Cell cell) { + return isCellDateFormatted(cell, null); + } + + /** + * Check if a cell contains a date + * Since dates are stored internally in Excel as double values + * we infer it is a date if it is formatted as such. + * Format is determined from applicable conditional formatting, if + * any, or cell style. + * @param cell + * @param cfEvaluator if available, or null + * @return true if it looks like a date + * @see #isADateFormat(int, String) + * @see #isInternalDateFormat(int) + */ + public static boolean isCellDateFormatted(Cell cell, ConditionalFormattingEvaluator cfEvaluator) { if (cell == null) return false; boolean bDate = false; double d = cell.getNumericCellValue(); if ( DateUtil.isValidExcelDate(d) ) { - CellStyle style = cell.getCellStyle(); - if(style==null) return false; - int i = style.getDataFormat(); - String f = style.getDataFormatString(); - bDate = isADateFormat(i, f); + ExcelNumberFormat nf = ExcelNumberFormat.from(cell, cfEvaluator); + if(nf==null) return false; + bDate = isADateFormat(nf); } return bDate; } + /** * Check if a cell contains a date, checking only for internal * excel date formats. diff --git a/src/java/org/apache/poi/ss/usermodel/ExcelNumberFormat.java b/src/java/org/apache/poi/ss/usermodel/ExcelNumberFormat.java new file mode 100644 index 0000000000..88f93915bb --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/ExcelNumberFormat.java @@ -0,0 +1,78 @@ +package org.apache.poi.ss.usermodel; + +import java.util.List; + +import org.apache.poi.ss.formula.ConditionalFormattingEvaluator; +import org.apache.poi.ss.formula.EvaluationConditionalFormatRule; + +/** + * Object to hold a number format index and string, for various formatting evaluations + */ +public class ExcelNumberFormat { + + private final int idx; + private final String format; + + /** + * @param style + * @return null if the style is null, instance from style data format values otherwise + */ + public static ExcelNumberFormat from(CellStyle style) { + if (style == null) return null; + return new ExcelNumberFormat(style.getDataFormat(), style.getDataFormatString()); + } + + /** + * @param cell cell to extract format from + * @param cfEvaluator ConditionalFormattingEvaluator to use, or null if none in this context + * @return number format from highest-priority rule with a number format, or the cell style, or null if none of the above apply/are defined + */ + public static ExcelNumberFormat from(Cell cell, ConditionalFormattingEvaluator cfEvaluator) { + if (cell == null) return null; + + ExcelNumberFormat nf = null; + + if (cfEvaluator != null) { + // first one wins (priority order, per Excel help) + List<EvaluationConditionalFormatRule> rules = cfEvaluator.getConditionalFormattingForCell(cell); + for (EvaluationConditionalFormatRule rule : rules) { + nf = rule.getNumberFormat(); + if (nf != null) break; + } + } + if (nf == null) { + CellStyle style = cell.getCellStyle(); + nf = ExcelNumberFormat.from(style); + } + return nf; + } + + /** + * Use this carefully, prefer factory methods to ensure id/format relationships are not broken or confused. + * Left public so {@link ConditionalFormattingRule#getNumberFormat()} implementations can use it. + * @param idx Excel number format index, either a built-in or a higher custom # mapped in the workbook style table + * @param format Excel number format string for the index + */ + public ExcelNumberFormat(int idx, String format) { + this.idx = idx; + this.format = format; + } + + + + /** + * + * @return Excel number format index, either a built-in or a higher custom # mapped in the workbook style table + */ + public int getIdx() { + return idx; + } + + /** + * + * @return Excel number format string for the index + */ + public String getFormat() { + return format; + } +} diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java index 69816b7fac..5ab6c299ac 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFConditionalFormattingRule.java @@ -326,6 +326,18 @@ public class XSSFConditionalFormattingRule implements ConditionalFormattingRule } /** + * Return the number format from the dxf style record if present, null if not + * @see org.apache.poi.ss.usermodel.ConditionalFormattingRule#getNumberFormat() + */ + public ExcelNumberFormat getNumberFormat() { + CTDxf dxf = getDxf(false); + if(dxf == null || !dxf.isSetNumFmt()) return null; + + CTNumFmt numFmt = dxf.getNumFmt(); + return new ExcelNumberFormat((int) numFmt.getNumFmtId(), numFmt.getFormatCode()); + } + + /** * Type of conditional formatting rule. */ @Override diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java index c7179531d8..b4c12f8cbe 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java @@ -21,11 +21,19 @@ import static org.junit.Assert.*; import java.io.IOException; +import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.ss.formula.ConditionalFormattingEvaluator; +import org.apache.poi.ss.formula.WorkbookEvaluatorProvider; import org.apache.poi.ss.usermodel.BaseTestDataFormat; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; +import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.DataFormat; +import org.apache.poi.ss.usermodel.DataFormatter; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.XSSFITestDataProvider; import org.apache.poi.xssf.XSSFTestDataSamples; import org.junit.Test; @@ -108,4 +116,37 @@ public final class TestXSSFDataFormat extends BaseTestDataFormat { wb2.close(); wb1.close(); } + + @Test + public void testConditionalFormattingEvaluation() throws IOException { + final Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61060-conditional-number-formatting.xlsx"); + + final DataFormatter formatter = new DataFormatter(); + final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + final ConditionalFormattingEvaluator cfEvaluator = new ConditionalFormattingEvaluator(wb, (WorkbookEvaluatorProvider) evaluator); + + CellReference ref = new CellReference("A1"); + Cell cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol()); + assertEquals("0.10", formatter.formatCellValue(cell, evaluator, cfEvaluator)); + // verify cell format without the conditional rule applied + assertEquals("0.1", formatter.formatCellValue(cell, evaluator)); + + ref = new CellReference("A3"); + cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol()); + assertEquals("-2.00E+03", formatter.formatCellValue(cell, evaluator, cfEvaluator)); + // verify cell format without the conditional rule applied + assertEquals("-2000", formatter.formatCellValue(cell, evaluator)); + + ref = new CellReference("A4"); + cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol()); + assertEquals("100", formatter.formatCellValue(cell, evaluator, cfEvaluator)); + + ref = new CellReference("A5"); + cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol()); + assertEquals("$1,000", formatter.formatCellValue(cell, evaluator, cfEvaluator)); + // verify cell format without the conditional rule applied + assertEquals("1000", formatter.formatCellValue(cell, evaluator)); + + wb.close(); + } } diff --git a/test-data/spreadsheet/61060-conditional-number-formatting.xlsx b/test-data/spreadsheet/61060-conditional-number-formatting.xlsx Binary files differnew file mode 100644 index 0000000000..95072c2464 --- /dev/null +++ b/test-data/spreadsheet/61060-conditional-number-formatting.xlsx |