配列操作関数

SpreadJSは、ワークシート内の配列をより簡単に操作できるように設計された関数を含んでいます。

ワークシートで配列を容易に操作するために、SpreadJSには11個の配列操作関数が用意されています。 配列の結合 ソースのサイズが固定でない場合は特に、データの結合は難しくなります。VSTACK関数とHSTACK関数を使用すると、動的配列を簡単に組み合わせて、データを垂直または水平に積み重ねることができます。 VSTACK関数 - 配列を垂直に積み重ねます HSTACK関数- 配列を水平に積み重ねます 配列の形状 配列からリストへ、または逆にリストから配列への場合は特に、データの「形状」の変更は難しくなります。2次元配列を単純なリストに変換したい場合は、TOROW関数およびTOCOL関数を使って2次元配列を1つの行または列のデータに変換します。 反対に、WRAPROWS関数およびWRAPCOLS関数を使えば、指定した幅や高さになったら次の行にデータを「折り返し」て2次元配列を作成します(このドキュメントのテキストのように)。 TOROW関数 - 配列を1つの行として返します TOCOL関数 - 配列を1つの列として返します WRAPROWS関数 - 行の配列を2次元配列に折り返します WRAPCOLS関数 - 列の配列を2次元配列に折り返します 配列のサイズ変更 TAKE関数およびDROP関数を使用すると、配列の先頭または末尾の行数を指定して、配列のサイズを縮小できます。 CHOOSEROWS関数およびCHOOSECOLS関数を使用すると、配列から特定の行や列をインデックスで選択できます。 EXPAND関数を使用すると、指定したサイズに配列を拡大できます。必要なのは、新しい次元と、余分なスペースを埋める値を指定することだけです。 TAKE関数 - 配列の先頭または末尾から行または列を返します DROP関数 - 配列の先頭または末尾から行または列を削除します CHOOSEROWS関数 - 配列から指定された行を返します CHOOSECOLS関数 - 配列から指定された列を返します EXPAND関数 - 配列を指定された次元に拡張します
import { Component, NgModule, enableProdMode } from '@angular/core'; import { bootstrapApplication, BrowserModule } from '@angular/platform-browser'; import { FormsModule } from '@angular/forms'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@mescius/spread-sheets-angular'; import GC from '@mescius/spread-sheets'; import '@mescius/spread-sheets-resources-ja'; GC.Spread.Common.CultureManager.culture("ja-jp"); import './styles.css'; const spreadNS = GC.Spread.Sheets, SheetArea = spreadNS.SheetArea; @Component({ standalone: true, imports: [SpreadSheetsModule, BrowserModule, FormsModule], selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { spread: GC.Spread.Sheets.Workbook; hostStyle = { width: '100%', height: '100%', overflow: 'hidden', float: 'left' }; constructor() {} initSpread($event: any) { var data = [ { sheetName: 'VSTACK', cells: [ [{v:"データ",s:"title"}], ["A","B","C",null,"AA","BB","CC"], ["D","E","F",null,"DD","EE","FF"], [{v:"計算式",s:"title"}], ["=FORMULATEXT(A7)"], [{v:"結果",s:"title"}], ["=VSTACK(A2:C3,E2:G3)"] ], columnsWidth: [69] }, { sheetName: 'HSTACK', cells: [ [{v:"データ",s:"title"}], ["A","B","C",null,"AA","BB","CC"], ["D","E","F",null,"DD","EE","FF"], [{v:"計算式",s:"title"}], ["=FORMULATEXT(A7)"], [{v:"結果",s:"title"}], ["=HSTACK(A2:C3,E2:G3)"] ], columnsWidth: [69] }, { sheetName: 'TOROW', cells: [ [{v:"データ",s:"title"}], ["Ben","Peter","Mary","Sam"], ["John","Hillary","Jenny","James"], ["Agnes","Harry","Felicity","Joe"], [{v:"計算式",s:"title"}], ["=FORMULATEXT(A8)"], [{v:"結果",s:"title"}], ["=TOROW(A2:D4)"] ], columnsWidth: [69] }, { sheetName: 'TOCOL', cells: [ [{v:"データ",s:"title"}], ["Ben","Peter","Mary","Sam"], ["John","Hillary","Jenny","James"], ["Agnes","Harry","Felicity","Joe"], [{v:"計算式",s:"title"}], ["=FORMULATEXT(A8)"], [{v:"結果",s:"title"}], ["=TOCOL(A2:D4)"] ], columnsWidth: [69] }, { sheetName: 'WRAPROWS', cells: [ [{v:"データ",s:"title"}], ["A","B","C","D","E","F","G"], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B5)"], [{v:"結果",s:"title"}, "=WRAPROWS(A2:G2,3)"], [], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B10)"], [{v:"結果",s:"title"}, '=WRAPROWS(A2:G2,3,"x")'], ], columnsWidth: [69] }, { sheetName: 'WRAPCOLS', cells: [ [{v:"データ",s:"title"}], ["A","B","C","D","E","F","G"], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B5)"], [{v:"結果",s:"title"}, "=WRAPCOLS(A2:G2,3)"], [], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B10)"], [{v:"結果",s:"title"}, '=WRAPCOLS(A2:G2,3,"x")'], ], columnsWidth: [69] }, { sheetName: 'TAKE', cells: [ [{v:"データ",s:"title"}], [1,2,3], [4,5,6], [7,8,9], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B7)"], [{v:"結果",s:"title"}, "=TAKE(A2:C4,2)"], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B11)"], [{v:"結果",s:"title"}, '=TAKE(A2:C4,,2)'], [], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B16)"], [{v:"結果",s:"title"}, '=TAKE(A2:C4,-2)'], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B20)"], [{v:"結果",s:"title"}, '=TAKE(A2:C4,2,2)'], ], columnsWidth: [69] }, { sheetName: 'DROP', cells: [ [{v:"データ",s:"title"}], [1,2,3], [4,5,6], [7,8,9], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B7)"], [{v:"結果",s:"title"}, "=DROP(A2:C4,2)"], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B10)"], [{v:"結果",s:"title"}, '=DROP(A2:C4,,2)'], [], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B15)"], [{v:"結果",s:"title"}, '=DROP(A2:C4,-2)'], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B18)"], [{v:"結果",s:"title"}, '=DROP(A2:C4,2,2)'], ], columnsWidth: [69] }, { sheetName: 'CHOOSEROWS', cells: [ [{v:"データ",s:"title"}], [1,2], [3,4], [5,6], [7,8], [9,10], [11,12], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B10)"], [{v:"結果",s:"title"}, "=CHOOSEROWS(A2:B7,1,3,5,1)"], [], [], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B16)"], [{v:"結果",s:"title"}, '=CHOOSEROWS(A2:B7,-1,-2)'], ], columnsWidth: [69] }, { sheetName: 'CHOOSECOLS', cells: [ [{v:"データ",s:"title"}], [1,2,3,4,5], [6,7,8,9,10], [11,12,13,14,15], [16,17,18,19,20], [21,22,23,24,25], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B9)"], [{v:"結果",s:"title"}, "=CHOOSECOLS(A2:E6,1,3,5,1)"], [], [], [], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B16)"], [{v:"結果",s:"title"}, '=CHOOSECOLS(A2:E6,-1,-2)'], ], columnsWidth: [69] }, { sheetName: 'EXPAND', cells: [ [{v:"データ",s:"title"}], [1,2], [3,4], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B6)"], [{v:"結果",s:"title"}, "=EXPAND(A2:B3,3,3)"], [], [], [], [{v:"計算式",s:"title"}, "=FORMULATEXT(B11)"], [{v:"結果",s:"title"}, '=EXPAND(A2:B3,3,3, "-")'], ], columnsWidth: [69] }, ]; let workbook = this.spread = $event.spread; workbook.options.allowDynamicArray = true; workbook.suspendPaint(); workbook.suspendCalcService(); this.initStyles(workbook); workbook.setSheetCount(data.length); for (var i = 0; i < data.length; i++) { var sheetData = data[i]; var sheet = workbook.sheets[i]; sheet.name(sheetData.sheetName); this.setCells(sheet, sheetData.cells, 0, 0); this.setColumnsWidth(sheet, sheetData.columnsWidth); } workbook.resumeCalcService(); workbook.resumePaint(); } initStyles(workbook: GC.Spread.Sheets.Workbook) { var style = new GC.Spread.Sheets.Style(); style.name = 'title'; style.font = 'normal bold 16px Segoe UI'; style.foreColor = "#172b4d"; workbook.addNamedStyle(style); } setCells(sheet: GC.Spread.Sheets.Worksheet, cells: any[], rowIndex: number, colIndex: number) { for(var i = 0; i < cells.length; i++) { var row = cells[i]; var r = rowIndex + i; for (var j = 0; j < row.length; j++) { var cell = row[j]; var c = colIndex + j; if (cell === null) { continue; } if (typeof cell === "object") { if (cell.v !== undefined) { sheet.setValue(r, c, cell.v); } if (cell.s !== undefined) { sheet.setStyle(r, c, cell.s) } } else if (cell[0] === '=') { sheet.setFormula(r, c, cell); } else { sheet.setValue(r, c, cell); } } } } setColumnsWidth(sheet: GC.Spread.Sheets.Worksheet, columnsWidth: number[]) { if (!columnsWidth) { return; } for (var i = 0; i < columnsWidth.length; i++) { sheet.setColumnWidth(i, columnsWidth[i]); } } } enableProdMode(); bootstrapApplication(AppComponent);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Polyfills --> <script src="$DEMOROOT$/ja/angular/node_modules/core-js/client/shim.min.js"></script> <script src="$DEMOROOT$/ja/angular/node_modules/zone.js/fesm2015/zone.min.js"></script> <!-- SystemJS --> <script src="$DEMOROOT$/ja/angular/node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.import('@angular/compiler'); System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('$DEMOROOT$/ja/lib/angular/license.ts'); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html>
<div class="sample-tutorial"> <gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)"> <gc-worksheet> </gc-worksheet> </gc-spread-sheets> </div>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .options-row { font-size: 14px; padding: 5px; margin-top: 10px; } input { padding: 4px 6px; display: inline-block; } input[type="text"] { width: 200px; } label { display: block; margin-bottom: 6px; } input[type=button] { margin-top: 6px; display: block; width:216px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }
(function (global) { System.config({ transpiler: 'ts', typescriptOptions: { tsconfig: true }, meta: { 'typescript': { "exports": "ts" }, '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'core-js': 'npm:core-js/client/shim.min.js', 'zone': 'npm:zone.js/fesm2015/zone.min.js', 'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/fesm2022', '@angular/common': 'npm:@angular/common/fesm2022/common.mjs', '@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs', '@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs', '@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs', '@angular/router': 'npm:@angular/router/fesm2022/router.mjs', '@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': './plugin.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', 'tslib':'npm:tslib/tslib.js', '@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-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'ts', meta: { "*.component.ts": { loader: "system.component-loader.js" } } }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, "node_modules/@angular": { defaultExtension: 'mjs' }, "@mescius/spread-sheets-angular": { defaultExtension: 'mjs' }, '@angular/core': { defaultExtension: 'mjs', main: 'core.mjs' } } }); })(this);