跨工作簿函数 | 功能例子 | SpreadJS JavaScript 学习指南

跨工作簿的引用包含用方括号内括起来的源工作簿名称，后跟工作表名称加上“!” 和单元格引用或范围引用。例如： =[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;
}