基本函数

在 SpreadJS 中, 你可以在一些单元格中引用并显示另外一些单元格的内容, 并使用各种函数来进行各种复杂公式的计算。

<p>SpreadJS 内置提供了丰富的函数: 包括一些经常会用到的基本函数,比如 <strong>SUM</strong> ; 以及一些更高级一些的函数,比如 <strong>MAX</strong>。 请参照以下的示例代码:</p> <pre><code class="hljs js language-js"><span class="hljs-comment">//Returns the sum of the values contained in the range A1:C5.</span> =SUM(A1:C5) <span class="hljs-comment">//Returns the largest value in the range A1:C5.</span> =MAX(A1:C5) </code></pre> <p>你可以在单元格中直接输入公式 <strong>=SUM(A1:C5)</strong> 。 也可以通过代码调用 <strong>setFormula</strong> 方法将公式应用到表单中。</p> <pre><code class="hljs js language-js"><span class="hljs-keyword">var</span> spread = GC.Spread.Sheets.findControl(<span class="hljs-built_in">document</span>.getElementById(<span class="hljs-string">'ss'</span>)); <span class="hljs-keyword">var</span> sheet = spread.getActiveSheet(); <span class="hljs-comment">// set some values to C4:C7</span> sheet.setFormula(<span class="hljs-number">8</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'=MAX(C4:C7)'</span>); </code></pre> <p><strong>注意:</strong> SpreadJS提供了下面这些Excel基础函数:</p> <table border=1 class='table-style'><tr><th class = 'table-th'>ABS</th><th class='table-th'>ACOS</th><th class='table-th'>ASIN</th><th class='table-th'>ATAN</th><th class='table-th'>ATAN2</th><th class='table-th'>COS</th></tr><tr><th class = 'table-th'>CEILING</th><th class='table-th'>ODD</th><th class='table-th'>EVEN</th><th class='table-th'>FLOOR</th><th class='table-th'>LN</th><th class='table-th'>SQRT</th></tr><tr><th class = 'table-th'>SIN</th><th class='table-th'>TAN</th><th class='table-th'>SIGN</th><th class='table-th'>GCD</th><th class='table-th'>LCM</th><th class='table-th'>PRODUCT</th></tr><tr><th class = 'table-th'>POWER</th><th class='table-th'>MOD</th><th class='table-th'>QUOTIENT</th><th class='table-th'>SUBTOTAL</th><th class='table-th'>INT</th><th class='table-th'>MROUND</th></tr><tr><th class = 'table-th'>ROUND</th><th class='table-th'>ROUNDDOWN</th><th class='table-th'>ROUNDUP</th><th class='table-th'>TRUNC</th><th class='table-th'>EXP</th><th class='table-th'>LOG</th></tr><tr><th class = 'table-th'>LOG10</th><th class='table-th'>SUM</th><th class='table-th'>SUMIF</th><th class='table-th'>SUMIFS</th><th class='table-th'>SUMPRODUCT</th><th class='table-th'>SUMSQ</th></tr><tr><th class = 'table-th'>SUMX2MY2</th><th class='table-th'>SUMX2PY2</th><th class='table-th'>SUMXMY2</th><th class='table-th'>SERIESSUM</th><th class='table-th'>PI</th><th class='table-th'>SQRTPI</th></tr><tr><th class = 'table-th'>DEGREES</th><th class='table-th'>RADIANS</th><th class='table-th'>COSH</th><th class='table-th'>ACOSH</th><th class='table-th'>SINH</th><th class='table-th'>ASINH</th></tr><tr><th class = 'table-th'>TANH</th><th class='table-th'>ATANH</th><th class='table-th'>MDETERM</th><th class='table-th'>MINVERSE</th><th class='table-th'>MMULT</th><th class='table-th'>FACT</th></tr><tr><th class = 'table-th'>FACTDOUBLE</th><th class='table-th'>MULTINOMIAL</th><th class='table-th'>RAND</th><th class='table-th'>RANDBETWEEN</th><th class='table-th'>COMBIN</th><th class='table-th'>ROMAN</th></tr><tr><th class = 'table-th'>CEILING.PRECISE</th><th class='table-th'>ISO.CEILING</th><th class='table-th'>FLOOR.PRECISE</th><th class='table-th'>MUNIT</th><th class='table-th'>AND</th><th class='table-th'>OR</th></tr><tr><th class = 'table-th'>NOT</th><th class='table-th'>IF</th><th class='table-th'>IFERROR</th><th class='table-th'>TRUE</th><th class='table-th'>FALSE</th><th class='table-th'>DATE</th></tr><tr><th class = 'table-th'>TIME</th><th class='table-th'>DATEVALUE</th><th class='table-th'>TIMEVALUE</th><th class='table-th'>NOW</th><th class='table-th'>TODAY</th><th class='table-th'>HOUR</th></tr><tr><th class = 'table-th'>MINUTE</th><th class='table-th'>SECOND</th><th class='table-th'>DAY</th><th class='table-th'>MONTH</th><th class='table-th'>YEAR</th><th class='table-th'>WEEKNUM</th></tr><tr><th class = 'table-th'>WEEKDAY</th><th class='table-th'>EDATE</th><th class='table-th'>EOMONTH</th><th class='table-th'>WORKDAY</th><th class='table-th'>WORKDAY.INTL</th><th class='table-th'>DAYS360</th></tr><tr><th class = 'table-th'>NETWORKDAYS</th><th class='table-th'>NETWORKDAYS.INTL</th><th class='table-th'>YEARFRAC</th><th class='table-th'>DATEDIF</th><th class='table-th'>CLEAN</th><th class='table-th'>TRIM</th></tr><tr><th class = 'table-th'>DOLLAR</th><th class='table-th'>FIXED</th><th class='table-th'>TEXT</th><th class='table-th'>VALUE</th><th class='table-th'>LOWER</th><th class='table-th'>UPPER</th></tr><tr><th class = 'table-th'>PROPER</th><th class='table-th'>CHAR</th><th class='table-th'>CODE</th><th class='table-th'>REPLACE</th><th class='table-th'>SUBSTITUTE</th><th class='table-th'>CONCATENATE</th></tr><tr><th class = 'table-th'>LEFT</th><th class='table-th'>MID</th><th class='table-th'>RIGHT</th><th class='table-th'>REPT</th><th class='table-th'>LEN</th><th class='table-th'>FIND</th></tr><tr><th class = 'table-th'>SEARCH</th><th class='table-th'>EXACT</th><th class='table-th'>T</th><th class='table-th'>ISERROR</th><th class='table-th'>ISERR</th><th class='table-th'>ISNA</th></tr><tr><th class = 'table-th'>ERROR.TYPE</th><th class='table-th'>ISNUMBER</th><th class='table-th'>ISEVEN</th><th class='table-th'>ISODD</th><th class='table-th'>N</th><th class='table-th'>ISBLANK</th></tr><tr><th class = 'table-th'>ISLOGICAL</th><th class='table-th'>ISTEXT</th><th class='table-th'>ISNONTEXT</th><th class='table-th'>ISREF</th><th class='table-th'>TYPE</th><th class='table-th'>NA</th></tr><tr><th class = 'table-th'>REFRESH</th><th class='table-th'>DAVERAGE</th><th class='table-th'>DCOUNT</th><th class='table-th'>DCOUNTA</th><th class='table-th'>DGET</th><th class='table-th'>DMAX</th></tr><tr><th class = 'table-th'>DMIN</th><th class='table-th'>DPRODUCT</th><th class='table-th'>DSTDEV</th><th class='table-th'>DSTDEVP</th><th class='table-th'>DSUM</th><th class='table-th'>DVAR</th></tr><tr><th class = 'table-th'>DVARP</th><th class='table-th'>BESSELI</th><th class='table-th'>BESSELJ</th><th class='table-th'>BESSELK</th><th class='table-th'>BESSELY</th><th class='table-th'>BIN2DEC</th></tr><tr><th class = 'table-th'>BIN2HEX</th><th class='table-th'>BIN2OCT</th><th class='table-th'>DEC2BIN</th><th class='table-th'>DEC2HEX</th><th class='table-th'>DEC2OCT</th><th class='table-th'>HEX2BIN</th></tr><tr><th class = 'table-th'>HEX2DEC</th><th class='table-th'>HEX2OCT</th><th class='table-th'>OCT2BIN</th><th class='table-th'>OCT2DEC</th><th class='table-th'>OCT2HEX</th><th class='table-th'>ERF</th></tr><tr><th class = 'table-th'>ERF.PRECISE</th><th class='table-th'>ERFC</th><th class='table-th'>ERFC.PRECISE</th><th class='table-th'>DELTA</th><th class='table-th'>GESTEP</th><th class='table-th'>COMPLEX</th></tr><tr><th class = 'table-th'>IMABS</th><th class='table-th'>IMAGINARY</th><th class='table-th'>IMARGUMENT</th><th class='table-th'>IMCONJUGATE</th><th class='table-th'>IMCOS</th><th class='table-th'>IMDIV</th></tr><tr><th class = 'table-th'>IMEXP</th><th class='table-th'>IMLN</th><th class='table-th'>IMLOG10</th><th class='table-th'>IMLOG2</th><th class='table-th'>IMREAL</th><th class='table-th'>IMSIN</th></tr><tr><th class = 'table-th'>IMSQRT</th><th class='table-th'>IMSUB</th><th class='table-th'>IMPOWER</th><th class='table-th'>IMPRODUCT</th><th class='table-th'>IMSUM</th><th class='table-th'>CONVERT</th></tr><tr><th class = 'table-th'>FV</th><th class='table-th'>FVSCHEDULE</th><th class='table-th'>NPV</th><th class='table-th'>PV</th><th class='table-th'>RECEIVED</th><th class='table-th'>XNPV</th></tr><tr><th class = 'table-th'>CUMIPMT</th><th class='table-th'>CUMPRINC</th><th class='table-th'>IPMT</th><th class='table-th'>ISPMT</th><th class='table-th'>PMT</th><th class='table-th'>PPMT</th></tr><tr><th class = 'table-th'>COUPDAYBS</th><th class='table-th'>COUPDAYS</th><th class='table-th'>COUPDAYSNC</th><th class='table-th'>COUPNCD</th><th class='table-th'>COUPNUM</th><th class='table-th'>COUPPCD</th></tr><tr><th class = 'table-th'>DURATION</th><th class='table-th'>MDURATION</th><th class='table-th'>NPER</th><th class='table-th'>YIELD</th><th class='table-th'>YIELDDISC</th><th class='table-th'>YIELDMAT</th></tr><tr><th class = 'table-th'>AMORDEGRC</th><th class='table-th'>AMORLINC</th><th class='table-th'>ODDFYIELD</th><th class='table-th'>ODDLYIELD</th><th class='table-th'>ODDLPRICE</th><th class='table-th'>TBILLEQ</th></tr><tr><th class = 'table-th'>TBILLYIELD</th><th class='table-th'>IRR</th><th class='table-th'>XIRR</th><th class='table-th'>RATE</th><th class='table-th'>VDB</th><th class='table-th'>ACCRINT</th></tr><tr><th class = 'table-th'>ACCRINTM</th><th class='table-th'>DISC</th><th class='table-th'>EFFECT</th><th class='table-th'>INTRATE</th><th class='table-th'>NOMINAL</th><th class='table-th'>DB</th></tr><tr><th class = 'table-th'>DDB</th><th class='table-th'>SLN</th><th class='table-th'>SYD</th><th class='table-th'>DOLLARDE</th><th class='table-th'>DOLLARFR</th><th class='table-th'>PRICE</th></tr><tr><th class = 'table-th'>PRICEDISC</th><th class='table-th'>PRICEMAT</th><th class='table-th'>ODDFPRICE</th><th class='table-th'>TBILLPRICE</th><th class='table-th'>EURO</th><th class='table-th'>EUROCONVERT</th></tr><tr><th class = 'table-th'>RRI</th><th class='table-th'>ADDRESS</th><th class='table-th'>INDEX</th><th class='table-th'>OFFSET</th><th class='table-th'>ROW</th><th class='table-th'>COLUMN</th></tr><tr><th class = 'table-th'>ROWS</th><th class='table-th'>COLUMNS</th><th class='table-th'>TRANSPOSE</th><th class='table-th'>LOOKUP</th><th class='table-th'>HLOOKUP</th><th class='table-th'>VLOOKUP</th></tr><tr><th class = 'table-th'>CHOOSE</th><th class='table-th'>MATCH</th><th class='table-th'>INDIRECT</th><th class='table-th'>TREND</th><th class='table-th'>GROWTH</th><th class='table-th'>FORECAST</th></tr><tr><th class = 'table-th'>AVERAGE</th><th class='table-th'>STDEV</th><th class='table-th'>STDEV.S</th><th class='table-th'>PERCENTILE</th><th class='table-th'>PERCENTILE.INC</th><th class='table-th'>MAX</th></tr><tr><th class = 'table-th'>MAXA</th><th class='table-th'>MIN</th><th class='table-th'>MINA</th><th class='table-th'>LARGE</th><th class='table-th'>SMALL</th><th class='table-th'>AVERAGEA</th></tr><tr><th class = 'table-th'>AVERAGEIF</th><th class='table-th'>AVERAGEIFS</th><th class='table-th'>MEDIAN</th><th class='table-th'>MODE</th><th class='table-th'>MODE.SNGL</th><th class='table-th'>GEOMEAN</th></tr><tr><th class = 'table-th'>HARMEAN</th><th class='table-th'>TRIMMEAN</th><th class='table-th'>FREQUENCY</th><th class='table-th'>RANK</th><th class='table-th'>RANK.EQ</th><th class='table-th'>KURT</th></tr><tr><th class = 'table-th'>PERCENTRANK</th><th class='table-th'>PERCENTRANK.INC</th><th class='table-th'>PERCENTRANK.EXC</th><th class='table-th'>QUARTILE</th><th class='table-th'>QUARTILE.INC</th><th class='table-th'>COUNT</th></tr><tr><th class = 'table-th'>COUNTA</th><th class='table-th'>COUNTBLANK</th><th class='table-th'>COUNTIF</th><th class='table-th'>COUNTIFS</th><th class='table-th'>AVEDEV</th><th class='table-th'>STDEVA</th></tr><tr><th class = 'table-th'>STDEVP</th><th class='table-th'>STDEV.P</th><th class='table-th'>STDEVPA</th><th class='table-th'>VAR</th><th class='table-th'>VAR.S</th><th class='table-th'>VARA</th></tr><tr><th class = 'table-th'>VARP</th><th class='table-th'>VAR.P</th><th class='table-th'>VARPA</th><th class='table-th'>COVAR</th><th class='table-th'>COVARIANCE.P</th><th class='table-th'>DEVSQ</th></tr><tr><th class = 'table-th'>CONFIDENCE</th><th class='table-th'>CONFIDENCE.NORM</th><th class='table-th'>CONFIDENCE.T</th><th class='table-th'>INTERCEPT</th><th class='table-th'>LINEST</th><th class='table-th'>SLOPE</th></tr><tr><th class = 'table-th'>LOGEST</th><th class='table-th'>STEYX</th><th class='table-th'>BETADIST</th><th class='table-th'>BETA.DIST</th><th class='table-th'>BETAINV</th><th class='table-th'>BETA.INV</th></tr><tr><th class = 'table-th'>BINOMDIST</th><th class='table-th'>BINOM.DIST</th><th class='table-th'>NEGBINOMDIST</th><th class='table-th'>NEGBINOM.DIST</th><th class='table-th'>CRITBINOM</th><th class='table-th'>BINOM.INV</th></tr><tr><th class = 'table-th'>CHIDIST</th><th class='table-th'>CHISQ.DIST.RT</th><th class='table-th'>CHISQ.DIST</th><th class='table-th'>CHIINV</th><th class='table-th'>CHISQ.INV.RT</th><th class='table-th'>CHISQ.INV</th></tr><tr><th class = 'table-th'>CHITEST</th><th class='table-th'>CHISQ.TEST</th><th class='table-th'>CORREL</th><th class='table-th'>EXPONDIST</th><th class='table-th'>EXPON.DIST</th><th class='table-th'>FDIST</th></tr><tr><th class = 'table-th'>F.DIST</th><th class='table-th'>F.DIST.RT</th><th class='table-th'>FINV</th><th class='table-th'>F.INV.RT</th><th class='table-th'>F.INV</th><th class='table-th'>FISHER</th></tr><tr><th class = 'table-th'>FISHERINV</th><th class='table-th'>FTEST</th><th class='table-th'>F.TEST</th><th class='table-th'>GAMMADIST</th><th class='table-th'>GAMMA.DIST</th><th class='table-th'>GAMMAINV</th></tr><tr><th class = 'table-th'>GAMMA.INV</th><th class='table-th'>GAMMALN</th><th class='table-th'>GAMMALN.PRECISE</th><th class='table-th'>HYPGEOMDIST</th><th class='table-th'>HYPGEOM.DIST</th><th class='table-th'>LOGNORMDIST</th></tr><tr><th class = 'table-th'>LOGNORM.DIST</th><th class='table-th'>LOGINV</th><th class='table-th'>LOGNORM.INV</th><th class='table-th'>NORMDIST</th><th class='table-th'>NORM.DIST</th><th class='table-th'>NORMINV</th></tr><tr><th class = 'table-th'>NORM.INV</th><th class='table-th'>NORMSDIST</th><th class='table-th'>NORMSINV</th><th class='table-th'>NORM.S.INV</th><th class='table-th'>NORM.S.DIST</th><th class='table-th'>PEARSON</th></tr><tr><th class = 'table-th'>RSQ</th><th class='table-th'>POISSON</th><th class='table-th'>POISSON.DIST</th><th class='table-th'>PROB</th><th class='table-th'>SKEW</th><th class='table-th'>STANDARDIZE</th></tr><tr><th class = 'table-th'>TDIST</th><th class='table-th'>T.DIST</th><th class='table-th'>T.DIST.RT</th><th class='table-th'>T.DIST.2T</th><th class='table-th'>TINV</th><th class='table-th'>T.INV.2T</th></tr><tr><th class = 'table-th'>T.INV</th><th class='table-th'>TTEST</th><th class='table-th'>T.TEST</th><th class='table-th'>WEIBULL</th><th class='table-th'>WEIBULL.DIST</th><th class='table-th'>ZTEST</th></tr><tr><th class = 'table-th'>Z.TEST</th><th class='table-th'>PERMUT</th><th class='table-th'>COVARIANCE.S</th><th class='table-th'>MODE.MULT</th><th class='table-th'>PERCENTILE.EXC</th><th class='table-th'>QUARTILE.EXC</th></tr><tr><th class = 'table-th'>RANK.AVG</th><th class = 'table-th-new'>ACOT</th><th class = 'table-th-new'>ACOTH</th><th class = 'table-th-new'>ARABIC</th><th class = 'table-th-new'>BASE</th><th class = 'table-th-new'>CEILING.MATH</th></tr><tr><th class = 'table-th-new'>COMBINA</th><th class='table-th-new'>COT</th><th class='table-th-new'>COTH</th><th class='table-th-new'>CSC</th><th class='table-th-new'>CSCH</th><th class='table-th-new'>DECIMAL</th></tr><tr><th class = 'table-th-new'>FLOOR.MATH</th><th class='table-th-new'>SEC</th><th class='table-th-new'>SECH</th><th class='table-th-new'>BINOM.DIST.RANGE</th><th class='table-th-new'>GAMMA</th><th class='table-th-new'>MAXIFS</th></tr><tr><th class = 'table-th-new'>GAUSS</th><th class='table-th-new'>MINIFS</th><th class='table-th-new'>PERMUTATIONA</th><th class='table-th-new'>PHI</th><th class='table-th-new'>SKEW.P</th><th class='table-th-new'>BAHTTEXT</th></tr><tr><th class = 'table-th-new'>CONCAT</th><th class='table-th-new'>FINDB</th><th class='table-th-new'>LEFTB</th><th class='table-th-new'>LENB</th><th class='table-th-new'>MIDB</th><th class='table-th-new'>REPLACEB</th></tr><tr><th class = 'table-th-new'>RIGHTB</th><th class='table-th-new'>SEARCHB</th><th class='table-th-new'>TEXTJOIN</th><th class='table-th-new'>UNICHAR</th><th class='table-th-new'>UNICODE</th><th class='table-th-new'>BITAND</th></tr><tr><th class = 'table-th-new'>BITLSHIFT</th><th class='table-th-new'>BITOR</th><th class='table-th-new'>BITRSHIFT</th><th class='table-th-new'>BITXOR</th><th class='table-th-new'>IMCOSH</th><th class='table-th-new'>IMCOT</th></tr><tr><th class = 'table-th-new'>IMCSC</th><th class='table-th-new'>IMCSCH</th><th class='table-th-new'>IMSEC</th><th class='table-th-new'>IMSECH</th><th class='table-th-new'>IMSINH</th><th class='table-th-new'>IMTAN</th></tr><tr><th class = 'table-th-new'>DAYS</th><th class='table-th-new'>ISOWEEKNUM</th><th class='table-th-new'>IFNA</th><th class='table-th-new'>IFS</th><th class='table-th-new'>SWITCH</th><th class='table-th-new'>XOR</th></tr><tr><th class = 'table-th-new'>PDURATION</th><th class='table-th-new'>RRI</th><th class='table-th-new'>ISFORMULA</th><th class='table-th-new'>AREAS</th><th class='table-th-new'>FORMULATEXT</th><th class='table-th-new'>HYPERLINK</th></tr><tr><th class = 'table-th-new'>ENCODEURL</th><th class='table-th-new'></th><th class='table-th-new'></th><th class='table-th-new'></th><th class='table-th-new'></th><th class='table-th-new'></th></tr></table> <p><strong>上表中红色的函数是Spread.Sheets支持的 Excel 2013~2016 新函数。</strong></p>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var gcns = GC.Spread.Sheets; var sheet = spread.getActiveSheet(); spread.suspendPaint(); var data = [ ['Math - Grade 5'], ['Assignments'], ['Student', 1, 2, 3, 4, 5, , 'Avg. Score'], ['Anna Mull', 76, 52, 91, 87, 98], ['Anna Sthesia', 95, 95, 94, 98, 95], ['Barb Ackue', 86, 83, 84, 89, 90], ['Barb Dwyer', 59, 40, 60, 20, 66], ['Barry Wine', 75, 55, 64, 76, 89], ['Bob Frapples', 91, 80, 72, 98, 95], ['Brock Lee', 86, 77, 89, 76, 70], ['Buck Kinnear', 100, 95, 94, 92, 91], ['Cliff Hanger', 97, 98, 99, 81, 89], ['Cory Ander', 53, 69, 93, 60, 95], [''], ['Average Score:'], ['Highest Score:'], ['Lowest Score:'], ['Median Score:'], ]; var formulas_r = [ ['=AVERAGE(C4:G4)'], ['=AVERAGE(C5:G5)'], ['=AVERAGE(C6:G6)'], ['=AVERAGE(C7:G7)'], ['=AVERAGE(C8:G8)'], ['=AVERAGE(C9:G9)'], ['=AVERAGE(C10:G10)'], ['=AVERAGE(C11:G11)'], ['=AVERAGE(C12:G12)'], ['=AVERAGE(C13:G13)'] ]; var formulas_b = [ ['=AVERAGE(C4:C13)', '=AVERAGE(D4:D13)', '=AVERAGE(E4:E13)', '=AVERAGE(F4:F13)', '=AVERAGE(G4:G13)'], ['=MAX(C4:C13)', '=MAX(D4:D13)', '=MAX(E4:E13)', '=MAX(F4:F13)', '=MAX(G4:G13)'], ['=MIN(C4:C13)', '=MIN(D4:D13)', '=MIN(E4:E13)', '=MIN(F4:F13)', '=MIN(G4:G13)'], ['=MEDIAN(C4:C13)', '=MEDIAN(D4:D13)', '=MEDIAN(E4:E13)', '=MEDIAN(F4:F13)', '=MEDIAN(G4:G13)'], ]; sheet.setArray(0, 1, data); sheet.setArray(3, 8, formulas_r, true); sheet.setArray(14, 2, formulas_b, true); sheet.setRowHeight(0, 40); sheet.getCell(0, 1).font('Bold 19px Arial').vAlign(gcns.VerticalAlign.center); sheet.addSpan(1, 1, 1, 8); sheet.getCell(1, 1).font('Bold 13px Arial') .hAlign(gcns.HorizontalAlign.center) .backColor('rgb(56,83,145') .foreColor('white') .vAlign(gcns.VerticalAlign.center); sheet.getRange(2, 1, 1, 8).font('Bold 13px Arial') .backColor('rgb(219,225,240)') .vAlign(gcns.VerticalAlign.center) .borderBottom(new gcns.LineBorder('black', gcns.LineStyle.thin)); sheet.getCell(2, 8).hAlign(gcns.HorizontalAlign.right).backColor('rgb(184,198,228)'); sheet.getRange(3, 1, 10, 8).font('12px Arial'); sheet.getRange(14, 1, 4, 8).backColor('rgb(230,230,230)'); sheet.getRange(14, 1, 4, 1).font('Bold 12px Arial').hAlign(gcns.HorizontalAlign.right); [110, 70, 70, 70, 70, 70, 10, 80].forEach(function (val, index) { sheet.setColumnWidth(index + 1, val); }); sheet.conditionalFormats.add3ScaleRule( gcns.ConditionalFormatting.ScaleValueType.lowestValue, null, 'rgb(231,114,111)', gcns.ConditionalFormatting.ScaleValueType.percentile, 50, 'rgb(252,252,255)', gcns.ConditionalFormatting.ScaleValueType.highestValue, null, 'rgb(122,188,129)', [new GC.Spread.Sheets.Range(3, 8, 10, 1)]); spread.resumePaint(); initExcel2010(spread) }; function initExcel2010(spread) { var spreadNS = GC.Spread.Sheets; var sheet = spread.getSheet(1); sheet.name("Excel 2010 Functions"); sheet.setColumnWidth(0, 200); sheet.setColumnWidth(1, 400); sheet.setColumnWidth(2, 80); sheet.getRange(-1, 0, -1, 1).wordWrap(true); sheet.getRange(-1, 1, -1, 1).wordWrap(true); var data = [ ["Formula", "Description", "Result"], ["=BETA.DIST(2,8,10,TRUE,1,3)", "Cumulative beta probability density function"], ["=BETA.DIST(2,8,10,FALSE,1,3)", "Beta probability density function"], ["=CEILING.PRECISE(4.3)", "Rounds 4.3 up to the nearest multiple of 1."], ["=CEILING.PRECISE(-4.3)", "Rounds -4.3 up to the nearest multiple of 1. Rounds toward 0 because the number is negative."], ["=CEILING.PRECISE(4.3, 2)", "Rounds 4.3 up to the nearest multiple of 2."], ["=CHISQ.DIST(0.5,1,TRUE)", "The chi-squared distribution for 0.5, returned as the cumulative distribution function, using 1 degree of freedom."], ["=CHISQ.DIST(2,3,FALSE)", "The chi-squared distribution for 2, returned as the probability density function, using 3 degrees of freedom."], ["=CHISQ.INV(0.93,1)", "Inverse of the left-tailed probability of the chi-squared distribution for 0.93, using 1 degree of freedom."], ["=CHISQ.INV(0.6,2)", "Inverse of the left-tailed probability of the chi-squared distribution for 0.6, using 2 degrees of freedom."], ["=CONFIDENCE.T(0.05,1,50)", "Confidence interval for the mean of a population based on a sample size of 50, with a 5% significance level and a standard deviation of 1. This is based on a Student's t-distribution."], ["=COVARIANCE.S({2,4,8},{5,8,11})", "Sample covariance for the data points entered as an array in the function."], ["=ERF.PRECISE(0.74500)", "Error function integrated between 0 and 0.74500 (0.707929)"], ["=ERFC.PRECISE(0.74500)", "Complementary ERF function of 0.74500."], ["=F.DIST(15.2069,6,4,TRUE)", "F probability using the cumulative distribution function (TRUE cumulative argument)."], ["=F.DIST(15.2069,6,4,FALSE)", "F probability using the probability density function (FALSE cumulative argument)."], ["=F.INV(0.01,6,4)", "Inverse of the F probability distribution."], ["=FLOOR.PRECISE(-3.2)", "Rounds -3.2 down to the nearest multiple of -1"], ["=FLOOR.PRECISE(3.2)", "Rounds 3.2 down to the nearest multiple of 1"], ["=FLOOR.PRECISE(3.2, 2)", "Rounds 3.2 down to the nearest multiple of 2"], ["=GAMMALN.PRECISE(4)", "Natural logarithm of the gamma function at 4"], ["=HYPGEOM.DIST(1,4,8,20,TRUE)", "Cumulative hypergeometric distribution function."], ["=HYPGEOM.DIST(1,4,8,20,FALSE)", "Probability hypergeometric distribution function."], ["=ISO.CEILING(4.3)", "Rounds 4.3 up to nearest multiple of 1"], ["=ISO.CEILING(-4.3)", "Rounds -4.3 up to nearest multiple of 1"], ["=ISO.CEILING(4.3, 2)", "Rounds 4.3 up to the nearest multiple of 2"], ["=LOGNORM.DIST(4,3.5,1.2,TRUE)", "Cumulative lognormal distribution at 4."], ["=LOGNORM.DIST(4,3.5,1.2,FALSE)", "Probability lognormal distribution at 4."], ["=NEGBINOM.DIST(10,5,0.25,TRUE)", "Cumulative negative binomial distribution."], ["=NEGBINOM.DIST(10,5,0.25,FALSE)", "Probability negative binomial distribution."], ["=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,1,31))", "Results in 22 future workdays. Subtracts 9 nonworking weekend days (5 Saturdays and 4 Sundays) from the 31 total days between the two dates. By default, Saturday and Sunday are considered non-working days."], ["=NETWORKDAYS.INTL(DATE(2006,2,28),DATE(2006,1,31))", "Results in -21, which is 21 workdays in the past."], ["=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),7,{\"2006/1/2\",\"2006/1/16\"})", "Results in 22 future workdays by sutracting 10 nonworking days (4 Fridays, 4 Saturdays, 2 Holidays) from the 32 days between Jan 1 2006 and Feb 1 2006. Uses the 7 argument for weekend, which is Friday and Saturday. There are also two holidays in this time period."], ["=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),\"0010001\",{\"2006/1/2\",\"2006/1/16\"})", "Results in 20 future workdays. Same time period as above, but with Sunday and Wednesday as weekend days."], ["=NORM.S.DIST(1.333333,TRUE)", "Normal cumulative distribution function at 1.333333."], ["=NORM.S.DIST(1.333333,FALSE)", "Normal probability distribution function at 1.333333."], ["=PERCENTRANK.EXC({1,2,3,4,6,6,7,8,9}, 7)", "Returns the rank of the value 7 from the array."], ["=PERCENTRANK.EXC({1,2,3,4,6,6,7,8,9}, 5.43)", "Returns the rank of the value 5.43 in the array."], ["=PERCENTRANK.EXC({1,2,3,4,6,6,7,8,9}, 5.43, 1)", "Returns the rank of the value 5.43 in the array, displaying only 1 significant digit in the result (the default is 3)."], ["=PERCENTILE.EXC({1,2,3,4,5,6}, 0.25)", "Interpolates when the value for the specified percentile lies between two values in the array."], ["=QUARTILE.EXC({1,2,3,4,5,6,7,8,9,10,11},1)", "Locates the position of the first quartile (3)."], ["=QUARTILE.EXC({1,2,3,4,5,6,7,8,9,10,11},3)", "Locates the position of the third quartile (9)."], ["=RANK.AVG(95, {89, 88, 92, 101, 94, 97, 95})", "Finds the rank (the position) of the value 95 in the array (descending order). In this case, 95 was the 3rd one in descending order."], ["=RANK.AVG(95, {89, 88, 92, 101, 94, 97, 95}, 1)", "Finds the rank (the position) of the value 95 in the array (ascending order). In this case, 95 was the 5th one in ascending order."], ["=T.DIST(60,1,TRUE)", "Student's left-tailed t-distribution for 60, returned as the cumulative distribution function, using 1 degree of freedom."], ["=T.DIST(8,3,FALSE)", "Student's left-tailed t-distribution for 8, returned as the probability density function, using 3 degrees of freedom."], ["=T.INV(0.05464,60)", "The t-value of the Student's t-distribution based on specified arguments."], ["=WORKDAY.INTL(DATE(2012,1,1),30,0)", "Using a 0 for the Weekend argument results in a #NUM! error."], ["=WORKDAY.INTL(DATE(2012,1,1),90,11)", "Finds the date 90 workdays from 1/1/2012, counting only Sundays as a weekend day (Weekend argument is 11)."], ["=TEXT(WORKDAY.INTL(DATE(2012,1,1),30,17), \"m/dd/yyyy\")", "Uses the TEXT function to format the resulting serial number (40944) in a \"m/dd/yyyy\" format. Finds the date 30 workdays from 1/1/2012, counting only Saturdays as a weekend day (Weekend argument is 17)."] ]; sheet.setArray(0, 0, data, false); var r, len, i; for (r = 1, len = data.length; r < len; r++) { sheet.setFormula(r, 2, data[r][0]); } var arrayFormulaData = [ ["=MODE.MULT({1,2,3,4,3,2,1,2,1,3})", "The formula must be entered as an array formula. It returns 1, 2, and 3 as the modes because they each appear 3 times. If the formula is not entered as an array formula, the single result is 1.", 3] ]; for (i = 0, len = arrayFormulaData.length; i < len; i++) { var cur = arrayFormulaData[i], rows = cur[2], cols = cur[3] || 1; sheet.addSpan(r, 0, rows, 1); sheet.setValue(r, 0, cur[0]); sheet.addSpan(r, 1, rows, 1); sheet.setValue(r, 1, cur[1]); sheet.setArrayFormula(r, 2, rows, cols, cur[0]); r += rows; } for (i = 1; i < r; i++) { sheet.autoFitRow(i); } sheet.getRange(-1, 2, -1, 1).formatter(".######"); sheet.setFormatter(48, 2, "M/d/yyyy"); var table = sheet.tables.add("FunctionTable", 0, 0, 50, 3, spreadNS.Tables.TableThemes.medium9); table.rowFilter().filterButtonVisible(false); // Array formula with merge cells, set style like above table rows var rowStyle = sheet.getActualStyle(48, 0); sheet.getRange(50, 0, 3, 3).backColor(rowStyle.backColor); sheet.getRange(49, 0, 4, 3).setBorder(rowStyle.borderBottom, { all: true }); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div></body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; border: 1px solid gray; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }