통합 문서 간 참조에는 대괄호로 둘러싸인 소스 통합 문서 이름이 포함되어 있고 그 뒤에 시트 이름, "!" 및 셀 참조 또는 범위 참조가 따라옵니다. 예를 들어 다음과 같습니다.
=[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="ko-kr" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/ko/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$/ko/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$/ko/purejs/node_modules/@mescius/spread-sheets-io/dist/gc.spread.sheets.io.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.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;
}
