* Internally, formats will be implemented using subclasses of {@link Format} * such as {@link DecimalFormat} and {@link SimpleDateFormat}. Therefore the * formats used by this class must obey the same pattern rules as these Format * subclasses. This means that only legal number pattern characters ("0", "#", * ".", "," etc.) may appear in number formats. Other characters can be * inserted before or after the number pattern to form a * prefix or suffix. *
*
* For example the Excel pattern "$#,##0.00 "USD"_);($#,##0.00 "USD")"
* will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)".
* However the pattern "00-00-00" is incorrectly formatted by
* DecimalFormat as "000000--". For Excel formats that are not compatible with
* DecimalFormat, you can provide your own custom {@link Format} implementation
* via DataFormatter.addFormat(String,Format). The following
* custom formats are already provided by this class:
*
*
* If the Excel format pattern cannot be parsed successfully, then a default
* format will be used. The default number format will mimic the Excel General
* format: "#" for whole numbers and "#.##########" for decimal numbers. You
* can override the default format pattern with
* DataFormatter.setDefaultNumberFormat(Format). Note: the
* default format will only be used when a Format cannot be created from the
* cell's data format string.
*
*
* Note that by default formatted numeric values are trimmed. * Excel formats can contain spacers and padding and the default behavior is to strip them off. *
*Example:
*
* Consider a numeric cell with a value 12.343 and format "##.##_ ".
* The trailing underscore and space ("_ ") in the format adds a space to the end and Excel formats this cell as "12.34 ",
* but DataFormatter trims the formatted value and returns "12.34".
*
emulateCsv=true flag in the DateFormatter cosntructor.
* If set to true, then the output tries to conform to what you get when you take an xls or xlsx in Excel and Save As CSV file:
* DataFormatter will output "0".
* null if the any of the
* following is true:
* true if d is a whole number
*/
private static boolean isWholeNumber(double d) {
return d == Math.floor(d);
}
/**
* Returns a default format for a cell.
* @param cell The cell
* @return a default format
*/
public Format getDefaultFormat(Cell cell) {
return getDefaultFormat(cell.getNumericCellValue());
}
private Format getDefaultFormat(double cellValue) {
// for numeric cells try user supplied default
if (defaultNumFormat != null) {
return defaultNumFormat;
// otherwise use general format
}
if (isWholeNumber(cellValue)){
return generalWholeNumFormat;
}
return generalDecimalNumFormat;
}
/**
* Performs Excel-style date formatting, using the
* supplied Date and format
*/
private String performDateFormatting(Date d, Format dateFormat) {
if(dateFormat != null) {
return dateFormat.format(d);
}
return d.toString();
}
/**
* Returns the formatted value of an Excel date as a String based
* on the cell's DataFormat. i.e. "Thursday, January 02, 2003"
* , "01/02/2003" , "02-Jan" , etc.
*
* @param cell The cell
* @return a formatted date string
*/
private String getFormattedDateString(Cell cell) {
Format dateFormat = getFormat(cell);
if(dateFormat instanceof ExcelStyleDateFormatter) {
// Hint about the raw excel value
((ExcelStyleDateFormatter)dateFormat).setDateToBeFormatted(
cell.getNumericCellValue()
);
}
Date d = cell.getDateCellValue();
return performDateFormatting(d, dateFormat);
}
/**
* Returns the formatted value of an Excel number as a String
* based on the cell's DataFormat. Supported formats include
* currency, percents, decimals, phone number, SSN, etc.:
* "61.54%", "$100.00", "(800) 555-1234".
*
* @param cell The cell
* @return a formatted number string
*/
private String getFormattedNumberString(Cell cell) {
Format numberFormat = getFormat(cell);
double d = cell.getNumericCellValue();
if (numberFormat == null) {
return String.valueOf(d);
}
return numberFormat.format(new Double(d));
}
/**
* Formats the given raw cell value, based on the supplied
* format index and string, according to excel style rules.
* @see #formatCellValue(Cell)
*/
public String formatRawCellContents(double value, int formatIndex, String formatString) {
return formatRawCellContents(value, formatIndex, formatString, false);
}
/**
* Formats the given raw cell value, based on the supplied
* format index and string, according to excel style rules.
* @see #formatCellValue(Cell)
*/
public String formatRawCellContents(double value, int formatIndex, String formatString, boolean use1904Windowing) {
// Is it a date?
if(DateUtil.isADateFormat(formatIndex,formatString)) {
if(DateUtil.isValidExcelDate(value)) {
Format dateFormat = getFormat(value, formatIndex, formatString);
if(dateFormat instanceof ExcelStyleDateFormatter) {
// Hint about the raw excel value
((ExcelStyleDateFormatter)dateFormat).setDateToBeFormatted(value);
}
Date d = DateUtil.getJavaDate(value, use1904Windowing);
return performDateFormatting(d, dateFormat);
}
// RK: Invalid dates are 255 #s.
if (emulateCsv) {
return invalidDateTimeString;
}
}
// else Number
Format numberFormat = getFormat(value, formatIndex, formatString);
if (numberFormat == null) {
return String.valueOf(value);
}
// RK: This hack handles scientific notation by adding the missing + back.
String result = numberFormat.format(new Double(value));
if (result.contains("E") && !result.contains("E-")) {
result = result.replaceFirst("E", "E+");
}
return result;
}
/**
* * Returns the formatted value of a cell as a String regardless * of the cell type. If the Excel format pattern cannot be parsed then the * cell value will be formatted using a default format. *
*When passed a null or blank cell, this method will return an empty * String (""). Formulas in formula type cells will not be evaluated. *
* * @param cell The cell * @return the formatted cell value as a String */ public String formatCellValue(Cell cell) { return formatCellValue(cell, null); } /** ** Returns the formatted value of a cell as a String regardless * of the cell type. If the Excel format pattern cannot be parsed then the * cell value will be formatted using a default format. *
*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 *
* * @param cell The cell (can be null) * @param evaluator The FormulaEvaluator (can be null) * @return a string value of the cell */ public String formatCellValue(Cell cell, FormulaEvaluator evaluator) { if (cell == null) { return ""; } int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) { if (evaluator == null) { return cell.getCellFormula(); } cellType = evaluator.evaluateFormulaCell(cell); } switch (cellType) { case Cell.CELL_TYPE_NUMERIC : if (DateUtil.isCellDateFormatted(cell)) { return getFormattedDateString(cell); } return getFormattedNumberString(cell); case Cell.CELL_TYPE_STRING : return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BOOLEAN : return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_BLANK : return ""; } throw new RuntimeException("Unexpected celltype (" + cellType + ")"); } /** ** Sets a default number format to be used when the Excel format cannot be * parsed successfully. Note: This is a fall back for when an error * occurs while parsing an Excel number format pattern. This will not * affect cells with the General format. *
*
* The value that will be passed to the Format's format method (specified
* by java.text.Format#format) will be a double value from a
* numeric cell. Therefore the code in the format method should expect a
* Number value.
*
* The value that will be passed to the Format's format method (specified
* by java.text.Format#format) will be a double value from a
* numeric cell. Therefore the code in the format method should expect a
* Number value.
*
true
*/
/* package */ static DecimalFormat createIntegerOnlyFormat(String fmt) {
DecimalFormat result = new DecimalFormat(fmt);
result.setParseIntegerOnly(true);
return result;
}
/**
* Enables excel style rounding mode (round half up)
* on the Decimal Format if possible.
* This will work for Java 1.6, but isn't possible
* on Java 1.5.
*/
public static void setExcelStyleRoundingMode(DecimalFormat format) {
setExcelStyleRoundingMode(format, RoundingMode.HALF_UP);
}
/**
* Enables custom rounding mode
* on the Decimal Format if possible.
* This will work for Java 1.6, but isn't possible
* on Java 1.5.
* @param format DecimalFormat
* @param roundingMode RoundingMode
*/
public static void setExcelStyleRoundingMode(DecimalFormat format, RoundingMode roundingMode) {
try {
Method srm = format.getClass().getMethod("setRoundingMode", RoundingMode.class);
srm.invoke(format, roundingMode);
} catch(NoSuchMethodException e) {
// Java 1.5
} catch(IllegalAccessException iae) {
// Shouldn't happen
throw new RuntimeException("Unable to set rounding mode", iae);
} catch(InvocationTargetException ite) {
// Shouldn't happen
throw new RuntimeException("Unable to set rounding mode", ite);
} catch(SecurityException se) {
// Not much we can do here
}
}
/**
* Format class for Excel's SSN format. This class mimics Excel's built-in
* SSN formatting.
*
* @author James May
*/
@SuppressWarnings("serial")
private static final class SSNFormat extends Format {
public static final Format instance = new SSNFormat();
private static final DecimalFormat df = createIntegerOnlyFormat("000000000");
private SSNFormat() {
// enforce singleton
}
/** Format a number as an SSN */
public static String format(Number num) {
String result = df.format(num);
StringBuffer sb = new StringBuffer();
sb.append(result.substring(0, 3)).append('-');
sb.append(result.substring(3, 5)).append('-');
sb.append(result.substring(5, 9));
return sb.toString();
}
public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
return toAppendTo.append(format((Number)obj));
}
public Object parseObject(String source, ParsePosition pos) {
return df.parseObject(source, pos);
}
}
/**
* Format class for Excel Zip + 4 format. This class mimics Excel's
* built-in formatting for Zip + 4.
* @author James May
*/
@SuppressWarnings("serial")
private static final class ZipPlusFourFormat extends Format {
public static final Format instance = new ZipPlusFourFormat();
private static final DecimalFormat df = createIntegerOnlyFormat("000000000");
private ZipPlusFourFormat() {
// enforce singleton
}
/** Format a number as Zip + 4 */
public static String format(Number num) {
String result = df.format(num);
StringBuffer sb = new StringBuffer();
sb.append(result.substring(0, 5)).append('-');
sb.append(result.substring(5, 9));
return sb.toString();
}
public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
return toAppendTo.append(format((Number)obj));
}
public Object parseObject(String source, ParsePosition pos) {
return df.parseObject(source, pos);
}
}
/**
* Format class for Excel phone number format. This class mimics Excel's
* built-in phone number formatting.
* @author James May
*/
@SuppressWarnings("serial")
private static final class PhoneFormat extends Format {
public static final Format instance = new PhoneFormat();
private static final DecimalFormat df = createIntegerOnlyFormat("##########");
private PhoneFormat() {
// enforce singleton
}
/** Format a number as a phone number */
public static String format(Number num) {
String result = df.format(num);
StringBuffer sb = new StringBuffer();
String seg1, seg2, seg3;
int len = result.length();
if (len <= 4) {
return result;
}
seg3 = result.substring(len - 4, len);
seg2 = result.substring(Math.max(0, len - 7), len - 4);
seg1 = result.substring(Math.max(0, len - 10), Math.max(0, len - 7));
if(seg1 != null && seg1.trim().length() > 0) {
sb.append('(').append(seg1).append(") ");
}
if(seg2 != null && seg2.trim().length() > 0) {
sb.append(seg2).append('-');
}
sb.append(seg3);
return sb.toString();
}
public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
return toAppendTo.append(format((Number)obj));
}
public Object parseObject(String source, ParsePosition pos) {
return df.parseObject(source, pos);
}
}
/**
* Format class that does nothing and always returns a constant string.
*
* This format is used to simulate Excel's handling of a format string
* of all # when the value is 0. Excel will output "", Java will output "0".
*
* @see DataFormatter#createFormat(double, int, String)
*/
@SuppressWarnings("serial")
private static final class ConstantStringFormat extends Format {
private static final DecimalFormat df = createIntegerOnlyFormat("##########");
private final String str;
public ConstantStringFormat(String s) {
str = s;
}
@Override
public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
return toAppendTo.append(str);
}
@Override
public Object parseObject(String source, ParsePosition pos) {
return df.parseObject(source, pos);
}
}
}