A cross workbook reference contains the source workbook name enclosed in square brackets, followed by the sheet name, "!" and a cell reference or range reference. For example:
=[Calc.xlsx]Sheet1!A1
=[Calc.xlsx]Sheet1!A1:B3
=[Detail]Sheet1!A1:B3 ("Detail" is the full name of the source workbook file)
If the file or sheet name contains invalid characters, the workbook and worksheet names need to be enclosed in single quotation marks.
='[Calc (0).xlsx]Sheet1'!A1
If the source workbook path is defined, SpreadJS will add the file path in front of the square brackets. The file path can also be used to select different source files that might have the same filename.
='C:\Users\Administrator\Downloads[calc.xlsx]Sheet1'!$C$6
SpreadJS provides the getExternalReferences and updateExternalReference API to get and set the external source data of the workbook.
Here is an example of setting cross workbook formula as well as setting the external source:
Here is an example of setting cross workbook formula and updating partial of the external source:
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 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">
<!-- 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$/en/purejs/node_modules/@mescius/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$/en/purejs/node_modules/@mescius/spread-sheets-io/dist/gc.spread.sheets.io.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;
}
