package demo;
import java.math.BigDecimal;
public class demo {
public static double irr(double[] income) {
return irr(income, 0.1D);
}
public static double irr(double[] values, double guess) {
int maxIterationCount = 20;
double absoluteAccuracy = 1.0E-007D;
double x0 = guess;
int i = 0;
while (i < maxIterationCount) {
double fValue = 0.0D;
double fDerivative = 0.0D;
for (int k = 0; k < values.length; k++) {
fValue += values[k] / Math.pow(1.0D + x0, k);
fDerivative += -k * values[k] / Math.pow(1.0D + x0, k + 1);
}
double x1 = x0 - fValue / fDerivative;
if (Math.abs(x1 - x0) <= absoluteAccuracy) {
return x1;
}
x0 = x1;
i++;
}
return (0.0D / 0.0D);
}
public static void main(String[] args) {
double[] income = {-21335000,960930.55,25374069.35};//按天
double ret = irr(income,0.00001d) ;
System.out.println(new BigDecimal(ret));
}
}
方法2:
package demo;
import java.util.ArrayList;
import java.util.List;
public class IrrUtil {
/**迭代次数*/
public static int LOOPNUM=10000;
/**最小差异*/
public static final double MINDIF=0.0000000001;
/**
* @desc 使用方法参考main方法
* @param cashFlow 资金流
* @return 收益率
*/
public static double getIrr(List<Double> cashFlow){
double flowOut=cashFlow.get(0);
double minValue=0d;
double maxValue=1d;
double testValue=0d;
while(LOOPNUM>0){
testValue=(minValue+maxValue)/2;
System.out.println(testValue+"=("+minValue+"+"+maxValue+")/2");
double npv=NPV(cashFlow,testValue);
System.out.println(npv);
if(Math.abs(flowOut+npv)<MINDIF){
break;
}else if(Math.abs(flowOut)>npv){
maxValue=testValue;
}else{
minValue=testValue;
}
LOOPNUM--;
}
System.out.println(testValue+"=testValue");
System.out.println(testValue*12+"=testValue*12");
return testValue;
}
//irr=现金/(1+rate)^1+
public static double NPV(List<Double> flowInArr,double rate){
double npv=0;
for(int i=1;i<flowInArr.size();i++){
npv+=flowInArr.get(i)/Math.pow(1+rate, i);
}
return npv;
}
public static void main(String[] args) {
double flowOut=-21335000;
List<Double> flowInArr=new ArrayList<Double>();
flowInArr.add(flowOut);
flowInArr.add(960930.55);//1
flowInArr.add(25374069.35);
System.out.println(Math.pow(64,1.0/3.0));
System.out.println(IrrUtil.getIrr(flowInArr)*12);
}
}
package com.SQF.tool;
import java.io.*;
import java.util.*;
import com.SQF.pub.*;
class formulaNum {
double num;
char rightsign;//右边运算符号
formulaNum next;//下一位数据
formulaNum before;//前一位数据
}
public class formulaTool {
private static boolean checkPreci(double v){
//v=v*10;
double prcinum=0.00000000000001;
if(v<=prcinum)
return true;
if(v<0){
if(v>=(0-prcinum))
return true;
}
return false;
}
//-----------------------------------------------------------------------------------
//计算数字的n次方. num:数字, sqrnum:指数
public static double calSqrNum(double num,int sqrnum){
double num0=1;
for(int x=0;x<sqrnum;x++)
num0=num0*num;
return num0;
}
//-----------------------------------------------------------------------------------
//excel的NPV计算方法
public static double calNPV(double rate,double In[]){
double npv=0;
for(int x=0;x<In.length;x++){
npv=npv+In[x]/formulaTool.calSqrNum((1+rate),x);
}
return npv;
}
//------------------------------------------------------------------------------------
//根据360或365计算irr返回
public static double calCmpIrr(double flows[],int days[],double max,double min,int func){
if(func==0)//360天
return calRealDayIrr(flows, days, 360, max, min);
return calRealDayIrr(flows, days, 365, max, min);
}
//---------------------------------------------------------------------------------
//按实际天数计算irr
public static double calRealDayIrr(double In[],int caldays[],double maxrt,double minrt){
return calRealDayIrr(In,caldays,365,maxrt,minrt);
}
public static double calRealDayIrr(double In[],int caldays[],int ydays,double maxrt,double minrt){
double midrt=(maxrt+minrt)/2;
double midcal=calRealDayIrrResult(In,caldays,ydays,midrt);
double tmidcal=0;
if(midcal<0){
tmidcal=0-midcal;
}else
tmidcal=midcal;
if(checkPreci(tmidcal)){//计算结果接近0
return midrt;
}
if(checkPreci(maxrt-minrt)){
return maxrt;
}
//System.out.println("---"+pub_function.numFormat(midcal,"0.000000000000000000000"));
if(midcal<0){
return calRealDayIrr(In,caldays,ydays,maxrt,midrt);
}
return calRealDayIrr(In,caldays,ydays,midrt,minrt);
}
public static double calRealDayIrrResult(double In[],int caldays[],int ydays,double irr){
double lnbal=0-In[0];
//System.out.println(pub_function.numFormat(lnbal,"0.00"));
for(int x=1;x<In.length;x++){
//System.out.println("x:"+x);
double lint=lnbal*caldays[x]*irr/ydays;
double retamt=In[x]-lint;
lnbal=lnbal-retamt;
/*
System.out.println(pub_function.numFormat(In[x],"0.00")+","+
pub_function.numFormat(lint,"0.00")+","+
pub_function.numFormat(retamt,"0.00")+","+
pub_function.numFormat(lnbal,"0.00")
);
*/
}
//System.out.println(pub_function.numFormat(lnbal,"0.00")+","+irr);
return lnbal;
}
//-----------------------------------------------------------------------------------
//Excel IRR算法
//结果接近于0
public static double calIrr(double In[],double maxrt,double minrt){
double midrt=(maxrt+minrt)/2;
double midcal=calIrrResult(In,midrt);
double tmidcal=0;
if(midcal<0){
tmidcal=0-midcal;
}else
tmidcal=midcal;
if(checkPreci(tmidcal)){//计算结果接近0
return midrt;
}
if(checkPreci(maxrt-minrt)){
return maxrt;
}
if(midcal<0){
return calIrr(In,midrt,minrt);
}
return calIrr(In,maxrt,midrt);
}
//结果接近于残值
public static double calIrr(double In[],double lstvalue,double maxrt,double minrt){
double midrt=(maxrt+minrt)/2;
double midcal=calIrrResult(In,midrt);
double tmidcal=0;
if(midcal<0){
tmidcal=0-midcal;
}else
tmidcal=midcal;
double decvalue=tmidcal-lstvalue;
if(checkPreci(decvalue)){//计算结果接近0
return midrt;
}
if(checkPreci(maxrt-minrt)){
return maxrt;
}
if(midcal<0){
return calIrr(In,midrt,minrt);
}
return calIrr(In,lstvalue,maxrt,midrt);
}
public static double calIrrResult(double In[],double irr){
double re=0;
for(int x=0;x<In.length;x++){
re=re+In[x]/formulaTool.calSqrNum(1+irr,x);
}
return re;
}
//带资产余值的irr计算
public static double calRealDayIrr(double In[],int caldays[],int ydays,double maxrt,double minrt,double lstvalue){
double midrt=(maxrt+minrt)/2;
double midcal=calRealDayIrrResult(In,caldays,ydays,midrt,lstvalue);
double tmidcal=0;
if(midcal<0){
tmidcal=0-midcal;
}else
tmidcal=midcal;
if(tmidcal<0.0000000000001){//计算结果接近0
return midrt;
}
if(maxrt-minrt<0.0000000000001){
return maxrt;
}
if(midcal<0){
return calRealDayIrr(In,caldays,ydays,maxrt,midrt,lstvalue);
}
return calRealDayIrr(In,caldays,ydays,midrt,minrt,lstvalue);
}
public static double calRealDayIrrResult(double In[],int caldays[],int ydays,double irr,double lstvalue){
double lnbal=0-In[0];
for(int x=1;x<In.length;x++){
double lint=lnbal*caldays[x]*irr/ydays;
double retamt=In[x]-lint;
lnbal=lnbal-retamt;
}
return lnbal-lstvalue;
}
//-----------------------------------------------------------------------------------
/**
* 按日折现 计算日XIRR算法
*/
public static double calXirr(double In[],int caldays[],double maxrt,double minrt){
double midrt=(maxrt+minrt)/2;
double midcal=calXirrResult(In,caldays,midrt);
double tmidcal=0;
if(midcal<0){
tmidcal=0-midcal;
}else
tmidcal=midcal;
if(checkPreci(tmidcal)){//计算结果接近0
return midrt;
}
//System.out.println("tmidcal:"+pub_function.numFormat(tmidcal,"0.0000000000000000")+
//" maxrt:"+pub_function.numFormat(maxrt,"0.0000000000000000")+
//" minrt:"+pub_function.numFormat(minrt,"0.0000000000000000"));
if(checkPreci(maxrt-minrt)){
return maxrt;
}
if(midcal<0){
return calXirr(In,caldays,midrt,minrt);
}
return calXirr(In,caldays,maxrt,midrt);
}
public static double calXirrResult(double In[],int caldays[],double irr){
double re=0;
if(caldays!=null&&caldays.length>0){
for(int x=0;x<In.length;x++){
re=re+In[x]/Math.pow(1+irr,caldays[x]/365.0);
}
}
return re;
}
//-----------------------------------------------------------------------------------
public static int getDays(String bdate,String edate,int func ) throws Exception{
if(func==0)
return formulaTool.day360(bdate,edate);
return pub_function.getDays(bdate,edate);
}
//-----------------------------------------------------------------------------------
//Excel day360计算 yyyy-mm-dd
public static int day360(String bdate,String edate) throws Exception{
bdate=pub_function.toStdDate(bdate);
edate=pub_function.toStdDate(edate);
if(bdate.equals(edate))
return 0;
int byear=Integer.parseInt(bdate.substring(0,4));
int bmonth=Integer.parseInt(bdate.substring(5,7));
int svbday=Integer.parseInt(bdate.substring(8));
int bday=get360DtDay(bdate);
int bdays=byear*360+bmonth*30+bday;
int eyear=Integer.parseInt(edate.substring(0,4));
int emonth=Integer.parseInt(edate.substring(5,7));
int eday=get360DtDay(edate);
int sveday=Integer.parseInt(edate.substring(8));
int edays=eyear*360+emonth*30+eday;
int caldays=edays-bdays;
String bdate0=pub_function.newDate(bdate,0,0,1);
if(bdate0.equals(edate))
return 1;
if(svbday==sveday){
if((eday-bday)>1)
caldays=(eyear*360+emonth*30)-(byear*360+bmonth*30);
}
return caldays;
}
private static int get360DtDay(String dt){
int byear=Integer.parseInt(dt.substring(0,4));
int bmonth=Integer.parseInt(dt.substring(5,7));
int bday=Integer.parseInt(dt.substring(8));
if(bday>30)
bday=30;
if(bday==28)
if(bmonth==2)
if(byear%4!=0)
bday=30;
if(bday==29)
if(bmonth==2)
bday=30;
return bday;
}
//------------------------------------------------------------------------------------
private static void listnode(formulaNum head){
while(head!=null){
//System.out.println(""+head.num+ " "+head.rightsign);
head=head.next;
}
}
/*对公式串进行预先处理*/
private static String dealformulastr(String formulastr){
formulastr=dealDoubleAssign(formulastr);
//System.out.println("begin1:"+ formulastr);
if(formulastr.charAt(0)=='-')
formulastr="0"+formulastr;
int len=formulastr.length();
Stack stp=new Stack();
for(int x=len-1;x>=0;x--)
stp.push(new StringBuffer().append(formulastr.charAt(x)).toString());
formulastr="";
char bch=' ';
while(!stp.isEmpty()){
char c=((String)stp.pop()).charAt(0);
/*
if((c=='+')||(c=='-')||(c=='*')||(c=='/')){
if((bch=='+')||(bch=='-')||(bch=='*')||(bch=='/'))
formulastr=new StringBuffer(formulastr).append('0').toString();
}
*/
if((c=='+')||(c=='-')){
if((bch=='+')||(bch=='-'))
formulastr=new StringBuffer(formulastr).append('0').toString();
}
formulastr=new StringBuffer(formulastr).append(c).toString();
bch=c;
}
return formulastr;
}
/*对包括扩号的公式串进行运算*/
public static double calculateall(String formulastr){
//先进行公式'('处理
char chars[]=formulastr.toCharArray();
formulastr=new StringBuffer().append(chars[0]).toString();
for(int x=1;x<chars.length;x++){
if(chars[x]=='('){
if((chars[x-1]>='0') && (chars[x-1]<='9'))
formulastr=new StringBuffer(formulastr).append("*1*").toString();
}
formulastr=formulastr+chars[x];
}
int bposi=formulastr.lastIndexOf('(');
if(bposi<0){
//System.out.println("formulastr 1:"+formulastr);
return calculate(formulastr);
}
else{
String afterformulastr=formulastr.substring(bposi+1);
//System.out.println("afterformulastr 2:"+afterformulastr);
int eposi=afterformulastr.indexOf(')');
String innerformulastr=afterformulastr.substring(0,eposi);
String beforeformulastr=formulastr.substring(0,bposi);
if(eposi<formulastr.length()-1)
afterformulastr=afterformulastr.substring(eposi+1);
else
afterformulastr="";
formulastr=beforeformulastr+calculateall(innerformulastr)+afterformulastr;
// System.out.println(" formulastr 3:"+formulastr);
return calculateall(formulastr);
}
}
/*对不包含括号的公式进行运算*/
public static double calculate(String formulastr){
//System.out.println("begin:"+ formulastr);
formulastr=dealformulastr(formulastr);
//System.out.println("---"+formulastr);
//将信息放如堆栈中
Stack formulainfos=new Stack();
int size=formulastr.length();
for(int x=size-1;x>=0;x--){
formulainfos.push(new StringBuffer().append(formulastr.charAt(x)).toString());
}
//将堆栈中的公式信息解析到公式数据链中
formulaNum formulaHead=new formulaNum();//公式头
formulaHead.before=null;
formulaNum formulaNode=formulaHead; //节点
String num="";
char bfc=' ';
while(true){
char c=((String)formulainfos.pop()).charAt(0);
//System.out.println("c:"+c+",bfc:"+bfc);
if(((c=='+')||(c=='-')||(c=='*')||(c=='/'))&&
(bfc!='E')&&(bfc!='e')){
formulaNode.rightsign=c;
try{
if((c=='-')&&((bfc=='*')||(bfc=='/'))){
formulaNode.num=-1.0;
formulaNode.rightsign=bfc;
}else
formulaNode.num=Double.parseDouble(num);
}catch(Exception e){
//e.printStackTrace();
formulaNode.num=0;
}
num="";
formulaNum newNode=new formulaNum();//建立新节点
newNode.next=null;
newNode.before=formulaNode;
formulaNode.next=newNode;
formulaNode=newNode;
bfc=c;
} else{
bfc=c;
if(c!=' ')
num=num+c;
if(formulainfos.isEmpty()){
if(!pub_function.checkNumber(num)){//非数字
//System.out.println(" warning: NUM:["+num+"] be convert to 0");
num="0.00";
}
formulaNode.num=Double.parseDouble(num);
formulaNode.rightsign='0';//信息已完全解析
break;
}
}
}
double numresult=0;
//开始进行公式运算
//先进行*,/运算
formulaNum node=formulaHead;
while(node!=null){
if(node.rightsign=='*'){
node.num=node.num*node.next.num;
node.rightsign=node.next.rightsign;//将当前节点断开
node.next=node.next.next;
if(node.next==null)//到达end节点
break;
node.next.before=node;
continue;
}
else if(node.rightsign=='/'){
node.num=node.num/node.next.num;
node.rightsign=node.next.rightsign;//将当前节点断开
node.next=node.next.next;
if(node.next==null)//到达end节点
break;
node.next.before=node;
continue;
}
else
node=node.next;
}
node=formulaHead;
//最后进行+,-运算
while(node!=null){
if(node.rightsign=='+'){
node.num=node.num+node.next.num;
node.rightsign=node.next.rightsign;//将当前节点断开
node.next=node.next.next;
if(node.next==null)//到达end节点
break;
node.next.before=node;
continue;
}
else if(node.rightsign=='-'){
node.num=node.num-node.next.num;
node.rightsign=node.next.rightsign;//将当前节点断开
node.next=node.next.next;
if(node.next==null)//到达end节点
break;
node.next.before=node;
continue;
}
node=node.next;
}
return formulaHead.num;
}
/*对电子表格解析完的公式进行运算*/
public static double calculateXlsCell(String formulastr) throws Exception{
formulastr=pub_function.clearSpace(formulastr);
formulastr=dealTruncatRound(formulastr);
formulastr=formulastr.toUpperCase();
// System.out.println("%formulastr:"+formulastr);
//对%号进行处理
formulastr=pub_function.replaceStr(formulastr,"%","*0.01");
//System.out.println("SUMformulastr:"+formulastr);
/*去除SUM串*/
int posi=0;
while(formulastr.indexOf("SUM")>=0)
formulastr=pub_function.replaceStr(formulastr,"SUM","");
/*去除ABS部分*/
// System.out.println("ABSformulastr:"+formulastr);
formulastr=calculateXlsABS(formulastr);
/*去除AVERAGE部分*/
formulastr=calculateXlsAVERAGE(formulastr);
/*计算MAX部分*/
//System.out.println("MAXformulastr:"+formulastr);
formulastr=calculateXlsMAX(formulastr);
/*计算MIN部分*/
//System.out.println("MINformulastr:"+formulastr);
formulastr=calculateXlsMIN(formulastr);
/*去除COUNTIF部分*/
// System.out.println("COUNTIFformulastr:"+formulastr);
formulastr=calculateXlsCOUNTIF(formulastr);
/*去除IF部分*/
//System.out.println("IF---:"+formulastr);
formulastr=calculateXlsIF(formulastr);
/*去除MEDIAN部分*/
//System.out.println("Median--:"+formulastr);
formulastr=calculateMedian(formulastr);
//System.out.println("last: formulastr:"+formulastr);
return calculateall(formulastr);
}
/*对公式中的COUNTIF进行处理*/
private static String calculateXlsCOUNTIF(String formulastr) throws Exception{
int posi=0;
posi=formulastr.indexOf("COUNTIF");
if(posi<0)
return formulastr;
String frontstr=formulastr.substring(0,posi);
int posi1=formulastr.indexOf(")");
if(posi1<0)
throw new Exception("COUNTIF syntax define error["+formulastr+"]");
int posi2=formulastr.indexOf("(");
if(posi2<0)
throw new Exception("COUNTIF syntax define error["+formulastr+"]");
String afterstr=formulastr.substring(posi1+1);
String countifstr=formulastr.substring(posi2+1,posi1);
//对countif部分进行计算
countifstr=calculateXlsCOUNTIFinnerStr(countifstr);
formulastr=frontstr+countifstr+afterstr;
return calculateXlsCOUNTIF(formulastr);
}
private static String calculateXlsCOUNTIFinnerStr(String innerstr) throws Exception{
int posi=innerstr.indexOf(",");
if(posi<0)
throw new Exception("COUNTIF syntax define error["+innerstr+"]");
String bfstr=innerstr.substring(0,posi);
String afterstr=innerstr.substring(posi+1);
afterstr=pub_function.replaceStr(afterstr,"\"","");
String precondis[]=pub_function.explainTagStr(bfstr,'+');
String cntstr="";
for(int x=0;x<precondis.length;x++){
String ifstr="IF("+precondis[x]+afterstr+",1,0)";
if(x>0)
cntstr=cntstr+"+";
cntstr=cntstr+ifstr;
}
return calculateXlsIF(cntstr);
}
/*对公式中IF部分进行处理*/
/*
private static String calculateXlsIF(String formulastr) throws Exception{
if(formulastr.indexOf("IF")<0)
return formulastr;
int posi=0;
System.out.println("IFstr:"+formulastr);
while((posi=formulastr.indexOf("IF"))>=0){
String frontstr=formulastr.substring(0,posi);
String afterstr=formulastr.substring(posi+2).trim();
System.out.println("frontstr:"+frontstr);
System.out.println("afterstr:"+afterstr);
//取IF中公式串进行处理
int po=findRightBracket(afterstr);
//String instr="";
if(afterstr.length()>po+1){
String instr=afterstr.substring(0,po+1);
System.out.println("instr:"+instr);
//检查是否还有IF部分
if(instr.indexOf("IF")>=0){
instr=calculateXlsIF(instr);//进行递归处理
}else{
instr=calculateIFinnerStr(instr);
System.out.println("end instr:"+instr);
}
afterstr=afterstr.substring(po+1);
if(pub_function.IsEmptyStr(instr))
formulastr=new StringBuffer(frontstr).append(afterstr).toString();
else
formulastr=new StringBuffer(frontstr).append(instr).append(afterstr).toString();
if(formulastr.indexOf("IF")<0)
return calculateIFinnerStr(formulastr);
continue;
}
}
return calculateXlsIF(formulastr);
}
*/
private static String calculateXlsIF(String formulastr) throws Exception{
if(formulastr.indexOf("IF")<0)
return formulastr;
int posi=0;
while((posi=formulastr.indexOf("IF"))>=0){
String frontstr=formulastr.substring(0,posi);
String afterstr=formulastr.substring(posi+2).trim();
//取IF中公式串进行处理
//System.out.println("---frontstr:"+frontstr);
//System.out.println("---afterstr:"+afterstr);
int po=findRightBracket(afterstr);
String instr=afterstr.substring(1,po);
//检查是否有内部的MEDIAN
if(instr.indexOf("MEDIAN")>=0)
instr=calculateMedian(instr);
//System.out.println("---instr:"+instr);
//检查是否还有IF部分
if(instr.indexOf("IF")>=0){
instr=calculateXlsIF(instr);//进行递归处理
}
String result=calculateIFinnerStr(instr);
//System.out.println("result:"+result);
/*
if(result<0)
result=result*(-1);
*/
afterstr=afterstr.substring(po+1);
formulastr=new StringBuffer(frontstr).append(result).append(afterstr).toString();
//System.out.println("***********result formulastr:"+formulastr);
}
//System.out.println("***********result:"+formulastr);
return formulastr;
}
/*对IF部分中的子串进行处理*/
private static String calculateIFinnerStr(String str) throws Exception{
if(pub_function.checkNumber(str))
return str;
if(str.indexOf("<")<0){
if(str.indexOf(">")<0)
if(str.indexOf(",")<0)
return str;
}
//System.out.println("cal in str:"+str);
str=str.trim();
//str=str.substring(1,str.length()-1);
if(pub_function.count_char(str,',')<2){
if(pub_function.count_char(str,',')==1)
str=str+",0";
else
throw new Exception("IF syntax define error["+str+"]");
}
if(pub_function.count_char(str,',')>2){
int addposi=str.lastIndexOf(',');
str=str.substring(0,addposi);
str='('+str+')';
return calculateIFinnerStr(str);
}
String results[]=pub_function.explainTagStr(str,',');
//System.out.println("0:"+results[0]);
//System.out.println("cal:"+calculateXlsCell(results[0]));
//System.out.println("1:"+results[1]);
//System.out.println("2:"+results[2]);
if((results[0].indexOf("<>")>0)
||(results[0].indexOf("!=")>0)
||(results[0].indexOf(">=")>0)
||(results[0].indexOf("<=")>0)
){
int flg=0;//0: <>,!= , 1:>= 2:<=
int posi=results[0].indexOf("<>");
if(posi<0)
posi=results[0].indexOf("!=");
if(posi<0){
flg=1;
posi=results[0].indexOf(">=");
}
if(posi<0){
flg=2;
posi=results[0].indexOf("<=");
}
//System.out.println("flg:"+flg);
String arr[]=new String[2];
arr[0]=results[0].substring(0,posi);
arr[1]=results[0].substring(posi+2);
if(arr[0].trim().equals(arr[1].trim())){
if(flg==0)
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
}else{
double a1=0;
double a2=0;
try{
a1=calculateXlsCell(arr[0]);
a2=calculateXlsCell(arr[1]);
//System.out.println("al="+a1);
//System.out.println("a2="+a2);
}catch(Exception e){
if(arr[0].equals(arr[1])){
if(flg==0)
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
}else{
if(flg==0)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
}
if((a1-a2)!=0){
if(flg==0){
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
}else if(flg==1){
if(a1>a2)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}else if(flg==2){
if(a1<a2)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
}else if((a1-a2)==0){
if((flg==1)||(flg==2))
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
//System.out.println(" if inner result:"+str);
return str;
}
else if(results[0].indexOf('=')>0){
String arr[]=pub_function.explainTagStr(results[0],'=');
if(arr[0].trim().equals(arr[1].trim()))
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else{
double a1=0;
double a2=0;
try{
a1=calculateXlsCell(arr[0]);
a2=calculateXlsCell(arr[1]);
}catch(Exception e){
if(arr[0].equals(arr[1]))
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
if(a1==a2)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
}
else if(results[0].indexOf('>')>0){
String arr[]=pub_function.explainTagStr(results[0],'>');
if(arr[0].trim().equals(arr[1].trim()))
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
else{
double a1=0;
double a2=0;
try{
a1=calculateXlsCell(arr[0]);
a2=calculateXlsCell(arr[1]);
}catch(Exception e){
int f=arr[0].compareTo(arr[1]);
if(f>0)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
if(a1>a2)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
}
else if(results[0].indexOf('<')>0){
String arr[]=pub_function.explainTagStr(results[0],'<');
if(arr.length>2)
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
if(arr[0].trim().equals(arr[1].trim()))
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
double a1=0;
double a2=0;
try{
//System.out.println("arr[0]:"+arr[0]);
//System.out.println("arr[1]:"+arr[1]);
a1=calculateXlsCell(arr[0]);
a2=calculateXlsCell(arr[1]);
//System.out.println("a1:"+a1);
//System.out.println("a2:"+a2);
}catch(Exception e){
int f=arr[0].compareTo(arr[1]);
if(f<0)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
if(a1<a2)
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
else
return new StringBuffer().append(calculateXlsCell(results[2])).toString();
}
else{
return new StringBuffer().append(calculateXlsCell(results[1])).toString();
}
}
/*对公式中MAX部分进行处理*/
private static String calculateXlsMAX(String formulastr) throws Exception{
if(formulastr.indexOf("MAX")<0)
return formulastr;
int posi=0;
while((posi=formulastr.indexOf("MAX"))>=0){
String frontstr=formulastr.substring(0,posi);
String afterstr=formulastr.substring(posi+3).trim();
//取MAX中公式串进行处理
//System.out.println("---frontstr:"+frontstr);
//System.out.println("---afterstr:"+afterstr);
int po=findRightBracket(afterstr);
String instr=afterstr.substring(0+1,po);
//System.out.println("---instr:"+instr);
//检查是否还有MAX部分
if(instr.indexOf("MAX")>=0){
instr=calculateXlsMAX(instr);//进行递归处理
}
double result=calculateXlsMAXInner(instr);
/*
if(result<0)
result=result*(-1);
*/
afterstr=afterstr.substring(po+1);
formulastr=new StringBuffer(frontstr).append(result).append(afterstr).toString();
}
//System.out.println("***********result:"+formulastr);
return formulastr;
}
private static double calculateXlsMAXInner(String innerstr) throws Exception{
//System.out.println("---------innerstr:"+innerstr);
innerstr=innerstr.trim();
//innerstr=innerstr.replace('+',',');
String numarray[]=pub_function.explainTagStr(innerstr,',');
for(int x=0;x<numarray.length;x++){
//System.out.println("-------------------------"+numarray[x]+"="+calculateXlsCell(numarray[x]));
numarray[x]=""+calculateXlsCell(numarray[x]);
}
/*
for(int x=0;x<numarray.length;x++){
System.out.println("result["+x+"]="+numarray[x]);
}
*/
numarray=pub_function.reorderArray(numarray);
/*
for(int x=0;x<numarray.length;x++){
System.out.println("result["+x+"]="+numarray[x]);
}
*/
double num=Double.parseDouble(numarray[numarray.length-1]);
return num;
}
/*对公式中MIN部分进行处理*/
private static String calculateXlsMIN(String formulastr) throws Exception{
if(formulastr.indexOf("MIN")<0)
return formulastr;
int posi=0;
while((posi=formulastr.indexOf("MIN"))>=0){
String frontstr=formulastr.substring(0,posi);
String afterstr=formulastr.substring(posi+3).trim();
//取MIN中公式串进行处理
int po=findRightBracket(afterstr);
String instr=afterstr.substring(0+1,po);
//检查是否还有MIN部分
if(instr.indexOf("MIN")>=0){
instr=calculateXlsMIN(instr);//进行递归处理
}
double result=calculateXlsMINInner(instr);
/*
if(result<0)
result=result*(-1);
*/
afterstr=afterstr.substring(po+1);
formulastr=new StringBuffer(frontstr).append(result).append(afterstr).toString();
}
return formulastr;
}
private static double calculateXlsMINInner(String innerstr) throws Exception{
innerstr=innerstr.trim();
//innerstr=innerstr.replace('+',',');
String numarray[]=pub_function.explainTagStr(innerstr,',');
for(int x=0;x<numarray.length;x++){
numarray[x]=""+calculateXlsCell(numarray[x]);
}
numarray=pub_function.reorderArray(numarray);
double num=Double.parseDouble(numarray[0]);
return num;
}
/*对公式中MEDIAN部分进行处理*/
private static String calculateMedian(String formulastr) throws Exception{
if(formulastr.indexOf("MEDIAN")<0)
return formulastr;
int posi=0;
//System.out.println("go here:"+formulastr);
while((posi=formulastr.indexOf("MEDIAN"))>=0){
String frontstr=formulastr.substring(0,posi);
String afterstr=formulastr.substring(posi+6).trim();
//取MEDIAN中公式串进行处理
int po=findRightBracket(afterstr);
String instr=afterstr.substring(0+1,po);
//检查是否还有MEDIAN部分
if(instr.indexOf("MEDIAN")>=0){
instr=calculateMedian(instr);//进行递归处理
}
// System.out.println("instr:"+instr);
double result=calculateMedianInner(instr);
/*
if(result<0)
result=result*(-1);
*/
afterstr=afterstr.substring(po+1);
formulastr=new StringBuffer(frontstr).append(result).append(afterstr).toString();
}
return formulastr;
}
private static double calculateMedianInner(String innerstr) throws Exception{
innerstr=innerstr.trim();
//innerstr=innerstr.replace('+',',');
String numarray[]=pub_function.explainTagStr(innerstr,',');
for(int x=0;x<numarray.length;x++){
// System.out.println(numarray[x]+"="+calculateXlsCell(numarray[x]));
numarray[x]=""+calculateXlsCell(numarray[x]);
}
numarray=pub_function.reorderArray(numarray);
if(numarray.length%2==1){
return Double.parseDouble(numarray[(numarray.length-1)/2]);
}
double num0=Double.parseDouble(numarray[(numarray.length-2)/2]);
double num1=Double.parseDouble(numarray[numarray.length/2]);
double num=(num0+num1)/2;
//System.out.println("num:"+num);
return num;
}
/*对公式中ABS部分进行处理*/
private static String calculateXlsABS(String formulastr) throws Exception{
if(formulastr.indexOf("ABS")<0)
return formulastr;
int posi=0;
/*去除ABS部分*/
while((posi=formulastr.indexOf("ABS"))>=0){
String frontstr=formulastr.substring(0,posi);
String afterstr=formulastr.substring(posi+3).trim();
//取ABS中公式串进行处理
//System.out.println("frontstr:"+frontstr);
//System.out.println("afterstr:"+afterstr);
int po=findRightBracket(afterstr);
String instr=afterstr.substring(0+1,po);
//检查是否还有ABS部分
if(instr.indexOf("ABS")>=0){
instr=calculateXlsABS(instr);//进行递归处理
}
double result=calculateall(instr);
if(result<0)
result=result*(-1);
afterstr=afterstr.substring(po+1);
formulastr=new StringBuffer(frontstr).append(result).append(afterstr).toString();
}
return formulastr;
}
/*对公式中AVERAGE部分进行处理*/
private static String calculateXlsAVERAGE(String formulastr) throws Exception{
if(formulastr.indexOf("AVERAGE")<0)
return formulastr;
int posi=0;
/*去除ABS部分*/
while((posi=formulastr.indexOf("AVERAGE"))>=0){
String frontstr=formulastr.substring(0,posi);
String afterstr=formulastr.substring(posi+7).trim();
//取AVERAGE中公式串进行处理
//System.out.println("frontstr:"+frontstr);
//System.out.println("afterstr:"+afterstr);
int po=findRightBracket(afterstr);
String instr=afterstr.substring(0+1,po);
//检查是否还有AVERAGE部分
if(instr.indexOf("AVERAGE")>=0){
instr=calculateXlsAVERAGE(instr);//进行递归处理
}
String nums[]=pub_function.explainTagStr(instr,',');
int n=nums.length;
double sumall=0;
for(int x=0;x<n;x++){
sumall=sumall+Double.parseDouble(nums[x]);
}
double result=sumall/(n*1.0);
afterstr=afterstr.substring(po+1);
formulastr=new StringBuffer(frontstr).append(result).append(afterstr).toString();
}
return formulastr;
}
/*获取串中第一个'('对应的的')'的位置*/
private static int findRightBracket(String bracketstr) throws Exception{
//System.out.println("go here :"+bracketstr);
if(bracketstr.charAt(0)!='(')
throw new Exception("the String ["+bracketstr+"]'s first char is not '('");
int posi=bracketstr.indexOf(')');//找串中第一处出现')'的位置
if(posi<0)
return posi;//找不到对应的')';
String frontstr=bracketstr.substring(0,posi);//取posi前的子串
int bposi=frontstr.lastIndexOf('(');//找子串中
if(bposi==0)//找到位置
return posi;
else{//将posi,bposi位置的两个字替换为'*',继续查找
bracketstr=pub_function.replaceStr(bracketstr,posi,'*');
bracketstr=pub_function.replaceStr(bracketstr,bposi,'*');
return findRightBracket(bracketstr);
}
}
/*对公式中truncat和round部分进行处理*/
public static String dealTruncatRound(String formula) throws Exception{
String truncatstr="java_truncat";
String roundstr="java_round";
String str=formula.toLowerCase();
if((str.indexOf("truncat")<0) && (str.indexOf("round")<0))
return formula;
//if(truncat(12.1234,3)>round(1234,2),12.13,12.12);
int a=str.indexOf(truncatstr);
if(a>=0){
StringBuffer sb=new StringBuffer(str.substring(0,a));
String endstr=str.substring(a+truncatstr.length());
int a1=findRightBracket(endstr); //找'('对应的')'
String midstr=endstr.substring(1,a1); //去除'('和')'
int a2=midstr.lastIndexOf(',');
int len=Integer.parseInt(midstr.substring(a2+1).trim());//获取truncat的长度
midstr=midstr.substring(0,a2);
midstr=new StringBuffer().append(calculateXlsCell(midstr)).toString();//进行公式运算
midstr=pub_function.runTruncat(midstr,len);
sb.append(midstr);
sb.append(endstr.substring(a1+1));
str=sb.toString();
sb=null;
}
int b=str.indexOf(roundstr);
if(b>=0){
StringBuffer sb1=new StringBuffer(str.substring(0,b));
String endstr1=str.substring(b+roundstr.length());
int b1=findRightBracket(endstr1);//找'('对应的')';
String midstr1=endstr1.substring(1,b1);//去除'('和')'
int b2=midstr1.lastIndexOf(',');
int len1=Integer.parseInt(midstr1.substring(b2+1).trim());//获取round的长度
midstr1=midstr1.substring(0,b2);
midstr1=pub_function.doubleToString(calculateXlsCell(midstr1),len1);
sb1.append(midstr1);
sb1.append(endstr1.substring(b1+1));
str=sb1.toString();
}
return dealTruncatRound(str);
}
//处理公式中的--,++,/-符号
private static String dealDoubleAssign(String formula){
int posi=formula.indexOf("--");
if(posi>=0)
formula=pub_function.replaceStr(formula,"--","+");
posi=formula.indexOf("++");
if(posi>=0)
formula=pub_function.replaceStr(formula,"++","+0+");
posi=formula.indexOf("+-");
if(posi>=0)
formula=pub_function.replaceStr(formula,"+-","+0-");
posi=formula.indexOf("-+");
if(posi>=0)
formula=pub_function.replaceStr(formula,"-+","-0+");
posi=formula.indexOf("/-");
if(posi>=0){
String str0=formula.substring(0,posi);
String str1="/"+formula.substring(posi+2);
int len=str0.length();
if(len==0)
str0="-";
int calposi=len;
while(true){
calposi--;
if(calposi==0){
str0="-"+str0;
break;
}
char c=str0.charAt(calposi);
if(((c>='0')&&(c<='9'))||(c=='.'))
continue;
else{
str0=str0.substring(0,calposi+1)+"-"+str0.substring(calposi+1);
str0=dealDoubleAssign(str0);
break;
}
}
formula=str0+str1;
}
if(formula.indexOf("--")>=0)
formula=dealDoubleAssign(formula);
if(formula.indexOf("++")>=0)
formula=dealDoubleAssign(formula);
return formula;
}
public static void main(String args[]) throws Exception{
/* String datas[][]={
{"2016-05-03", "-100000000"}
,{"2016-06-15", "9354166.67"}
,{"2016-09-15", "9907066.67"}
,{"2016-12-15", "9789372.22"}
,{"2017-03-15", "9674000.00"}
,{"2017-06-15", "9586600.00"}
,{"2017-09-15", "9479777.78"}
,{"2017-12-15", "9366727.78"}
,{"2018-03-15", "9256000.00"}
,{"2018-06-15", "9159311.11"}
,{"2018-09-15", "9052488.89"}
,{"2018-12-15", "7144083.33"}
,{"2019-05-02", "5091041.67"}
};
Hashtable ht=new Hashtable();
for(int x=0;x<datas.length;x++){
ht.put(datas[x][0],datas[x][1]);
}
String bgdt="2016-05-03";
String eddt="2019-05-02";
String caldt=bgdt;
String lstcaldt=bgdt;
Hashtable htpool=new Hashtable();
while(pub_function.getDays(caldt,eddt)>=0){
Hashtable svht=new Hashtable();
String retbalstr="0";
String tmpstr=(String)ht.get(caldt);
if(!pub_function.IsEmptyStr(tmpstr))
retbalstr=tmpstr;
//if(caldt.equals(bgdt))
// retbalstr="0";
svht.put("retdate",caldt);
svht.put("retbal",retbalstr);
svht.put("lstcaldt",lstcaldt);
int caldays=pub_function.getDays(lstcaldt,caldt);
svht.put("caldays",""+caldays);
htpool.put(""+htpool.size(),svht);
lstcaldt=caldt;
caldt=pub_function.newDate(caldt,0,0,1);
}
double cashflow[]=new double[htpool.size()];
int days[]=new int[htpool.size()];
for(int x=0;x<htpool.size();x++){
Hashtable svht=(Hashtable)htpool.get(""+x);
int caldays=Integer.parseInt((String)svht.get("caldays"));
days[x]=caldays;
double retbal=Double.parseDouble((String)svht.get("retbal"));
cashflow[x]=retbal;
//System.out.println(pub_function.numFormat(cashflow[x],"0.00")+" "+days[x]);
}
*/
String strs[][]={
{"2017-01-18","-350000000.00"}
,{"2017-07-15","41940153.30" }
,{"2018-01-15","41352004.72" }
,{"2018-07-15","40646226.42" }
,{"2019-01-15","39940448.11" }
,{"2019-07-15","39234669.81" }
,{"2020-01-15","38528891.51" }
,{"2020-07-15","37823113.21" }
,{"2021-01-15","37117334.91" }
,{"2021-07-15","36411556.60" }
,{"2022-01-18","35717541.27" }
};
Hashtable ht=new Hashtable();
for(int x=0;x<strs.length;x++){
ht.put(strs[x][0],strs[x][1]);
}
String caldt="2017-01-18";
String eddt="2022-01-18";
Hashtable htpool=new Hashtable();
String bday="0";
while(true){
String num=(String)ht.get(caldt);
if(pub_function.IsEmptyStr(num))
num="0.00";
String calstr[]={num,bday};
htpool.put(""+htpool.size(),calstr);
System.out.println(caldt+ " "+num);
if(caldt.equals(eddt))
break;
caldt=pub_function.newDate(caldt,0,0,1);
bday="1";
}
System.out.println("--------------");
double cashflow[]=new double[htpool.size()];
int days[]=new int[htpool.size()];
for(int x=0;x<htpool.size();x++){
String[] calstr=(String[])htpool.get(""+x);
cashflow[x]=Double.parseDouble(calstr[0].trim());
days[x]=Integer.parseInt(calstr[1].trim());
System.out.println(x+" "+pub_function.numFormat(cashflow[x],"0.00")+" "+days[x]);
}
double acyirr=formulaTool.calCmpIrr(cashflow,days,10.00,-10,1);
double dayirr=acyirr/365;
System.out.println("acyirr:"+pub_function.numFormat(acyirr,"0.000000000000000000000000000"));
System.out.println("dayirr:"+pub_function.numFormat(dayirr,"0.000000000000000000000000000"));
//double lvamt=0-Double.parseDouble(strs[0][0].trim());
//for(int x=0;x<strs.length;x++){
// int caldays=days[x];
// double retbal=cashflow[x];
// if(x==0)
// retbal=0;
// double retint=lvamt*caldays*dayirr;
// retint=Double.parseDouble(pub_function.numFormat(retint,"0.00"));
// double retamt=retbal-retint;
// retamt=Double.parseDouble(pub_function.numFormat(retamt,"0.00"));
// lvamt-=retamt;
// System.out.println(x+" "+pub_function.numFormat(retbal,"0.00")
// +" "+pub_function.numFormat(retint,"0.00")
// +" "+pub_function.numFormat(retamt,"0.00")
// +" "+pub_function.numFormat(lvamt,"0.00")
// );
//}
//double irr360=calCmpIrr(ln,days,10,0,0);
//System.out.println("irr360:"+pub_function.numFormat(irr360/360,"0.000000000000000000000000"));
//System.out.println("xirr:"+calXirr(ln,days1,10.0,0.0));
// double irr365=calCmpIrr(ln,days,10,0,1);
// System.out.println("irr360:"+pub_function.numFormat(irr365/365,"0.000000000000000000000000"));
//String formula="9+SUM(12+3+9-5)+IF(33=11,12,-12)+ABS(12-ABS(13-20)+ABS(15)-ABS(-15))+SUM(ABS(-11)+3(23+2)+ABS(11))";
/*
String formula0="IF(33=44,12,-12)";
String formula1="IF(33<>44,12,-12)";
String formula2="IF(33>33,12,-12)";
String formula3="IF(33>=33,12,-12)";
String formula4="IF(33<33,12,-12)";
String formula5="IF(33<=33,12,-12)";
System.out.println(formula0+" "+calculateXlsCell(formula0));
System.out.println(formula1+" "+calculateXlsCell(formula1));
System.out.println(formula2+" "+calculateXlsCell(formula2));
System.out.println(formula3+" "+calculateXlsCell(formula3));
System.out.println(formula4+" "+calculateXlsCell(formula4));
System.out.println(formula5+" "+calculateXlsCell(formula5));
*/
//String formula="(700000.0-(-1)*(-93333.36)";
/*String formula=
"B0001!B001/10000+B0001!B002/10000+B0001!B008/10000+B0001!B009/10000+"+
"SUM(C217:(B0001!B006+B0001!B007)/10000)+B0001!B017/10000-"+
"ABS(B0001!B019/10000)+ABS(B0001!C021/10000)-"+
"ABS(B0001!C022/10000)+ABS(B0001!C024-B0001!C025)/10000)+"+
"ABS(B0001!C027/10000)";
*/
//String formula="COUNTIF(3,\">=25%\")+COUNTIF(0.1,\">=25%\")+COUNTIF(5,\">=25%\")";
//String formula=args[0];
//System.out.println(""+formula+"="+calculateXlsCOUNTIF(formula));
//String formula="IF(L13/J13>1,100,L13*100/J13)";
//String formula="(2-0+0.58/0.5)*100";
//String formula="median(67.45,132.58,89.75,82.89,36.31)";
//String formula="MEDIAN(20+30,200,0)*0.03";
//String formula="MEDIAN(200,0,IF(30>20,IF(50<30,100-20/50*100,(30-20)*100*100*2),0))";
/*
String formula="IF(21607.62623/7323.8*100<0,0,IF(21607.62623<7323.8,21607.62623/7323.8*100,IF(100+(21607.62623-7323.8)/10>200,200,100+(21607.62623-7323.8)/10)))*8%";
String result[]=operFile.readFileContent("c:/tmp/log.log");
Hashtable ht=new Hashtable();
for(int x=0;x<result.length;x++){
String tmpstr="~~~";
int posi=result[x].indexOf(tmpstr);
String str0=result[x].substring(0,posi);
String str1=result[x].substring(posi+4);
ht.put(str0.trim(),str1.trim());
}
*/
/*
String L5="21607.62623";
String K5="7323.8";
String formula="IF(L5/K5*100<0,0,IF(L5<K5,L5/K5*100,IF(100+(L5-K5)/10>200,200,100+(L5-K5)/10)))*8%";
System.out.println(""+formula);
formula=pub_function.replaceStr(formula,"L5",L5);
formula=pub_function.replaceStr(formula,"K5",K5);
*/
/*
String formula="MEDIAN(0,200,IF(50<6880.46/3525.0*0.7*100<120,IF(1170.0/900.0"+
"*0.7*100>120,MIN(120,(6880.46+1170.0-900.0)/3525.0*0.7*100)),6880.46/3525.0*0.7*100)"+
",IF(1170.0/900.0*0.7*100>120,6880.46/3525.0*0.7*100,IF(50<1170.0/900.0*0.7*100,"+
"IF((1170.0+6880.46-3525.0)/900.0*0.7*100>120,120/100*900.0/0.7-1170.0,6880.46/3525.0*0.7*100),"+
"6880.46/3525.0*0.7*100)))*0.0200000";
*/
/*
String U54="-2";
String T54="3";
String U53="93";
String T53="75";
String V53="101.8";
String formula="IF(U54/T54<80/100,IF(U53/T53<100/100,(U54/T54-100/100)*100,MAX((U54/T54-100/100)*100,50-V53)),MIN((U54-T54*80/100)/1,200-V53)+MIN((U54/T54-100/100)*100,0))";
formula=pub_function.replaceStr(formula,"U54",U54);
formula=pub_function.replaceStr(formula,"T54",T54);
formula=pub_function.replaceStr(formula,"U53",U53);
formula=pub_function.replaceStr(formula,"T53",T53);
formula=pub_function.replaceStr(formula,"V53",V53);
*/
/*
String U17="1.48%";
String T17="0.8%";
String formula="IF(100+(U17-T17)*100*100<0,0,IF(100+(U17-T17)*100*100>200,200,100+(U17-T17)*100*100))*1.5/100";
formula=pub_function.replaceStr(formula,"U17",U17);
formula=pub_function.replaceStr(formula,"T17",T17);
*/
/*
String formula0="MIN((-2-3*80/100)/1,200-101.8)+MIN((-2/3-100/100)*100,0)";
System.out.println(""+formula0+"="+formulaTool.calculateXlsCell(formula0));
String formula1="IF(93/75<100/100,(-2/3-100/100)*100,MAX((-2/3-100/100)*100,50-101.8))";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
*/
//System.out.println(""+txtExcelTool.getNumFormula(ht,formula));
//System.out.println("8%:"+pub_function.checkNumber("8%"));
//String formula="IF(50<6880.46/3525*0.7*100<120,0,6880.46/3525*0.7*100)";
//String formula="median(0,200,136.6332,372.8571428571429)";
//String formula="MEDIAN(-25,50,IF(0.06860000000000001>0.0729,IF(5033.67<4725.13,100-5033.67/4725.13*100,MIN((0.06860000000000001-0.0729)*100*100*0.5,50)),IF(0.06860000000000001>5.7225,100-5033.67/4725.13*100,(0.06860000000000001-0.0729)*100*100*0.25)))";
//String formula="IF(-2/3<80/100,IF(93/75<100/100,(-2/3-100/100)*100,MAX((-2/3-100/100)*100,50-101.8)),MIN((-2-3*80/100)/1,200-101.8)+MIN((-2/3-100/100)*100,0))";
/*
System.out.println(""+formula+"="+formulaTool.calculateXlsCell(formula));
String formula1="IF(100+(0.0148-0.0080)*100*100<0,0,IF(100+(0.0148-0.80)*100*100>200,200,100+(0.0148-0.0080)*100*100))*1.5%";
*/
// String formula="MEDIAN(100+51.9999999999998,200,0)*3%";
//String formula="average(-10)";
//String formula="IF((-22/-64)*100<0,0,IF(-22<-64,(-22/-64)*100,IF(100+(-22--64)/30>200,200,100+(-22--64)/30)))*0.03";
//String formula="IF((-22/-64)*100<0,0,101.4)";
//String formula="(1-14/19.5)*-1";
// String formula="MEDIAN(200,0,IF(0.0358>0.038,IF(80270<71040,100-80270/71040*100,(0.0358-0.038)*100*100*2),0))";
//String formula="IF(0.0358>0.038,IF(80270<71040,100-80270/71040*100,(0.0358-0.038)*100*100*2),0)";
//System.out.println(""+formula+"="+formulaTool.calculateXlsCell(formula));
//System.out.println("IF(-2/3<80/100,-171.06666666666666,-51.8 )"+"="+formulaTool.calculateXlsCell("IF(-2/3<80/100,-171.06666666666666,-51.8 )"));
/*
String strs[]={
"99",
"89",
"-1000",
"-2000",
"-1500",
"-10",
"10"
};
strs=pub_function.reorderArray(strs);
for(int x=0;x<strs.length;x++)
System.out.println(strs[x]);
*/
/*
String formula1="IF(3<=0,1,-1)";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
formula1="IF(3.00>=0,1,-1)";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
formula1="IF(0>=0,1,-1)";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
formula1="IF(-1.00>=0,1,-1)";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
formula1="IF(-1.00<=0,1,-1)";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
formula1="IF(0.00<=0,1,-1)";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
formula1="IF(0.00>=0,1,-1)";
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
*/
// String formula1="IF(20120629>=20120629,1,-1)";
// System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
// formula1="IF(20120921>20120629,1,-1)";
// System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
/*
5: 正式起租日:输入信息校验失败,校验规则[>=开始投放日],校验信息[2012-06-29>=20120629]
58: 保险费比例:输入信息校验失败,校验规则[>0],校验信息[0.0>0]
67: 首期收租日:输入信息校验失败,校验规则[>正式起租日],校验信息[2012-09-21>20120629]
*/
/*
System.out.println(""+formula1+"="+formulaTool.calculateXlsCell(formula1));
String formula2="IF(4981.23/6950.0<=0,0,3)";
System.out.println(""+formula2+"="+formulaTool.calculateXlsCell(formula2));
*/
//calculateXlsABS
//System.out.println("12345123434:"+runTruncat("12345123434",7));
//String formula="IF(javaound(2815262.112)!=javaound(2815262.112w),1,0)";
//String formula="ABS(12-ABS(13-20)+ABS(15)-ABS(-15))";
//System.out.println(""+formula+" "+calculateXlsABS(formula));
/*
String days[][]={
{"2007-01-02","2007-02-02"},
{"2008-01-02","2008-03-19"},
{"2008-02-02","2008-02-19"},
{"2008-03-02","2008-02-19"},
{"2007-01-02","2008-02-02"}
};
for(int x=0;x<days.length;x++){
System.out.println(days[x][0]+","+days[x][1]+"="+day360(days[x][0],days[x][1]));
}
*/
/*
double In[]={
-276000000.00
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,27816561.71
,12816561.71
};
String dates[]={
"2015-10-15"
,"2015-11-15"
,"2016-02-15"
,"2016-05-15"
,"2016-08-15"
,"2016-11-15"
,"2017-02-15"
,"2017-05-15"
,"2017-08-15"
,"2017-11-15"
,"2018-02-15"
,"2018-05-15"
,"2018-08-15"
};
int days[]=new int[dates.length];
for(int x=0;x<days.length;x++){
days[x]=pub_function.getDays(dates[0],dates[x]);
System.out.println(x+"---"+days[x]);
}
System.out.println("xirr:"+ pub_function.numFormat(formulaTool.calXirr(In,days,10,0),"0.0000000"));
*/
//System.out.println("---:"+pub_function.numFormat((formulaTool.calNPV(0.0774/12,In)),"#,##0.00"));
/// System.out.println("---:"+pub_function.numFormat((formulaTool.calIrr(In)),"0.00000000000"));
// String bdate="2015-01-28";
// String edate="2015-02-28";
// System.out.println(edate+"-"+bdate+"="+day360(bdate,edate));
}
}