From 0ac926cf10ad3ed92f6660757880637d18ae7f6e Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Mon, 7 Sep 2009 05:04:17 +0000 Subject: [PATCH] Implementation of Excel Days360 and Npv functions git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@811996 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 2 + .../record/formula/functions/Days360.java | 74 +++++++++++++++++++ .../hssf/record/formula/functions/Npv.java | 47 ++++++++++++ 3 files changed, 123 insertions(+) create mode 100755 src/java/org/apache/poi/hssf/record/formula/functions/Days360.java create mode 100755 src/java/org/apache/poi/hssf/record/formula/functions/Npv.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index d3496ab094..9d0d3a4a7f 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,8 @@ + 47768 - Implementation of Excel "Days360" and "Npv" functions + 47751 - Do not allow HSSF's cell text longer than 32,767 characters 47757 - Added an example demonstrating how to convert an XLSX workbook to CSV 44770 - Fixed PPT parser to tolerate Comment2000 containers with missing comment text 47773 - Fix for extraction paragraphs and sections from headers/footers with XWPFWordExtractor diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java b/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java new file mode 100755 index 0000000000..5087348aab --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Days360.java @@ -0,0 +1,74 @@ +package org.apache.poi.hssf.record.formula.functions; + +import java.util.Calendar; +import java.util.GregorianCalendar; + +import org.apache.poi.hssf.record.formula.eval.EvaluationException; +import org.apache.poi.ss.usermodel.DateUtil; + +/** + * Calculates the number of days between two dates based on a 360-day year + * (twelve 30-day months), which is used in some accounting calculations. Use + * this function to help compute payments if your accounting system is based on + * twelve 30-day months. + * + * + * @author PUdalau + */ +public class Days360 extends NumericFunction.TwoArg { + + @Override + protected double evaluate(double d0, double d1) throws EvaluationException { + Calendar startingDate = getStartingDate(d0); + Calendar endingDate = getEndingDateAccordingToStartingDate(d1, startingDate); + long startingDay = startingDate.get(Calendar.MONTH) * 30 + startingDate.get(Calendar.DAY_OF_MONTH); + long endingDay = (endingDate.get(Calendar.YEAR) - startingDate.get(Calendar.YEAR)) * 360 + + endingDate.get(Calendar.MONTH) * 30 + endingDate.get(Calendar.DAY_OF_MONTH); + return endingDay - startingDay; + } + + private Calendar getDate(double date) { + Calendar processedDate = new GregorianCalendar(); + processedDate.setTime(DateUtil.getJavaDate(date, false)); + return processedDate; + } + + private Calendar getStartingDate(double date) { + Calendar startingDate = getDate(date); + if (isLastDayOfMonth(startingDate)) { + startingDate.set(Calendar.DAY_OF_MONTH, 30); + } + return startingDate; + } + + private Calendar getEndingDateAccordingToStartingDate(double date, Calendar startingDate) { + Calendar endingDate = getDate(date); + endingDate.setTime(DateUtil.getJavaDate(date, false)); + if (isLastDayOfMonth(endingDate)) { + if (startingDate.get(Calendar.DATE) < 30) { + endingDate = getFirstDayOfNextMonth(endingDate); + } + } + return endingDate; + } + + private boolean isLastDayOfMonth(Calendar date) { + Calendar clone = (Calendar) date.clone(); + clone.add(java.util.Calendar.MONTH, 1); + clone.add(java.util.Calendar.DAY_OF_MONTH, -1); + int lastDayOfMonth = clone.get(Calendar.DAY_OF_MONTH); + return date.get(Calendar.DAY_OF_MONTH) == lastDayOfMonth; + } + + private Calendar getFirstDayOfNextMonth(Calendar date) { + Calendar newDate = (Calendar) date.clone(); + if (date.get(Calendar.MONTH) < Calendar.DECEMBER) { + newDate.set(Calendar.MONTH, date.get(Calendar.MONTH) + 1); + } else { + newDate.set(Calendar.MONTH, 1); + newDate.set(Calendar.YEAR, date.get(Calendar.YEAR) + 1); + } + newDate.set(Calendar.DATE, 1); + return newDate; + } +} diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Npv.java b/src/java/org/apache/poi/hssf/record/formula/functions/Npv.java new file mode 100755 index 0000000000..114aa4d02e --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Npv.java @@ -0,0 +1,47 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import org.apache.poi.hssf.record.formula.eval.EvaluationException; + +/** + * Calculates the net present value of an investment by using a discount rate + * and a series of future payments (negative values) and income (positive + * values). Minimum 2 arguments, first arg is the rate of discount over the + * length of one period others up to 254 arguments representing the payments and + * income. + * + * @author SPetrakovsky + */ +public class Npv extends NumericFunction.MultiArg { + + public Npv() { + super(2, 255); + } + + @Override + protected double evaluate(double[] ds) throws EvaluationException { + double rate = ds[0]; + double sum = 0; + for (int i = 1; i < ds.length; i++) { + sum += ds[i] / Math.pow(rate + 1, i); + } + return sum; + } + +} -- 2.39.5