公式函数

<!DOCTYPE html>

<head>

<title>SpreadJS Demo : 函数</title>

<!-- Local Reference

-->

<style>

body {

background: rgb(250, 250, 250);

color: #333;

}

#ss {

border: 1px #ccc solid;

}

.container {

width: 95%;

background: rgb(250, 250, 250);

margin: 0 auto;

height: 600px;

}

.full-height {

height: 100%;

}

.left{

height: 100%;

overflow: auto;

}

</style>

</head>

<body>

<h3>SpreadJS Demo : 函数</h3>

</div>

<h5>

<li class="inline-block mr1"><a class="btn" href="https://www.grapecity.com.cn/developer/spreadjs "

target="_blank ">产品官网</a></li>

<li class="inline-block mr1"><a class="" href="https://gcdn.grapecity.com.cn/showforum-185-1.html "

target="_blank ">论坛求助</a></li>

<li class="inline-block mr1"><a class="" href="https://demo.grapecity.com.cn/SpreadJS/TutorialSample/#/samples "

target="_blank ">更多示例</a></li>

</ul>

</h5>

</div>

<h4 class="mb0">INDIRECT</h4>

<h4 class="mb0">函数使用通配符</h4>

<h4 class="mb0">自定义函数</h4>

</div>

</div>

</body>

$(document).ready(function () {

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));

var sheet = spread.getActiveSheet();

/*----------------------------基本函数-----------------------*/

sheet.suspendPaint();

sheet.setValue(2,1,'姓名');

sheet.setValue(3,1,'丁玉琴');

sheet.setValue(4,1,'杨国');

sheet.setValue(5,1,'董超杨');

sheet.setValue(6,1,'杨猫猫');

sheet.setValue(7,1,'杨*猫');

sheet.setValue(2,2,'余额');

sheet.setValue(3,2,342);

sheet.setValue(4,2,3);

sheet.setValue(5,2,5654);

sheet.setValue(6,2,3455);

sheet.setValue(7,2,2);

sheet.setValue(9,1,'平均：');

sheet.setValue(10,1,'合计：');

sheet.setValue(11,1,'最大值：');

sheet.setValue(12,1,'最小值：');

sheet.setValue(13,1,'名字包含杨的：');

sheet.setValue(14,1,'姓杨的：');

sheet.setValue(15,1,'名字包含杨*的：');

sheet.setFormula(9,2,'=AVERAGE(C4:C8)');

sheet.setFormula(10,2,'=SUM(C4:C8)');

sheet.setFormula(11,2,'=MAX(C4:C8)');

sheet.setFormula(12,2,'=MIN(C4:C8)');

sheet.setFormula(13,2, 'COUNTIF(B4:B8,"*杨*")');

sheet.setFormula(14,2, 'COUNTIF(B4:B8,"杨?")');

sheet.setFormula(15,2, 'COUNTIF(B4:B8,"杨~**")');

sheet.setColumnWidth(1,100)

/*--------------------------INDIRECT函数----------------------*/

sheet.setValue(1,4,234);

sheet.setValue(2,4,'E2');

sheet.setValue(3,4,'B4');

sheet.setValue(4,4,23423);

sheet.setValue(5,5,'INDIRECT("E1")=');

sheet.setValue(6,5,'INDIRECT("B3")=');

sheet.setValue(7,5,'INDIRECT("E"&(1+2))=');

sheet.setValue(8,5,'INDIRECT(E4)=');

sheet.setValue(9,5,'INDIRECT("Sheet2!B3")');

sheet.setColumnWidth(5,150);

sheet.setFormula(5,6,'=INDIRECT("a")');

sheet.setFormula(6,6,'=INDIRECT("B3")');

sheet.setFormula(7,6,'=INDIRECT("E"&(1+2))');

sheet.setFormula(8,6,'=INDIRECT(E4)');

sheet.setFormula(9,6,'=INDIRECT("Sheet2!B3")');

sheet.resumePaint();

/*-----------------------------自定义函数-------------------*/

let sheet2 = new GC.Spread.Sheets.Worksheet();

spread.addSheet(1,sheet2)

sheet2.setArray(1, 1, [["序号","底边长","高","面积"],

[1,4,5],[2,3,4],[3,1,44],[4,8,3],[5,4,10],[6,7,10]]);

sheet2.addSpan(0,1,1,4);

sheet2.setValue(0,1,"计算三角形面积");

sheet2.getRange(0,1,1,1).hAlign(GC.Spread.Sheets.HorizontalAlign.center);

sheet2.setFormula(2,4,'=(C3*D3)/2');

sheet2.setValue(2,0,'使用普通公式:');

sheet2.setValue(3,0,'使用自定义函数:');

sheet2.setValue(7,0,'异步函数:');

sheet2.setValue(8,0,'当前时间:');

sheet2.setColumnWidth(0,120);

function calcuArea(){

this.name = "area";

this.maxArgs =2;

this.minArgs =2;

}

calcuArea.prototype = new GC.Spread.CalcEngine.Functions.Function();

calcuArea.prototype.evaluate = function (arg1,arg2){

if(arguments.length ==2 && !isNaN(parseInt(arg1)) && !isNaN(parseInt(arg2))){

return (arg1*arg2)/2;

}

return "#value"

};

var area = new calcuArea();

sheet2.addCustomFunction(area);

sheet2.removeCustomFunction('area');

//sheet2.clearCustomFunctions();

sheet2.setFormula(3,4,"=area(C4,D4)");

/*-----------------------数组公式----------------------*/

sheet2.setValue(4,0,'使用数组公式:');

sheet2.addSpan(4,0,3,1);

sheet2.setArrayFormula(4,4,3,1,"=(C5:C7*D5:D7)/2");

/*----------------------异步函数---------------------*/

var asyncSum = function (){

this.name = "asyncArea";

this.maxArgs =2;

this.minArgs =2;

};

asyncSum.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction();

asyncSum.prototype.defaultValue = function(){

return "计算中...";

};

asyncSum.prototype.evaluateAsync = function(context){

var args = arguments;

var result = 0;

setTimeout(function(){

result = (args[1]*args[2])/2;

context.setAsyncResult(result);

},5000);

};

var asyncArea = new asyncSum();

sheet2.addCustomFunction(asyncArea);

sheet2.setFormula(7,4,"=asyncArea(C8,D8)");

// 异步函数获取时间

var asyncTime = function () {

this.name ="asyncTime";

this.maxArgs =2;

this.minArgs =0;

};

asyncTime.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction();

asyncTime.prototype.evaluateAsync = function(context){

var args = arguments;

var time = new Date().toString();

context.setAsyncResult(time);

};

var asyncTime = new asyncTime();

sheet2.addCustomFunction(asyncTime);

setInterval(function(){

sheet2.setFormula(8,1,"=asyncTime()");

},1000);

});

</script>

</html>

公式函数

相关阅读更多精彩内容

友情链接更多精彩内容