数组公式对于熟悉Excel的用户,是一种非常实用的高级功能。SpreadJS 能够支持数组公式。本文主要介绍在SpreadJS中使用数组公式。

1.使用setArrayFormula来使用数组公式

activeSheet.getCell(0,1).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,2).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,3).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,3).text("Result");
activeSheet.getCell(0,1).text("Column B");
activeSheet.getCell(0,2).text("Column C");
activeSheet.getCell(1,1).value(3);
activeSheet.getCell(2,1).value(1);
activeSheet.getCell(3,1).value(3);
activeSheet.getCell(4,1).value(7);
activeSheet.getCell(1,2).value(7);
activeSheet.getCell(2,2).value(7);
activeSheet.getCell(3,2).value(7);
activeSheet.getCell(4,2).value(7);
spread.canUserEditFormula(true);
activeSheet.setArrayFormula(1, 3, 4, 1, "B2:B5*C2:C5");

 

2015-09-06_121348

2.使用公式时用setArrayFormula

activeSheet.getCell(1,0).text("Quantity");
activeSheet.getCell(2,0).text("Price");
activeSheet.getCell(4,0).text("Total Value");
activeSheet.getColumn(0).width(80);
activeSheet.getCell(1,1).value(445);
activeSheet.getCell(1,2).value(100);
activeSheet.getCell(2,1).value(15);
activeSheet.getCell(2,2).value(30);
activeSheet.setArrayFormula(4, 2, 1, 1, "SUM(B2:C2*B3:C3)");

 

2015-09-06_121356

3.在使用表达式时如何使用数组公式

activeSheet.getCell(0, 1).text("Value B");
activeSheet.getCell(0, 2).text("Value C");
activeSheet.getCell(6, 0).text("Sum (B*C if B < 5)");
activeSheet.getColumn(0).width(120);
spread.canUserEditFormula(true);
activeSheet.getCell(0,1).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,2).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(0,3).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.getCell(1,1).value(4);
activeSheet.getCell(1,2).value(3);
activeSheet.getCell(2,1).value(2);
activeSheet.getCell(2,2).value(2);
activeSheet.getCell(3,1).value(5);
activeSheet.getCell(3,2).value(7);
activeSheet.getCell(4,1).value(6);
activeSheet.getCell(4,2).value(2);
activeSheet.setArrayFormula(6, 2, 1, 1, "Sum(IF(B2:B5<5,B2:B5*C2:C5))");

 

2015-09-06_121402

示例下载:

 

这就是你想要的SpreadJS,快来官网了解并下载它吧!