数据区域管理器
您可以使用以下简单步骤来使用数据区域：
创建一个自定义数据提供者实例（核心）
在工作表中选择目标范围
使用工作表的数据区域管理器添加数据区域
通过数据区域名称删除数据区域。
清除工作表中的所有数据区域。
通过数据区域名称获取数据区域。
获取工作表中的所有数据区域。
数据区域
您可以从数据区域管理器或数据提供者获取数据区域。
在数据源更改后，您可能需要：
重新绘制区域本身
更改大小
更改位置
数据区域选项
您可以使数据区域的顶部行粘贴到工作表中的第一个可见行，并且可以定义和更改粘贴行数。
数据提供者
数据提供者是由开发人员创建的实例，它应实现 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="zh-cn"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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);
}
