From f9fc75461142acf8108356d84d0f1967b414ed77 Mon Sep 17 00:00:00 2001 From: Cédric Walter Date: Tue, 8 Oct 2013 12:54:35 +0000 Subject: Bug 55081: patch for missing function WEEKNUM Add simple spreadsheet with Excel 2003, and another with 2013 (both fresh files) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1530256 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/atp/AnalysisToolPak.java | 2 +- .../apache/poi/ss/formula/functions/WeekNum.java | 88 +++++++++++++++++++++ .../TestWeekNumFunctionsFromSpreadsheet.java | 30 +++++++ .../TestWeekNumFunctionsFromSpreadsheet2013.java | 31 ++++++++ test-data/spreadsheet/FormulaEvalTestData.xls | Bin 169984 -> 169984 bytes .../spreadsheet/WeekNumFunctionTestCaseData.xls | Bin 0 -> 35840 bytes .../WeekNumFunctionTestCaseData2013.xls | Bin 0 -> 29696 bytes 7 files changed, 150 insertions(+), 1 deletion(-) create mode 100644 src/java/org/apache/poi/ss/formula/functions/WeekNum.java create mode 100644 src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet.java create mode 100644 src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet2013.java create mode 100644 test-data/spreadsheet/WeekNumFunctionTestCaseData.xls create mode 100644 test-data/spreadsheet/WeekNumFunctionTestCaseData2013.xls diff --git a/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java index b866c61bf3..1a191b174d 100644 --- a/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java +++ b/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java @@ -156,7 +156,7 @@ public final class AnalysisToolPak implements UDFFinder { r(m, "TBILLEQ", null); r(m, "TBILLPRICE", null); r(m, "TBILLYIELD", null); - r(m, "WEEKNUM", null); + r(m, "WEEKNUM", WeekNum.instance); r(m, "WORKDAY", WorkdayFunction.instance); r(m, "XIRR", null); r(m, "XNPV", null); diff --git a/src/java/org/apache/poi/ss/formula/functions/WeekNum.java b/src/java/org/apache/poi/ss/formula/functions/WeekNum.java new file mode 100644 index 0000000000..e678104a23 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/WeekNum.java @@ -0,0 +1,88 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You 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.apache.poi.ss.formula.functions; + +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.*; +import org.apache.poi.ss.usermodel.DateUtil; + +import java.util.Calendar; +import java.util.GregorianCalendar; + +/** + * Implementation for Excel WeekNum() function.

+ *

+ * Syntax:
WeekNum (Serial_num,Return_type)
+ *

+ * Returns a number that indicates where the week falls numerically within a year. + *

+ *

+ * Serial_num is a date within the week. Dates should be entered by using the DATE function, + * or as results of other formulas or functions. For example, use DATE(2008,5,23) + * for the 23rd day of May, 2008. Problems can occur if dates are entered as text. + * Return_type is a number that determines on which day the week begins. The default is 1. + * 1 Week begins on Sunday. Weekdays are numbered 1 through 7. + * 2 Week begins on Monday. Weekdays are numbered 1 through 7. + * + * @author cedric dot walter @ gmail dot com + */ +public class WeekNum extends Fixed2ArgFunction implements FreeRefFunction { + + public static final FreeRefFunction instance = new WeekNum(); + + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval serialNumVE, ValueEval returnTypeVE) { + double serialNum = 0.0; + try { + serialNum = NumericFunction.singleOperandEvaluate(serialNumVE, srcRowIndex, srcColumnIndex); + } catch (EvaluationException e) { + return ErrorEval.VALUE_INVALID; + } + Calendar serialNumCalendar = new GregorianCalendar(); + serialNumCalendar.setTime(DateUtil.getJavaDate(serialNum, false)); + + int returnType = 0; + try { + ValueEval ve = OperandResolver.getSingleValue(returnTypeVE, srcRowIndex, srcColumnIndex); + returnType = OperandResolver.coerceValueToInt(ve); + } catch (EvaluationException e) { + return ErrorEval.NUM_ERROR; + } + + if (returnType != 1 && returnType != 2) { + return ErrorEval.NUM_ERROR; + } + + return new NumberEval(this.getWeekNo(serialNumCalendar, returnType)); + } + + public int getWeekNo(Calendar cal, int weekStartOn) { + if (weekStartOn == 1) { + cal.setFirstDayOfWeek(Calendar.SUNDAY); + } else { + cal.setFirstDayOfWeek(Calendar.MONDAY); + } + return cal.get(Calendar.WEEK_OF_YEAR); + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length == 2) { + return evaluate(ec.getRowIndex(), ec.getColumnIndex(), args[0], args[1]); + } + return ErrorEval.VALUE_INVALID; + } +} diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet.java new file mode 100644 index 0000000000..77b832ea14 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet.java @@ -0,0 +1,30 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You 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.apache.poi.ss.formula.functions; + +/** + * Tests WeekNum() as loaded from a test data spreadsheet.

+ * + * @author cedric dot walter @ gmail dot com + */ +public class TestWeekNumFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet { + + protected String getFilename() { + return "WeekNumFunctionTestCaseData.xls"; + } +} diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet2013.java b/src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet2013.java new file mode 100644 index 0000000000..d7d286468a --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestWeekNumFunctionsFromSpreadsheet2013.java @@ -0,0 +1,31 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You 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.apache.poi.ss.formula.functions; + +/** + * Tests WeekNum() as loaded from a test data 2013 excel spreadsheet.

+ * + * @author cedric dot walter @ gmail dot com + */ +public class TestWeekNumFunctionsFromSpreadsheet2013 extends BaseTestFunctionsFromSpreadsheet { + + protected String getFilename() { + //Only open this file with Excel 2013 to keep binary specific to that version + return "WeekNumFunctionTestCaseData2013.xls"; + } +} diff --git a/test-data/spreadsheet/FormulaEvalTestData.xls b/test-data/spreadsheet/FormulaEvalTestData.xls index 434a04f747..df81bc8a78 100644 Binary files a/test-data/spreadsheet/FormulaEvalTestData.xls and b/test-data/spreadsheet/FormulaEvalTestData.xls differ diff --git a/test-data/spreadsheet/WeekNumFunctionTestCaseData.xls b/test-data/spreadsheet/WeekNumFunctionTestCaseData.xls new file mode 100644 index 0000000000..42fd48cf78 Binary files /dev/null and b/test-data/spreadsheet/WeekNumFunctionTestCaseData.xls differ diff --git a/test-data/spreadsheet/WeekNumFunctionTestCaseData2013.xls b/test-data/spreadsheet/WeekNumFunctionTestCaseData2013.xls new file mode 100644 index 0000000000..2afd0262dc Binary files /dev/null and b/test-data/spreadsheet/WeekNumFunctionTestCaseData2013.xls differ -- cgit v1.2.3