parser.js

在线ExeclCopyright (c) iRows.com. All rights reserved. Do not copy

if(J7)
minParms=0
if(P.H3(token,parms,errors,minParms,-1,'T')){
for(var i=0;i<parms.length;i++){
var p=parms[i]
c+=p.value
}
token.value=c
token.valueType='text'
}}
else
if(func=='and'||func=='or'){
var count=0
var J8=true
var J9=false
if(P.H3(token,parms,errors,1,-1,'*')){
for(var i=0;i<parms.length;i++){
var p=parms[i]
if(p.valueType=='number'||p.valueType=='boolean'){
count++
var b=p.value>0
J8=J8&&b
J9=J9||b
if((func=='and'&&!J8)||(func=="or"&&J9))
break
}}
if(count==0)
P.H9(errors,'value',"No numeric or boolean pareameters for function ["+func+"]",token.start,func.length)
else{
if(func=='and')token.value=J8?1:0
else
if(func=='or')token.value=J9?1:0
}
token.valueType='boolean'
}}
else
if(func=='pmt'){
if(P.H3(token,parms,errors,3,3,'N')){
var gr=1+parms[0].value
var perd=parms[1].value
var vl=parms[2].value
var tp=vl * Math.pow(gr,perd)
var st=0
var ft=1
for(var p=0;p<perd;p++){
st+=ft
ft *=gr
}
v=-tp / st
token.value=v
token.valueType='number'
}}
else
if(func=="pi"){
if(P.H3(token,parms,errors,0,0,'N')){
token.value=Math.PI
token.valueType='number'
}}
else
if(func=="power"){
if(P.H3(token,parms,errors,2,2,'N')){
token.value=Math.pow(parms[0].value,parms[1].value)
token.valueType='number'
}}
else
if(func=="len"||func=="lower"||func=="upper"||func=="trim"){
if(P.H3(token,parms,errors,1,1,'T')){
if(func=="len"){
token.value=parms[0].value.length
token.valueType="number"
}
else
if(func=="lower"){
token.value=parms[0].value.toLowerCase()
token.valueType="text"
}
else
if(func=="upper"){
token.value=parms[0].value.toUpperCase()
token.valueType="text"
}
else
if(func=="trim"){
token.value=FP_trim(parms[0].value)
token.valueType="text"
}}}
else
if(func=="stockprice"||func=="stpr"){
if(P.H3(token,parms,errors,1,1,"T")){
var code=parms[0].value
var dynid='stockprice("'+code.toLowerCase()+'")'
P.getDynVal(dynid,root,token,errors)
}}
else
P.H9(errors,'syntax',"Un-supported function ["+func+"]",token.start,func.length)
if(token.valueType=='number'){
if(isNaN(token.value)||token.value=="Infinity")
P.H9(errors,'value',"Function result for given parameters is undefined",token.start,func.length)
}}
P.J5=function(root,token,errors){
var parms=token.parms
var ok=true
if(parms.length<2||parms.length>3){
P.H9(errors,"value","Function [if] requires 2 or 3 parameters",token.start,token.callName.length)
ok=false
}
if(ok&&(parms[0].type=="range"||parms[1].type=="range"||(parms.length==3&&parms[2].type=="range"))){
P.H9(errors,"value","Function [if] can not accept range parameters",token.start,token.callName.length)
ok=false
}
var vc=false
if(ok){
var cond=parms[0]
if(cond.op!=null&&cond.op!=0)
P.J4(root,cond,errors)
if(errors.length==0){
if(cond.valueType=="pending"){
token.valueType="pending"
return
}
else
if(cond.valueType=="null")
vc=false
else
if(cond.valueType=="number"||cond.valueType=="boolean")
vc=cond.value!=0
else
if(cond.valueType=="text"){
P.H9(errors,"value","Condition ["+cond.value+"] is invalid for function [if]. Expecting a boolean value (true or false)",token.start,token.callName.length)
ok=false
}}}
if(ok){
if(vc){
var res=parms[1]
if(res.op!=null&&res.op!=0)
P.J4(root,res,errors)
if(res.valueType=="pending"){
token.valueType="pending"
return
}
token.value=res.value
token.valueType=res.valueType
}
else{
if(parms.length==3){
var res=parms[2]
if(res.op!=null&&res.op!=0)
P.J4(root,res,errors)
if(res.valueType=="pending"){
token.valueType="pending"
return
}
token.value=res.value
token.valueType=res.valueType
}
else{
token.value=0
token.valueType="boolean"
}}}
if(errors.length>0){
token.value=""
token.valueType="null"
}}
P.J6=function(root,token,errors){
var parms=token.parms
if(P.H3(token,parms,errors,2,token.callName=="sumif"?3:2,'R*R')){
var cpType=parms[1].valueType
var cpValue=parms[1].value
var cpCond=OPC_E
if(cpType=="text"&&cpValue.length>1){
if(cpValue.charAt(0)=='<'){
if(cpValue.length>2&&cpValue.charAt(1)=='='){
cpCond=OPC_LE
cpValue=cpValue.substring(2)
}
else
if(cpValue.length>2&&cpValue.charAt(1)=='>'){
cpCond=OPC_NE
cpValue=cpValue.substring(2)
}
else{
cpCond=OPC_L
cpValue=cpValue.substring(1)
}}
else
if(cpValue.charAt(0)=='>'){
if(cpValue.length>2&&cpValue.charAt(1)=='='){
cpCond=OPC_GE
cpValue=cpValue.substring(2)
}
else{
cpCond=OPC_G
cpValue=cpValue.substring(1)
}}
else
if(cpValue.charAt(0)=='='){
cpValue=cpValue.substring(1)
}}
if(cpValue=="true"||cpValue=="false"){
cpType="boolean"
cpValue=cpValue=="true"?1:0
}
else
if(cpType=="text"){
var pn=G3(cpValue)
if(pn!=null){
cpType="number"
cpValue=pn.value
}}
var res=0
var dr=0
var dc=0
if(parms.length==3){
dr=parms[2].startRow-parms[0].startRow
dc=parms[2].startCol-parms[0].startCol
if(parms[0].endRow-parms[0].startRow!=parms[2].endRow-parms[2].startRow||
parms[0].endCol-parms[0].startCol!=parms[2].endCol-parms[2].startCol)
P.H9(errors,"syntax","Sum range (paremeter 3) for [sumif] function must have the same size and the range (parameter 1)",token.start,token.callName.length)
}
var isCount=(token.callName=="countif")
for(var r=parms[0].startRow;r<=parms[0].endRow;r++){
for(var c=parms[0].startCol;c<=parms[0].endCol;c++){
var cell=P.M.getCell(r,c)
if(cell!=null){
var cv=H7(cell)
var match=false
if(cv.valueType==cpType){
if(cpCond==OPC_E)match=cv.value==cpValue
else
if(cpCond==OPC_NE)match=cv.value!=cpValue
else
if(cpCond==OPC_L)match=cv.value<cpValue
else
if(cpCond==OPC_LE)match=cv.value<=cpValue
else
if(cpCond==OPC_G)match=cv.value>cpValue
else
if(cpCond==OPC_GE)match=cv.value>=cpValue
}
if(match){
if(isCount)
res++
else{
if(dr!=0||dc!=0)
cell=P.M.getCell(r+dr,c+dc)
if(cell!=null&&cell.type=="number")
res+=cell.value
}}}}}
token.value=res
token.valueType="number"
}}
P.parseBinary=function(token,errors){
var r=0
var v=token.text
for(var i=2;i<v.length;i++){
r *=2
if(v.charAt(i)=='1')
r++
else
if(v.charAt(i)!='0'){
P.H9(errors,'syntax',"Binary values can have only digits 0 or 1",token.start+i,1)
return 0
}}
return r
}
P.adjustReferencesCopy=function(formula,dRow,dCol){
var res=''
var te=P.tokenize(formula)
if(te.errors.length>0)
return formula
var tokens=te.tokens
for(var i=0;i<tokens.length;i++){
var token=tokens[i]
if(token.type=='name'){
var cellRef=H6(token.text)
if(cellRef){
var r=cellRef.row
var c=cellRef.col
if(cellRef.fixedCol)
res+='$'
else
c=Math.max(1,c+dCol)
res+=FP_colId(c)
if(cellRef.fixedRow)
res+='$'
else
r=Math.max(1,r+dRow)
res+=r
}
else
res+=token.text
}
else{
if(token.type=='text')
res+='"'+token.text+'"'
else
res+=token.text
}}
return res
}
P.adjustReferencesMove=function(formula,fr,fc,tr,tc,dr,dc){
var changed=false
var res=''
var te=P.tokenize(formula)
if(te.errors.length>0)
return formula
var tokens=te.tokens
for(var i=0;i<tokens.length;i++){
var token=tokens[i]
if(token.type=='name'){
var cellRef=H6(token.text)
if(cellRef){
var r=cellRef.row
var c=cellRef.col
if(r>=fr&&r<=tr&&c>=fc&&c<=tc){
changed=true
if(cellRef.fixedCol)
res+='$'
res+=FP_colId(c+dc)
if(cellRef.fixedRow)
res+='$'
res+=(r+dr)
}
else
if(r>=fr+dr&&r<=tr+dr&&c>=fc+dc&&c<=tc+dc){
changed=true
res+="_ref"
}
else
res+=token.text
}
else
res+=token.text
}
else{
if(token.type=='text')
res+='"'+token.text+'"'
else
res+=token.text
}}
return changed?res:null
}
return P
}
function J1(value){
var sv=""+value
if(sv.indexOf('.')<0)
return value
var s=sv.substring(sv.indexOf('.'))
var l=s.length
if(l<10)
return value
var l5=s.substring(l-5)
var l5m1=l5.substring(0,4)
if(l5m1=='9999'){
var i9=s.length-3
while(s.charAt(i9-1)=='9')
i9--
s=s.substring(0,i9+1)
var d='.000000000000000000000000000000000'.substring(0,s.length-1)+'1'
var w=sv.substring(0,sv.indexOf('.'))
if(w=='')
w='0'
var op='+'
if(value<0)
op='-'
value=eval(w+op+'('+s+'+'+d+')')
}
else
if(l5m1=='0000'){
value=eval(sv.substring(0,sv.length-4))
}
return value
}
function H6(name){
var fixedRow=false
var fixedCol=false
var v=name.toUpperCase()
if(v.length>0&&v.charAt(0)=='$'){
fixedCol=true
v=v.substring(1)
}
if(v.length==0)
return null
var c1=v.charAt(0)
var ic1=FP_COL_IDS.indexOf(c1)
if(ic1<0)
return null
v=v.substring(1)
if(v.length==0)
return null
var c2=v.charAt(0)
var ic2=FP_COL_IDS.indexOf(c2)
if(ic2>=0)
v=v.substring(1)
var col=ic1+1
if(ic2>=0)
col=1+26+(ic1 * 26)+ic2
if(v.length>0&&v.charAt(0)=='$'){
fixedRow=true
v=v.substring(1)
}
for(var i=0;i<v.length;i++){
if("0123456789".indexOf(v.charAt(i))<0)
return null
}
if(v.length==0)
return null
var row=parseInt(v,10)
if(isNaN(row)||row==0)
return null
var res={row:row,col:col,fixedRow:fixedRow,fixedCol:fixedCol}
return res
}
function H7(cell){
value=cell.value
valueType=cell.type
if(cell.formulaErrorType){
value=''
valueType='error'
}
if(valueType=='boolean')
value=value=='true'?1:0
return{value:value,valueType:valueType,pattern:cell.pattern}}
function H8(tokens){
var res
for(var i=0;i<tokens.length;i++){
if(tokens[i].cellReference){
if(!res)res=[]
res[res.length]=tokens[i].cellReference
}}
return res
}
function FP_colId(c){
var colId
var cind=c-1
if(cind<FP_COL_IDS.length)
colId=FP_COL_IDS.charAt(cind)
else{
var l1=Math.floor(cind / FP_COL_IDS.length)-1
var l2=cind % FP_COL_IDS.length
colId=FP_COL_IDS.charAt(l1)+FP_COL_IDS.charAt(l2)
}
return colId
}
function FP_cellId(r,c){
return FP_colId(c)+r
}
function FP_trim(text){
var s=0
while(s<text.length&&text.charAt(s)==' ')
s++
var e=text.length-1
while(e>=0&&text.charAt(e)==' ')
e--
if(s>e)
return""
else
return text.substring(s,e+1)
}
function G3(text){
if(text.length==0)
return null
var t=text.toLowerCase()
if(t.length>2&&t.substring(0,2)=='0x'){
for(var i=2;i<t.length;i++)
if("0123456789abcdef".indexOf(t.charAt(i))<0)
return null
return{value:parseInt(t,16),pattern:'n:hex'}}
if(t.length>2&&t.substring(0,2)=='0b'){
var v=0
for(var i=2;i<t.length;i++){
if(t.charAt(i)=='0')
v *=2
else
if(t.charAt(i)=='1')
v=v*2+1
else
return null
}
return{value:v,pattern:'n:bin'}}
var symbol=''
if('$\u20ac\u20a4\u20aa\u00a5'.indexOf(t.charAt(0))>=0){
symbol=t.charAt(0)
t=t.substring(1)
if(t.length==0)
return null
}
if(symbol==''&&t.charAt(t.length-1)=='%'){
symbol='%'
t=t.substring(0,t.length-1)
if(t.length==0)
return null
}
if(t.charAt(0)=='+'){
t=t.substring(1)
if(t.length==0)
return null
}
var neg=false
if(t.charAt(0)=='-'){
neg=true
t=t.substring(1)
}
else
if(t.charAt(0)=='('&&t.charAt(t.length-1)==')'){
neg=true
t=t.substring(1,t.length-1)
if(t.length==0||t.charAt(0)=='-')
return null
}
var ct=''
for(var i=0;i<t.length;i++){
var c=t.charAt(i)
if("0123456789".indexOf(c)>=0)
ct+=c
else
if(c=='.'){
if(ct.indexOf('.')>=0||ct.indexOf('e')>=0)
return null
ct+=c
}
else
if(c==','){
}
else
if(c=='e'){
if(ct.indexOf('e')>=0||ct=='')
return null
ct+='e'
}
else
if("+-".indexOf(c)>=0){
if(ct.length==0||ct.charAt(ct.length-1)!='e')
return null
ct+=c
}
else
return null
}
if(ct==''||ct=='e'||ct=='.')
return null
if(t.indexOf(',')==0)
return null
var v
var p
if(ct.charAt(ct.length-1)=='e')
return null
if(ct.indexOf('.')>=0||ct.indexOf('e')>=0)
v=parseFloat(ct)
else
v=parseInt(ct,10)
if(neg)
v=-v
if(t.indexOf(',')>0||symbol!=''){
if(symbol=='%')
v=v / 100
p='n:'
p+=t.indexOf(',')>0?'y':'n'
var dp=0
if(ct.indexOf('.')>=0){
dp=ct.length-ct.indexOf('.')-1
}
if(dp<9)
p+='0'
p+=dp
p+=(symbol==''?'-':symbol)
p+='-'
}
var res={value:v,pattern:p}
return res
}