方案面板

这个 demo 直接把内置的 ScenarioPanel 挂到工作簿上,让用户在面板里管理方案。

概述 这个 demo 载入带方案的工作簿后,直接创建 ScenarioPanel 并附加到当前工作簿。 实现思路 先用 JSON 还原工作簿内容。 再创建方案面板实例。 把面板绑定到 workbook,让它自己处理方案展示和编辑。 代码解析 这就是这个 demo 的核心逻辑,面板的创建和挂载都在这里完成。 运行效果 页面右侧会出现方案管理面板。 用户可以直接在面板里查看和操作方案。 工作簿内容会跟着方案选择变化。
var spread; var scenarioPanel; window.onload = function () { spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 }); initSpread(spread); }; function initSpread(workbook) { workbook.suspendPaint(); workbook.fromJSON(json); workbook.resumePaint(); scenarioPanel = new GC.Spread.Sheets.Scenarios.ScenarioPanel('scenarioPanelHost'); scenarioPanel.attach(workbook); }
<!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"> <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-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="data.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 id="scenarioPanelHost" class="scenario-panel-host"></div> </div> </body> </html>
var json = { "version": "18.0.0", "name": "", "docProps": { "docPropsApp": {}, "docPropsCore": {} }, "sheetCount": 1, "users": [ { "id": "{483FF98B-BB90-4EB4-8329-761CD05F2BAE}", "name": "Ethan(销售副总裁)", "color": "#26A69A" }, { "id": "{483FF98B-BB90-4EB4-8329-761CD56778AE}", "name": "Emma(财务总监)", "color": "#A6A634" } ], "customList": [], "defaultSheetTabStyles": {}, "builtInFileIcons": {}, "sheets": { "年度电风扇销售预测": { "name": "年度电风扇销售预测", "isSelected": true, "rowCount": 19, "columnCount": 7, "activeRow": 11, "activeCol": 4, "visible": 1, "theme": "Office", "data": { "dataTable": { "0": { "0": { "value": "电风扇年度销售预测", "style": { "autoFormatter": {}, "font": "bold 16px Arial", "fontWeight": "bold", "fontSize": "16px", "fontFamily": "Arial" } } }, "1": { "0": { "value": "前四个月为实际销量,剩余月份为全年预测输入。" } }, "2": { "0": { "value": "月份", "style": { "backColor": "#e8f4ff", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "1": { "value": "销量", "style": { "backColor": "#e8f4ff", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "2": { "value": "单价", "style": { "backColor": "#e8f4ff", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "3": { "value": "收入", "style": { "backColor": "#e8f4ff", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "4": { "value": "成本", "style": { "backColor": "#e8f4ff", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "5": { "value": "利润", "style": { "backColor": "#e8f4ff", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } } }, "3": { "0": { "value": "一月", "style": { "backColor": "#eef8f1" } }, "1": { "value": 622, "style": { "backColor": "#eef8f1", "formatter": "#,##0" } }, "2": { "value": 48, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" } }, "3": { "value": 29856, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 17416, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 12440, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "4": { "0": { "value": "二月", "style": { "backColor": "#eef8f1" } }, "1": { "value": 631, "style": { "backColor": "#eef8f1", "formatter": "#,##0" } }, "2": { "value": 48, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" } }, "3": { "value": 30288, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 17668, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 12620, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "5": { "0": { "value": "三月", "style": { "backColor": "#eef8f1" } }, "1": { "value": 713, "style": { "backColor": "#eef8f1", "formatter": "#,##0" } }, "2": { "value": 48, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" } }, "3": { "value": 34224, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 19964, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 14260, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "6": { "0": { "value": "四月", "style": { "backColor": "#eef8f1" } }, "1": { "value": 854, "style": { "backColor": "#eef8f1", "formatter": "#,##0" } }, "2": { "value": 48, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" } }, "3": { "value": 40992, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 23912, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 17080, "style": { "backColor": "#eef8f1", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "7": { "0": { "value": "五月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "formatter": "#,##0" } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "8": { "0": { "value": "六月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "formatter": "#,##0" } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "9": { "0": { "value": "七月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "#,##0", "imeMode": 1 } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "10": { "0": { "value": "八月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "#,##0", "imeMode": 1 } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "11": { "0": { "value": "九月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "#,##0", "imeMode": 1 } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "12": { "0": { "value": "十月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "#,##0", "imeMode": 1 } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "13": { "0": { "value": "十一月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "#,##0", "imeMode": 1 } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "14": { "0": { "value": "十二月", "style": { "backColor": "#fff8dc" } }, "1": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "#,##0", "imeMode": 1 } }, "2": { "style": { "backColor": "#fff8dc", "hAlign": 3, "vAlign": 0, "themeFont": "Body", "formatter": "$#,##0.00", "imeMode": 1 } }, "3": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 0 } }, "4": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 1 } }, "5": { "value": 0, "style": { "backColor": "#fff8dc", "formatter": "$#,##0.00" }, "formula": { "si": 2 } } }, "15": { "0": { "value": "年度合计", "style": { "backColor": "#dff3e3", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "1": { "value": 2820, "style": { "backColor": "#dff3e3", "font": "bold 12px Arial", "formatter": "#,##0", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" }, "formula": { "si": 3 } }, "2": { "style": { "backColor": "#dff3e3", "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "3": { "value": 135360, "style": { "backColor": "#dff3e3", "font": "bold 12px Arial", "formatter": "$#,##0.00", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" }, "formula": { "si": 3 } }, "4": { "value": 78960, "style": { "backColor": "#dff3e3", "font": "bold 12px Arial", "formatter": "$#,##0.00", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" }, "formula": { "si": 3 } }, "5": { "value": 56400, "style": { "backColor": "#dff3e3", "font": "bold 12px Arial", "formatter": "$#,##0.00", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" }, "formula": { "si": 3 } } }, "17": { "0": { "value": "方案覆盖单元格", "style": { "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } }, "1": { "value": "B8:C15", "style": { "font": "bold 12px Arial", "fontWeight": "bold", "fontSize": "12px", "fontFamily": "Arial" } } } }, "defaultDataNode": { "style": { "themeFont": "Body" } } }, "rowHeaderData": { "defaultDataNode": { "style": { "themeFont": "Body" } } }, "colHeaderData": { "defaultDataNode": { "style": { "themeFont": "Body" } } }, "columns": [ { "size": 143 }, { "size": 88 }, { "size": 92 }, { "size": 116 }, { "size": 108 }, { "size": 116 } ], "defaultData": { "7": {}, "8": {}, "9": {}, "10": {}, "11": {}, "12": {}, "13": {}, "14": {} }, "leftCellIndex": 0, "topCellIndex": 0, "selections": { "0": { "row": 11, "col": 4, "rowCount": 1, "colCount": 1 }, "length": 1 }, "rowOutlines": { "items": [] }, "columnOutlines": { "items": [] }, "cellStates": {}, "states": {}, "outlineColumnOptions": {}, "autoMergeRangeInfos": [], "sharedFormulas": { "0": { "formula": "B4*C4", "baseRow": 3, "baseColumn": 3, "lastRow": 14, "lastColumn": 3 }, "1": { "formula": "B4*28", "baseRow": 3, "baseColumn": 4, "lastRow": 14, "lastColumn": 4 }, "2": { "formula": "D4-E4", "baseRow": 3, "baseColumn": 5, "lastRow": 14, "lastColumn": 5 }, "3": { "formula": "SUM(B4:B15)", "baseRow": 15, "baseColumn": 1, "lastRow": 15, "lastColumn": 5 }, "nextIndex": 4 }, "printInfo": { "paperSize": { "width": 850, "height": 1100, "kind": 1 } }, "gridline": { "showVerticalGridline": false }, "index": 0, "order": 0 } }, "sheetTabCount": 0, "namedPatterns": {}, "scenarios": { "盈亏平衡": { "name": "盈亏平衡", "userId": "{483FF98B-BB90-4EB4-8329-761CD56778AE}", "overrides": [ { "sheetName": "年度电风扇销售预测", "dataTable": { "7": { "1": { "value": 900 }, "2": { "value": 48 } }, "8": { "1": { "value": 1000 }, "2": { "value": 48 } }, "9": { "1": { "value": 1200 }, "2": { "value": 48 } }, "10": { "1": { "value": 1200 }, "2": { "value": 48 } }, "11": { "1": { "value": 1000 }, "2": { "value": 48 } }, "12": { "1": { "value": 800 }, "2": { "value": 48 } }, "13": { "1": { "value": 600 }, "2": { "value": 48 } }, "14": { "1": { "value": 600 }, "2": { "value": 48 } } } } ] }, "保守预测": { "name": "保守预测", "userId": "{483FF98B-BB90-4EB4-8329-761CD05F2BAE}", "overrides": [ { "sheetName": "年度电风扇销售预测", "dataTable": { "7": { "1": { "value": 960 }, "2": { "value": 48 } }, "8": { "1": { "value": 1100 }, "2": { "value": 48 } }, "9": { "1": { "value": 1800 }, "2": { "value": 48 } }, "10": { "1": { "value": 1800 }, "2": { "value": 48 } }, "11": { "1": { "value": 1200 }, "2": { "value": 48 } }, "12": { "1": { "value": 800 }, "2": { "value": 48 } }, "13": { "1": { "value": 600 }, "2": { "value": 48 } }, "14": { "1": { "value": 600 }, "2": { "value": 48 } } } } ] }, "乐观预测": { "name": "乐观预测", "userId": "{483FF98B-BB90-4EB4-8329-761CD05F2BAE}", "overrides": [ { "sheetName": "年度电风扇销售预测", "dataTable": { "7": { "1": { "value": 1000 }, "2": { "value": 48 } }, "8": { "1": { "value": 1400 }, "2": { "value": 55 } }, "9": { "1": { "value": 2300 }, "2": { "value": 55 } }, "10": { "1": { "value": 2300 }, "2": { "value": 55 } }, "11": { "1": { "value": 1400 }, "2": { "value": 48 } }, "12": { "1": { "value": 1000 }, "2": { "value": 48 } }, "13": { "1": { "value": 700 }, "2": { "value": 48 } }, "14": { "1": { "value": 700 }, "2": { "value": 48 } } } } ] } }, "scenarioAdditionalInfo": { "appliedScenarios": {}, "baseValues": { "年度电风扇销售预测": {} } }, "pivotCaches": {} };
html, body { height: 100%; margin: 0; overflow: hidden; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; font-family: Arial, sans-serif; } .sample-spreadsheets { width: calc(100% - 360px); height: 100%; overflow: hidden; float: left; } .scenario-panel-host { float: right; width: 360px; height: 100%; box-sizing: border-box; padding: 6px; border-left: 1px solid #ddd; background: #fafafa; overflow: hidden; }