概要と基本的な使い方

データ範囲は、ワークシートの構造内で特定の領域をデータ範囲として定義および管理することができます。 セル範囲の行(row)と列(col)の座標をシートとデータ範囲の間で自動変換し、データの管理が簡単になり、アプリケーションのビジネスロジックの開発に集中できるようになります。 この機能を使用すると、ワークシート上で複雑なデータ構造の表示や操作ができるようになります。

データ範囲マネージャー 以下の手順でデータ範囲を使用できます: カスタムデータプロバイダのインスタンスを作成する(コア機能) シートの対象範囲を選択する シートのデータ範囲マネージャーを使用してデータ範囲を追加する 指定した名前のデータ範囲を削除する シート内のすべてのデータ範囲を削除する 引数に名前を指定してデータ範囲を取得する シート内のすべてのデータ範囲を取得する データ範囲 データ範囲は、データ範囲マネージャーまたはデータプロバイダーから取得できます。 データソースを変更すると、以下の動作を実行できます。 データ範囲を再描画する サイズを変更する 配置を変更する データ範囲のオプション設定 データ範囲の最上部の行をシート内で最初の表示行として固定でき、固定する行数は定義または変更することができます。 データプロバイダー データプロバイダーはユーザー定義のインスタンスであり、GC.Spread.Sheets.DataRange.IDataProviderAPIを実装する必要があります。
import * as React from 'react'; import { createRoot } from 'react-dom/client'; import './styles.css'; import { AppFunc } from './app-func'; import { App } from './app-class'; // 1. Functional Component sample createRoot(document.getElementById('app')).render(<AppFunc />); // 2. Class Component sample // createRoot(document.getElementById('app')).render(<App />);
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ import * as React from 'react'; import GC from '@mescius/spread-sheets'; import { SpreadSheets } from '@mescius/spread-sheets-react'; import '@mescius/spread-sheets-resources-ja'; GC.Spread.Common.CultureManager.culture("ja-jp"); import './styles.css'; 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(); } } const useState = React.useState; let sheet = null; let dataRange = null; export function AppFunc() { const [state, setState] = useState({ stickyRowsCount: 3, isAutoResize: true }); const initSpread = (spread) => { spread.suspendPaint(); sheet = spread.getActiveSheet(); var data = products; initDataRange(sheet, data); spread.undoManager().clear(); spread.resumePaint(); initSplitView(spread); } const 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); dataRange = sheet.dataRanges.add("dr1", dataProvider, range, { sticky: { top: 3 } }); sheet.autoFitColumn(range.col + range.colCount - 1); return dataRange; } const updateOptions = () => { var stickyRowsCount = Number(state.stickyRowsCount); sheet.suspendPaint(); dataRange.options.sticky.top = stickyRowsCount; sheet.resumePaint(); } const moveDown = () => { sheet.suspendPaint(); var range = dataRange.range(); dataRange.range(new GC.Spread.Sheets.Range(range.row + 1, range.col, range.rowCount, range.colCount)); sheet.resumePaint(); } const moveUp = () => { sheet.suspendPaint(); var range = dataRange.range(); dataRange.range(new GC.Spread.Sheets.Range(range.row - 1, range.col, range.rowCount, range.colCount)); sheet.resumePaint(); } const increaseSize = () => { sheet.suspendPaint(); var range = dataRange.range(); dataRange.range(new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount + 1, range.colCount)); sheet.resumePaint(); } const decreaseSize = () => { sheet.suspendPaint(); var range = dataRange.range(); dataRange.range(new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount - 1, range.colCount)); sheet.resumePaint(); } const autoResize = () => { let autoResizeCheckbox = document.getElementById("auto-resize-checkbox"); if (autoResizeCheckbox.classList.contains("active")) { autoResizeCheckbox.classList.remove("active"); setState({ ...state, isAutoResize: false }); dataRange.dataProvider.autoResize = false; } else { autoResizeCheckbox.classList.add("active"); setState({ ...state, isAutoResize: true }); dataRange.dataProvider.autoResize = true; } } return ( <div id="split-view"> <div id="ss" class="split-content"> <SpreadSheets workbookInitialized={spread => initSpread(spread)}></SpreadSheets> </div> <div class="split-panel"> <div class="option-block"> <div class="option-row input-box"> <label for="sticky-rows-count-input">固定行数</label> <input type="text" id="sticky-rows-count-input" value={state.stickyRowsCount} onChange={(e) => { setState({ stickyRowsCount: e.target.value }) }} /> <div class="option-info valid">* 有効値: 数値</div> </div> <div class="option-row"> <input type="button" id="update-options-button" class="option-button" value="設定更新" onClick={() => { updateOptions() }} /> </div> </div> <div class="option-block"> <div class="option-row option-title"> データ範囲の位置を移動する </div> <div class="option-row"> <div class="option-row"> <input type="button" id="move-down-button" class="option-button" value="下へ移動" onClick={() => { moveDown() }} /> </div> <div class="option-row"> <input type="button" id="move-up-button" class="option-button" value="上へ移動" onClick={() => { moveUp() }} /> </div> </div> </div> <div class="option-block"> <div class="option-row option-title"> データ範囲のサイズを変更する </div> <div class="option-row"> <div class="option-row"> <input type="button" id="increase-size-button" class="option-button" value="拡大" onClick={() => { increaseSize() }} /> </div> <div class="option-row"> <input type="button" id="decrease-size-button" class="option-button" value="縮小" onClick={() => { decreaseSize() }} /> </div> </div> </div> <div class="option-block"> <div class="option-row"> <label class="option-checkbox active" id="auto-resize-checkbox" onClick={() => { autoResize() }}>サイズ自動変更</label> <div class="option-info">* サイズ自動変更の有効/無効を切り替える<br /> サイズ自動変更を無効にすると、マウスホイールでデータ範囲をスクロールできます。 <br /> シートの行を追加すると確認できます。</div> </div> </div> </div> </div> ); } 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(); } }); }
import * as React from 'react'; import GC from '@mescius/spread-sheets'; import { SpreadSheets } from '@mescius/spread-sheets-react'; import '@mescius/spread-sheets-resources-ja'; GC.Spread.Common.CultureManager.culture("ja-jp"); import './styles.css'; const Component = React.Component; 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(); } } export class App extends Component { constructor(props) { super(props); this.spread = null; this.dataRange = null; this.state = { stickyRowsCount: 3, isAutoResize: true }; } render() { return ( <div id="split-view"> <div id="ss" class="split-content"> <SpreadSheets workbookInitialized={spread => this.initSpread(spread)}></SpreadSheets> </div> <div class="split-panel"> <div class="option-block"> <div class="option-row input-box"> <label for="sticky-rows-count-input">固定行数</label> <input type="text" id="sticky-rows-count-input" value={this.state.stickyRowsCount} onChange={(e) => { this.setState({ stickyRowsCount: e.target.value }) }} /> <div class="option-info valid">* 有効値: 数値</div> </div> <div class="option-row"> <input type="button" id="update-options-button" class="option-button" value="設定更新" onClick={() => { this.updateOptions() }} /> </div> </div> <div class="option-block"> <div class="option-row option-title"> データ範囲の位置を移動する </div> <div class="option-row"> <div class="option-row"> <input type="button" id="move-down-button" class="option-button" value="下へ移動" onClick={() => { this.moveDown() }} /> </div> <div class="option-row"> <input type="button" id="move-up-button" class="option-button" value="上へ移動" onClick={() => { this.moveUp() }} /> </div> </div> </div> <div class="option-block"> <div class="option-row option-title"> データ範囲のサイズを変更する </div> <div class="option-row"> <div class="option-row"> <input type="button" id="increase-size-button" class="option-button" value="拡大" onClick={() => { this.increaseSize() }} /> </div> <div class="option-row"> <input type="button" id="decrease-size-button" class="option-button" value="縮小" onClick={() => { this.decreaseSize() }} /> </div> </div> </div> <div class="option-block"> <div class="option-row"> <label class="option-checkbox active" id="auto-resize-checkbox" onClick={() => { this.autoResize() }}>サイズ自動変更</label> <div class="option-info">* サイズ自動変更の有効/無効を切り替える<br /> サイズ自動変更を無効にすると、マウスホイールでデータ範囲をスクロールできます。 <br /> シートの行を追加すると確認できます。</div> </div> </div> </div> </div> ); } initSpread(spread) { this.spread = spread; spread.suspendPaint(); var data = products; var sheet = spread.getActiveSheet(); this.initDataRange(sheet, data); spread.undoManager().clear(); spread.resumePaint(); initSplitView(spread); } 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 } }); this.dataRange = dataRange; sheet.autoFitColumn(range.col + range.colCount - 1); return dataRange; } updateOptions() { var stickyRowsCount = Number(this.state.stickyRowsCount); this.sheet.suspendPaint(); this.dataRange.options.sticky.top = stickyRowsCount; this.sheet.resumePaint(); } moveDown() { this.sheet.suspendPaint(); var range = this.dataRange.range(); this.dataRange.range(new GC.Spread.Sheets.Range(range.row + 1, range.col, range.rowCount, range.colCount)); this.sheet.resumePaint(); } moveUp() { this.sheet.suspendPaint(); var range = this.dataRange.range(); this.dataRange.range(new GC.Spread.Sheets.Range(range.row - 1, range.col, range.rowCount, range.colCount)); this.sheet.resumePaint(); } increaseSize() { this.sheet.suspendPaint(); var range = this.dataRange.range(); this.dataRange.range(new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount + 1, range.colCount)); this.sheet.resumePaint(); } decreaseSize() { this.sheet.suspendPaint(); var range = this.dataRange.range(); this.dataRange.range(new GC.Spread.Sheets.Range(range.row, range.col, range.rowCount - 1, range.colCount)); this.sheet.resumePaint(); } autoResize() { let autoResizeCheckbox = document.getElementById("auto-resize-checkbox"); if (autoResizeCheckbox.classList.contains("active")) { autoResizeCheckbox.classList.remove("active"); this.setState({ isAutoResize: false }); this.dataRange.dataProvider.autoResize = false; } else { autoResizeCheckbox.classList.add("active"); this.setState({ isAutoResize: true }); this.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$/ja/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/spread/source/splitView/splitView.css"> <!-- SystemJS --> <script src="$DEMOROOT$/ja/react/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <!-- plugins --> <script src="$DEMOROOT$/spread/source/splitView/SplitView.js"></script> <script src="$DEMOROOT$/spread/source/data/data-range/products.js" type="text/javascript"></script> <script> System.import('$DEMOROOT$/ja/lib/react/license.js').then(function () { System.import('./src/app'); }); </script> </head> <body> <div id="app" style="height: 100%;"></div> </body> </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; } .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); }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true, react: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', '@mescius/spread-sheets-resources-ja': 'npm:@mescius/spread-sheets-resources-ja/index.js', '@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'react': 'npm:react/cjs/react.production.js', 'react-dom': 'npm:react-dom/cjs/react-dom.production.js', 'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js', 'scheduler': 'npm:scheduler/cjs/scheduler.production.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' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'jsx' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);