Java Code Examples for org.apache.poi.ss.usermodel.DateUtil#isValidExcelDate()

/**
 * 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 (ultimateTypeEnum(c)) {
    case BLANK:
        return apply("");
    case BOOLEAN:
        return apply(c.getBooleanCellValue());
    case NUMERIC:
        Double value = c.getNumericCellValue();
        if (getApplicableFormatPart(value).getCellFormatType() == CellFormatType.DATE) {
            if (DateUtil.isValidExcelDate(value)) {
                return apply(c.getDateCellValue(), value);
            } else {
                return apply(INVALID_VALUE_FOR_FORMAT);
            }
        } else {
            return apply(value);
        }
    case STRING:
        return apply(c.getStringCellValue());
    default:
        return apply("?");
    }
}
 

/**
 * 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 (ultimateTypeEnum(c)) {
        case BLANK:
            return apply(label, "");
        case BOOLEAN:
            return apply(label, c.getBooleanCellValue());
        case NUMERIC:
            Double value = c.getNumericCellValue();
            if (getApplicableFormatPart(value).getCellFormatType() == CellFormatType.DATE) {
                if (DateUtil.isValidExcelDate(value)) {
                    return apply(label, c.getDateCellValue(), value);
                } else {
                    return apply(label, INVALID_VALUE_FOR_FORMAT);
                }
            } else {
                return apply(label, value);
            }
        case STRING:
            return apply(label, c.getStringCellValue());
        default:
            return apply(label, "?");
        }
}
 

/**
 * 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 &&
                ((formatPartCount == 2
                        && !posNumFmt.hasCondition() && !negNumFmt.hasCondition())
                || (formatPartCount == 3 && !negNumFmt.hasCondition())
                || (formatPartCount == 4 && !negNumFmt.hasCondition()))) {
            // The negative number format has the negative formatting required,
            // e.g. minus sign or brackets, so pass a positive value so that
            // the default leading minus sign is not also output
            return negNumFmt.apply(-val);
        } else {
            return getApplicableFormatPart(val).apply(val);
        }
    } else if (value instanceof java.util.Date) {
        // Don't know (and can't get) the workbook date windowing (1900 or 1904)
        // so assume 1900 date windowing
        Double numericValue = DateUtil.getExcelDate((Date) value);
        if (DateUtil.isValidExcelDate(numericValue)) {
            return getApplicableFormatPart(numericValue).apply(value);
        } else {
            throw new IllegalArgumentException("value " + numericValue + " of date " + value + " is not a valid Excel date");
        }
    } else {
        return textFmt.apply(value);
    }
}
 

@Override
public Date getDate(int columnIndex) throws ParseException {
	String dateValue = getValue(columnIndex);
	if (dateValue == null) {
		return null;
	}
	switch (getCellType(columnIndex)) {
		case NUMBER:
		case DATE:
			// XLSX stores dates as double values
			double dateAsDouble = Double.parseDouble(dateValue);

			// Use POI methods to convert value to Date java object
			if (DateUtil.isValidExcelDate(dateAsDouble)) {
				return DateUtil.getJavaDate(dateAsDouble, xlsxWorkbook.isDate1904);
			} else {
				throw new ParseException(new ParsingError(getCurrentRow() + 1, columnIndex,
						ParsingError.ErrorCode.UNPARSEABLE_DATE, dateValue));
			}
		case INLINE_STRING:
		case SHARED_STRING:
		case STRING:
			// In case a date is stored as String, we try to parse it here
			String dateString = dateValue;
			try {
				return dateFormatProvider.geDateFormat().parse(dateString);
			} catch (java.text.ParseException e) {
				throw new ParseException(new ParsingError(getCurrentRow() + 1, columnIndex,
						ParsingError.ErrorCode.UNPARSEABLE_DATE, dateString));
			}
		default:
			throw new ParseException(new ParsingError(getCurrentRow() + 1, columnIndex,
					ParsingError.ErrorCode.UNPARSEABLE_DATE, dateValue));

	}
}
 

@Override
public String formatRawCellContents(double value, int formatIndex, String formatString, boolean use1904Windowing) {
    // TDP-1656 (olamy) for some reasons poi use date format with only 2 digits for years
    // even the excel data ws using 4 so force the pattern here
    if ( DateUtil.isValidExcelDate( value) && StringUtils.countMatches( formatString, "y") == 2) {
        formatString = StringUtils.replace(formatString, "yy", "yyyy");
    }
    if (DateUtil.isValidExcelDate(value) && StringUtils.countMatches(formatString, "Y") == 2) {
        formatString = StringUtils.replace(formatString, "YY", "YYYY");
    }
    return super.formatRawCellContents(value, formatIndex, formatString, use1904Windowing);

}
 

@Override
public String formatRawCellContents(double value, int formatIndex, String formatString,
        boolean use1904Windowing) {
    // TDP-1656 (olamy) for some reasons poi use date format with only 2 digits for years
    // even the excel data ws using 4 so force the pattern here
    if (DateUtil.isValidExcelDate(value) && StringUtils.countMatches(formatString, "y") == 2) {
        formatString = StringUtils.replace(formatString, "yy", "yyyy");
    }
    if (DateUtil.isValidExcelDate(value) && StringUtils.countMatches(formatString, "Y") == 2) {
        formatString = StringUtils.replace(formatString, "YY", "YYYY");
    }
    return super.formatRawCellContents(value, formatIndex, formatString, use1904Windowing);

}
 

/**
 * 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) {
    localeChangedObservable.checkForLocaleChange();

    // 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);
    }

    // When formatting 'value', double to text to BigDecimal produces more
    // accurate results than double to Double in JDK8 (as compared to
    // previous versions). However, if the value contains E notation, this
    // would expand the values, which we do not want, so revert to
    // original method.
    String result;
    final String textValue = NumberToTextConverter.toText(value);
    if (textValue.indexOf('E') > -1) {
        result = numberFormat.format(new Double(value));
    } else {
        result = numberFormat.format(new BigDecimal(textValue));
    }
    // Complete scientific notation by adding the missing +.
    if (result.indexOf('E') > -1 && !result.contains("E-")) {
        result = result.replaceFirst("E", "E+");
    }
    return result;
}
 

/**
 * Perform WEEKDAY(date, returnOption) function.
 * Note: Parameter texts are from German EXCEL-2010 help.
 * Parameters in args[]:
 *  args[0] serialDate
 * EXCEL-date value
 * Standardmaessig ist der 1. Januar 1900 die fortlaufende Zahl 1 und
 * der 1. Januar 2008 die fortlaufende Zahl 39.448, da dieser Tag nach 39.448 Tagen
 * auf den 01.01.1900 folgt.
 * @return Option (optional)
 * Bestimmt den Rueckgabewert:
	1	oder nicht angegeben Zahl 1 (Sonntag) bis 7 (Samstag). Verhaelt sich wie fruehere Microsoft Excel-Versionen.
	2	Zahl 1 (Montag) bis 7 (Sonntag).
	3	Zahl 0 (Montag) bis 6 (Sonntag).
	11	Die Zahlen 1 (Montag) bis 7 (Sonntag)
	12	Die Zahlen 1 (Dienstag) bis 7 (Montag)
	13	Die Zahlen 1 (Mittwoch) bis 7 (Dienstag)
	14	Die Zahlen 1 (Donnerstag) bis 7 (Mittwoch)
	15	Die Zahlen 1 (Freitag) bis 7 (Donnerstag)
	16	Die Zahlen 1 (Samstag) bis 7 (Freitag)
	17	Die Zahlen 1 (Sonntag) bis 7 (Samstag)
 */
public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
	try {
		if (args.length < 1  ||  args.length > 2) {
			return ErrorEval.VALUE_INVALID;
		}

		// extract first parameter
		ValueEval serialDateVE = OperandResolver.getSingleValue(args[0], srcRowIndex, srcColumnIndex);
		double serialDate = OperandResolver.coerceValueToDouble(serialDateVE);
		if (!DateUtil.isValidExcelDate(serialDate)) {
			return ErrorEval.NUM_ERROR;						// EXCEL uses this and no VALUE_ERROR
		}
		Calendar date = DateUtil.getJavaCalendar(serialDate, false);		// (XXX 1904-windowing not respected)
		int weekday = date.get(Calendar.DAY_OF_WEEK);		// => sunday = 1, monday = 2, ..., saturday = 7

		// extract second parameter
		int returnOption = 1;					// default value
		if (args.length == 2) {
			ValueEval ve = OperandResolver.getSingleValue(args[1], srcRowIndex, srcColumnIndex);
			if (ve == MissingArgEval.instance  ||  ve == BlankEval.instance) {
				return ErrorEval.NUM_ERROR;		// EXCEL uses this and no VALUE_ERROR
			}
			returnOption = OperandResolver.coerceValueToInt(ve);
			if (returnOption == 2) {
				returnOption = 11;				// both mean the same
			}
		} // if

		// perform calculation
		double result;
		if (returnOption == 1) {
			result = weekday;
		// value 2 is handled above (as value 11)
		} else if (returnOption == 3) {
			result = (weekday + 6 - 1) % 7;
		} else if (returnOption >= 11  &&  returnOption <= 17) {
			result = (weekday + 6 - (returnOption - 10)) % 7 + 1;		// rotate in the value range 1 to 7
		} else {
			return ErrorEval.NUM_ERROR;		// EXCEL uses this and no VALUE_ERROR
		}

		return new NumberEval(result);
	} catch (EvaluationException e) {
		return e.getErrorEval();
	}
}