diff options
Diffstat (limited to 'src/java/org/apache')
12 files changed, 2841 insertions, 0 deletions
diff --git a/src/java/org/apache/poi/ss/format/CellDateFormatter.java b/src/java/org/apache/poi/ss/format/CellDateFormatter.java new file mode 100644 index 0000000000..5007305e21 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellDateFormatter.java @@ -0,0 +1,213 @@ +/* ==================================================================== + 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.format; + +import java.text.AttributedCharacterIterator; +import java.text.CharacterIterator; +import java.text.DateFormat; +import java.text.SimpleDateFormat; +import java.util.Calendar; +import java.util.Date; +import java.util.Formatter; +import java.util.regex.Matcher; + +/** + * Formats a date value. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellDateFormatter extends CellFormatter { + private boolean amPmUpper; + private boolean showM; + private boolean showAmPm; + private final DateFormat dateFmt; + private String sFmt; + + private static final long EXCEL_EPOCH_TIME; + private static final Date EXCEL_EPOCH_DATE; + + private static final CellFormatter SIMPLE_DATE = new CellDateFormatter( + "mm/d/y"); + + static { + Calendar c = Calendar.getInstance(); + c.set(1904, 0, 1, 0, 0, 0); + EXCEL_EPOCH_DATE = c.getTime(); + EXCEL_EPOCH_TIME = c.getTimeInMillis(); + } + + private class DatePartHandler implements CellFormatPart.PartHandler { + private int mStart = -1; + private int mLen; + private int hStart = -1; + private int hLen; + + public String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc) { + + int pos = desc.length(); + char firstCh = part.charAt(0); + switch (firstCh) { + case 's': + case 'S': + if (mStart >= 0) { + for (int i = 0; i < mLen; i++) + desc.setCharAt(mStart + i, 'm'); + mStart = -1; + } + return part.toLowerCase(); + + case 'h': + case 'H': + mStart = -1; + hStart = pos; + hLen = part.length(); + return part.toLowerCase(); + + case 'd': + case 'D': + mStart = -1; + if (part.length() <= 2) + return part.toLowerCase(); + else + return part.toLowerCase().replace('d', 'E'); + + case 'm': + case 'M': + mStart = pos; + mLen = part.length(); + return part.toUpperCase(); + + case 'y': + case 'Y': + mStart = -1; + if (part.length() == 3) + part = "yyyy"; + return part.toLowerCase(); + + case '0': + mStart = -1; + int sLen = part.length(); + sFmt = "%0" + (sLen + 2) + "." + sLen + "f"; + return part.replace('0', 'S'); + + case 'a': + case 'A': + case 'p': + case 'P': + if (part.length() > 1) { + // am/pm marker + mStart = -1; + showAmPm = true; + showM = Character.toLowerCase(part.charAt(1)) == 'm'; + // For some reason "am/pm" becomes AM or PM, but "a/p" becomes a or p + amPmUpper = showM || Character.isUpperCase(part.charAt(0)); + + return "a"; + } + //noinspection fallthrough + + default: + return null; + } + } + + public void finish(StringBuffer toAppendTo) { + if (hStart >= 0 && !showAmPm) { + for (int i = 0; i < hLen; i++) { + toAppendTo.setCharAt(hStart + i, 'H'); + } + } + } + } + + /** + * Creates a new date formatter with the given specification. + * + * @param format The format. + */ + public CellDateFormatter(String format) { + super(format); + DatePartHandler partHandler = new DatePartHandler(); + StringBuffer descBuf = CellFormatPart.parseFormat(format, + CellFormatType.DATE, partHandler); + partHandler.finish(descBuf); + dateFmt = new SimpleDateFormat(descBuf.toString()); + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object value) { + if (value == null) + value = 0.0; + if (value instanceof Number) { + Number num = (Number) value; + double v = num.doubleValue(); + if (v == 0.0) + value = EXCEL_EPOCH_DATE; + else + value = new Date((long) (EXCEL_EPOCH_TIME + v)); + } + + AttributedCharacterIterator it = dateFmt.formatToCharacterIterator( + value); + boolean doneAm = false; + boolean doneMillis = false; + + it.first(); + for (char ch = it.first(); + ch != CharacterIterator.DONE; + ch = it.next()) { + if (it.getAttribute(DateFormat.Field.MILLISECOND) != null) { + if (!doneMillis) { + Date dateObj = (Date) value; + int pos = toAppendTo.length(); + Formatter formatter = new Formatter(toAppendTo); + long msecs = dateObj.getTime() % 1000; + formatter.format(LOCALE, sFmt, msecs / 1000.0); + toAppendTo.delete(pos, pos + 2); + doneMillis = true; + } + } else if (it.getAttribute(DateFormat.Field.AM_PM) != null) { + if (!doneAm) { + if (showAmPm) { + if (amPmUpper) { + toAppendTo.append(Character.toUpperCase(ch)); + if (showM) + toAppendTo.append('M'); + } else { + toAppendTo.append(Character.toLowerCase(ch)); + if (showM) + toAppendTo.append('m'); + } + } + doneAm = true; + } + } else { + toAppendTo.append(ch); + } + } + } + + /** + * {@inheritDoc} + * <p/> + * For a date, this is <tt>"mm/d/y"</tt>. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + SIMPLE_DATE.formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellElapsedFormatter.java b/src/java/org/apache/poi/ss/format/CellElapsedFormatter.java new file mode 100644 index 0000000000..07ebadf2a0 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellElapsedFormatter.java @@ -0,0 +1,215 @@ +/* ==================================================================== + 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.format; + +import java.util.ArrayList; +import java.util.Formatter; +import java.util.List; +import java.util.ListIterator; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +/** + * This class implements printing out an elapsed time format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellElapsedFormatter extends CellFormatter { + private final List<TimeSpec> specs; + private TimeSpec topmost; + private final String printfFmt; + + private static final Pattern PERCENTS = Pattern.compile("%"); + + private static final double HOUR__FACTOR = 1.0 / 24.0; + private static final double MIN__FACTOR = HOUR__FACTOR / 60.0; + private static final double SEC__FACTOR = MIN__FACTOR / 60.0; + + private static class TimeSpec { + final char type; + final int pos; + final int len; + final double factor; + double modBy; + + public TimeSpec(char type, int pos, int len, double factor) { + this.type = type; + this.pos = pos; + this.len = len; + this.factor = factor; + modBy = 0; + } + + public long valueFor(double elapsed) { + double val; + if (modBy == 0) + val = elapsed / factor; + else + val = elapsed / factor % modBy; + if (type == '0') + return Math.round(val); + else + return (long) val; + } + } + + private class ElapsedPartHandler implements CellFormatPart.PartHandler { + // This is the one class that's directly using printf, so it can't use + // the default handling for quoted strings and special characters. The + // only special character for this is '%', so we have to handle all the + // quoting in this method ourselves. + + public String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc) { + + int pos = desc.length(); + char firstCh = part.charAt(0); + switch (firstCh) { + case '[': + if (part.length() < 3) + break; + if (topmost != null) + throw new IllegalArgumentException( + "Duplicate '[' times in format"); + part = part.toLowerCase(); + int specLen = part.length() - 2; + topmost = assignSpec(part.charAt(1), pos, specLen); + return part.substring(1, 1 + specLen); + + case 'h': + case 'm': + case 's': + case '0': + part = part.toLowerCase(); + assignSpec(part.charAt(0), pos, part.length()); + return part; + + case '\n': + return "%n"; + + case '\"': + part = part.substring(1, part.length() - 1); + break; + + case '\\': + part = part.substring(1); + break; + + case '*': + if (part.length() > 1) + part = CellFormatPart.expandChar(part); + break; + + // An escape we can let it handle because it can't have a '%' + case '_': + return null; + } + // Replace ever "%" with a "%%" so we can use printf + return PERCENTS.matcher(part).replaceAll("%%"); + } + } + + /** + * Creates a elapsed time formatter. + * + * @param pattern The pattern to parse. + */ + public CellElapsedFormatter(String pattern) { + super(pattern); + + specs = new ArrayList<TimeSpec>(); + + StringBuffer desc = CellFormatPart.parseFormat(pattern, + CellFormatType.ELAPSED, new ElapsedPartHandler()); + + ListIterator<TimeSpec> it = specs.listIterator(specs.size()); + while (it.hasPrevious()) { + TimeSpec spec = it.previous(); + desc.replace(spec.pos, spec.pos + spec.len, "%0" + spec.len + "d"); + if (spec.type != topmost.type) { + spec.modBy = modFor(spec.type, spec.len); + } + } + + printfFmt = desc.toString(); + } + + private TimeSpec assignSpec(char type, int pos, int len) { + TimeSpec spec = new TimeSpec(type, pos, len, factorFor(type, len)); + specs.add(spec); + return spec; + } + + private static double factorFor(char type, int len) { + switch (type) { + case 'h': + return HOUR__FACTOR; + case 'm': + return MIN__FACTOR; + case 's': + return SEC__FACTOR; + case '0': + return SEC__FACTOR / Math.pow(10, len); + default: + throw new IllegalArgumentException( + "Uknown elapsed time spec: " + type); + } + } + + private static double modFor(char type, int len) { + switch (type) { + case 'h': + return 24; + case 'm': + return 60; + case 's': + return 60; + case '0': + return Math.pow(10, len); + default: + throw new IllegalArgumentException( + "Uknown elapsed time spec: " + type); + } + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object value) { + double elapsed = ((Number) value).doubleValue(); + + if (elapsed < 0) { + toAppendTo.append('-'); + elapsed = -elapsed; + } + + Object[] parts = new Long[specs.size()]; + for (int i = 0; i < specs.size(); i++) { + parts[i] = specs.get(i).valueFor(elapsed); + } + + Formatter formatter = new Formatter(toAppendTo); + formatter.format(printfFmt, parts); + } + + /** + * {@inheritDoc} + * <p/> + * For a date, this is <tt>"mm/d/y"</tt>. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormat.java b/src/java/org/apache/poi/ss/format/CellFormat.java new file mode 100644 index 0000000000..c7c22f004e --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormat.java @@ -0,0 +1,313 @@ +/* ==================================================================== + 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.format; + +import org.apache.poi.ss.usermodel.Cell; + +import javax.swing.*; +import java.util.ArrayList; +import java.util.List; +import java.util.Map; +import java.util.WeakHashMap; +import java.util.logging.Level; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +/** + * Format a value according to the standard Excel behavior. This "standard" is + * not explicitly documented by Microsoft, so the behavior is determined by + * experimentation; see the tests. + * <p/> + * An Excel format has up to four parts, separated by semicolons. Each part + * specifies what to do with particular kinds of values, depending on the number + * of parts given: <dl> <dt>One part (example: <tt>[Green]#.##</tt>) <dd>If the + * value is a number, display according to this one part (example: green text, + * with up to two decimal points). If the value is text, display it as is. + * <dt>Two parts (example: <tt>[Green]#.##;[Red]#.##</tt>) <dd>If the value is a + * positive number or zero, display according to the first part (example: green + * text, with up to two decimal points); if it is a negative number, display + * according to the second part (example: red text, with up to two decimal + * points). If the value is text, display it as is. <dt>Three parts (example: + * <tt>[Green]#.##;[Black]#.##;[Red]#.##</tt>) <dd>If the value is a positive + * number, display according to the first part (example: green text, with up to + * two decimal points); if it is zero, display according to the second part + * (example: black text, with up to two decimal points); if it is a negative + * number, display according to the third part (example: red text, with up to + * two decimal points). If the value is text, display it as is. <dt>Four parts + * (example: <tt>[Green]#.##;[Black]#.##;[Red]#.##;[@]</tt>) <dd>If the value is + * a positive number, display according to the first part (example: green text, + * with up to two decimal points); if it is zero, display according to the + * second part (example: black text, with up to two decimal points); if it is a + * negative number, display according to the third part (example: red text, with + * up to two decimal points). If the value is text, display according to the + * fourth part (example: text in the cell's usual color, with the text value + * surround by brackets). </dl> + * <p/> + * In addition to these, there is a general format that is used when no format + * is specified. This formatting is presented by the {@link #GENERAL_FORMAT} + * object. + * + * @author Ken Arnold, Industrious Media LLC + */ +@SuppressWarnings({"Singleton"}) +public class CellFormat { + private final String format; + private final CellFormatPart posNumFmt; + private final CellFormatPart zeroNumFmt; + private final CellFormatPart negNumFmt; + private final CellFormatPart textFmt; + + private static final Pattern ONE_PART = Pattern.compile( + CellFormatPart.FORMAT_PAT.pattern() + "(;|$)", + Pattern.COMMENTS | Pattern.CASE_INSENSITIVE); + + private static final CellFormatPart DEFAULT_TEXT_FORMAT = + new CellFormatPart("@"); + + /** + * Format a value as it would be were no format specified. This is also + * used when the format specified is <tt>General</tt>. + */ + public static final CellFormat GENERAL_FORMAT = new CellFormat("General") { + @Override + public CellFormatResult apply(Object value) { + String text; + if (value == null) { + text = ""; + } else if (value instanceof Number) { + text = CellNumberFormatter.SIMPLE_NUMBER.format(value); + } else { + text = value.toString(); + } + return new CellFormatResult(true, text, null); + } + }; + + /** Maps a format string to its parsed version for efficiencies sake. */ + private static final Map<String, CellFormat> formatCache = + new WeakHashMap<String, CellFormat>(); + + /** + * Returns a {@link CellFormat} that applies the given format. Two calls + * with the same format may or may not return the same object. + * + * @param format The format. + * + * @return A {@link CellFormat} that applies the given format. + */ + public static CellFormat getInstance(String format) { + CellFormat fmt = formatCache.get(format); + if (fmt == null) { + if (format.equals("General")) + fmt = GENERAL_FORMAT; + else + fmt = new CellFormat(format); + formatCache.put(format, fmt); + } + return fmt; + } + + /** + * Creates a new object. + * + * @param format The format. + */ + private CellFormat(String format) { + this.format = format; + Matcher m = ONE_PART.matcher(format); + List<CellFormatPart> parts = new ArrayList<CellFormatPart>(); + + while (m.find()) { + try { + String valueDesc = m.group(); + + // Strip out the semicolon if it's there + if (valueDesc.endsWith(";")) + valueDesc = valueDesc.substring(0, valueDesc.length() - 1); + + parts.add(new CellFormatPart(valueDesc)); + } catch (RuntimeException e) { + CellFormatter.logger.log(Level.WARNING, + "Invalid format: " + CellFormatter.quote(m.group()), e); + parts.add(null); + } + } + + switch (parts.size()) { + case 1: + posNumFmt = zeroNumFmt = negNumFmt = parts.get(0); + textFmt = DEFAULT_TEXT_FORMAT; + break; + case 2: + posNumFmt = zeroNumFmt = parts.get(0); + negNumFmt = parts.get(1); + textFmt = DEFAULT_TEXT_FORMAT; + break; + case 3: + posNumFmt = parts.get(0); + zeroNumFmt = parts.get(1); + negNumFmt = parts.get(2); + textFmt = DEFAULT_TEXT_FORMAT; + break; + case 4: + default: + posNumFmt = parts.get(0); + zeroNumFmt = parts.get(1); + negNumFmt = parts.get(2); + textFmt = parts.get(3); + break; + } + } + + /** + * Returns the result of applying the format to the given value. If the + * value is a number (a type of {@link Number} object), the correct number + * format type is chosen; otherwise it is considered a text object. + * + * @param value The value + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(Object value) { + if (value instanceof Number) { + Number num = (Number) value; + double val = num.doubleValue(); + if (val > 0) + return posNumFmt.apply(value); + else if (val < 0) + return negNumFmt.apply(-val); + else + return zeroNumFmt.apply(value); + } else { + return textFmt.apply(value); + } + } + + /** + * Fetches the appropriate value from the cell, and returns the result of + * applying it to the appropriate format. For formula cells, the computed + * value is what is used. + * + * @param c The cell. + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(Cell c) { + switch (ultimateType(c)) { + case Cell.CELL_TYPE_BLANK: + return apply(""); + case Cell.CELL_TYPE_BOOLEAN: + return apply(c.getStringCellValue()); + case Cell.CELL_TYPE_NUMERIC: + return apply(c.getNumericCellValue()); + case Cell.CELL_TYPE_STRING: + return apply(c.getStringCellValue()); + default: + return apply("?"); + } + } + + /** + * Uses the result of applying this format to the value, setting the text + * and color of a label before returning the result. + * + * @param label The label to apply to. + * @param value The value to process. + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(JLabel label, Object value) { + CellFormatResult result = apply(value); + label.setText(result.text); + if (result.textColor != null) { + label.setForeground(result.textColor); + } + return result; + } + + /** + * Fetches the appropriate value from the cell, and uses the result, setting + * the text and color of a label before returning the result. + * + * @param label The label to apply to. + * @param c The cell. + * + * @return The result, in a {@link CellFormatResult}. + */ + public CellFormatResult apply(JLabel label, Cell c) { + switch (ultimateType(c)) { + case Cell.CELL_TYPE_BLANK: + return apply(label, ""); + case Cell.CELL_TYPE_BOOLEAN: + return apply(label, c.getStringCellValue()); + case Cell.CELL_TYPE_NUMERIC: + return apply(label, c.getNumericCellValue()); + case Cell.CELL_TYPE_STRING: + return apply(label, c.getStringCellValue()); + default: + return apply(label, "?"); + } + } + + /** + * Returns the ultimate cell type, following the results of formulas. If + * the cell is a {@link Cell#CELL_TYPE_FORMULA}, this returns the result of + * {@link Cell#getCachedFormulaResultType()}. Otherwise this returns the + * result of {@link Cell#getCellType()}. + * + * @param cell The cell. + * + * @return The ultimate type of this cell. + */ + public static int ultimateType(Cell cell) { + int type = cell.getCellType(); + if (type == Cell.CELL_TYPE_FORMULA) + return cell.getCachedFormulaResultType(); + else + return type; + } + + /** + * Returns <tt>true</tt> if the other object is a {@link CellFormat} object + * with the same format. + * + * @param obj The other object. + * + * @return <tt>true</tt> if the two objects are equal. + */ + @Override + public boolean equals(Object obj) { + if (this == obj) + return true; + if (obj instanceof CellFormat) { + CellFormat that = (CellFormat) obj; + return format.equals(that.format); + } + return false; + } + + /** + * Returns a hash code for the format. + * + * @return A hash code for the format. + */ + @Override + public int hashCode() { + return format.hashCode(); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatCondition.java b/src/java/org/apache/poi/ss/format/CellFormatCondition.java new file mode 100644 index 0000000000..23fd2f2e7a --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatCondition.java @@ -0,0 +1,121 @@ +/* ==================================================================== + 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.format; + +import java.util.HashMap; +import java.util.Map; + +/** + * This object represents a condition in a cell format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public abstract class CellFormatCondition { + private static final int LT = 0; + private static final int LE = 1; + private static final int GT = 2; + private static final int GE = 3; + private static final int EQ = 4; + private static final int NE = 5; + + private static final Map<String, Integer> TESTS; + + static { + TESTS = new HashMap<String, Integer>(); + TESTS.put("<", LT); + TESTS.put("<=", LE); + TESTS.put(">", GT); + TESTS.put(">=", GE); + TESTS.put("=", EQ); + TESTS.put("==", EQ); + TESTS.put("!=", NE); + TESTS.put("<>", NE); + } + + /** + * Returns an instance of a condition object. + * + * @param opString The operator as a string. One of <tt>"<"</tt>, + * <tt>"<="</tt>, <tt>">"</tt>, <tt>">="</tt>, + * <tt>"="</tt>, <tt>"=="</tt>, <tt>"!="</tt>, or + * <tt>"<>"</tt>. + * @param constStr The constant (such as <tt>"12"</tt>). + * + * @return A condition object for the given condition. + */ + public static CellFormatCondition getInstance(String opString, + String constStr) { + + if (!TESTS.containsKey(opString)) + throw new IllegalArgumentException("Unknown test: " + opString); + int test = TESTS.get(opString); + + final double c = Double.parseDouble(constStr); + + switch (test) { + case LT: + return new CellFormatCondition() { + public boolean pass(double value) { + return value < c; + } + }; + case LE: + return new CellFormatCondition() { + public boolean pass(double value) { + return value <= c; + } + }; + case GT: + return new CellFormatCondition() { + public boolean pass(double value) { + return value > c; + } + }; + case GE: + return new CellFormatCondition() { + public boolean pass(double value) { + return value >= c; + } + }; + case EQ: + return new CellFormatCondition() { + public boolean pass(double value) { + return value == c; + } + }; + case NE: + return new CellFormatCondition() { + public boolean pass(double value) { + return value != c; + } + }; + default: + throw new IllegalArgumentException( + "Cannot create for test number " + test + "(\"" + opString + + "\")"); + } + } + + /** + * Returns <tt>true</tt> if the given value passes the constraint's test. + * + * @param value The value to compare against. + * + * @return <tt>true</tt> if the given value passes the constraint's test. + */ + public abstract boolean pass(double value); +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatPart.java b/src/java/org/apache/poi/ss/format/CellFormatPart.java new file mode 100644 index 0000000000..cedc94fe27 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatPart.java @@ -0,0 +1,494 @@ +/* ==================================================================== + 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.format; + +import org.apache.poi.hssf.util.HSSFColor; + +import javax.swing.*; +import java.awt.*; +import java.util.Map; +import java.util.TreeMap; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +import static org.apache.poi.ss.format.CellFormatter.logger; +import static org.apache.poi.ss.format.CellFormatter.quote; + +/** + * Objects of this class represent a single part of a cell format expression. + * Each cell can have up to four of these for positive, zero, negative, and text + * values. + * <p/> + * Each format part can contain a color, a condition, and will always contain a + * format specification. For example <tt>"[Red][>=10]#"</tt> has a color + * (<tt>[Red]</tt>), a condition (<tt>>=10</tt>) and a format specification + * (<tt>#</tt>). + * <p/> + * This class also contains patterns for matching the subparts of format + * specification. These are used internally, but are made public in case other + * code has use for them. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellFormatPart { + private final Color color; + private CellFormatCondition condition; + private final CellFormatter format; + + private static final Map<String, Color> NAMED_COLORS; + + static { + NAMED_COLORS = new TreeMap<String, Color>( + String.CASE_INSENSITIVE_ORDER); + + Map colors = HSSFColor.getIndexHash(); + for (Object val : colors.values()) { + HSSFColor color = (HSSFColor) val; + Class type = color.getClass(); + String name = type.getSimpleName(); + if (name.equals(name.toUpperCase())) { + short[] rgb = color.getTriplet(); + Color c = new Color(rgb[0], rgb[1], rgb[2]); + NAMED_COLORS.put(name, c); + if (name.indexOf('_') > 0) + NAMED_COLORS.put(name.replace('_', ' '), c); + if (name.indexOf("_PERCENT") > 0) + NAMED_COLORS.put(name.replace("_PERCENT", "%").replace('_', + ' '), c); + } + } + } + + /** Pattern for the color part of a cell format part. */ + public static final Pattern COLOR_PAT; + /** Pattern for the condition part of a cell format part. */ + public static final Pattern CONDITION_PAT; + /** Pattern for the format specification part of a cell format part. */ + public static final Pattern SPECIFICATION_PAT; + /** Pattern for an entire cell single part. */ + public static final Pattern FORMAT_PAT; + + /** Within {@link #FORMAT_PAT}, the group number for the matched color. */ + public static final int COLOR_GROUP; + /** + * Within {@link #FORMAT_PAT}, the group number for the operator in the + * condition. + */ + public static final int CONDITION_OPERATOR_GROUP; + /** + * Within {@link #FORMAT_PAT}, the group number for the value in the + * condition. + */ + public static final int CONDITION_VALUE_GROUP; + /** + * Within {@link #FORMAT_PAT}, the group number for the format + * specification. + */ + public static final int SPECIFICATION_GROUP; + + static { + // A condition specification + String condition = "([<>=]=?|!=|<>) # The operator\n" + + " \\s*([0-9]+(?:\\.[0-9]*)?)\\s* # The constant to test against\n"; + + String color = + "\\[(black|blue|cyan|green|magenta|red|white|yellow|color [0-9]+)\\]"; + + // A number specification + // Note: careful that in something like ##, that the trailing comma is not caught up in the integer part + + // A part of a specification + String part = "\\\\. # Quoted single character\n" + + "|\"([^\\\\\"]|\\\\.)*\" # Quoted string of characters (handles escaped quotes like \\\") \n" + + "|_. # Space as wide as a given character\n" + + "|\\*. # Repeating fill character\n" + + "|@ # Text: cell text\n" + + "|([0?\\#](?:[0?\\#,]*)) # Number: digit + other digits and commas\n" + + "|e[-+] # Number: Scientific: Exponent\n" + + "|m{1,5} # Date: month or minute spec\n" + + "|d{1,4} # Date: day/date spec\n" + + "|y{2,4} # Date: year spec\n" + + "|h{1,2} # Date: hour spec\n" + + "|s{1,2} # Date: second spec\n" + + "|am?/pm? # Date: am/pm spec\n" + + "|\\[h{1,2}\\] # Elapsed time: hour spec\n" + + "|\\[m{1,2}\\] # Elapsed time: minute spec\n" + + "|\\[s{1,2}\\] # Elapsed time: second spec\n" + + "|[^;] # A character\n" + ""; + + String format = "(?:" + color + ")? # Text color\n" + + "(?:\\[" + condition + "\\])? # Condition\n" + + "((?:" + part + ")+) # Format spec\n"; + + int flags = Pattern.COMMENTS | Pattern.CASE_INSENSITIVE; + COLOR_PAT = Pattern.compile(color, flags); + CONDITION_PAT = Pattern.compile(condition, flags); + SPECIFICATION_PAT = Pattern.compile(part, flags); + FORMAT_PAT = Pattern.compile(format, flags); + + // Calculate the group numbers of important groups. (They shift around + // when the pattern is changed; this way we figure out the numbers by + // experimentation.) + + COLOR_GROUP = findGroup(FORMAT_PAT, "[Blue]@", "Blue"); + CONDITION_OPERATOR_GROUP = findGroup(FORMAT_PAT, "[>=1]@", ">="); + CONDITION_VALUE_GROUP = findGroup(FORMAT_PAT, "[>=1]@", "1"); + SPECIFICATION_GROUP = findGroup(FORMAT_PAT, "[Blue][>1]\\a ?", "\\a ?"); + } + + interface PartHandler { + String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc); + } + + /** + * Create an object to represent a format part. + * + * @param desc The string to parse. + */ + public CellFormatPart(String desc) { + Matcher m = FORMAT_PAT.matcher(desc); + if (!m.matches()) { + throw new IllegalArgumentException("Unrecognized format: " + quote( + desc)); + } + color = getColor(m); + condition = getCondition(m); + format = getFormatter(m); + } + + /** + * Returns <tt>true</tt> if this format part applies to the given value. If + * the value is a number and this is part has a condition, returns + * <tt>true</tt> only if the number passes the condition. Otherwise, this + * allways return <tt>true</tt>. + * + * @param valueObject The value to evaluate. + * + * @return <tt>true</tt> if this format part applies to the given value. + */ + public boolean applies(Object valueObject) { + if (condition == null || !(valueObject instanceof Number)) { + if (valueObject == null) + throw new NullPointerException("valueObject"); + return true; + } else { + Number num = (Number) valueObject; + return condition.pass(num.doubleValue()); + } + } + + /** + * Returns the number of the first group that is the same as the marker + * string. The search starts with group 1. + * + * @param pat The pattern to use. + * @param str The string to match against the pattern. + * @param marker The marker value to find the group of. + * + * @return The matching group number. + * + * @throws IllegalArgumentException No group matches the marker. + */ + private static int findGroup(Pattern pat, String str, String marker) { + Matcher m = pat.matcher(str); + if (!m.find()) + throw new IllegalArgumentException( + "Pattern \"" + pat.pattern() + "\" doesn't match \"" + str + + "\""); + for (int i = 1; i <= m.groupCount(); i++) { + String grp = m.group(i); + if (grp != null && grp.equals(marker)) + return i; + } + throw new IllegalArgumentException( + "\"" + marker + "\" not found in \"" + pat.pattern() + "\""); + } + + /** + * Returns the color specification from the matcher, or <tt>null</tt> if + * there is none. + * + * @param m The matcher for the format part. + * + * @return The color specification or <tt>null</tt>. + */ + private static Color getColor(Matcher m) { + String cdesc = m.group(COLOR_GROUP); + if (cdesc == null || cdesc.length() == 0) + return null; + Color c = NAMED_COLORS.get(cdesc); + if (c == null) + logger.warning("Unknown color: " + quote(cdesc)); + return c; + } + + /** + * Returns the condition specification from the matcher, or <tt>null</tt> if + * there is none. + * + * @param m The matcher for the format part. + * + * @return The condition specification or <tt>null</tt>. + */ + private CellFormatCondition getCondition(Matcher m) { + String mdesc = m.group(CONDITION_OPERATOR_GROUP); + if (mdesc == null || mdesc.length() == 0) + return null; + return CellFormatCondition.getInstance(m.group( + CONDITION_OPERATOR_GROUP), m.group(CONDITION_VALUE_GROUP)); + } + + /** + * Returns the formatter object implied by the format specification for the + * format part. + * + * @param matcher The matcher for the format part. + * + * @return The formatter. + */ + private CellFormatter getFormatter(Matcher matcher) { + String fdesc = matcher.group(SPECIFICATION_GROUP); + CellFormatType type = formatType(fdesc); + return type.formatter(fdesc); + } + + /** + * Returns the type of format. + * + * @param fdesc The format specification + * + * @return The type of format. + */ + private CellFormatType formatType(String fdesc) { + fdesc = fdesc.trim(); + if (fdesc.equals("") || fdesc.equalsIgnoreCase("General")) + return CellFormatType.GENERAL; + + Matcher m = SPECIFICATION_PAT.matcher(fdesc); + boolean couldBeDate = false; + boolean seenZero = false; + while (m.find()) { + String repl = m.group(0); + if (repl.length() > 0) { + switch (repl.charAt(0)) { + case '@': + return CellFormatType.TEXT; + case 'd': + case 'D': + case 'y': + case 'Y': + return CellFormatType.DATE; + case 'h': + case 'H': + case 'm': + case 'M': + case 's': + case 'S': + // These can be part of date, or elapsed + couldBeDate = true; + break; + case '0': + // This can be part of date, elapsed, or number + seenZero = true; + break; + case '[': + return CellFormatType.ELAPSED; + case '#': + case '?': + return CellFormatType.NUMBER; + } + } + } + + // Nothing definitive was found, so we figure out it deductively + if (couldBeDate) + return CellFormatType.DATE; + if (seenZero) + return CellFormatType.NUMBER; + return CellFormatType.TEXT; + } + + /** + * Returns a version of the original string that has any special characters + * quoted (or escaped) as appropriate for the cell format type. The format + * type object is queried to see what is special. + * + * @param repl The original string. + * @param type The format type representation object. + * + * @return A version of the string with any special characters replaced. + * + * @see CellFormatType#isSpecial(char) + */ + static String quoteSpecial(String repl, CellFormatType type) { + StringBuilder sb = new StringBuilder(); + for (int i = 0; i < repl.length(); i++) { + char ch = repl.charAt(i); + if (ch == '\'' && type.isSpecial('\'')) { + sb.append('\u0000'); + continue; + } + + boolean special = type.isSpecial(ch); + if (special) + sb.append("'"); + sb.append(ch); + if (special) + sb.append("'"); + } + return sb.toString(); + } + + /** + * Apply this format part to the given value. This returns a {@link + * CellFormatResult} object with the results. + * + * @param value The value to apply this format part to. + * + * @return A {@link CellFormatResult} object containing the results of + * applying the format to the value. + */ + public CellFormatResult apply(Object value) { + boolean applies = applies(value); + String text; + Color textColor; + if (applies) { + text = format.format(value); + textColor = color; + } else { + text = format.simpleFormat(value); + textColor = null; + } + return new CellFormatResult(applies, text, textColor); + } + + /** + * Apply this format part to the given value, applying the result to the + * given label. + * + * @param label The label + * @param value The value to apply this format part to. + * + * @return <tt>true</tt> if the + */ + public CellFormatResult apply(JLabel label, Object value) { + CellFormatResult result = apply(value); + label.setText(result.text); + if (result.textColor != null) { + label.setForeground(result.textColor); + } + return result; + } + + public static StringBuffer parseFormat(String fdesc, CellFormatType type, + PartHandler partHandler) { + + // Quoting is very awkward. In the Java classes, quoting is done + // between ' chars, with '' meaning a single ' char. The problem is that + // in Excel, it is legal to have two adjacent escaped strings. For + // example, consider the Excel format "\a\b#". The naive (and easy) + // translation into Java DecimalFormat is "'a''b'#". For the number 17, + // in Excel you would get "ab17", but in Java it would be "a'b17" -- the + // '' is in the middle of the quoted string in Java. So the trick we + // use is this: When we encounter a ' char in the Excel format, we + // output a \u0000 char into the string. Now we know that any '' in the + // output is the result of two adjacent escaped strings. So after the + // main loop, we have to do two passes: One to eliminate any '' + // sequences, to make "'a''b'" become "'ab'", and another to replace any + // \u0000 with '' to mean a quote char. Oy. + // + // For formats that don't use "'" we don't do any of this + Matcher m = SPECIFICATION_PAT.matcher(fdesc); + StringBuffer fmt = new StringBuffer(); + while (m.find()) { + String part = group(m, 0); + if (part.length() > 0) { + String repl = partHandler.handlePart(m, part, type, fmt); + if (repl == null) { + switch (part.charAt(0)) { + case '\"': + repl = quoteSpecial(part.substring(1, + part.length() - 1), type); + break; + case '\\': + repl = quoteSpecial(part.substring(1), type); + break; + case '_': + repl = " "; + break; + case '*': //!! We don't do this for real, we just put in 3 of them + repl = expandChar(part); + break; + default: + repl = part; + break; + } + } + m.appendReplacement(fmt, Matcher.quoteReplacement(repl)); + } + } + m.appendTail(fmt); + + if (type.isSpecial('\'')) { + // Now the next pass for quoted characters: Remove '' chars, making "'a''b'" into "'ab'" + int pos = 0; + while ((pos = fmt.indexOf("''", pos)) >= 0) { + fmt.delete(pos, pos + 2); + } + + // Now the final pass for quoted chars: Replace any \u0000 with '' + pos = 0; + while ((pos = fmt.indexOf("\u0000", pos)) >= 0) { + fmt.replace(pos, pos + 1, "''"); + } + } + + return fmt; + } + + /** + * Expands a character. This is only partly done, because we don't have the + * correct info. In Excel, this would be expanded to fill the rest of the + * cell, but we don't know, in general, what the "rest of the cell" is. + * + * @param part The character to be repeated is the second character in this + * string. + * + * @return The character repeated three times. + */ + static String expandChar(String part) { + String repl; + char ch = part.charAt(1); + repl = "" + ch + ch + ch; + return repl; + } + + /** + * Returns the string from the group, or <tt>""</tt> if the group is + * <tt>null</tt>. + * + * @param m The matcher. + * @param g The group number. + * + * @return The group or <tt>""</tt>. + */ + public static String group(Matcher m, int g) { + String str = m.group(g); + return (str == null ? "" : str); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatResult.java b/src/java/org/apache/poi/ss/format/CellFormatResult.java new file mode 100644 index 0000000000..3c45de3de6 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatResult.java @@ -0,0 +1,58 @@ +/* ==================================================================== + 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.format; + +import java.awt.*; + +/** + * This object contains the result of applying a cell format or cell format part + * to a value. + * + * @author Ken Arnold, Industrious Media LLC + * @see CellFormatPart#apply(Object) + * @see CellFormat#apply(Object) + */ +public class CellFormatResult { + /** + * This is <tt>true</tt> if no condition was given that applied to the + * value, or if the condition is satisfied. If a condition is relevant, and + * when applied the value fails the test, this is <tt>false</tt>. + */ + public final boolean applies; + + /** The resulting text. This will never be <tt>null</tt>. */ + public final String text; + + /** + * The color the format sets, or <tt>null</tt> if the format sets no color. + * This will always be <tt>null</tt> if {@link #applies} is <tt>false</tt>. + */ + public final Color textColor; + + /** + * Creates a new format result object. + * + * @param applies The value for {@link #applies}. + * @param text The value for {@link #text}. + * @param textColor The value for {@link #textColor}. + */ + public CellFormatResult(boolean applies, String text, Color textColor) { + this.applies = applies; + this.text = text; + this.textColor = (applies ? textColor : null); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatType.java b/src/java/org/apache/poi/ss/format/CellFormatType.java new file mode 100644 index 0000000000..363af1c483 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatType.java @@ -0,0 +1,74 @@ +package org.apache.poi.ss.format; + +/** + * The different kinds of formats that the formatter understands. + * + * @author Ken Arnold, Industrious Media LLC + */ +public enum CellFormatType { + + /** The general (default) format; also used for <tt>"General"</tt>. */ + GENERAL { + CellFormatter formatter(String pattern) { + return new CellGeneralFormatter(); + } + boolean isSpecial(char ch) { + return false; + } + }, + /** A numeric format. */ + NUMBER { + boolean isSpecial(char ch) { + return false; + } + CellFormatter formatter(String pattern) { + return new CellNumberFormatter(pattern); + } + }, + /** A date format. */ + DATE { + boolean isSpecial(char ch) { + return ch == '\'' || (ch <= '\u007f' && Character.isLetter(ch)); + } + CellFormatter formatter(String pattern) { + return new CellDateFormatter(pattern); + } + }, + /** An elapsed time format. */ + ELAPSED { + boolean isSpecial(char ch) { + return false; + } + CellFormatter formatter(String pattern) { + return new CellElapsedFormatter(pattern); + } + }, + /** A text format. */ + TEXT { + boolean isSpecial(char ch) { + return false; + } + CellFormatter formatter(String pattern) { + return new CellTextFormatter(pattern); + } + }; + + /** + * Returns <tt>true</tt> if the format is special and needs to be quoted. + * + * @param ch The character to test. + * + * @return <tt>true</tt> if the format is special and needs to be quoted. + */ + abstract boolean isSpecial(char ch); + + /** + * Returns a new formatter of the appropriate type, for the given pattern. + * The pattern must be appropriate for the type. + * + * @param pattern The pattern to use. + * + * @return A new formatter of the appropriate type, for the given pattern. + */ + abstract CellFormatter formatter(String pattern); +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellFormatter.java b/src/java/org/apache/poi/ss/format/CellFormatter.java new file mode 100644 index 0000000000..a125181200 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellFormatter.java @@ -0,0 +1,102 @@ +/* ==================================================================== + 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.format; + +import java.util.Locale; +import java.util.logging.Logger; + +/** + * This is the abstract supertype for the various cell formatters. + * + * @@author Ken Arnold, Industrious Media LLC + */ +public abstract class CellFormatter { + /** The original specified format. */ + protected final String format; + + /** + * This is the locale used to get a consistent format result from which to + * work. + */ + public static final Locale LOCALE = Locale.US; + + /** + * Creates a new formatter object, storing the format in {@link #format}. + * + * @param format The format. + */ + public CellFormatter(String format) { + this.format = format; + } + + /** The logger to use in the formatting code. */ + static final Logger logger = Logger.getLogger( + CellFormatter.class.getName()); + + /** + * Format a value according the format string. + * + * @param toAppendTo The buffer to append to. + * @param value The value to format. + */ + public abstract void formatValue(StringBuffer toAppendTo, Object value); + + /** + * Format a value according to the type, in the most basic way. + * + * @param toAppendTo The buffer to append to. + * @param value The value to format. + */ + public abstract void simpleValue(StringBuffer toAppendTo, Object value); + + /** + * Formats the value, returning the resulting string. + * + * @param value The value to format. + * + * @return The value, formatted. + */ + public String format(Object value) { + StringBuffer sb = new StringBuffer(); + formatValue(sb, value); + return sb.toString(); + } + + /** + * Formats the value in the most basic way, returning the resulting string. + * + * @param value The value to format. + * + * @return The value, formatted. + */ + public String simpleFormat(Object value) { + StringBuffer sb = new StringBuffer(); + simpleValue(sb, value); + return sb.toString(); + } + + /** + * Returns the input string, surrounded by quotes. + * + * @param str The string to quote. + * + * @return The input string, surrounded by quotes. + */ + static String quote(String str) { + return '"' + str + '"'; + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellGeneralFormatter.java b/src/java/org/apache/poi/ss/format/CellGeneralFormatter.java new file mode 100644 index 0000000000..e2adc61c0c --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellGeneralFormatter.java @@ -0,0 +1,84 @@ +/* ==================================================================== + 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.format; + +import java.util.Formatter; + +/** + * A formatter for the default "General" cell format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellGeneralFormatter extends CellFormatter { + /** Creates a new general formatter. */ + public CellGeneralFormatter() { + super("General"); + } + + /** + * The general style is not quite the same as any other, or any combination + * of others. + * + * @param toAppendTo The buffer to append to. + * @param value The value to format. + */ + public void formatValue(StringBuffer toAppendTo, Object value) { + if (value instanceof Number) { + double val = ((Number) value).doubleValue(); + if (val == 0) { + toAppendTo.append('0'); + return; + } + + String fmt; + double exp = Math.log10(Math.abs(val)); + boolean stripZeros = true; + if (exp > 10 || exp < -9) + fmt = "%1.5E"; + else if ((long) val != val) + fmt = "%1.9f"; + else { + fmt = "%1.0f"; + stripZeros = false; + } + + Formatter formatter = new Formatter(toAppendTo); + formatter.format(LOCALE, fmt, value); + if (stripZeros) { + // strip off trailing zeros + int removeFrom; + if (fmt.endsWith("E")) + removeFrom = toAppendTo.lastIndexOf("E") - 1; + else + removeFrom = toAppendTo.length() - 1; + while (toAppendTo.charAt(removeFrom) == '0') { + toAppendTo.deleteCharAt(removeFrom--); + } + if (toAppendTo.charAt(removeFrom) == '.') { + toAppendTo.deleteCharAt(removeFrom--); + } + } + } else { + toAppendTo.append(value.toString()); + } + } + + /** Equivalent to {@link #formatValue(StringBuffer,Object)}. {@inheritDoc}. */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellNumberFormatter.java b/src/java/org/apache/poi/ss/format/CellNumberFormatter.java new file mode 100644 index 0000000000..8276afda49 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellNumberFormatter.java @@ -0,0 +1,1085 @@ +/* ==================================================================== + 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.format; + +import org.apache.poi.ss.format.CellFormatPart.PartHandler; + +import java.text.DecimalFormat; +import java.text.FieldPosition; +import java.util.BitSet; +import java.util.Collections; +import java.util.Formatter; +import java.util.Iterator; +import java.util.LinkedList; +import java.util.List; +import java.util.ListIterator; +import java.util.Set; +import java.util.TreeSet; +import java.util.regex.Matcher; + +/** + * This class implements printing out a value using a number format. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellNumberFormatter extends CellFormatter { + private final String desc; + private String printfFmt; + private double scale; + private Special decimalPoint; + private Special slash; + private Special exponent; + private Special numerator; + private Special afterInteger; + private Special afterFractional; + private boolean integerCommas; + private final List<Special> specials; + private List<Special> integerSpecials; + private List<Special> fractionalSpecials; + private List<Special> numeratorSpecials; + private List<Special> denominatorSpecials; + private List<Special> exponentSpecials; + private List<Special> exponentDigitSpecials; + private int maxDenominator; + private String numeratorFmt; + private String denominatorFmt; + private boolean improperFraction; + private DecimalFormat decimalFmt; + + static final CellFormatter SIMPLE_NUMBER = new CellFormatter("General") { + public void formatValue(StringBuffer toAppendTo, Object value) { + if (value == null) + return; + if (value instanceof Number) { + Number num = (Number) value; + if (num.doubleValue() % 1.0 == 0) + SIMPLE_INT.formatValue(toAppendTo, value); + else + SIMPLE_FLOAT.formatValue(toAppendTo, value); + } else { + CellTextFormatter.SIMPLE_TEXT.formatValue(toAppendTo, value); + } + } + + public void simpleValue(StringBuffer toAppendTo, Object value) { + formatValue(toAppendTo, value); + } + }; + + private static final CellFormatter SIMPLE_INT = new CellNumberFormatter( + "#"); + private static final CellFormatter SIMPLE_FLOAT = new CellNumberFormatter( + "#.#"); + + /** + * This class is used to mark where the special characters in the format + * are, as opposed to the other characters that are simply printed. + */ + static class Special { + final char ch; + int pos; + + Special(char ch, int pos) { + this.ch = ch; + this.pos = pos; + } + + @Override + public String toString() { + return "'" + ch + "' @ " + pos; + } + } + + /** + * This class represents a single modification to a result string. The way + * this works is complicated, but so is numeric formatting. In general, for + * most formats, we use a DecimalFormat object that will put the string out + * in a known format, usually with all possible leading and trailing zeros. + * We then walk through the result and the orginal format, and note any + * modifications that need to be made. Finally, we go through and apply + * them all, dealing with overlapping modifications. + */ + static class StringMod implements Comparable<StringMod> { + final Special special; + final int op; + CharSequence toAdd; + Special end; + boolean startInclusive; + boolean endInclusive; + + public static final int BEFORE = 1; + public static final int AFTER = 2; + public static final int REPLACE = 3; + + private StringMod(Special special, CharSequence toAdd, int op) { + this.special = special; + this.toAdd = toAdd; + this.op = op; + } + + public StringMod(Special start, boolean startInclusive, Special end, + boolean endInclusive, char toAdd) { + this(start, startInclusive, end, endInclusive); + this.toAdd = toAdd + ""; + } + + public StringMod(Special start, boolean startInclusive, Special end, + boolean endInclusive) { + special = start; + this.startInclusive = startInclusive; + this.end = end; + this.endInclusive = endInclusive; + op = REPLACE; + toAdd = ""; + } + + public int compareTo(StringMod that) { + int diff = special.pos - that.special.pos; + if (diff != 0) + return diff; + else + return op - that.op; + } + + @Override + public boolean equals(Object that) { + try { + return compareTo((StringMod) that) == 0; + } catch (RuntimeException ignored) { + // NullPointerException or CastException + return false; + } + } + + @Override + public int hashCode() { + return special.hashCode() + op; + } + } + + private class NumPartHandler implements PartHandler { + private char insertSignForExponent; + + public String handlePart(Matcher m, String part, CellFormatType type, + StringBuffer desc) { + int pos = desc.length(); + char firstCh = part.charAt(0); + switch (firstCh) { + case 'e': + case 'E': + // See comment in writeScientific -- exponent handling is complex. + // (1) When parsing the format, remove the sign from after the 'e' and + // put it before the first digit of the exponent. + if (exponent == null && specials.size() > 0) { + specials.add(exponent = new Special('.', pos)); + insertSignForExponent = part.charAt(1); + return part.substring(0, 1); + } + break; + + case '0': + case '?': + case '#': + if (insertSignForExponent != '\0') { + specials.add(new Special(insertSignForExponent, pos)); + desc.append(insertSignForExponent); + insertSignForExponent = '\0'; + pos++; + } + for (int i = 0; i < part.length(); i++) { + char ch = part.charAt(i); + specials.add(new Special(ch, pos + i)); + } + break; + + case '.': + if (decimalPoint == null && specials.size() > 0) + specials.add(decimalPoint = new Special('.', pos)); + break; + + case '/': + //!! This assumes there is a numerator and a denominator, but these are actually optional + if (slash == null && specials.size() > 0) { + numerator = previousNumber(); + // If the first number in the whole format is the numerator, the + // entire number should be printed as an improper fraction + if (numerator == firstDigit(specials)) + improperFraction = true; + specials.add(slash = new Special('.', pos)); + } + break; + + case '%': + // don't need to remember because we don't need to do anything with these + scale *= 100; + break; + + default: + return null; + } + return part; + } + } + + /** + * Creates a new cell number formatter. + * + * @param format The format to parse. + */ + public CellNumberFormatter(String format) { + super(format); + + scale = 1; + + specials = new LinkedList<Special>(); + + NumPartHandler partHandler = new NumPartHandler(); + StringBuffer descBuf = CellFormatPart.parseFormat(format, + CellFormatType.NUMBER, partHandler); + + // These are inconsistent settings, so ditch 'em + if ((decimalPoint != null || exponent != null) && slash != null) { + slash = null; + numerator = null; + } + + interpretCommas(descBuf); + + int precision; + int fractionPartWidth = 0; + if (decimalPoint == null) { + precision = 0; + } else { + precision = interpretPrecision(); + fractionPartWidth = 1 + precision; + if (precision == 0) { + // This means the format has a ".", but that output should have no decimals after it. + // We just stop treating it specially + specials.remove(decimalPoint); + decimalPoint = null; + } + } + + if (precision == 0) + fractionalSpecials = Collections.emptyList(); + else + fractionalSpecials = specials.subList(specials.indexOf( + decimalPoint) + 1, fractionalEnd()); + if (exponent == null) + exponentSpecials = Collections.emptyList(); + else { + int exponentPos = specials.indexOf(exponent); + exponentSpecials = specialsFor(exponentPos, 2); + exponentDigitSpecials = specialsFor(exponentPos + 2); + } + + if (slash == null) { + numeratorSpecials = Collections.emptyList(); + denominatorSpecials = Collections.emptyList(); + } else { + if (numerator == null) + numeratorSpecials = Collections.emptyList(); + else + numeratorSpecials = specialsFor(specials.indexOf(numerator)); + + denominatorSpecials = specialsFor(specials.indexOf(slash) + 1); + if (denominatorSpecials.isEmpty()) { + // no denominator follows the slash, drop the fraction idea + numeratorSpecials = Collections.emptyList(); + } else { + maxDenominator = maxValue(denominatorSpecials); + numeratorFmt = singleNumberFormat(numeratorSpecials); + denominatorFmt = singleNumberFormat(denominatorSpecials); + } + } + + integerSpecials = specials.subList(0, integerEnd()); + + if (exponent == null) { + StringBuffer fmtBuf = new StringBuffer("%"); + + int integerPartWidth = calculateIntegerPartWidth(); + int totalWidth = integerPartWidth + fractionPartWidth; + + fmtBuf.append('0').append(totalWidth).append('.').append(precision); + + fmtBuf.append("f"); + printfFmt = fmtBuf.toString(); + } else { + StringBuffer fmtBuf = new StringBuffer(); + boolean first = true; + List<Special> specialList = integerSpecials; + if (integerSpecials.size() == 1) { + // If we don't do this, we get ".6e5" instead of "6e4" + fmtBuf.append("0"); + first = false; + } else + for (Special s : specialList) { + if (isDigitFmt(s)) { + fmtBuf.append(first ? '#' : '0'); + first = false; + } + } + if (fractionalSpecials.size() > 0) { + fmtBuf.append('.'); + for (Special s : fractionalSpecials) { + if (isDigitFmt(s)) { + if (!first) + fmtBuf.append('0'); + first = false; + } + } + } + fmtBuf.append('E'); + placeZeros(fmtBuf, exponentSpecials.subList(2, + exponentSpecials.size())); + decimalFmt = new DecimalFormat(fmtBuf.toString()); + } + + if (exponent != null) + scale = + 1; // in "e" formats,% and trailing commas have no scaling effect + + desc = descBuf.toString(); + } + + private static void placeZeros(StringBuffer sb, List<Special> specials) { + for (Special s : specials) { + if (isDigitFmt(s)) + sb.append('0'); + } + } + + private static Special firstDigit(List<Special> specials) { + for (Special s : specials) { + if (isDigitFmt(s)) + return s; + } + return null; + } + + static StringMod insertMod(Special special, CharSequence toAdd, int where) { + return new StringMod(special, toAdd, where); + } + + static StringMod deleteMod(Special start, boolean startInclusive, + Special end, boolean endInclusive) { + + return new StringMod(start, startInclusive, end, endInclusive); + } + + static StringMod replaceMod(Special start, boolean startInclusive, + Special end, boolean endInclusive, char withChar) { + + return new StringMod(start, startInclusive, end, endInclusive, + withChar); + } + + private static String singleNumberFormat(List<Special> numSpecials) { + return "%0" + numSpecials.size() + "d"; + } + + private static int maxValue(List<Special> s) { + return (int) Math.round(Math.pow(10, s.size()) - 1); + } + + private List<Special> specialsFor(int pos, int takeFirst) { + if (pos >= specials.size()) + return Collections.emptyList(); + ListIterator<Special> it = specials.listIterator(pos + takeFirst); + Special last = it.next(); + int end = pos + takeFirst; + while (it.hasNext()) { + Special s = it.next(); + if (!isDigitFmt(s) || s.pos - last.pos > 1) + break; + end++; + last = s; + } + return specials.subList(pos, end + 1); + } + + private List<Special> specialsFor(int pos) { + return specialsFor(pos, 0); + } + + private static boolean isDigitFmt(Special s) { + return s.ch == '0' || s.ch == '?' || s.ch == '#'; + } + + private Special previousNumber() { + ListIterator<Special> it = specials.listIterator(specials.size()); + while (it.hasPrevious()) { + Special s = it.previous(); + if (isDigitFmt(s)) { + Special numStart = s; + Special last = s; + while (it.hasPrevious()) { + s = it.previous(); + if (last.pos - s.pos > 1) // it has to be continuous digits + break; + if (isDigitFmt(s)) + numStart = s; + else + break; + last = s; + } + return numStart; + } + } + return null; + } + + private int calculateIntegerPartWidth() { + ListIterator<Special> it = specials.listIterator(); + int digitCount = 0; + while (it.hasNext()) { + Special s = it.next(); + //!! Handle fractions: The previous set of digits before that is the numerator, so we should stop short of that + if (s == afterInteger) + break; + else if (isDigitFmt(s)) + digitCount++; + } + return digitCount; + } + + private int interpretPrecision() { + if (decimalPoint == null) { + return -1; + } else { + int precision = 0; + ListIterator<Special> it = specials.listIterator(specials.indexOf( + decimalPoint)); + if (it.hasNext()) + it.next(); // skip over the decimal point itself + while (it.hasNext()) { + Special s = it.next(); + if (isDigitFmt(s)) + precision++; + else + break; + } + return precision; + } + } + + private void interpretCommas(StringBuffer sb) { + // In the integer part, commas at the end are scaling commas; other commas mean to show thousand-grouping commas + ListIterator<Special> it = specials.listIterator(integerEnd()); + + boolean stillScaling = true; + integerCommas = false; + while (it.hasPrevious()) { + Special s = it.previous(); + if (s.ch != ',') { + stillScaling = false; + } else { + if (stillScaling) { + scale /= 1000; + } else { + integerCommas = true; + } + } + } + + if (decimalPoint != null) { + it = specials.listIterator(fractionalEnd()); + while (it.hasPrevious()) { + Special s = it.previous(); + if (s.ch != ',') { + break; + } else { + scale /= 1000; + } + } + } + + // Now strip them out -- we only need their interpretation, not their presence + it = specials.listIterator(); + int removed = 0; + while (it.hasNext()) { + Special s = it.next(); + s.pos -= removed; + if (s.ch == ',') { + removed++; + it.remove(); + sb.deleteCharAt(s.pos); + } + } + } + + private int integerEnd() { + if (decimalPoint != null) + afterInteger = decimalPoint; + else if (exponent != null) + afterInteger = exponent; + else if (numerator != null) + afterInteger = numerator; + else + afterInteger = null; + return afterInteger == null ? specials.size() : specials.indexOf( + afterInteger); + } + + private int fractionalEnd() { + int end; + if (exponent != null) + afterFractional = exponent; + else if (numerator != null) + afterInteger = numerator; + else + afterFractional = null; + end = afterFractional == null ? specials.size() : specials.indexOf( + afterFractional); + return end; + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object valueObject) { + double value = ((Number) valueObject).doubleValue(); + value *= scale; + + // the '-' sign goes at the front, always, so we pick it out + boolean negative = value < 0; + if (negative) + value = -value; + + // Split out the fractional part if we need to print a fraction + double fractional = 0; + if (slash != null) { + if (improperFraction) { + fractional = value; + value = 0; + } else { + fractional = value % 1.0; + //noinspection SillyAssignment + value = (long) value; + } + } + + Set<StringMod> mods = new TreeSet<StringMod>(); + StringBuffer output = new StringBuffer(desc); + + if (exponent != null) { + writeScientific(value, output, mods); + } else if (improperFraction) { + writeFraction(value, null, fractional, output, mods); + } else { + StringBuffer result = new StringBuffer(); + Formatter f = new Formatter(result); + f.format(LOCALE, printfFmt, value); + + if (numerator == null) { + writeFractional(result, output); + writeInteger(result, output, integerSpecials, mods, + integerCommas); + } else { + writeFraction(value, result, fractional, output, mods); + } + } + + // Now strip out any remaining '#'s and add any pending text ... + ListIterator<Special> it = specials.listIterator(); + Iterator<StringMod> changes = mods.iterator(); + StringMod nextChange = (changes.hasNext() ? changes.next() : null); + int adjust = 0; + BitSet deletedChars = new BitSet(); // records chars already deleted + while (it.hasNext()) { + Special s = it.next(); + int adjustedPos = s.pos + adjust; + if (!deletedChars.get(s.pos) && output.charAt(adjustedPos) == '#') { + output.deleteCharAt(adjustedPos); + adjust--; + deletedChars.set(s.pos); + } + while (nextChange != null && s == nextChange.special) { + int lenBefore = output.length(); + int modPos = s.pos + adjust; + int posTweak = 0; + switch (nextChange.op) { + case StringMod.AFTER: + // ignore adding a comma after a deleted char (which was a '#') + if (nextChange.toAdd.equals(",") && deletedChars.get(s.pos)) + break; + posTweak = 1; + //noinspection fallthrough + case StringMod.BEFORE: + output.insert(modPos + posTweak, nextChange.toAdd); + break; + + case StringMod.REPLACE: + int delPos = + s.pos; // delete starting pos in original coordinates + if (!nextChange.startInclusive) { + delPos++; + modPos++; + } + + // Skip over anything already deleted + while (deletedChars.get(delPos)) { + delPos++; + modPos++; + } + + int delEndPos = + nextChange.end.pos; // delete end point in original + if (nextChange.endInclusive) + delEndPos++; + + int modEndPos = + delEndPos + adjust; // delete end point in current + + if (modPos < modEndPos) { + if (nextChange.toAdd == "") + output.delete(modPos, modEndPos); + else { + char fillCh = nextChange.toAdd.charAt(0); + for (int i = modPos; i < modEndPos; i++) + output.setCharAt(i, fillCh); + } + deletedChars.set(delPos, delEndPos); + } + break; + + default: + throw new IllegalStateException( + "Unknown op: " + nextChange.op); + } + adjust += output.length() - lenBefore; + + if (changes.hasNext()) + nextChange = changes.next(); + else + nextChange = null; + } + } + + // Finally, add it to the string + if (negative) + toAppendTo.append('-'); + toAppendTo.append(output); + } + + private void writeScientific(double value, StringBuffer output, + Set<StringMod> mods) { + + StringBuffer result = new StringBuffer(); + FieldPosition fractionPos = new FieldPosition( + DecimalFormat.FRACTION_FIELD); + decimalFmt.format(value, result, fractionPos); + writeInteger(result, output, integerSpecials, mods, integerCommas); + writeFractional(result, output); + + /* + * Exponent sign handling is complex. + * + * In DecimalFormat, you never put the sign in the format, and the sign only + * comes out of the format if it is negative. + * + * In Excel, you always say whether to always show the sign ("e+") or only + * show negative signs ("e-"). + * + * Also in Excel, where you put the sign in the format is NOT where it comes + * out in the result. In the format, the sign goes with the "e"; in the + * output it goes with the exponent value. That is, if you say "#e-|#" you + * get "1e|-5", not "1e-|5". This makes sense I suppose, but it complicates + * things. + * + * Finally, everything else in this formatting code assumes that the base of + * the result is the original format, and that starting from that situation, + * the indexes of the original special characters can be used to place the new + * characters. As just described, this is not true for the exponent's sign. + * <p/> + * So here is how we handle it: + * + * (1) When parsing the format, remove the sign from after the 'e' and put it + * before the first digit of the exponent (where it will be shown). + * + * (2) Determine the result's sign. + * + * (3) If it's missing, put the sign into the output to keep the result + * lined up with the output. (In the result, "after the 'e'" and "before the + * first digit" are the same because the result has no extra chars to be in + * the way.) + * + * (4) In the output, remove the sign if it should not be shown ("e-" was used + * and the sign is negative) or set it to the correct value. + */ + + // (2) Determine the result's sign. + int ePos = fractionPos.getEndIndex(); + int signPos = ePos + 1; + char expSignRes = result.charAt(signPos); + if (expSignRes != '-') { + // not a sign, so it's a digit, and therefore a positive exponent + expSignRes = '+'; + // (3) If it's missing, put the sign into the output to keep the result + // lined up with the output. + result.insert(signPos, '+'); + } + + // Now the result lines up like it is supposed to with the specials' indexes + ListIterator<Special> it = exponentSpecials.listIterator(1); + Special expSign = it.next(); + char expSignFmt = expSign.ch; + + // (4) In the output, remove the sign if it should not be shown or set it to + // the correct value. + if (expSignRes == '-' || expSignFmt == '+') + mods.add(replaceMod(expSign, true, expSign, true, expSignRes)); + else + mods.add(deleteMod(expSign, true, expSign, true)); + + StringBuffer exponentNum = new StringBuffer(result.substring( + signPos + 1)); + writeInteger(exponentNum, output, exponentDigitSpecials, mods, false); + } + + private void writeFraction(double value, StringBuffer result, + double fractional, StringBuffer output, Set<StringMod> mods) { + + // Figure out if we are to suppress either the integer or fractional part. + // With # the suppressed part is removed; with ? it is replaced with spaces. + if (!improperFraction) { + // If fractional part is zero, and numerator doesn't have '0', write out + // only the integer part and strip the rest. + if (fractional == 0 && !hasChar('0', numeratorSpecials)) { + writeInteger(result, output, integerSpecials, mods, false); + + Special start = integerSpecials.get(integerSpecials.size() - 1); + Special end = denominatorSpecials.get( + denominatorSpecials.size() - 1); + if (hasChar('?', integerSpecials, numeratorSpecials, + denominatorSpecials)) { + //if any format has '?', then replace the fraction with spaces + mods.add(replaceMod(start, false, end, true, ' ')); + } else { + // otherwise, remove the fraction + mods.add(deleteMod(start, false, end, true)); + } + + // That's all, just return + return; + } else { + // New we check to see if we should remove the integer part + boolean allZero = (value == 0 && fractional == 0); + boolean willShowFraction = fractional != 0 || hasChar('0', + numeratorSpecials); + boolean removeBecauseZero = allZero && (hasOnly('#', + integerSpecials) || !hasChar('0', numeratorSpecials)); + boolean removeBecauseFraction = + !allZero && value == 0 && willShowFraction && !hasChar( + '0', integerSpecials); + if (removeBecauseZero || removeBecauseFraction) { + Special start = integerSpecials.get( + integerSpecials.size() - 1); + if (hasChar('?', integerSpecials, numeratorSpecials)) { + mods.add(replaceMod(start, true, numerator, false, + ' ')); + } else { + mods.add(deleteMod(start, true, numerator, false)); + } + } else { + // Not removing the integer part -- print it out + writeInteger(result, output, integerSpecials, mods, false); + } + } + } + + // Calculate and print the actual fraction (improper or otherwise) + try { + int n; + int d; + // the "fractional % 1" captures integer values in improper fractions + if (fractional == 0 || (improperFraction && fractional % 1 == 0)) { + // 0 as a fraction is reported by excel as 0/1 + n = (int) Math.round(fractional); + d = 1; + } else { + Fraction frac = new Fraction(fractional, maxDenominator); + n = frac.getNumerator(); + d = frac.getDenominator(); + } + if (improperFraction) + n += Math.round(value * d); + writeSingleInteger(numeratorFmt, n, output, numeratorSpecials, + mods); + writeSingleInteger(denominatorFmt, d, output, denominatorSpecials, + mods); + } catch (RuntimeException ignored) { + ignored.printStackTrace(); + } + } + + private static boolean hasChar(char ch, List<Special>... numSpecials) { + for (List<Special> specials : numSpecials) { + for (Special s : specials) { + if (s.ch == ch) { + return true; + } + } + } + return false; + } + + private static boolean hasOnly(char ch, List<Special>... numSpecials) { + for (List<Special> specials : numSpecials) { + for (Special s : specials) { + if (s.ch != ch) { + return false; + } + } + } + return true; + } + + private void writeSingleInteger(String fmt, int num, StringBuffer output, + List<Special> numSpecials, Set<StringMod> mods) { + + StringBuffer sb = new StringBuffer(); + Formatter formatter = new Formatter(sb); + formatter.format(LOCALE, fmt, num); + writeInteger(sb, output, numSpecials, mods, false); + } + + private void writeInteger(StringBuffer result, StringBuffer output, + List<Special> numSpecials, Set<StringMod> mods, + boolean showCommas) { + + int pos = result.indexOf(".") - 1; + if (pos < 0) { + if (exponent != null && numSpecials == integerSpecials) + pos = result.indexOf("E") - 1; + else + pos = result.length() - 1; + } + + int strip; + for (strip = 0; strip < pos; strip++) { + char resultCh = result.charAt(strip); + if (resultCh != '0' && resultCh != ',') + break; + } + + ListIterator<Special> it = numSpecials.listIterator(numSpecials.size()); + boolean followWithComma = false; + Special lastOutputIntegerDigit = null; + int digit = 0; + while (it.hasPrevious()) { + char resultCh; + if (pos >= 0) + resultCh = result.charAt(pos); + else { + // If result is shorter than field, pretend there are leading zeros + resultCh = '0'; + } + Special s = it.previous(); + followWithComma = showCommas && digit > 0 && digit % 3 == 0; + boolean zeroStrip = false; + if (resultCh != '0' || s.ch == '0' || s.ch == '?' || pos >= strip) { + zeroStrip = s.ch == '?' && pos < strip; + output.setCharAt(s.pos, (zeroStrip ? ' ' : resultCh)); + lastOutputIntegerDigit = s; + } + if (followWithComma) { + mods.add(insertMod(s, zeroStrip ? " " : ",", StringMod.AFTER)); + followWithComma = false; + } + digit++; + --pos; + } + StringBuffer extraLeadingDigits = new StringBuffer(); + if (pos >= 0) { + // We ran out of places to put digits before we ran out of digits; put this aside so we can add it later + ++pos; // pos was decremented at the end of the loop above when the iterator was at its end + extraLeadingDigits = new StringBuffer(result.substring(0, pos)); + if (showCommas) { + while (pos > 0) { + if (digit > 0 && digit % 3 == 0) + extraLeadingDigits.insert(pos, ','); + digit++; + --pos; + } + } + mods.add(insertMod(lastOutputIntegerDigit, extraLeadingDigits, + StringMod.BEFORE)); + } + } + + private void writeFractional(StringBuffer result, StringBuffer output) { + int digit; + int strip; + ListIterator<Special> it; + if (fractionalSpecials.size() > 0) { + digit = result.indexOf(".") + 1; + if (exponent != null) + strip = result.indexOf("e") - 1; + else + strip = result.length() - 1; + while (strip > digit && result.charAt(strip) == '0') + strip--; + it = fractionalSpecials.listIterator(); + while (it.hasNext()) { + Special s = it.next(); + char resultCh = result.charAt(digit); + if (resultCh != '0' || s.ch == '0' || digit < strip) + output.setCharAt(s.pos, resultCh); + else if (s.ch == '?') { + // This is when we're in trailing zeros, and the format is '?'. We still strip out remaining '#'s later + output.setCharAt(s.pos, ' '); + } + digit++; + } + } + } + + /** + * {@inheritDoc} + * <p/> + * For a number, this is <tt>"#"</tt> for integer values, and <tt>"#.#"</tt> + * for floating-point values. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + SIMPLE_NUMBER.formatValue(toAppendTo, value); + } + + /** + * Based on org.apache.commons.math.fraction.Fraction from Apache Commons-Math. + * YK: The only reason of having this inner class is to avoid dependency on the Commons-Math jar. + */ + private static class Fraction { + /** The denominator. */ + private final int denominator; + + /** The numerator. */ + private final int numerator; + + /** + * Create a fraction given the double value and either the maximum error + * allowed or the maximum number of denominator digits. + * + * @param value the double value to convert to a fraction. + * @param epsilon maximum error allowed. The resulting fraction is within + * <code>epsilon</code> of <code>value</code>, in absolute terms. + * @param maxDenominator maximum denominator value allowed. + * @param maxIterations maximum number of convergents + * @throws RuntimeException if the continued fraction failed to + * converge. + */ + private Fraction(double value, double epsilon, int maxDenominator, int maxIterations) + { + long overflow = Integer.MAX_VALUE; + double r0 = value; + long a0 = (long)Math.floor(r0); + if (a0 > overflow) { + throw new IllegalArgumentException("Overflow trying to convert "+value+" to fraction ("+a0+"/"+1l+")"); + } + + // check for (almost) integer arguments, which should not go + // to iterations. + if (Math.abs(a0 - value) < epsilon) { + this.numerator = (int) a0; + this.denominator = 1; + return; + } + + long p0 = 1; + long q0 = 0; + long p1 = a0; + long q1 = 1; + + long p2; + long q2; + + int n = 0; + boolean stop = false; + do { + ++n; + double r1 = 1.0 / (r0 - a0); + long a1 = (long)Math.floor(r1); + p2 = (a1 * p1) + p0; + q2 = (a1 * q1) + q0; + if ((p2 > overflow) || (q2 > overflow)) { + throw new RuntimeException("Overflow trying to convert "+value+" to fraction ("+p2+"/"+q2+")"); + } + + double convergent = (double)p2 / (double)q2; + if (n < maxIterations && Math.abs(convergent - value) > epsilon && q2 < maxDenominator) { + p0 = p1; + p1 = p2; + q0 = q1; + q1 = q2; + a0 = a1; + r0 = r1; + } else { + stop = true; + } + } while (!stop); + + if (n >= maxIterations) { + throw new RuntimeException("Unable to convert "+value+" to fraction after "+maxIterations+" iterations"); + } + + if (q2 < maxDenominator) { + this.numerator = (int) p2; + this.denominator = (int) q2; + } else { + this.numerator = (int) p1; + this.denominator = (int) q1; + } + + } + + /** + * Create a fraction given the double value and maximum denominator. + * <p> + * References: + * <ul> + * <li><a href="http://mathworld.wolfram.com/ContinuedFraction.html"> + * Continued Fraction</a> equations (11) and (22)-(26)</li> + * </ul> + * </p> + * @param value the double value to convert to a fraction. + * @param maxDenominator The maximum allowed value for denominator + * @throws RuntimeException if the continued fraction failed to + * converge + */ + public Fraction(double value, int maxDenominator) + { + this(value, 0, maxDenominator, 100); + } + + /** + * Access the denominator. + * @return the denominator. + */ + public int getDenominator() { + return denominator; + } + + /** + * Access the numerator. + * @return the numerator. + */ + public int getNumerator() { + return numerator; + } + + } + +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/CellTextFormatter.java b/src/java/org/apache/poi/ss/format/CellTextFormatter.java new file mode 100644 index 0000000000..ebefa9847a --- /dev/null +++ b/src/java/org/apache/poi/ss/format/CellTextFormatter.java @@ -0,0 +1,79 @@ +/* ==================================================================== + 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.format; + +import org.apache.poi.ss.format.CellFormatPart.PartHandler; + +import java.util.regex.Matcher; + +/** + * This class implements printing out text. + * + * @author Ken Arnold, Industrious Media LLC + */ +public class CellTextFormatter extends CellFormatter { + private final int[] textPos; + private final String desc; + + static final CellFormatter SIMPLE_TEXT = new CellTextFormatter("@"); + + public CellTextFormatter(String format) { + super(format); + + final int[] numPlaces = new int[1]; + + desc = CellFormatPart.parseFormat(format, CellFormatType.TEXT, + new PartHandler() { + public String handlePart(Matcher m, String part, + CellFormatType type, StringBuffer desc) { + if (part.equals("@")) { + numPlaces[0]++; + return "\u0000"; + } + return null; + } + }).toString(); + + // Remember the "@" positions in last-to-first order (to make insertion easier) + textPos = new int[numPlaces[0]]; + int pos = desc.length() - 1; + for (int i = 0; i < textPos.length; i++) { + textPos[i] = desc.lastIndexOf("\u0000", pos); + pos = textPos[i] - 1; + } + } + + /** {@inheritDoc} */ + public void formatValue(StringBuffer toAppendTo, Object obj) { + int start = toAppendTo.length(); + String text = obj.toString(); + toAppendTo.append(desc); + for (int i = 0; i < textPos.length; i++) { + int pos = start + textPos[i]; + toAppendTo.replace(pos, pos + 1, text); + } + } + + /** + * {@inheritDoc} + * <p/> + * For text, this is just printing the text. + */ + public void simpleValue(StringBuffer toAppendTo, Object value) { + SIMPLE_TEXT.formatValue(toAppendTo, value); + } +}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/format/package.html b/src/java/org/apache/poi/ss/format/package.html new file mode 100644 index 0000000000..d5ab99fb60 --- /dev/null +++ b/src/java/org/apache/poi/ss/format/package.html @@ -0,0 +1,3 @@ +<body> +This package contains classes that implement cell formatting +</body> |