From 9244ab36468fa321cf659a25661fac1651596c3e Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sun, 3 Oct 2021 13:17:48 +0000 Subject: [bug-65606] try to fix weeknum function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1893852 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/functions/WeekNum.java | 68 ++++++++++++++++------ .../poi/ss/formula/functions/TestWeekNumFunc.java | 27 ++++++++- .../poi/ss/formula/functions/TestWeekdayFunc.java | 1 - 3 files changed, 74 insertions(+), 22 deletions(-) (limited to 'poi') diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/WeekNum.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/WeekNum.java index 04fb1dec7e..76b620ab39 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/functions/WeekNum.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/WeekNum.java @@ -17,16 +17,17 @@ package org.apache.poi.ss.formula.functions; -import java.util.Calendar; +import java.time.DayOfWeek; +import java.time.LocalDate; +import java.time.ZoneId; +import java.time.temporal.WeekFields; +import java.util.Arrays; +import java.util.Date; +import java.util.HashSet; import org.apache.poi.ss.formula.OperationEvaluationContext; -import org.apache.poi.ss.formula.eval.ErrorEval; -import org.apache.poi.ss.formula.eval.EvaluationException; -import org.apache.poi.ss.formula.eval.NumberEval; -import org.apache.poi.ss.formula.eval.OperandResolver; -import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.*; import org.apache.poi.ss.usermodel.DateUtil; -import org.apache.poi.util.LocaleUtil; /** * Implementation for Excel WeekNum() function. @@ -45,6 +46,8 @@ import org.apache.poi.util.LocaleUtil; public class WeekNum extends Fixed2ArgFunction implements FreeRefFunction { public static final FreeRefFunction instance = new WeekNum(); private static final NumberEval DEFAULT_RETURN_TYPE = new NumberEval(1); + private static final HashSet VALID_RETURN_TYPES = new HashSet<>( + Arrays.asList(1, 2, 11, 12, 13, 14, 15, 16, 17, 21)); @Override public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval serialNumVE, ValueEval returnTypeVE) { @@ -54,31 +57,60 @@ public class WeekNum extends Fixed2ArgFunction implements FreeRefFunction { } catch (EvaluationException e) { return ErrorEval.VALUE_INVALID; } - Calendar serialNumCalendar = LocaleUtil.getLocaleCalendar(); - serialNumCalendar.setTime(DateUtil.getJavaDate(serialNum, false)); - + LocalDate localDate; + try { + Date dateToConvert = DateUtil.getJavaDate(serialNum, false); + localDate = dateToConvert.toInstant() + .atZone(ZoneId.systemDefault()) + .toLocalDate(); + } catch (Exception e) { + return ErrorEval.NUM_ERROR; + } int returnType; try { ValueEval ve = OperandResolver.getSingleValue(returnTypeVE, srcRowIndex, srcColumnIndex); - returnType = OperandResolver.coerceValueToInt(ve); + if (ve instanceof MissingArgEval) { + returnType = (int)DEFAULT_RETURN_TYPE.getNumberValue(); + } else { + returnType = OperandResolver.coerceValueToInt(ve); + } } catch (EvaluationException e) { return ErrorEval.NUM_ERROR; } - if (returnType != 1 && returnType != 2) { + if (!VALID_RETURN_TYPES.contains(returnType)) { return ErrorEval.NUM_ERROR; } - return new NumberEval(this.getWeekNo(serialNumCalendar, returnType)); + return new NumberEval(this.getWeekNo(localDate, returnType)); } - public int getWeekNo(Calendar cal, int weekStartOn) { - if (weekStartOn == 1) { - cal.setFirstDayOfWeek(Calendar.SUNDAY); + private WeekFields SUNDAY_START = WeekFields.of(DayOfWeek.SUNDAY, 1); + private WeekFields MONDAY_START = WeekFields.of(DayOfWeek.MONDAY, 1); + private WeekFields TUESDAY_START = WeekFields.of(DayOfWeek.TUESDAY, 1); + private WeekFields WEDNESDAY_START = WeekFields.of(DayOfWeek.WEDNESDAY, 1); + private WeekFields THURSDAY_START = WeekFields.of(DayOfWeek.THURSDAY, 1); + private WeekFields FRIDAY_START = WeekFields.of(DayOfWeek.FRIDAY, 1); + private WeekFields SATURDAY_START = WeekFields.of(DayOfWeek.SATURDAY, 1); + + public int getWeekNo(LocalDate date, int weekStartOn) { + if (weekStartOn == 1 || weekStartOn == 17) { + return date.get(SUNDAY_START.weekOfYear()); + } else if (weekStartOn == 2 || weekStartOn == 11) { + return date.get(MONDAY_START.weekOfYear()); + } else if (weekStartOn == 12) { + return date.get(TUESDAY_START.weekOfYear()); + } else if (weekStartOn == 13) { + return date.get(WEDNESDAY_START.weekOfYear()); + } else if (weekStartOn == 14) { + return date.get(THURSDAY_START.weekOfYear()); + } else if (weekStartOn == 15) { + return date.get(FRIDAY_START.weekOfYear()); + } else if (weekStartOn == 16) { + return date.get(SATURDAY_START.weekOfYear()); } else { - cal.setFirstDayOfWeek(Calendar.MONDAY); + return date.get(WeekFields.ISO.weekOfYear()); } - return cal.get(Calendar.WEEK_OF_YEAR); } @Override diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekNumFunc.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekNumFunc.java index 2a915b7742..9ccd18dc9d 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekNumFunc.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekNumFunc.java @@ -37,11 +37,32 @@ class TestWeekNumFunc { @Test void testEvaluate() { - assertEvaluateEquals(10.0, DateUtil.getExcelDate(LocalDate.parse("2012-03-09"))); - //next assert returns 10 when it should be 11.0. - //assertEvaluateEquals(11.0, DateUtil.getExcelDate(LocalDate.parse("2012-03-09")), 2); + double date = DateUtil.getExcelDate(LocalDate.parse("2012-03-09")); + assertEvaluateEquals(10.0, date); + assertEvaluateEquals(10.0, date, 1); + assertEvaluateEquals(11.0, date, 2); + assertEvaluateEquals(11.0, date, 11); + assertEvaluateEquals(11.0, date, 12); + assertEvaluateEquals(11.0, date, 13); + assertEvaluateEquals(11.0, date, 14); + assertEvaluateEquals(11.0, date, 15); + assertEvaluateEquals(10.0, date, 16); + assertEvaluateEquals(10.0, date, 17); + assertEvaluateEquals(10.0, date, 21); } + @Test + void testEvaluateInvalid() { + assertEvaluateEquals("no args", ErrorEval.VALUE_INVALID); + assertEvaluateEquals("too many args", ErrorEval.VALUE_INVALID, new NumberEval(1.0), new NumberEval(1.0), new NumberEval(1.0)); + assertEvaluateEquals("negative date", ErrorEval.NUM_ERROR, new NumberEval(-1.0)); + assertEvaluateEquals("cannot coerce serial_number to number", ErrorEval.VALUE_INVALID, new StringEval("")); + assertEvaluateEquals("cannot coerce return_type to number", ErrorEval.NUM_ERROR, new NumberEval(1.0), new StringEval("")); + assertEvaluateEquals("return_type is blank", ErrorEval.NUM_ERROR, new StringEval("2"), BlankEval.instance); + assertEvaluateEquals("invalid return_type", ErrorEval.NUM_ERROR, new NumberEval(1.0), new NumberEval(18.0)); + } + + private static final OperationEvaluationContext DEFAULT_CONTEXT = new OperationEvaluationContext(null, null, 0, 1, 0, null); diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekdayFunc.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekdayFunc.java index d1c48a265f..56234d6ece 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekdayFunc.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestWeekdayFunc.java @@ -43,7 +43,6 @@ class TestWeekdayFunc { assertEquals(expected, result.getNumberValue(), TOLERANCE, formula); } - @Test void testEvaluate() { assertEvaluateEquals(2.0, 1.0); -- cgit v1.2.3