org.ttzero.excel.util.BIFF8DateUtil Maven / Gradle / Ivy
/*
* Copyright (c) 2019-2020, [email protected] All Rights Reserved.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.ttzero.excel.util;
import org.ttzero.excel.reader.UncheckedTypeException;
import java.sql.Time;
import java.sql.Timestamp;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Arrays;
import java.util.Date;
import static org.ttzero.excel.util.DateUtil.SECOND_OF_DAY;
import static org.ttzero.excel.util.DateUtil.tz;
/**
* The time stamp field is an unsigned 64-bit integer
* value that contains the time elapsed since
* {@code 1601-Jan-01 00:00:00} (Gregorian calendar3).
* One unit of this value is equal to 100 nanoseconds
* ({@code 10}-7 seconds). That means,
* each second the time stamp value will be increased
* by 10 million units.
*
* @author guanquan.wang on 2019/1/21.
*/
public class BIFF8DateUtil {
private static final int DAYS_1899_TO_1970 = ~(int) LocalDate.of(1899, 12, 30).toEpochDay() + 3;
// private static final int DAYS_1900_TO_1970 = ~(int)LocalDate.of(1900, 1, 1).toEpochDay() + 3;
private BIFF8DateUtil() { }
/**
* MS_AOUT 2.2.25 DATE
*
* The date information is represented by whole-number
* increments, starting with December 30, 1899 midnight
* as time zero. The time information is represented by
* the fraction of a day since the preceding midnight.
* For example, 6:00 A.M. on January 4, 1900 would be
* represented by the value 5.25 (5 and 1/4 of a day
* past December 30, 1899).
*
* @param d 8-byte (double-precision) IEEE floating-point number
* @return java.sql.Timestamp
*/
public static java.sql.Timestamp toAoutTimestamp(double d) {
if (d - DAYS_1899_TO_1970 < 1.00001) {
throw new UncheckedTypeException("ConstantNumber " + d + " can't convert to java.util.Date");
}
int n = (int) d, m = (int) ((d - n) * SECOND_OF_DAY);
return Timestamp.from(Instant.ofEpochSecond((n - DAYS_1899_TO_1970) * 86400L + m).minusMillis(tz));
}
/**
* Check leap year
*
* - a year divisible by 4 is a leap year;
* - with the exception that a year divisible by 100 is
* not a leap year (e.g. 1900 was no leap year);
* - with the exception that a year divisible by 400 is
* a leap year (e.g. 2000 was a leap year).
*
*
* @param year the year
* @return true if leap year
*/
public static boolean isLeapYear(int year) {
return year % 4 == 0 && year % 100 != 0 || year % 400 == 0 && year % 3200 != 0;
}
// --- BIFF Timestamp support
// One unit of this value is equal to 100 nanoseconds (10–7 seconds).
private static final int base_nano = 10_000_000;
private static final int base_year = 1601;
private static final int max_range_year = 2056;
private static final int leap_year = 366;
private static final int common_year = 365;
// Days of month
private static final int[] month_table = {
31, 59, 90,
120, 151, 181,
212, 243, 273,
304, 334, 365
};
private static final int[] leap_month_table = {
31, 60, 91,
121, 152, 182,
213, 244, 274,
305, 335, 366
};
/**
* biff timestamp to LocalDateTime
*
* @param time the biff timestamp value
* @return java.time.LocalDateTime
*/
public static LocalDateTime toLocalDateTime(long time) {
// Fractional amount of a second
int frac = (int) (time % base_nano);
// Remaining entire seconds
long t1 = time / base_nano;
// Seconds in a minute
int sec = (int) (t1 % 60);
// Remaining entire minutes
long t2 = t1 / 60;
// Minutes in an hour
int min = (int) (t2 % 60);
// Remaining entire hours
int t3 = (int) (t2 / 60);
// Hours in a day
int hour = t3 % 24;
return LocalDateTime.of(doLocalDate(t3 / 24.0), LocalTime.of(hour, min, sec, frac * 100));
}
/**
* biff timestamp to java.sql.Timestamp
*
* @param time the biff timestamp value
* @return java.sql.Timestamp
*/
public static java.sql.Timestamp toTimestamp(long time) {
return Timestamp.valueOf(toLocalDateTime(time));
// return Timestamp.from(toLocalDateTime(time).toInstant(ZoneOffset.ofHours(tz)));
}
/**
* biff timestamp to LocalDate
*
* @param time the biff timestamp value
* @return java.time.LocalDate
*/
public static LocalDate toLocalDate(long time) {
return doLocalDate(time / 864_000_000_000.0);
}
private static LocalDate doLocalDate(double t) {
int t4 = (int) t;
// Entire years from 1601-Jan-01
int n = (int) (t / 365.242222), year = base_year + n;
if (year > max_range_year) {
int _year = year, m = _year - max_range_year;
n -= m;
for (; m-- > 0; t4 -= isLeapYear(--_year) ? leap_year : common_year) ;
}
if (n >= 1) t4 -= daysOfYear[n - 1];
else if (n < 0) throw new RuntimeException("Time is earlier than 1601-Jan-01 00:00:00.");
boolean isLeapYear = isLeapYear(year);
if (t4 >= common_year) {
if (isLeapYear && t4 == leap_year) {
t4 -= leap_year;
year++;
} else if (!isLeapYear) {
t4 -= common_year;
year++;
}
isLeapYear = isLeapYear(year);
}
else if (t4 < 0) {
year--;
t4 += isLeapYear ? leap_year : common_year;
}
// number of full months in t5
int[] monthTable = isLeapYear ? leap_month_table : month_table;
int m = Arrays.binarySearch(monthTable, t4);
if (m < 0) {
m = ~m;
if (m >= 1) t4 -= monthTable[m - 1];
} else t4 -= monthTable[m++];
int month = 1 + m;
// Resulting day of month
int day = 1 + t4;
return LocalDate.of(year, month, day);
}
/**
* biff timestamp to LocalTime
*
* @param time the biff timestamp value
* @return java.time.LocalTime
*/
public static LocalTime toLocalTime(long time) {
// Fractional amount of a second
int frac = (int) (time % base_nano);
// Remaining entire seconds
long t1 = time / base_nano;
// Seconds in a minute
int sec = (int) (t1 % 60);
// Remaining entire minutes
long t2 = t1 / 60;
// Minutes in an hour
int min = (int) (t2 % 60);
// Remaining entire hours
int t3 = (int) (t2 / 60);
// Hours in a day
int hour = t3 % 24;
return LocalTime.of(hour, min, sec, frac);
}
/**
* biff timestamp value to java.sql.Time
*
* @param time the biff timestamp value
* @return java.sql.Time
*/
public static java.sql.Time toTime(long time) {
return Time.valueOf(toLocalTime(time));
}
/**
* biff timestamp value to java.util.Date
*
* @param time the biff timestamp value
* @return java.util.Date
*/
public static java.util.Date toDate(long time) {
return toTimestamp(time);
}
/**
* Timestamp to unsigned 64-bit integer value
*
* @param ts the Timestamp
* @return unsigned 64-bit value
*/
public static long toDateTimeValue(Timestamp ts) {
return toDateTimeValue(ts.toLocalDateTime());
}
/**
* java.util.Date to unsigned 64-bit integer value
*
* @param date the java.util.Date
* @return unsigned 64-bit value
*/
public static long toDateTimeValue(Date date) {
return toDateTimeValue(new Timestamp(date.getTime()));
}
/**
* LocalDateTime to unsigned 64-bit integer value
*
* @param ldt the local-date-time
* @return unsigned 64-bit value
*/
public static long toDateTimeValue(LocalDateTime ldt) {
// Days of month
int day = ldt.getDayOfMonth() - 1;
// Month of year from 1 to 12.
int month = ldt.getMonthValue() - 1;
int year = ldt.getYear();
if (year < 1601) {
throw new RuntimeException("Time is earlier than 1601-Jan-01 00:00:00.");
}
long t0 = day;
if (month > 1 && isLeapYear(year)) t0 += 1;
if (month > 0) {
t0 += month_table[month - 1];
}
// number of full years from 1601-Jan-01
if (year > max_range_year) {
for (int n = year - max_range_year;
n-- > 0;
t0 += isLeapYear(--year) ? leap_year : common_year) ;
}
year -= base_year;
if (year > 0) {
t0 += daysOfYear[year - 1];
}
return (((t0 * 24 + ldt.getHour()) * 60 + ldt.getMinute()) * 60 + ldt.getSecond())
* base_nano + ldt.getNano() / 100;
}
// List days of year from 1601 to 2056
private static final int[] daysOfYear = {
365, 730, 1095, 1461, 1826, 2191, 2556, 2922, 3287, 3652, 4017, 4383,
4748, 5113, 5478, 5844, 6209, 6574, 6939, 7305, 7670, 8035, 8400, 8766,
9131, 9496, 9861, 10227, 10592, 10957, 11322, 11688, 12053, 12418, 12783, 13149,
13514, 13879, 14244, 14610, 14975, 15340, 15705, 16071, 16436, 16801, 17166, 17532,
17897, 18262, 18627, 18993, 19358, 19723, 20088, 20454, 20819, 21184, 21549, 21915,
22280, 22645, 23010, 23376, 23741, 24106, 24471, 24837, 25202, 25567, 25932, 26298,
26663, 27028, 27393, 27759, 28124, 28489, 28854, 29220, 29585, 29950, 30315, 30681,
31046, 31411, 31776, 32142, 32507, 32872, 33237, 33603, 33968, 34333, 34698, 35064,
35429, 35794, 36159, 36524, 36889, 37254, 37619, 37985, 38350, 38715, 39080, 39446,
39811, 40176, 40541, 40907, 41272, 41637, 42002, 42368, 42733, 43098, 43463, 43829,
44194, 44559, 44924, 45290, 45655, 46020, 46385, 46751, 47116, 47481, 47846, 48212,
48577, 48942, 49307, 49673, 50038, 50403, 50768, 51134, 51499, 51864, 52229, 52595,
52960, 53325, 53690, 54056, 54421, 54786, 55151, 55517, 55882, 56247, 56612, 56978,
57343, 57708, 58073, 58439, 58804, 59169, 59534, 59900, 60265, 60630, 60995, 61361,
61726, 62091, 62456, 62822, 63187, 63552, 63917, 64283, 64648, 65013, 65378, 65744,
66109, 66474, 66839, 67205, 67570, 67935, 68300, 68666, 69031, 69396, 69761, 70127,
70492, 70857, 71222, 71588, 71953, 72318, 72683, 73048, 73413, 73778, 74143, 74509,
74874, 75239, 75604, 75970, 76335, 76700, 77065, 77431, 77796, 78161, 78526, 78892,
79257, 79622, 79987, 80353, 80718, 81083, 81448, 81814, 82179, 82544, 82909, 83275,
83640, 84005, 84370, 84736, 85101, 85466, 85831, 86197, 86562, 86927, 87292, 87658,
88023, 88388, 88753, 89119, 89484, 89849, 90214, 90580, 90945, 91310, 91675, 92041,
92406, 92771, 93136, 93502, 93867, 94232, 94597, 94963, 95328, 95693, 96058, 96424,
96789, 97154, 97519, 97885, 98250, 98615, 98980, 99346, 99711, 100076, 100441, 100807,
101172, 101537, 101902, 102268, 102633, 102998, 103363, 103729, 104094, 104459, 104824, 105190,
105555, 105920, 106285, 106651, 107016, 107381, 107746, 108112, 108477, 108842, 109207, 109572,
109937, 110302, 110667, 111033, 111398, 111763, 112128, 112494, 112859, 113224, 113589, 113955,
114320, 114685, 115050, 115416, 115781, 116146, 116511, 116877, 117242, 117607, 117972, 118338,
118703, 119068, 119433, 119799, 120164, 120529, 120894, 121260, 121625, 121990, 122355, 122721,
123086, 123451, 123816, 124182, 124547, 124912, 125277, 125643, 126008, 126373, 126738, 127104,
127469, 127834, 128199, 128565, 128930, 129295, 129660, 130026, 130391, 130756, 131121, 131487,
131852, 132217, 132582, 132948, 133313, 133678, 134043, 134409, 134774, 135139, 135504, 135870,
136235, 136600, 136965, 137331, 137696, 138061, 138426, 138792, 139157, 139522, 139887, 140253,
140618, 140983, 141348, 141714, 142079, 142444, 142809, 143175, 143540, 143905, 144270, 144636,
145001, 145366, 145731, 146097, 146462, 146827, 147192, 147558, 147923, 148288, 148653, 149019,
149384, 149749, 150114, 150480, 150845, 151210, 151575, 151941, 152306, 152671, 153036, 153402,
153767, 154132, 154497, 154863, 155228, 155593, 155958, 156324, 156689, 157054, 157419, 157785,
158150, 158515, 158880, 159246, 159611, 159976, 160341, 160707, 161072, 161437, 161802, 162168,
162533, 162898, 163263, 163629, 163994, 164359, 164724, 165090, 165455, 165820, 166185, 166551,
};
}