获取扩展单元格的值

您可以使用一些内置的API在报表工作表中获取扩展单元格的值。

在新窗口中打开演示可以打开设计器: 报表支持使用公式(R.V)获取任何扩展单元格的值。您可以使用此公式计算任何您想要的统计数据(例如YOY、MOM、百分比、累积等)。 函数 可用的函数如下: R.V(cell, [offset | context_cell | "CurrentPage"]),通过偏移或上下文获取特定扩展单元格的值。 R.A(cell, offset),绝对偏移,此函数只能用作R.V函数的参数。 R.R(cell, offset),相对偏移,此函数只能用作R.V函数的参数。 基本用法 例如,如果单元格A2绑定到年份列,B2绑定到月份列,C2绑定到销售列,则函数的行为如下: R.V(C2)将返回C2单元格的所有值作为数组,因此您可以使用SUM(R.V(C2))获取C2单元格所有值的总和。 R.V(C2, A2)将返回当前年份的所有销售值作为数组。 C2/SUM(R.V(C2, A2))将返回当前月份的销售额占当年销售额的百分比。 R.V(C2, R.A(A2, 1), R.A(B2, 5))将返回第一年的第五个月的销售额。 R.V(C2, R.A(A2, 2), R.A(B2, 2))将返回第二年的第二个月的销售额。 R.V(C2,R.R(B2,-1))将返回上个月的销售额。 从Spread JSON创建示例 此报表使用fromJSON加载预定义的模板。您可以在新窗口中打开示例,以查看设计器中的报表设置。
window.onload = async () => { const loadingTip = addLoadingTip(); const { spread, designer } = createSpreadAndDesigner(); const res = await fetch('$DEMOROOT$/zh/sample/features/report-sheet/formula/hierarchical-cell-index/get-value/spread.json'); await spread.fromJSON(await res.json()); loadingTip.remove(); if (designer) { designer.refresh(); } }; function createSpreadAndDesigner() { const demoHost = document.getElementById('demo-host'); if (window !== top) { return { spread: new GC.Spread.Sheets.Workbook(demoHost, { sheetCount: 1 }), }; } else { const designer = new GC.Spread.Sheets.Designer.Designer(demoHost, undefined, undefined, { sheetCount: 1 }); return { designer, spread: designer.getWorkbook(), }; } } function addLoadingTip() { const div = document.createElement('div'); div.style.position = 'absolute'; div.style.inset = '0'; div.style.display = 'flex'; div.style.alignItems = 'center'; div.style.justifyContent = 'center'; div.style.background = 'white'; div.style.zIndex = '100'; div.textContent = 'Loading data from server ...'; document.body.appendChild(div); return div; }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="spreadjs culture" content="zh-cn" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer/styles/gc.spread.sheets.designer.min.css"> <link rel="stylesheet" type="text/css" href="styles.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-print/dist/gc.spread.sheets.print.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-reportsheet-addon/dist/gc.spread.report.reportsheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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> const designerDependencyScripts = [ '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-barcode/dist/gc.spread.sheets.barcode.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pdf/dist/gc.spread.sheets.pdf.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-ganttsheet/dist/gc.spread.sheets.ganttsheet.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-formula-panel/dist/gc.spread.sheets.formulapanel.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-io/dist/gc.spread.sheets.io.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-excelio/dist/gc.spread.excelio.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer-resources-cn/dist/gc.spread.sheets.designer.resource.cn.min.js', '$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer/dist/gc.spread.sheets.designer.all.min.js', '$DEMOROOT$/spread/source/js/designer/license.js', ]; function appendScriptNode(src) { const script = document.createElement('script'); script.src = src; script.async = false; script.type = 'text/javascript'; document.head.appendChild(script); } if (top === window) { // not in iframe designerDependencyScripts.forEach(appendScriptNode); } </script> <script src="app.js" type="text/javascript"></script> </head> <body> <div class="sample-tutorial"> <div id="demo-host"></div> </div> </body> </html>
body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } #demo-host { width: 100%; height: 100%; overflow: hidden; float: left; }