Data Range Manager
You can use a data range with the following simple steps:
Create a custom Data Provider instance (core)
Choose a target range in sheet
Use the Data Range Manager of the sheet to add a data range
Remove a data range by data range name.
Clear all the data range in sheet.
Get a data range by data range name.
Get all the data ranges in sheet.
Data Range
You can get the data range from the Data Range Manager or from the data provider.
After your data source changed, you may want to:
repaint the area itself
change the size
change the position
Data Range Options
You could make the data range top rows to stick to the first visible row in the sheet, and the sticky rows count can be defined and changed.
Data Provider
The data provider is an instance which is created by the developer, and it should implement the APIs from GC.Spread.Sheets.DataRange.IDataProvider.
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="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">
<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$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.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);
}
