Basic Functions

window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var spreadNS = 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(spreadNS.VerticalAlign.center); sheet.addSpan(1, 1, 1, 8); sheet.getCell(1, 1).font('Bold 13px Arial') .hAlign(spreadNS.HorizontalAlign.center) .backColor('rgb(130, 188, 0)') .foreColor('white') .vAlign(spreadNS.VerticalAlign.center); sheet.getRange(2, 1, 1, 8).font('Bold 13px Arial') .backColor('rgb(244, 248, 235)') .vAlign(spreadNS.VerticalAlign.center) .borderBottom(new spreadNS.LineBorder('black', spreadNS.LineStyle.thin)); sheet.getCell(2, 8).hAlign(spreadNS.HorizontalAlign.right); 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(spreadNS.HorizontalAlign.right); [110, 70, 70, 70, 70, 70, 10, 80].forEach(function (val, index) { sheet.setColumnWidth(index + 1, val); }); sheet.conditionalFormats.add3ScaleRule( spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null, 'rgb(231,114,111)', spreadNS.ConditionalFormatting.ScaleValueType.percentile, 50, 'rgb(252,252,255)', spreadNS.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 charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.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; }