기본 데이터 범위 | 기능별학습

데이터 범위 관리자 다음과 같은 간단한 절차를 따라 데이터 범위를 이용할 수 있습니다. 사용자 정의 데이터 공급자 인스턴스를 만듭니다(코어). 시트에서 대상 범위를 선택합니다. 시트의 데이터 범위 관리자를 사용하여 데이터 범위를 추가합니다. 데이터 범위 이름으로 데이터 범위를 제거합니다. 시트의 모든 데이터 범위를 지웁니다. 데이터 범위 이름으로 데이터 범위를 가져옵니다. 시트의 모든 데이터 범위를 가져옵니다. 데이터 범위 데이터 범위 관리자 또는 데이터 공급자로부터 데이터 범위를 가져올 수 있습니다. 데이터 소스를 변경한 후에는 다음과 같은 작업을 수행해야 할 수 있습니다. 영역 자체 다시 칠하기 크기 변경 위치 변경 데이터 범위 옵션 데이터 범위를 맨 위의 행으로 만들어서 시트에서 보이는 첫 번째 행에 고정할 수 있으며, 스티커 행 수를 정의하고 변경할 수 있습니다. 데이터 공급자 데이터 공급자는 개발자가 생성한 인스턴스이며, GC.Spread.Sheets.DataRange.IDataProvider에서 API를 구현해야 합니다.

window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
initSpread(spread);
initSplitView(spread);
};
class CustomDataProvider {
host;
data = [];
host;
autoResize = true;
topRow = 0;
success = true;
_columns = [];
_schema;
constructor (data) {
this.data = data;
this.typeName = "CustomDataProvider";
this._init();
}
getActualColumnCount () {
return this._columns.length;
}
_init () {
if (!this.data || !Array.isArray(this.data) || this.data.length === 0) {
this.success = false;
return;
}
this._schema = this._generateSchema();
if (this.data.length > 1) {
for (let i = 1; i < this.data.length; i++) {
this._recheckSchema(this._schema, this.data[i]);
}
}
if (!this._schema) {
this.success = false;
return;
}
this._columns = this._generateColumns();
}
_dataAnalysis (schema, columns, parentKeys) {
columns = columns ? columns : [];
parentKeys = parentKeys ? parentKeys : [];
for (let key in schema) {
if (schema[key] !== null && typeof schema[key] === "object") {
this._dataAnalysis(schema[key], columns, parentKeys.concat([key]));
} else {
const fieldPath = parentKeys.concat([key]);
const column = { field: fieldPath, _fieldPath: fieldPath.join() };
columns.push(column);
}
}
}
_generateColumns () {
const columns = [];
const schema = this._schema;
this._dataAnalysis(schema, columns);
return columns;
}
_generateSchema (data) {
data = data || this.data[0];
var schema = JSON.parse(JSON.stringify(data));
for (let key in schema) {
if (schema[key] !== null && typeof schema[key] === "object") {
schema[key] = this._generateSchema(schema[key]);
} else {
schema[key] = null;
}
}
return schema;
}
_recheckSchema (schema, item) {
for (let key in schema) {
if (Array.isArray(schema[key]) || Array.isArray(item[key])) {
var schemaLength = schema[key] ? schema[key].length : 0;
var itemLength = item[key] ? item[key].length : 0;
if (itemLength > schemaLength) {
schema[key] = new Array(itemLength).fill(null);
}
} else if (schema[key] !== null && typeof schema[key] === "object") {
this._recheckSchema(schema[key], item[key]);
}
}
}
_getColumnInfo (col) {
let column = Object.assign({}, this._columns[col]);
return column;
}
_getHeaderRowCount () {
const columns = this._columns;
let count = 0;
for (const column of columns) {
count = Math.max(count, column.field.length);
}
return count;
}
_getSpansFromHeader () {
const verticalSpans = this._getVerticalSpans();
const horizontalSpans = this._getHorizontalSpans();
return verticalSpans.concat(horizontalSpans);
}
_getVerticalSpans () {
const spans = [];
const columns = this._columns;
const headerRowCount = this._getHeaderRowCount();
for (let col = 0; col < columns.length; col++) {
const column = columns[col];
const field = column.field;
if (field.length < headerRowCount) {
const row = field.length - 1;
spans.push(new GC.Spread.Sheets.Range(row, col, headerRowCount - row, 1));
}
}
return spans;
}
_getHorizontalSpans () {
const spans = [];
const columns = this._columns;
const headerRowCount = this._getHeaderRowCount();
let currentText;
let startCol;
for (let row = 0; row < headerRowCount; row++) {
currentText = undefined;
startCol = undefined;
for (let col = 0; col < columns.length; col++) {
const column = columns[col];
const field = column.field;
const text = field[row];
if (text) {
if (!currentText) {
currentText = text;
startCol = col;
}
if (text === currentText) {
if (col === columns.length - 1 && col > startCol - 1) {
spans.push(new GC.Spread.Sheets.Range(row, startCol, 1, col - startCol + 1));
}
} else {
if (startCol + 1 === col) {
currentText = text;
startCol = col;
} else {
spans.push(new GC.Spread.Sheets.Range(row, startCol, 1, col - startCol));
currentText = text;
startCol = col;
}
}
} else {
currentText = null;
startCol = null;
}
}
}
return spans;
}
_isHeaderArea (row) {
return row < this._getHeaderRowCount();
}
_getColumnName (row, col) {
const column = this._columns[col];
let name = column.field[row] || null;
const columnInfo = this._getColumnInfo(col);
if (columnInfo) {
const captions = columnInfo.captions;
if (captions) {
name = captions[row] || null;
}
}
return name;
}
_getColumnHeaderStyle (col) {
const columnInfo = this._getColumnInfo(col);
if (columnInfo) {
let headerStyle = columnInfo.headerStyle;
if (headerStyle) {
headerStyle = this._normalizeStyle(headerStyle);
} else {
headerStyle = new GC.Spread.Sheets.Style();
}
if (this._isProtect(col)) {
headerStyle.decoration = {
icons: [
{
src: this._getReadonlyIcon(),
position: GC.Spread.Sheets.IconPosition.left,
width: 14,
height: 14
}
]
};
}
return headerStyle;
}
}
_getReadonlyIcon () {
return 'data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIGhlaWdodD0iMjRweCIgdmlld0JveD0iMCAwIDI0IDI0IiB3aWR0aD0iMjRweCIgZmlsbD0iIzAwMDAwMCI+PGcgZmlsbD0ibm9uZSI+PHBhdGggZD0iTTAgMGgyNHYyNEgwVjB6Ii8+PHBhdGggZD0iTTAgMGgyNHYyNEgwVjB6IiBvcGFjaXR5PSIuODciLz48L2c+PHBhdGggZD0iTTIwIDhoLTNWNi4yMWMwLTIuNjEtMS45MS00Ljk0LTQuNTEtNS4xOUM5LjUxLjc0IDcgMy4wOCA3IDZ2Mkg0djE0aDE2Vjh6bS04IDljLTEuMSAwLTItLjktMi0ycy45LTIgMi0yIDIgLjkgMiAyLS45IDItMiAyek05IDhWNmMwLTEuNjYgMS4zNC0zIDMtM3MzIDEuMzQgMyAzdjJIOXoiLz48L3N2Zz4=';
}
_getRuleStyle () {
const style = new GC.Spread.Sheets.Style();
style.foreColor = "red";
return style;
}
_getPriceStyle () {
const style = new GC.Spread.Sheets.Style();
style.formatter = "$ 0.00";
return style;
}
_getColumnStyle (row, col) {
const columnInfo = this._getColumnInfo(col);
if (columnInfo) {
const value = this.getValue(row, col);
const style = new GC.Spread.Sheets.Style();
if (this._isPrice(value, columnInfo.field)) {
style.compose(this._getPriceStyle());
}
if (this._evaluateRule(value, columnInfo.field)) {
style.compose(this._getRuleStyle());
}
return style;
}
}
_normalizeStyle (styleOptions) {
return new GC.Spread.Sheets.Style(styleOptions);
}
_getHeaderStyle (col) {
const style = new GC.Spread.Sheets.Style();
style.backColor = "#217346";
style.foreColor = "#FFFFFF";
style.fontWeight = "bold";
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
style.borderLeft = new GC.Spread.Sheets.LineBorder('white', GC.Spread.Sheets.LineStyle.thin);
style.borderRight = new GC.Spread.Sheets.LineBorder('white', GC.Spread.Sheets.LineStyle.thin);
style.borderTop = new GC.Spread.Sheets.LineBorder('white', GC.Spread.Sheets.LineStyle.thin);
style.borderBottom = new GC.Spread.Sheets.LineBorder('white', GC.Spread.Sheets.LineStyle.thin);
const headerStyle = this._getColumnHeaderStyle(col);
if (headerStyle) {
style.compose(headerStyle);
}
return style;
}
_getBodyStyle (row, col) {
const style = new GC.Spread.Sheets.Style();
style.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
const alternatingStyle = this._getAlternatingRowStyle(row);
if (alternatingStyle) {
style.compose(alternatingStyle);
}
const columnStyle = this._getColumnStyle(row, col);
if (columnStyle) {
style.compose(columnStyle);
}
return style;
}
_getAlternatingRowStyle (row) {
if (row % 2 === 1) {
return new GC.Spread.Sheets.Style("#ddd");
}
}
_insertRows (row, rowCount, records) {
const headerRowCount = this._getHeaderRowCount();
row -= headerRowCount;
for (let i = row; i < row + rowCount; i++) {
const record = records ? records[i - row] : this._createNewRecord();
this.data.splice(i, 0, record);
}
const oldRange = this._range();
this._updateRange(new GC.Spread.Sheets.Range(oldRange.row, oldRange.col, oldRange.rowCount + rowCount, oldRange.colCount));
}
_removeRows (row, rowCount) {
const headerRowCount = this._getHeaderRowCount();
row -= headerRowCount;
for (let i = row + rowCount - 1; i >= row; i--) {
this.data.splice(i, 1);
}
const oldRange = this._range();
this._updateRange(new GC.Spread.Sheets.Range(oldRange.row, oldRange.col, oldRange.rowCount - rowCount, oldRange.colCount));
}
_range () {
return this.host.range();
}
_updateRange (range) {
if (this.autoResize) {
this.host.range(range);
}
}
_getRecords (row, rowCount) {
const headerRowCount = this._getHeaderRowCount();
row -= headerRowCount;
return this.data.slice(row, row + rowCount);
}
_isPrimaryKey (field) {
return field.some(item => item.toLowerCase() === "id");
}
_isPrice (value, field) {
if (typeof value !== "number") {
return false;
}
if (field && field.length > 0) {
return field.some(item => item.toLowerCase().includes("price"));
}
return false;
}
_evaluateRule (value, field) {
if (typeof value !== "number") {
return false;
}
if (field && field.length > 0) {
const supported = field.some(item => item.toLowerCase().includes("units"));
if (supported) {
return value < 20;
}
}
return false;
}
_isProtect (col) {
const columnInfo = this._getColumnInfo(col);
if (columnInfo) {
const field = columnInfo.field;
if (field && field.length > 0) {
return this._isPrimaryKey(field);
}
}
return false;
}
_getValueByPath (record, path) {
let value = record;
for (let key of path) {
if (value[key] === undefined) {
return undefined;
}
value = value[key];
}
return value;
}
_setValueByPath (record, path, value) {
path = [...path];
let current = record;
let lastKey = path.splice(path.length - 1, 1)[0];
for (let key of path) {
current = current[key];
}
current[lastKey] = value;
}
_createNewRecord () {
return JSON.parse(JSON.stringify(this._schema));
}
// implement the GC.Spread.Sheets.DataRange.IDataProvider hooks
getValue (row, col) {
if (this._isHeaderArea(row)) {
return this._getColumnName(row, col);
}
const column = this._columns[col];
const rowIndex = row - this._getHeaderRowCount() + this.topRow;
const record = this.data[rowIndex];
if (record) {
return this._getValueByPath(record, column.field);
}
return null;
}
getStyle (row, col) {
if (this._isHeaderArea(row)) {
return this._getHeaderStyle(col);
}
return this._getBodyStyle(row, col);
}
setValue (row, col, value, changes) {
if (!this._isHeaderArea(row)) {
if (changes) {
changes.push({ row, col, oldValue: this.getValue(row, col), type: "setValue" });
}
const rowIndex = row - this._getHeaderRowCount() + this.topRow;
const record = this.data[rowIndex];
if (record) {
const column = this._columns[col];
if (column) {
const path = column.field;
this._setValueByPath(record, path, value);
}
}
}
return true;
}
onClear (row, col, rowCount, colCount) {
for (let r = row; r < row + rowCount; r++) {
if (this._isHeaderArea(r)) {
continue;
}
for (let c = col; c < col + colCount; c++) {
if (this._isProtect(c)) {
continue;
}
this.setValue(r, c, null);
}
}
}
getSpans (row, col, rowCount, colCount) {
const spans = [];
const headerSpans = this._getSpansFromHeader();
for (let span of headerSpans) {
if (span.intersect(row, col, rowCount, colCount)) {
spans.push(span);
}
}
return spans;
}
onRowChange (row, rowCount, changeType, changes) {
if (this._isHeaderArea(row)) {
return;
}
const isDelete = changeType === "delete";
if (changes) {
const records = this._getRecords(row, rowCount);
changes.push({ row, rowCount, isDelete, records: records, type: "rowChange" });
}
if (isDelete) {
this._removeRows(row, rowCount);
} else {
this._insertRows(row, rowCount);
}
}
onDoubleClick (row, col) {
if (this._isProtect(col)) {
return true;
}
}
onKeyDown (row, col) {
if (this._isProtect(col)) {
return true;
}
}
onMouseWheel (deltaX, deltaY, e) {
if (!this.autoResize && deltaY !== 0) {
const step = deltaY / 3;
const newTopRow = this.topRow + step;
const isScrollOut = newTopRow + this._range().rowCount - this._getHeaderRowCount() > this.data.length;
if (isScrollOut) {
return true;
}
this.topRow = newTopRow;
if (this.topRow <= 0) {
this.topRow = 0;
}
this.host.repaint();
return true;
}
}
undo (change) {
if (!change) {
return;
}
if (change.type === "setValue") {
var row = change.row;
var col = change.col;
var oldValue = change.oldValue;
this.setValue(row, col, oldValue);
}
if (change.type === "rowChange") {
var row = change.row;
var rowCount = change.rowCount;
var isDelete = change.isDelete;
var records = change.records;
if (isDelete) {
this._insertRows(row, rowCount, records);
} else {
this._removeRows(row, rowCount);
}
}
}
toJSON () {
return { typeName: this.typeName, data: this.data };
}
fromJSON (json) {
this.data = json.data;
this._init();
}
}
window.CustomDataProvider = CustomDataProvider;
function initSpread(spread) {
spread.suspendPaint();
var sheet = spread.getActiveSheet();
var data = products;
var dataRange = initDataRange(sheet, data);
initSidePanel(sheet, dataRange);
spread.undoManager().clear();
spread.resumePaint();
}
function initDataRange(sheet, data) {
var dataProvider = new CustomDataProvider(data);
var row = 2;
var col = 1;
if (!dataProvider.success) {
sheet.getCell(row, col)
.value("Failure in creating data range due to data source analysis failure.")
.fontWeight("bold")
.foreColor("red");
sheet.getCell(row + 1, col)
.value("Please using the standard data source: array of json structure data source.")
.fontWeight("bold")
.foreColor("red");
return;
}
var limitRecordsCount = 20;
var actualColumnCount = dataProvider.getActualColumnCount();
var range = new GC.Spread.Sheets.Range(row, col, limitRecordsCount, actualColumnCount);
var dataRange = sheet.dataRanges.add("dr1", dataProvider, range, {
sticky: {
top: 3
}
});
sheet.autoFitColumn(range.col + range.colCount - 1);
return dataRange;
}
function initSidePanel(sheet, dataRange) {
if (!dataRange) {
return;
}
var stickyRowsCountInput = document.getElementById("sticky-rows-count-input");
var updateOptionsButton = document.getElementById("update-options-button");
updateOptionsButton.addEventListener("click", function() {
var stickyRowsCount = Number(stickyRowsCountInput.value);
sheet.suspendPaint();
dataRange.options.sticky.top = stickyRowsCount;
sheet.resumePaint();
});
var moveDownButton = document.getElementById("move-down-button");
moveDownButton.addEventListener("click", function() {
sheet.suspendPaint();
var range = dataRange.range();
dataRange.range(new GC.Spread.Sheets.Range(range.row + 1, range.col, range.rowCount, range.colCount));
sheet.resumePaint();
});
var moveUpButton = document.getElementById("move-up-button");
moveUpButton.addEventListener("click", function() {
sheet.suspendPaint();
var range = dataRange.range();
dataRange.range(new GC.Spread.Sheets.Range(range.row - 1, range.col, range.rowCount, range.colCount));
sheet.resumePaint();
});
var increaseSizeButton = document.getElementById("increase-size-button");
increaseSizeButton.addEventListener("click", function() {
sheet.suspendPaint();
var range = dataRange.range();
dataRange.range(new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount + 1, range.colCount));
sheet.resumePaint();
});
var decreaseSizeButton = document.getElementById("decrease-size-button");
decreaseSizeButton.addEventListener("click", function() {
sheet.suspendPaint();
var range = dataRange.range();
dataRange.range(new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount - 1, range.colCount));
sheet.resumePaint();
});
var autoResizeCheckbox = document.getElementById("auto-resize-checkbox");
autoResizeCheckbox.addEventListener("click", function() {
if (autoResizeCheckbox.classList.contains("active")) {
autoResizeCheckbox.classList.remove("active");
dataRange.dataProvider.autoResize = false;
} else {
autoResizeCheckbox.classList.add("active");
dataRange.dataProvider.autoResize = true;
}
});
}
function initSplitView(spread) {
var host = document.getElementById("split-view");
var content = host.getElementsByClassName("split-content")[0];
var panel = host.getElementsByClassName("split-panel")[0];
new SplitView({
host: host,
content: content,
panel: panel,
refreshContent: function() {
spread.refresh();
}
});
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="ko-kr"/>
<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">
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/spread/source/splitView/splitView.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$/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/splitView/splitView.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/data-range/products.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 id="split-view">
<div id="ss" class="split-content"></div>
<div class="split-panel">
<div class="option-block">
<div class="option-row input-box">
<label for="sticky-rows-count-input">Sticky Rows Count</label>
<input type="text" id="sticky-rows-count-input" value="3" />
<div class="option-info valid">* valid value: integer</div>
</div>
<div class="option-row">
<input type="button" id="update-options-button" class="option-button" value="Update Options" />
</div>
</div>
<div class="option-block">
<div class="option-row option-title">
Move the Data Range Position.
</div>
<div class="option-row">
<div class="option-row">
<input type="button" id="move-down-button" class="option-button" value="Move Down" />
</div>
<div class="option-row">
<input type="button" id="move-up-button" class="option-button" value="Move Up" />
</div>
</div>
</div>
<div class="option-block">
<div class="option-row option-title">
Change the Data Range Size.
</div>
<div class="option-row">
<div class="option-row">
<input type="button" id="increase-size-button" class="option-button" value="Increase" />
</div>
<div class="option-row">
<input type="button" id="decrease-size-button" class="option-button" value="Decrease" />
</div>
</div>
</div>
<div class="option-block">
<div class="option-row">
<label class="option-checkbox active" id="auto-resize-checkbox">Auto Resize</label>
<div class="option-info">* Toggle enable auto resize range or not. <br> If disable auto resize, you could scroll the data range area by mouse wheel. <br> You could add some sheet rows for checking.</div>
</div>
</div>
</div>
</div>
</html>

.option-block {
background: #fff;
padding: 8px;
margin: 12px 0;
border-radius: 4px;
border: 1px dashed #82bc00;
box-shadow: 0px 0 6px 0 rgba(0,0,0,0.1);
}
.option-block.toggle {
border: 1px dotted #f7a711;
}
.option-row {
font-size: 14px;
box-sizing: border-box;
padding: 4px 0;
}
.option-title {
font-weight: bold;
color: #656565;
}
.option-info {
font-size: 12px;
color: #919191;
margin-top: 6px;
font-weight: normal;
}
.option-info.valid {
color: #82bc00;
}
.option-info.toggle {
color: #f7a711;
}
.option-button {
width: 100%;
padding: 0;
line-height: 20px;
background: #82bc00;
color: #fff;
transition: 0.3s;
cursor: pointer;
outline: none;
border-radius: 4px;
box-sizing: border-box;
box-shadow: 0 1px 4px 0 rgba(0,0,0,0.3);
border: none;
}
.option-button:hover {
background: #82bc00;
color: #fff;
box-shadow: 0 3px 8px 0 rgba(0,0,0,0.4);
}
.option-checkbox {
background: #fff;
border: 1px dashed #f7a711;
color: #f7a711;
padding: 2px 4px;
transition: 0.3s;
box-sizing: border-box;
cursor: pointer;
-webkit-user-select: none;
-moz-user-select: none;
-o-user-select: none;
-user-select: none;
}
.option-checkbox.active {
color: #fff;
background: #f7a711;
box-shadow: 0 1px 4px 0 rgba(0,0,0,0.3);
border-radius: 4px;
}
.selection-box {
position: relative;
}
.selection-box > select {
text-align: left;
width: 100%;
height: 20px;
padding: 0;
line-height: 20px;
background: transparent;
border: none;
border-bottom: 2px solid #656565;
color: #656565;
transition: 0.3s;
cursor: pointer;
outline: none;
box-sizing: border-box;
}
.selection-box > select > option {
background: white;
}
.selection-box > select:focus {
border-bottom: 2px solid #82bc00;
color: #82bc00;
box-shadow: 0 2px 6px 0 rgba(0,0,0,0.3);
}
.selection-box > label {
position: absolute;
cursor: pointer;
font-size: 12px;
color: #fff;
background: #656565;
padding: 0 4px;
right: 0;
top: 6px;
box-shadow: 0 1px 4px 0 rgba(0,0,0,0.3);
}
.input-box {
position: relative;
}
.input-box > input[type=text] {
width: 100%;
background: transparent;
border: none;
color: #656565;
border-bottom: 2px solid #656565;
outline: none;
box-sizing: border-box;
transition: 0.3s;
}
.input-box > input[type=text]:focus {
color: #82bc00;
border-bottom: 2px solid #82bc00;
}
.input-box > label {
cursor: pointer;
position: absolute;
right: 0;
top: 5px;
font-size: 12px;
color: #fff;
background: #656565;
padding: 0 4px;
box-shadow: 0 1px 4px 0 rgba(0,0,0,0.3);
}