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:
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { FormsModule } from '@angular/forms';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-io";
import './styles.css';
window.GC = GC;
let spreadNS = GC.Spread.Sheets;
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
spread: GC.Spread.Sheets.Workbook;
hostStyle = {
width: 'calc(100% - 580px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
constructor() {
}
initSpread($event: any) {
let spread = $event.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);
var openButton = document.getElementById('openButton');
openButton.addEventListener('click', function () {
readJSONFromFile(document.getElementById("importFile"), spread, function () {
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 showLinkList(spread: any) {
let table: any = 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);
}
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule, FormsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)"></gc-spread-sheets>
<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>
.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;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-io': 'npm:@mescius/spread-sheets-io/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
