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

/**
    * Calculate the workday past x workdays from a starting date, considering a range of holidays.
    *
    * @param start start date.
    * @param workdays number of workdays to be past from starting date.
    * @param holidays an array of holidays.
    * @return date past x workdays.
    */
public Date calculateWorkdays(double start, int workdays, double[] holidays) {
	Date startDate = DateUtil.getJavaDate(start);
	int direction = workdays < 0 ? -1 : 1;
	Calendar endDate = LocaleUtil.getLocaleCalendar();
	endDate.setTime(startDate);
	double excelEndDate = DateUtil.getExcelDate(endDate.getTime());
	while (workdays != 0) {
		endDate.add(Calendar.DAY_OF_YEAR, direction);
		excelEndDate += direction;
		if (endDate.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY
				&& endDate.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY
				&& !isHoliday(excelEndDate,	holidays)) {
			workdays -= direction;
		}
	}
	return endDate.getTime();
}
 

/**
 * Evaluate for WORKDAY. Given a date, a number of days and a optional date or interval of holidays, determines which date it is past
 * number of parametrized workdays.
 * 
 * @return {@link ValueEval} with date as its value.
 */
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    if (args.length < 2 || args.length > 3) {
        return ErrorEval.VALUE_INVALID;
    }

    int srcCellRow = ec.getRowIndex();
    int srcCellCol = ec.getColumnIndex();

    double start;
    int days;
    double[] holidays;
    try {
        start = this.evaluator.evaluateDateArg(args[0], srcCellRow, srcCellCol);
        days = (int) Math.floor(this.evaluator.evaluateNumberArg(args[1], srcCellRow, srcCellCol));
        ValueEval holidaysCell = args.length == 3 ? args[2] : null;
        holidays = this.evaluator.evaluateDatesArg(holidaysCell, srcCellRow, srcCellCol);
        return new NumberEval(DateUtil.getExcelDate(WorkdayCalculator.instance.calculateWorkdays(start, days, holidays)));
    } catch (EvaluationException e) {
        return ErrorEval.VALUE_INVALID;
    }
}
 

public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    if (args.length != 2) {
        return ErrorEval.VALUE_INVALID;
    }
    try {
        double startDateAsNumber = getValue(args[0]);
        int offsetInMonthAsNumber = (int) getValue(args[1]);

        Date startDate = DateUtil.getJavaDate(startDateAsNumber);
        Calendar calendar = LocaleUtil.getLocaleCalendar();
        calendar.setTime(startDate);
        calendar.add(Calendar.MONTH, offsetInMonthAsNumber);
        return new NumberEval(DateUtil.getExcelDate(calendar.getTime()));
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}
 

/**
 * Evaluate a generic {@link ValueEval} argument to a double value that represents a date in POI.
 * 
 * @param arg {@link ValueEval} an argument.
 * @param srcCellRow number cell row.
 * @param srcCellCol number cell column.
 * @return a double representing a date in POI.
 * @throws EvaluationException exception upon argument evaluation.
 */
public double evaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
    ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol);

    if (ve instanceof StringEval) {
        String strVal = ((StringEval) ve).getStringValue();
        Double dVal = OperandResolver.parseDouble(strVal);
        if (dVal != null) {
            return dVal.doubleValue();
        }
        Calendar date = DateParser.parseDate(strVal);
        return DateUtil.getExcelDate(date, false);
    }
    return OperandResolver.coerceValueToDouble(ve);
}
 

private static double evaluateDateArg(ValueEval arg, int srcCellRow, int srcCellCol) throws EvaluationException {
	ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow, (short) srcCellCol);

	if (ve instanceof StringEval) {
		String strVal = ((StringEval) ve).getStringValue();
		Double dVal = OperandResolver.parseDouble(strVal);
		if (dVal != null) {
			return dVal.doubleValue();
		}
		Calendar date = DateParser.parseDate(strVal);
		return DateUtil.getExcelDate(date, false);
	}
	return OperandResolver.coerceValueToDouble(ve);
}
 

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
	Calendar now = LocaleUtil.getLocaleCalendar();
	now.clear(Calendar.HOUR);
       now.set(Calendar.HOUR_OF_DAY,0);
	now.clear(Calendar.MINUTE);
	now.clear(Calendar.SECOND);
	now.clear(Calendar.MILLISECOND);
	return new NumberEval(DateUtil.getExcelDate(now.getTime()));
}
 

@Override
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
    if (args.length != 2) {
        return ErrorEval.VALUE_INVALID;
    }

    try {
        double startDateAsNumber = NumericFunction.singleOperandEvaluate(args[0], ec.getRowIndex(), ec.getColumnIndex());
        int months = (int) NumericFunction.singleOperandEvaluate(args[1], ec.getRowIndex(), ec.getColumnIndex());

        // Excel treats date 0 as 1900-01-00; EOMONTH results in 1900-01-31
        if (startDateAsNumber >= 0.0 && startDateAsNumber < 1.0) {
            startDateAsNumber = 1.0;
        }

        Date startDate = DateUtil.getJavaDate(startDateAsNumber, false);

        Calendar cal = LocaleUtil.getLocaleCalendar();
        cal.setTime(startDate);
        cal.clear(Calendar.HOUR);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.clear(Calendar.MINUTE);
        cal.clear(Calendar.SECOND);
        cal.clear(Calendar.MILLISECOND);

        cal.add(Calendar.MONTH, months + 1);
        cal.set(Calendar.DAY_OF_MONTH, 1);
        cal.add(Calendar.DAY_OF_MONTH, -1);

        return new NumberEval(DateUtil.getExcelDate(cal.getTime()));
    } catch (EvaluationException e) {
        return e.getErrorEval();
    }
}
 

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

@Test
public void setValueWithDate_SetsADateValueOnTheCell() {
    Date date = new Date();
    double excelDateNumber = DateUtil.getExcelDate(date);

    SpreadsheetCell cell = getCell();
    cell.setValue(date);
    assertThat(cell.getPoiCell().getCellType())
            .isEqualTo(CellType.NUMERIC);
    assertThat(cell.getPoiCell().getNumericCellValue())
            .isCloseTo(excelDateNumber, within(0.001));
}
 

@Test
public void setValueWithCalendar_SetsADateValueOnTheCell() {
    Calendar calendar = Calendar.getInstance();
    double excelDateNumber = DateUtil.getExcelDate(calendar.getTime());

    SpreadsheetCell cell = getCell();
    cell.setValue(calendar);

    assertThat(cell.getPoiCell().getCellType())
            .isEqualTo(CellType.NUMERIC);

    assertThat(cell.getPoiCell().getNumericCellValue())
            .isCloseTo(excelDateNumber, within(0.001));
}
 

/**
 * Note - works with Java Calendar months, not Excel months
 */
private static double evaluate(int year, int month, int pDay) throws EvaluationException {
   // We don't support negative years yet
	if (year < 0) {
		throw new EvaluationException(ErrorEval.VALUE_INVALID);
	}
	// Negative months are fairly easy
	while (month < 0) {
	   year--;
	   month += 12;
	}
	// Negative days are handled by the Java Calendar
	
	// Excel has bugs around leap years in 1900, handle them
	// Special case for the non-existant 1900 leap year
	if (year == 1900 && month == Calendar.FEBRUARY && pDay == 29) {
		return 60.0;
	}

	// If they give a date in 1900 in Jan/Feb, with the days
	//  putting it past the leap year, adjust
	int day = pDay;
	if (year == 1900) {
		if ((month == Calendar.JANUARY && day >= 60) ||
				(month == Calendar.FEBRUARY && day >= 30)) {
			day--;
		}
	}

	// Turn this into a Java date
	Calendar c = LocaleUtil.getLocaleCalendar(year, month, day);
	
	// Handle negative days of the week, that pull us across
	//  the 29th of Feb 1900
	if (pDay < 0 && c.get(Calendar.YEAR) == 1900 &&
	      month > Calendar.FEBRUARY && 
	      c.get(Calendar.MONTH) < Calendar.MARCH) {
	   c.add(Calendar.DATE, 1);
	}

	// TODO Identify if we're doing 1900 or 1904 date windowing
	boolean use1904windowing = false;
	
	// Have this Java date turned back into an Excel one
	return DateUtil.getExcelDate(c.getTime(), use1904windowing);
}
 

public ValueEval evaluate(int srcRowIndex, int srcColumnIndex) {
	Date now = new Date(System.currentTimeMillis());
	return new NumberEval(DateUtil.getExcelDate(now));
}