Package org.apache.poi.ss.usermodel
Class DateUtil
java.lang.Object
org.apache.poi.ss.usermodel.DateUtil
- Direct Known Subclasses:
HSSFDateUtil
Contains methods for dealing with Excel dates.
-
Field Summary
FieldsModifier and TypeFieldDescriptionstatic final long
static final int
static final int
static final int
static final int
-
Constructor Summary
Constructors -
Method Summary
Modifier and TypeMethodDescriptionprotected static int
absoluteDay
(Calendar cal, boolean use1904windowing) Given a Calendar, return the number of days since 1900/12/31.static double
convertTime
(String timeStr) Converts a string of format "HH:MM" or "HH:MM:SS" to its (Excel) numeric equivalentstatic double
getExcelDate
(Calendar date, boolean use1904windowing) Given a Date in the form of a Calendar, converts it into a double representing its internal Excel representation, which is the number of days since 1/1/1900.static double
getExcelDate
(Date date) Given a Date, converts it into a double representing its internal Excel representation, which is the number of days since 1/1/1900.static double
getExcelDate
(Date date, boolean use1904windowing) Given a Date, converts it into a double representing its internal Excel representation, which is the number of days since 1/1/1900.static Calendar
getJavaCalendar
(double date) Get EXCEL date as Java Calendar (with default time zone).static Calendar
getJavaCalendar
(double date, boolean use1904windowing) Get EXCEL date as Java Calendar (with default time zone).static Calendar
getJavaCalendar
(double date, boolean use1904windowing, TimeZone timeZone) Get EXCEL date as Java Calendar with given time zone.static Calendar
getJavaCalendar
(double date, boolean use1904windowing, TimeZone timeZone, boolean roundSeconds) Get EXCEL date as Java Calendar with given time zone.static Calendar
getJavaCalendarUTC
(double date, boolean use1904windowing) Get EXCEL date as Java Calendar with UTC time zone.static Date
getJavaDate
(double date) Given an Excel date with using 1900 date windowing, and converts it to a java.util.Date.static Date
getJavaDate
(double date, boolean use1904windowing) Given an Excel date with either 1900 or 1904 date windowing, converts it to a java.util.Date.static Date
getJavaDate
(double date, boolean use1904windowing, TimeZone tz) Given an Excel date with either 1900 or 1904 date windowing, converts it to a java.util.Date.static Date
getJavaDate
(double date, boolean use1904windowing, TimeZone tz, boolean roundSeconds) Given an Excel date with either 1900 or 1904 date windowing, converts it to a java.util.Date.static Date
getJavaDate
(double date, TimeZone tz) Given an Excel date with using 1900 date windowing, and converts it to a java.util.Date.static boolean
isADateFormat
(int formatIndex, String formatString) Given a format ID and its format String, will check to see if the format represents a date format or not.static boolean
isADateFormat
(ExcelNumberFormat numFmt) Given a format ID and its format String, will check to see if the format represents a date format or not.static boolean
isCellDateFormatted
(Cell cell) Check if a cell contains a date Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such.static boolean
isCellDateFormatted
(Cell cell, ConditionalFormattingEvaluator cfEvaluator) Check if a cell contains a date Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such.static boolean
Check if a cell contains a date, checking only for internal excel date formats.static boolean
isInternalDateFormat
(int format) Given a format ID this will check whether the format represents an internal excel date format or not.static boolean
isValidExcelDate
(double value) Given a double, checks if it is a valid Excel date.static Date
parseYYYYMMDDDate
(String dateStr) Converts a string of format "YYYY/MM/DD" to its (Excel) numeric equivalentstatic void
setCalendar
(Calendar calendar, int wholeDays, int millisecondsInDay, boolean use1904windowing, boolean roundSeconds)
-
Field Details
-
SECONDS_PER_MINUTE
public static final int SECONDS_PER_MINUTE- See Also:
-
MINUTES_PER_HOUR
public static final int MINUTES_PER_HOUR- See Also:
-
HOURS_PER_DAY
public static final int HOURS_PER_DAY- See Also:
-
SECONDS_PER_DAY
public static final int SECONDS_PER_DAY- See Also:
-
DAY_MILLISECONDS
public static final long DAY_MILLISECONDS- See Also:
-
-
Constructor Details
-
DateUtil
protected DateUtil()
-
-
Method Details
-
getExcelDate
Given a Date, converts it into a double representing its internal Excel representation, which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.- Parameters:
date
- the Date- Returns:
- Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
-
getExcelDate
Given a Date, converts it into a double representing its internal Excel representation, which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.- Parameters:
date
- the Dateuse1904windowing
- Should 1900 or 1904 date windowing be used?- Returns:
- Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
-
getExcelDate
Given a Date in the form of a Calendar, converts it into a double representing its internal Excel representation, which is the number of days since 1/1/1900. Fractional days represent hours, minutes, and seconds.- Parameters:
date
- the Calendar holding the date to convertuse1904windowing
- Should 1900 or 1904 date windowing be used?- Returns:
- Excel representation of Date (-1 if error - test for error by checking for less than 0.1)
-
getJavaDate
Given an Excel date with using 1900 date windowing, and converts it to a java.util.Date. Excel Dates and Times are stored without any timezone information. If you know (through other means) that your file uses a different TimeZone to the system default, you can use this version of the getJavaDate() method to handle it.- Parameters:
date
- The Excel date.tz
- The TimeZone to evaluate the date in- Returns:
- Java representation of the date, or null if date is not a valid Excel date
-
getJavaDate
Given an Excel date with using 1900 date windowing, and converts it to a java.util.Date. NOTE: If the defaultTimeZone
in Java uses Daylight Saving Time then the conversion back to an Excel date may not give the same value, that is the comparisonexcelDate == getExcelDate(getJavaDate(excelDate,false))
is not always true. For example if default timezone isEurope/Copenhagen
, on 2004-03-28 the minute after 01:59 CET is 03:00 CEST, if the excel date represents a time between 02:00 and 03:00 then it is converted to past 03:00 summer time- Parameters:
date
- The Excel date.- Returns:
- Java representation of the date, or null if date is not a valid Excel date
- See Also:
-
getJavaDate
Given an Excel date with either 1900 or 1904 date windowing, converts it to a java.util.Date. Excel Dates and Times are stored without any timezone information. If you know (through other means) that your file uses a different TimeZone to the system default, you can use this version of the getJavaDate() method to handle it.- Parameters:
date
- The Excel date.use1904windowing
- true if date uses 1904 windowing, or false if using 1900 date windowing.tz
- The TimeZone to evaluate the date in- Returns:
- Java representation of the date, or null if date is not a valid Excel date
-
getJavaDate
public static Date getJavaDate(double date, boolean use1904windowing, TimeZone tz, boolean roundSeconds) Given an Excel date with either 1900 or 1904 date windowing, converts it to a java.util.Date. Excel Dates and Times are stored without any timezone information. If you know (through other means) that your file uses a different TimeZone to the system default, you can use this version of the getJavaDate() method to handle it.- Parameters:
date
- The Excel date.use1904windowing
- true if date uses 1904 windowing, or false if using 1900 date windowing.tz
- The TimeZone to evaluate the date inroundSeconds
- round to closest second- Returns:
- Java representation of the date, or null if date is not a valid Excel date
-
getJavaDate
Given an Excel date with either 1900 or 1904 date windowing, converts it to a java.util.Date. NOTE: If the defaultTimeZone
in Java uses Daylight Saving Time then the conversion back to an Excel date may not give the same value, that is the comparisonexcelDate == getExcelDate(getJavaDate(excelDate,false))
is not always true. For example if default timezone isEurope/Copenhagen
, on 2004-03-28 the minute after 01:59 CET is 03:00 CEST, if the excel date represents a time between 02:00 and 03:00 then it is converted to past 03:00 summer time- Parameters:
date
- The Excel date.use1904windowing
- true if date uses 1904 windowing, or false if using 1900 date windowing.- Returns:
- Java representation of the date, or null if date is not a valid Excel date
- See Also:
-
setCalendar
public static void setCalendar(Calendar calendar, int wholeDays, int millisecondsInDay, boolean use1904windowing, boolean roundSeconds) -
getJavaCalendar
Get EXCEL date as Java Calendar (with default time zone). This is likegetJavaDate(double)
but returns a Calendar object.- Parameters:
date
- The Excel date.- Returns:
- Java representation of the date, or null if date is not a valid Excel date
-
getJavaCalendar
Get EXCEL date as Java Calendar (with default time zone). This is likegetJavaDate(double, boolean)
but returns a Calendar object.- Parameters:
date
- The Excel date.use1904windowing
- true if date uses 1904 windowing, or false if using 1900 date windowing.- Returns:
- Java representation of the date, or null if date is not a valid Excel date
-
getJavaCalendarUTC
Get EXCEL date as Java Calendar with UTC time zone. This is similar togetJavaDate(double, boolean)
but returns a Calendar object that has UTC as time zone, so no daylight saving hassle.- Parameters:
date
- The Excel date.use1904windowing
- true if date uses 1904 windowing, or false if using 1900 date windowing.- Returns:
- Java representation of the date in UTC, or null if date is not a valid Excel date
-
getJavaCalendar
Get EXCEL date as Java Calendar with given time zone.- Parameters:
date
- The Excel date.use1904windowing
- true if date uses 1904 windowing, or false if using 1900 date windowing.timeZone
- The TimeZone to evaluate the date in- Returns:
- Java representation of the date, or null if date is not a valid Excel date
-
getJavaCalendar
public static Calendar getJavaCalendar(double date, boolean use1904windowing, TimeZone timeZone, boolean roundSeconds) Get EXCEL date as Java Calendar with given time zone.- Parameters:
date
- The Excel date.use1904windowing
- true if date uses 1904 windowing, or false if using 1900 date windowing.timeZone
- The TimeZone to evaluate the date inroundSeconds
- round to closest second- Returns:
- Java representation of the date, or null if date is not a valid Excel date
-
isADateFormat
Given a format ID and its format String, will check to see if the format represents a date format or not. Firstly, it will check to see if the format ID corresponds to an internal excel date format (eg most US date formats) If not, it will check to see if the format string only contains date formatting characters (ymd-/), which covers most non US date formats.- Parameters:
numFmt
- The number format index and string expression, or null if not specified- Returns:
- true if it is a valid date format, false if not or null
- See Also:
-
isADateFormat
Given a format ID and its format String, will check to see if the format represents a date format or not. Firstly, it will check to see if the format ID corresponds to an internal excel date format (eg most US date formats) If not, it will check to see if the format string only contains date formatting characters (ymd-/), which covers most non US date formats.- Parameters:
formatIndex
- The index of the format, eg from ExtendedFormatRecord.getFormatIndexformatString
- The format string, eg from FormatRecord.getFormatString- Returns:
- true if it is a valid date format, false if not or null
- See Also:
-
isInternalDateFormat
public static boolean isInternalDateFormat(int format) Given a format ID this will check whether the format represents an internal excel date format or not.- See Also:
-
isCellDateFormatted
Check if a cell contains a date Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such.- Parameters:
cell
-- Returns:
- true if it looks like a date
- See Also:
-
isCellDateFormatted
Check if a cell contains a date Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such. Format is determined from applicable conditional formatting, if any, or cell style.- Parameters:
cell
-cfEvaluator
- if available, or null- Returns:
- true if it looks like a date
- See Also:
-
isCellInternalDateFormatted
Check if a cell contains a date, checking only for internal excel date formats. As Excel stores a great many of its dates in "non-internal" date formats, you will not normally want to use this method.- See Also:
-
isValidExcelDate
public static boolean isValidExcelDate(double value) Given a double, checks if it is a valid Excel date.- Parameters:
value
- the double value- Returns:
- true if valid
-
absoluteDay
Given a Calendar, return the number of days since 1900/12/31.- Parameters:
cal
- the Calendar- Returns:
- days number of days since 1900/12/31
- Throws:
IllegalArgumentException
- if date is invalid
-
convertTime
Converts a string of format "HH:MM" or "HH:MM:SS" to its (Excel) numeric equivalent- Returns:
- a double between 0 and 1 representing the fraction of the day
-
parseYYYYMMDDDate
Converts a string of format "YYYY/MM/DD" to its (Excel) numeric equivalent- Returns:
- a double representing the (integer) number of days since the start of the Excel epoch
-