为什么要写下来呢,前人栽树后人乘凉这句话说得很有道理,java调用poi读取excel中的数据,不支持很多excel当中的函数,我最近就遇到了连个 一个是XIRR还有一个是XNPV函数 下一篇补上
我个人也主要是用作记录使用 并没有看的太懂 避免下次再用到的时候找不到,也方便刚接触的人可以更加轻松的去查找和学习,里面还有很多不足的地方 有大佬看到可以帮忙补充一下
其实这个也是我从网上找的后边补上连接 来自https://blog.csdn.net/wangqing84411433/article/details/85164999
先是时间工具类,XIRR函数需要用到时间 excel的时间计算和java不同 excel的时间是从1900年1月1日计算 不过因为函数中的计算方式是减 所以1900或者java中的1970都可以
package com.p1.excel.XIRR; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; /** * * @{# DateUtil.java Create on 2013-4-9 * * class desc: * * <p> * Copyright: Copyright(c) 2013 * </p> * <p> * Company: morelap * </p> * @Version 1.0 * @Author <a href="mailto:morelap@morelap.com">Morelap</a> * * */ public class DateUtil { public static final long Minute_MilliSecond = 60 * 1000; public static final long Hour_MilliSecond = Minute_MilliSecond * 60; /** * milliseconds of a day */ public static final long Day_MilliSecond = Hour_MilliSecond * 24; /** * milliseconds of a week */ public static final long Week_MilliSecond = Day_MilliSecond * 7; /** * milliseconds of a month */ public static final long Month_MilliSecond = Week_MilliSecond * 30; /** * yyyyMMdd */ public static final String Date_Default_Formate = "yyyyMMdd"; /** * yyyy-MM-dd HH:mm:ss 2010-05-11 17:22:26 */ public static final String Date_Formate_All = "yyyy-MM-dd HH:mm:ss"; /** * dd/MM/yyyy, hh:mm */ public static final String DATE_FORMATE_TRANSACTION = "dd/MM/yyyy, hh:mm"; /** * MM/dd HH:mm */ public static final String DATE_FORMATE_DAY_HOUR_MINUTE = "MM/dd HH:mm"; /** * HH:mm */ public static final String DATE_FORMATE_HOUR_MINUTE = "HH:mm"; public static final String DATE_FORMATE_HOUR_MINUTE_SECOND = "HH:mm:ss"; public static SimpleDateFormat dateFormate = new SimpleDateFormat(); /** * 获取当前的字符串日期 * * @param splite * 格式的分割线如 - 则获取的日期时间格式如下: * @return 返回2013-07-19 */ public static String getNowStringDate(String splite) { StringBuffer format = new StringBuffer(); if (splite == null) { format.append(Date_Default_Formate); } else { format.append("yyyy").append(splite).append("MM").append(splite) .append("dd"); } return (new SimpleDateFormat(format.toString())).format(new Date()); } /** * 获取当前的字符串日期时间 * * @param splite * 格式的分割线如 - 则获取的日期时间格式如下: * @return 返回2013-07-19 09:08:22 */ public static String getNowStringDateTime(String splite) { StringBuffer format = new StringBuffer(); if (splite == null) { format.append(Date_Default_Formate); } else { format.append("yyyy").append(splite).append("MM").append(splite) .append("dd").append(" HH:mm:ss"); } return (new SimpleDateFormat(format.toString())).format(new Date()); } /** * 获取当前的字符串期时间 * * @param splite * 格式的分割线如 - 则获取的日期时间格式如下: * @return 返回09:08:22 */ public static String getNowStringTime() { StringBuffer format = new StringBuffer(DATE_FORMATE_HOUR_MINUTE_SECOND); return (new SimpleDateFormat(format.toString())).format(new Date()); } /** * 获取昨天的日期 * * @return Date */ public static Date getYesterdayDate() { Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.DATE, -1); return calendar.getTime(); } /** * 获取指定日期的后面的一天 即日期+1 * * @param stringDate * 格式为:2012-02-02 * @return Date */ public static Date getForwardDate(String stringDate) { int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); int day = Integer.parseInt(stringDate.substring(8, 10)); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); c.add(Calendar.DATE, 1);// 在此日期的基础上+1 return c.getTime(); } /** * 获取指定日期的后面的一天 即日期+1 * * @param stringDate * 格式为:2012-02-02 * @return Date */ public static String getForwardStringDate(String stringDate, String splite) { int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); int day = Integer.parseInt(stringDate.substring(8, 10)); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); c.add(Calendar.DATE, 1);// 在此日期的基础上+1 return getStringDateByDate(c.getTime(), splite); } /** * 根据传进来的日期date 和格式分割线返回一个字符串的日期 * * @param date * 日期 * @param splite * 日期格式的分割线 如:- * @return 2013-07-19 */ public static String getStringDateByDate(Date date, String splite) { StringBuffer format = new StringBuffer(); if (splite == null) { format.append(Date_Default_Formate); } else { format.append("yyyy").append(splite).append("MM").append(splite) .append("dd"); } if (date == null) { date = new Date(); } return (new SimpleDateFormat(format.toString())).format(date); } /** * 根据传进来的日期stringdate 和格式分割线返回一个字符串的日期 * * @param stringDate * 日期 * @param splite * 日期格式的分割线 如:- * @return 2013-07-19 */ public static String getStringDateByStringDate(String stringDate, String splite) { Date date = getDateByStringTime(stringDate); return getStringDateByDate(date, splite); } /** * 根据传进来的整数获取一个日期Date * * @param year * 年 * @param month * 月 * @param day * 日 * @return Date */ public static Date getDateByIntegerDate(int year, int month, int day) { Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); return c.getTime(); } /** * 根据传进来的字符串日期格式 获得一个date * * @param stringTime * 格式如:2013-02-13 中间的-可以为其他字符 * @return date */ public static Date getDateByStringTime(String stringTime) { int year = Integer.parseInt(stringTime.substring(0, 4)); int month = Integer.parseInt(stringTime.substring(5, 7)); int day = Integer.parseInt(stringTime.substring(8, 10)); return getDateByIntegerDate(year, month, day); } /** * 获得指定日期 在1970年到现在的天数 * * @param date * @return 天数 */ public static long getDaysFrom1970(Date date) { String stringTime = DateUtil.getStringDateByDate(date, "-"); int year = Integer.parseInt(stringTime.substring(0, 4)); int month = Integer.parseInt(stringTime.substring(5, 7)); int day = Integer.parseInt(stringTime.substring(8, 10)); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); return (int) (c.getTimeInMillis() / 86400000); } /** * 获得指定日期 在1970年到现在的天数 * * @param 年 * 月 日 * @return 天数 */ public static long getDaysFrom1970(int year, int month, int day) { Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); return (int) (c.getTimeInMillis() / 86400000); } public static int differentDaysByMillisecond(Date date1, Date date2) { int days = (int) ((date2.getTime() - date1.getTime()) / (1000*3600*24)); return days; } /** * 获得指定日期 在1970年到现在的天数 * * @param date 格式2013-03-05 中间的-可以是其他符号 或者没有符号 * @param hasSplite 代表中间是否有符号,默认是有的,如果为false则日期的格式为:20130305 * @return 天数 */ public static long getDaysFrom1970(String stringTime,boolean hasSplite) { int year,month,day; if(hasSplite){ year = Integer.parseInt(stringTime.substring(0, 4)); month = Integer.parseInt(stringTime.substring(5, 7)); day = Integer.parseInt(stringTime.substring(8, 10)); }else{ year = Integer.parseInt(stringTime.substring(0, 4)); month = Integer.parseInt(stringTime.substring(4, 6)); day = Integer.parseInt(stringTime.substring(6, 8)); } Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); return (int) (c.getTimeInMillis() / 86400000); } /** * 获得今年年初的第一天日期 如2013-01-01 * * @param splite * 分隔符号 * @return 2013-01-01(字符串形式) */ public static String getYearStartString(String splite) { String sDate = getNowStringDate(splite); int year = Integer.parseInt(sDate.substring(0, 4)); String yearStart = new StringBuffer().append(year).append(splite) .append("01").append(splite).append("01").toString(); return yearStart; } /** * 获得今年年初的第一天日期 如2013-01-01 * * @return 2013-01-01日期格式 */ public static Date getYearStartDate() { String sDate = getNowStringDate("-"); int year = Integer.parseInt(sDate.substring(0, 4)); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, 0); c.set(Calendar.DAY_OF_MONTH, 1); return c.getTime(); } /** * 获得上个周末的 字符串日期, 指星期日 * @param splite * 分隔符号 - * @return String 格式:2013-06-22 */ public static String getLastWeekStringDate(String splite) { String stringDate=getNowStringDate("-"); int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); int day = Integer.parseInt(stringDate.substring(8, 10)); int sundayPlus=getSundayPlus(); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); c.add(Calendar.DATE, -sundayPlus);// 在此日期的基础上减去sundayPlus就是上个周末的日期 return getStringDateByDate(c.getTime(), splite); } /** * 获得上个周末的日期,date格式 * * @return Date */ public static Date getLastWeekDate() { String stringDate=getNowStringDate("-"); int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); int day = Integer.parseInt(stringDate.substring(8, 10)); int sundayPlus=getSundayPlus(); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); c.add(Calendar.DATE, -sundayPlus);// 在此日期的基础上减去sundayPlus就是上个周末的日期 return c.getTime(); } /** * 获得上个月末的 字符串日期 * @return String */ public static String getLastMonthStringDate(String splite) { String stringDate=getNowStringDate("-"); int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); int day = Integer.parseInt(stringDate.substring(8, 10)); int monthPlus=getMonthPlus(); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); c.add(Calendar.DATE, -monthPlus);// 在此日期的基础上减去sundayPlus就是上个周末的日期 return getStringDateByDate(c.getTime(), splite); } /** * 获得上个月末的日期,date格式 * * @return Date */ public static Date getLastMonthDate() { String stringDate=getNowStringDate("-"); int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); int day = Integer.parseInt(stringDate.substring(8, 10)); int monthPlus=getMonthPlus(); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); c.add(Calendar.DATE, -monthPlus);// 在此日期的基础上减去sundayPlus就是上个周末的日期 return c.getTime(); } /** * 获得上个季度末的 字符串日期 * @return String 2012-03-30 */ public static String getLastQuarterStringDate(String splite) { String stringDate=getNowStringDate("-"); int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); switch (month) { case 1: case 2: case 3: month=12; year--; break; case 4: case 5: case 6: month=3; break; case 7: case 8: case 9: month=6; break; case 10: case 11: case 12: month=9; break; default: break; } Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month);//这里月份本来是要-1的,所以这里就相当于月份month+1, c.set(Calendar.DAY_OF_MONTH, 1);//此时的日期刚好为month+1月份的一号 c.add(Calendar.DATE, -1);// 在1号的基础上减去一天,就是month月末的日期 return getStringDateByDate(c.getTime(), splite); } /** * 获得上个季度末的日期,date格式 * @return Date */ public static Date getLastQuarterDate() { String stringDate=getNowStringDate("-"); int year = Integer.parseInt(stringDate.substring(0, 4)); int month = Integer.parseInt(stringDate.substring(5, 7)); switch (month) { case 1: case 2: case 3: month=12; year--; break; case 4: case 5: case 6: month=3; break; case 7: case 8: case 9: month=6; break; case 10: case 11: case 12: month=9; break; default: break; } Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month);//这里月份本来是要-1的,所以这里就相当于月份month+1, c.set(Calendar.DAY_OF_MONTH, 1);//此时的日期刚好为month+1月份的一号 c.add(Calendar.DATE, -1);// 在1号的基础上减去一天,就是month月末的日期 return c.getTime(); } /** * 获得指定月份的,月末日期 * @param month 月 * @return String */ public static String getMonthEndStringDateByMonth(int month,String splite){ String stringDate=getNowStringDate("-"); int year = Integer.parseInt(stringDate.substring(0, 4)); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month);//这里月份本来是要-1的,所以这里就相当于月份month+1, c.set(Calendar.DAY_OF_MONTH, 1);//此时的日期刚好为month+1月份的一号 c.add(Calendar.DATE, -1);// 在1号的基础上减去一天,就是month月末的日期 return getStringDateByDate(c.getTime(), splite); } /** * 获得当前日期与上周日相差的天数 按照外国的理解:星期日是第一天,星期一是第二天. 中国:星期一是第一天. * * @return int今天和上周日的相差日期 */ public static int getSundayPlus() { Calendar c = Calendar.getInstance(); // 获得今天是一周的第几天,星期日是第一天,星期二是第二天...... int dayOfWeek = c.get(Calendar.DAY_OF_WEEK) - 1; // 因为按中国礼拜一,作为第一天所以这里减1 if(dayOfWeek==0){ dayOfWeek=7; } return dayOfWeek; } /** * 获得当前日期与上个月末日期的相差天数 * * @return int 今天和上月末的相差日期 */ public static int getMonthPlus() { Calendar c = Calendar.getInstance(); // 获得今天是一周的第几天,星期日是第一天,星期二是第二天...... int dayOfMonth = c.get(Calendar.DAY_OF_MONTH); return dayOfMonth; } /** * 获得两个时间相差的时间间隔,以分钟来计算 * @param time01 格式为:2012-02-02 08:08:20 * @param time02 格式为:2012-02-02 08:08:20 * @return 相差的分钟数 */ public static long getTwoTimeInterval(String time01,String time02){ long t01=stringTimeToMilliseconds(time01); long t02=stringTimeToMilliseconds(time02); return Math.abs((t01-t02)/60000); } /** * 把一个字符串日期 转换成毫秒数 * @param time 格式为:2012-02-02 08:08:20 * @return 毫秒数 */ public static long stringTimeToMilliseconds(String time){ int year = Integer.parseInt(time.substring(0, 4)); int month = Integer.parseInt(time.substring(5, 7)); int day = Integer.parseInt(time.substring(8, 10)); int hour= Integer.parseInt(time.substring(11, 13)); int minus= Integer.parseInt(time.substring(14, 16)); int second= Integer.parseInt(time.substring(17, 19)); Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, year); c.set(Calendar.MONTH, month - 1); c.set(Calendar.DAY_OF_MONTH, day); c.add(Calendar.HOUR_OF_DAY, hour); c.add(Calendar.MINUTE, minus); c.add(Calendar.SECOND, second); return c.getTimeInMillis(); } // java.util.Date 是 java.sql.Date 的父类 // java.sql.Date转为java.util.Date // java.sql.Date date=new java.sql.Date(); // java.util.Date d=new java.util.Date (date.getTime()); 接着是UpbaaDate类
package com.p1.excel.XIRR; /** * * @author 小木桩(staker) 这个类是辅助计算xirr的类, 主要是以时间对应现金的形式组成一条现金流 而xirr则是由多条现金流计算出来的 */ public class UpbaaDate { public int year; public int month; public int day; public double payment;// 对应的现金 public UpbaaDate(int year, int month, int day, double payment) { this.year = year; this.month = month; this.day = day; this.payment = payment; } /** * * @param stringTime * 格式为:2013/02/05 * @param payment 对应的现金 */ public UpbaaDate(String stringTime, double payment) { try { this.year=Integer.parseInt(stringTime.substring(0, 4)); this.month=Integer.parseInt(stringTime.substring(5, 7)); this.day=Integer.parseInt(stringTime.substring(8, 10)); } catch (Exception e) { // TODO: handle exception } this.payment=payment; } /** * 使用默认构造方法的话,则对应的是当前的时间,即今天的时间 */ public UpbaaDate() { String stringTime= DateUtil.getNowStringDate("/"); setStringDate(stringTime); } /** * 设置年月日的字符串格式的时间 * @param stringTime 格式为:2013/02/05 */ public void setStringDate(String stringTime){ try { this.year=Integer.parseInt(stringTime.substring(0, 4)); this.month=Integer.parseInt(stringTime.substring(5, 7)); this.day=Integer.parseInt(stringTime.substring(8, 10)); } catch (Exception e) { // TODO: handle exception } } /** * 获得1970年到现在的天数 * @return long */ public long getDaysFrom1970(){ return DateUtil.getDaysFrom1970(year, month, day); } /** * 获得字符串的日期 * @param split 分隔符 如 - * @return 如:2013-02-22 */ public String getStringDate(String split){ if(split==null){ split="-"; } StringBuffer stringDate=new StringBuffer(); stringDate.append(year).append(split); if(month<10){ stringDate.append(0).append(month); }else{ stringDate.append(month); } stringDate.append(split); if(day<10){ stringDate.append(0).append(day); }else{ stringDate.append(day); } return stringDate.toString(); } }
// java.util.Date转为java.sql.Date // java.util.Date utilDate=new Date(); // java.sql.Date sqlDate=new java.sql.Date(utilDate.getTime()); }
接着是XirrData类 这里面主要是计算方法
package com.p1.excel.XIRR; import java.util.ArrayList; /** * * @author 小木桩(staker) * 这是真正计算xirr算法的类,通过传进来的多条现金流进行计算xirr值和收益值 */ public class XirrData { private static final double Max_Rate=99999.9;//最大收益率 private static final double Min_Rate=-0.99999999;//最小收益率 private static final double Critical=0.00000001;//精确值 public static final int Error_Null_List=501;//代表传进来的list为空 public static final int Error_Less_Cash=502;//少于一条现金流 public static final int Error_Date=503;//传进来的现金流的第一条现金流记录的时间不是最早的时间 public static final int Error_First_Payment=504;//第一条现金流的payment的值不为负 /** * 第一条现金流具体某个时间点的差值天数,这个天数应该是所有现金流里面的差值天数最大的 */ private long startDays = 0; private ArrayList<UpbaaDate> listUpbaa; public XirrData(ArrayList<UpbaaDate> listUpbaa){ this.listUpbaa=listUpbaa; if (listUpbaa != null) { try { startDays = listUpbaa.get(0).getDaysFrom1970(); } catch (Exception e) { } } } /** * 计算收益值 * @return double */ public double getPal(){ if(listUpbaa==null){ return 0.0; } double pal=0; int count=listUpbaa.size(); for (int i = 0; i < count; i++) { pal=pal+listUpbaa.get(i).payment; } return pal; } /** * 通过传进来的多条现金流获得xirr值 * @return 返回收益率 */ public double getXirr() { if(listUpbaa == null){ return Error_Null_List; } int count=listUpbaa.size(); if (count <= 1) { return Error_Less_Cash;// 如果只有一条现金流则返回Error_Less_Cash } if (listUpbaa.get(0).payment > 0) { return Error_First_Payment; } for (int i = 0; i < count; i++) { if (listUpbaa.get(1).getDaysFrom1970() < startDays) { return Error_Date;// 如果不止一条现金流则判断第一条现金流是否为时间最早的,如果不是的话则返回ERROR_DATE } } boolean isEarn = getXNPVByRate(0.1) > 0;// 记录是赚钱了还是亏本了 double XIRR = 0; double tempMax = 0; double tempMin = 0; int calculateCount = 50; if (isEarn) { tempMax = Max_Rate; tempMin = 0; while (calculateCount > 0) { XIRR = (tempMin + tempMax) / 2f; double xnvp = getXNPVByRate(XIRR); if (xnvp > 0) { tempMin = XIRR; } else { tempMax = XIRR; } if (Math.abs(XIRR) < Critical) { break; } calculateCount--; } } else { tempMax = 0; tempMin = Min_Rate; while (calculateCount > 0) { XIRR = (tempMin + tempMax) / 2f; double xnvp = getXNPVByRate(XIRR); if (xnvp > 0) { tempMin = XIRR; } else { tempMax = XIRR; } if (Math.abs(XIRR) < Critical) { break; } calculateCount--; } } return XIRR; } private double getXNPVByRate(double rate) { double result = 0; int size = listUpbaa.size(); for (int i = 0; i < size; i++) { UpbaaDate date = listUpbaa.get(i); result = result + getOneValue(date.payment, rate, (int)date.getDaysFrom1970() - (int)startDays); } return result; } private double getOneValue(double payment, double rate, int dateDistance) { return payment / ((Math.pow((1 + rate), dateDistance / 365f))); } }
补上一个test类
package com.p1.excel.XIRR; import java.util.ArrayList; import java.util.List; //计算XIRR public class Xirr{ public static void main(String[] args) { ArrayList<UpbaaDate> list = new ArrayList<UpbaaDate>(); List<String> date = getDates(); List<Double> money = getCashFlow(); for(int i=0;i<date.size();i++){ list.add(new UpbaaDate(date.get(i),money.get(i))); } double xirr = new XirrData(list).getXirr();//普通XIRR System.out.println(xirr);//输出 0.049039049520590336 } //准备测试数据--日期 public static List<String> getDates(){ List<String> list = new ArrayList<String>(); list.add("2018-09-30"); list.add("2019-05-30"); list.add("2020-08-30"); list.add("2021-08-30"); list.add("2022-08-30"); list.add("2023-08-30"); list.add("2024-08-30"); list.add("2025-08-30"); list.add("2026-08-30"); list.add("2027-08-30"); list.add("2028-08-30"); list.add("2029-08-30"); list.add("2030-05-30"); list.add("2018-09-30"); list.add("2018-09-30"); list.add("2018-09-30"); list.add("2018-09-30"); return list; } //准备测试数据--现金流(先负 后正) public static List<Double> getCashFlow(){ List<Double> list = new ArrayList<Double>(); list.add(-4054820.00); list.add(866704.00); list.add(978151.00); list.add(944035.00); list.add(1046535.00); list.add(1153022.00); list.add(-73762.00); list.add(1378584.00); list.add(1497983.00); list.add(1622023.00); list.add(1750882.00); list.add(1868534.00); list.add(864064.00); list.add(0.00); list.add(0.00); list.add(0.00); list.add(0.00); return list; } }