跨工作簿函数

公式可能包含引用其他工作簿中数据的引用。 使用这些公式时,SpreadJS 支持获取外部引用并为它们更新数据。

跨工作簿的引用包含用方括号内括起来的源工作簿名称,后跟工作表名称加上“!” 和单元格引用或范围引用。 例如: =[Calc.xlsx]Sheet1!A1 =[Calc.xlsx]Sheet1!A1:B3 =[Detail]Sheet1!A1:B3 ("Detail" 是源工作簿文件的全名) 如果文件或工作表名称包含无效字符,则工作簿和工作表名称需要用单引号引起来。 ='[Calc (0).xlsx]Sheet1'!A1 如果定义了源工作簿路径,SpreadJS 将在方括号前添加文件路径。 文件路径还可用于选择可能具有相同文件名的不同源文件。 ='C:\Users\Administrator\Downloads[calc.xlsx]Sheet1'!$C$6 SpreadJS 提供了 getExternalReferences 和 updateExternalReference API 来获取和设置工作簿的外部数据源。 这是设置跨工作簿公式以及设置外部源的示例:
let spreadNS = GC.Spread.Sheets; window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); addEvents(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet = spread.getActiveSheet(); let data = [ ['Math - Grade 5'], ['Assignments read from each files'], ['Student', 1, 2, 3, 4, 5, , 'Avg. Score'], ['Anna Mull'], ['Anna Sthesia'], ['Barb Ackue'], ['Barb Dwyer'], ['Barry Wine'], ['Bob Frapples'], ['Brock Lee'], ['Buck Kinnear'], ['Cliff Hanger'], ['Cory Ander'], [''], ['Average Score:'], ['Highest Score:'], ['Lowest Score:'], ['Median Score:'], ]; let 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)'] ]; let 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); for (let i = 3; i <= 12; i++) { let name = sheet.getValue(i, 1); for (let j = 2; j <= 6; j++) { sheet.setFormula(i, j, `'[${name}.xlsx]Sheet1'!A${j}`); } } spread.getExternalReferences().forEach(item => { let data = { Sheet1: [["Score:"], [parseInt(Math.random() * 50 + 50)], [parseInt(Math.random() * 50 + 50)], [parseInt(Math.random() * 50 + 50)], [parseInt(Math.random() * 50 + 50)], [parseInt(Math.random() * 50 + 50)]] }; spread.updateExternalReference(item.name, data, item.filePath); }); 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(); showLinkList(spread); } function readJSONFromFile(input, spread, callback) { var file = input.files[0]; if (file) { var fileName = file.name; var suffix = fileName.substr(fileName.lastIndexOf('.')).toLowerCase(); if (suffix === '.xlsx') { spread.import(file, function () { callback(); }, function (e) { console.log(e); }, { fileType: GC.Spread.Sheets.FileType.excel }); } else if (suffix === '.ssjson') { var reader = new FileReader(); reader.onload = function () { spread.fromJSON(JSON.parse(this.result)); callback(); }; reader.readAsText(file); } } } function addEvents(spread) { var openButton = document.getElementById('openButton'); openButton.addEventListener('click', function () { readJSONFromFile(document.getElementById("importFile"), spread, function () { showLinkList(spread); }); }); } function showLinkList(spread) { let table = document.getElementById("states-table"); while (table.rows.length > 1) { table.deleteRow(1); } spread.getExternalReferences().forEach(item => { var tr = document.createElement("tr"); var td = document.createElement("td"); td.appendChild(document.createTextNode(item.name)); tr.appendChild(td); var td = document.createElement("td"); td.appendChild(document.createTextNode(item.filePath)); tr.appendChild(td); var td = document.createElement("td"); var input = document.createElement("input"); input.type="file"; input.onchange = function (e){ updateExternalLink(e, spread) }; input.setAttribute("info", JSON.stringify(item)); td.appendChild(input); tr.appendChild(td); table.appendChild(tr); }); } function updateExternalLink(e, spread) { let item = JSON.parse(e.target.getAttribute("info")); var file = e.target.files[0]; if (file) { var fileName = file.name; var suffix = fileName.substr(fileName.lastIndexOf('.')).toLowerCase(); if (suffix === '.xlsx') { var tempWorkbook = new GC.Spread.Sheets.Workbook(); tempWorkbook.import(file, function () { spread.updateExternalReference(item.name, tempWorkbook.toJSON(), item.filePath); }, function (e) { console.log(e); }, { fileType: GC.Spread.Sheets.FileType.excel }); } else if (suffix === '.ssjson') { var reader = new FileReader(); reader.onload = function () { spread.updateExternalReference(item.name, JSON.parse(this.result), item.filePath); }; reader.readAsText(file); } } }
<!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-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <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$/spread/source/js/FileSaver.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-io/dist/gc.spread.sheets.io.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="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 class="options-container"> <h3>Import File</h3> <div class="option-row"> <label>Import a file contains cross workbook formula.</label> </div> <div class="option-row"> <input type="file" id="importFile" class="input"> <input type="button" id="openButton" value="import" class="button"> </div> <br> <h3>Update cross workbook values</h3> <div class="option-row"><label>Select files to update values</label></div> <div class="option-row"> <table id="states-table"> <tr> <td>Name</td> <td>File Path</td> <td>Update source<br>(supports .ssjson / .xlsx)</td> </tr> <tr> <td>Hover</td> <td><input type="checkbox" checked="checked" myState="hover" myDirection="row" /></td> <td><input type="checkbox" checked="checked" myState="hover" myDirection="column" /></td> </tr> </table> </div> </div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 580px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 580px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } #formula-input { width: calc(100% - 10px); margin-bottom: 6px; } .clear:after { display: block; width: 0; height: 0; visibility: hidden; content: ""; clear: both; } .button-container > input { width: calc(48%); } .float-left { float: left; } .float-right { float: right; } .option-row { font-size: 14px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } #states-table { width: 100%; border-collapse: collapse; text-align: center; } #states-table td { border: 1px solid grey; }