hssfdataformatter.java

来自「EXCEL read and write」· Java 代码 · 共 684 行 · 第 1/2 页

				i--;
			}
		}

		try {
			return new DecimalFormat(sb.toString());
		} catch(IllegalArgumentException iae) {

			// the pattern could not be parsed correctly,
			// so fall back to the default number format
			return getDefaultFormat(cellValue);
		}
	}

	/**
	 * Return true if the double value represents a whole number
	 * @param d the double value to check
	 * @return <code>true</code> 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(HSSFCell 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;
	}

	/**
	 * Returns the formatted value of an Excel date as a <tt>String</tt> based
	 * on the cell's <code>DataFormat</code>. i.e. "Thursday, January 02, 2003"
	 * , "01/02/2003" , "02-Jan" , etc.
	 *
	 * @param cell The cell
	 * @return a formatted date string
	 */
	private String getFormattedDateString(HSSFCell cell) {
		Format dateFormat = getFormat(cell);
		Date d = cell.getDateCellValue();
		if (dateFormat != null) {
			return dateFormat.format(d);
		}
		return d.toString();
	}

	/**
	 * Returns the formatted value of an Excel number as a <tt>String</tt>
	 * based on the cell's <code>DataFormat</code>. Supported formats include
	 * currency, percents, decimals, phone number, SSN, etc.:
	 * "61.54%", "$100.00", "[phone]".
	 *
	 * @param cell The cell
	 * @return a formatted number string
	 */
	private String getFormattedNumberString(HSSFCell 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(HSSFCell)
	 */
	public String formatRawCellContents(double value, int formatIndex, String formatString) {
		// Is it a date?
		if(HSSFDateUtil.isADateFormat(formatIndex,formatString) &&
				HSSFDateUtil.isValidExcelDate(value)) {

			Format dateFormat = getFormat(value, formatIndex, formatString);
			Date d = HSSFDateUtil.getJavaDate(value);
			if (dateFormat == null) {
				return d.toString();
			}
			return dateFormat.format(d);
		}
		// else Number
		Format numberFormat = getFormat(value, formatIndex, formatString);
		if (numberFormat == null) {
			return String.valueOf(value);
		}
		return numberFormat.format(new Double(value));
	}

	/**
	 * <p>
	 * Returns the formatted value of a cell as a <tt>String</tt> regardless
	 * of the cell type. If the Excel format pattern cannot be parsed then the
	 * cell value will be formatted using a default format.
	 * </p>
	 * <p>When passed a null or blank cell, this method will return an empty
	 * String (""). Formulas in formula type cells will not be evaluated.
	 * </p>
	 *
	 * @param cell The cell
	 * @return the formatted cell value as a String
	 */
	public String formatCellValue(HSSFCell cell) {
		return formatCellValue(cell, null);
	}

	/**
	 * <p>
	 * Returns the formatted value of a cell as a <tt>String</tt> regardless
	 * of the cell type. If the Excel format pattern cannot be parsed then the
	 * cell value will be formatted using a default format.
	 * </p>
	 * <p>When passed a null or blank cell, this method will return an empty
	 * String (""). Formula cells will be evaluated using the given
	 * {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
	 * evaluator is null, then the formula String will be returned. The caller
	 * is responsible for setting the currentRow on the evaluator
	 *</p>
	 *
	 * @param cell The cell (can be null)
	 * @param evaluator The HSSFFormulaEvaluator (can be null)
	 * @return a string value of the cell
	 */
	public String formatCellValue(HSSFCell cell,
			HSSFFormulaEvaluator evaluator) throws IllegalArgumentException {

		if (cell == null) {
			return "";
		}

		int cellType = cell.getCellType();
		if (evaluator != null && cellType == HSSFCell.CELL_TYPE_FORMULA) {
			try {
				cellType = evaluator.evaluateFormulaCell(cell);
			} catch (RuntimeException e) {
				throw new RuntimeException("Did you forget to set the current" +
						" row on the HSSFFormulaEvaluator?", e);
			}
		}
		switch (cellType)
		{
			case HSSFCell.CELL_TYPE_FORMULA :
				// should only occur if evaluator is null
				return cell.getCellFormula();

			case HSSFCell.CELL_TYPE_NUMERIC :

				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					return getFormattedDateString(cell);
				}
				return getFormattedNumberString(cell);

			case HSSFCell.CELL_TYPE_STRING :
				return cell.getRichStringCellValue().getString();

			case HSSFCell.CELL_TYPE_BOOLEAN :
				return String.valueOf(cell.getBooleanCellValue());
			case HSSFCell.CELL_TYPE_BLANK :
				return "";
		}
		throw new RuntimeException("Unexpected celltype (" + cellType + ")");
	}


	/**
	 * <p>
	 * Sets a default number format to be used when the Excel format cannot be
	 * parsed successfully. <b>Note:</b> This is a fall back for when an error
	 * occurs while parsing an Excel number format pattern. This will not
	 * affect cells with the <em>General</em> format.
	 * </p>
	 * <p>
	 * The value that will be passed to the Format's format method (specified
	 * by <code>java.text.Format#format</code>) will be a double value from a
	 * numeric cell. Therefore the code in the format method should expect a
	 * <code>Number</code> value.
	 * </p>
	 *
	 * @param format A Format instance to be used as a default
	 * @see java.text.Format#format
	 */
	public void setDefaultNumberFormat(Format format) {
		Iterator itr = formats.entrySet().iterator();
		while(itr.hasNext()) {
			Map.Entry entry = (Map.Entry)itr.next();
			if (entry.getValue() == generalDecimalNumFormat
					|| entry.getValue() == generalWholeNumFormat) {
				entry.setValue(format);
			}
		}
		defaultNumFormat = format;
	}

	/**
	 * Adds a new format to the available formats.
	 * <p>
	 * The value that will be passed to the Format's format method (specified
	 * by <code>java.text.Format#format</code>) will be a double value from a
	 * numeric cell. Therefore the code in the format method should expect a
	 * <code>Number</code> value.
	 * </p>
	 * @param excelFormatStr The data format string
	 * @param format A Format instance
	 */
	public void addFormat(String excelFormatStr, Format format) {
		formats.put(excelFormatStr, format);
	}

	// Some custom formats

	/**
	 * @return a <tt>DecimalFormat</tt> with parseIntegerOnly set <code>true</code>
	 */
	/* package */ static DecimalFormat createIntegerOnlyFormat(String fmt) {
		DecimalFormat result = new DecimalFormat(fmt);
		result.setParseIntegerOnly(true);
		return result;
	}
	/**
	 * Format class for Excel's SSN format. This class mimics Excel's built-in
	 * SSN formatting.
	 *
	 * @author James May
	 */
	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
	 */
	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
	 */
	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);
		}
	}
}