データ検証機能をシートに追加するには、まず検証機能を作成してから、これをシートに追加します。次に、例を示します。
作成可能なデータ検証機能は、以下の6つです。
createNumberValidator: 数値に基づくバリデーターを作成します。
createDateValidator: 日付に基づくバリデーターを作成します。
createTextLengthValidator: テキストの長さに基づくバリデーターを作成します。
createFormulaValidator: 数式に基づくバリデーターを作成します。
createFormulaListValidator: 数式リストに基づくバリデーターを作成します。
createListValidator: リストに基づくバリデーターを作成します。
データ検証機能をセルに設定した後は、getDataValidatorメソッドを使用して、セルのデータ検証機能を取得できます。また、isValidメソッドを使用すると、セル値が有効かどうかを判断できます。次に、例を示します。
無効データを強調表示するかどうかを取得または設定するには、highlightInvalidData属性を使用します。trueに設定すると、値が無効な場合にハイライトスタイルが表示されます。
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 />);
import React, { useState, useEffect } from 'react';
import { createRoot } from 'react-dom/client';
import GC from '@mescius/spread-sheets';
import '@mescius/spread-sheets-resources-ja';
GC.Spread.Common.CultureManager.culture("ja-jp");
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
function _getElementById(id) {
return document.getElementById(id);
}
export function AppFunc() {
const [highlightInvalidData, sethighlightInvalidData] = useState(true);
const [spread, setspread] = useState(null);
let initSpread = function(value) {
setspread(value);
value.suspendPaint();
loadData(value);
setValidator(value);
value.resumePaint();
}
let loadData = function(spread) {
let spreadNS = GC.Spread.Sheets;
let sheet = spread.getActiveSheet();
sheet.setRowHeight(3, 40);
sheet.setValue(3, 0, "Shopping Place");
let title = sheet.getCell(3, 0);
title.font("bold 20px arial");
title.vAlign(spreadNS.VerticalAlign.center);
title.backColor("#D1CBC5");
sheet.setColumnWidth(0, 160);
sheet.setColumnWidth(1, 35);
sheet.getRange(3, 0, 3, 1).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), { all: true });
sheet.setValue(4, 0, "Food Shop");
sheet.setValue(5, 0, "Other");
sheet.getCell(4, 0).font("bold 15px arial");
sheet.getCell(5, 0).font("bold 15px arial");
let startRow = 3;
let startCol = 1;
sheet.addSpan(startRow + 0, startCol + 0, 1, 4);
sheet.setRowHeight(startRow + 0, 40);
sheet.setValue(startRow + 0, startCol + 0, "Goods List");
title = sheet.getCell(startRow + 0, startCol + 0);
title.font("bold 30px arial");
title.vAlign(spreadNS.VerticalAlign.center);
title.backColor("#D1CBC5");
sheet.setColumnWidth(startCol + 0, 100);
sheet.setColumnWidth(startCol + 1, 100);
sheet.setColumnWidth(startCol + 2, 100);
sheet.setColumnWidth(startCol + 3, 120);
sheet.getRange(startRow + 0, startCol + 0, 8, 4).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), { all: true });
sheet.setValue(startRow + 1, startCol + 0, "Name");
sheet.setValue(startRow + 1, startCol + 1, "Category");
sheet.setValue(startRow + 1, startCol + 2, "Price");
sheet.setValue(startRow + 1, startCol + 3, "Shopping Place");
for (let i = 0; i < 4; i++) {
sheet.getCell(startRow + 1, startCol + i).font("bold 15px arial");
}
sheet.setValue(startRow + 2, startCol + 0, "Apple");
sheet.setValue(startRow + 3, startCol + 0, "Potato");
sheet.setValue(startRow + 4, startCol + 0, "Tomato");
sheet.setValue(startRow + 5, startCol + 0, "Sandwich");
sheet.setValue(startRow + 6, startCol + 0, "Hamburger");
sheet.setValue(startRow + 7, startCol + 0, "Grape");
sheet.setValue(startRow + 2, startCol + 1, "Fruit");
sheet.setValue(startRow + 3, startCol + 1, "Vegetable");
sheet.setValue(startRow + 4, startCol + 1, "Vegetable");
sheet.setValue(startRow + 5, startCol + 1, "Food");
sheet.setValue(startRow + 6, startCol + 1, "Food");
sheet.setValue(startRow + 7, startCol + 1, "Fruit");
sheet.setValue(startRow + 2, startCol + 2, 1.00);
sheet.setValue(startRow + 3, startCol + 2, 2.01);
sheet.setValue(startRow + 4, startCol + 2, 3.21);
sheet.setValue(startRow + 5, startCol + 2, 2);
sheet.setValue(startRow + 6, startCol + 2, 2);
sheet.setValue(startRow + 7, startCol + 2, 4);
let myFormatter = new GC.Spread.Formatter.GeneralFormatter("$#,##0.00;[Red] $#,##0.00");
for (let i = 2; i < 8; i++) {
sheet.getCell(startRow + i, startCol + 2).formatter(myFormatter);
}
sheet.setValue(startRow + 2, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 3, startCol + 3, "Other");
sheet.setValue(startRow + 4, startCol + 3, "Other");
sheet.setValue(startRow + 5, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 6, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 7, startCol + 3, "Other");
//set invalid data
sheet.setValue(6, 2, "sss");
sheet.setValue(10, 4, "Sun Store");
}
let setValidator = function(spread) {
let spreadNS = GC.Spread.Sheets;
let sheet = spread.getActiveSheet();
spread.options.highlightInvalidData = true;
//ListValidator
let dv1 = new spreadNS.DataValidation.createListValidator("Fruit,Vegetable,Food");
dv1.inputTitle("Please choose a category:");
dv1.inputMessage("Fruit, Vegetable, Food");
dv1.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: "gold",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideRight,
});
for (let i = 5; i < 11; i++) {
sheet.setDataValidator(i, 2, dv1);
}
//FormulaListValidator
let dv2 = new spreadNS.DataValidation.createFormulaListValidator("$A$5:$A$6")
for (let i = 5; i < 11; i++) {
sheet.setDataValidator(i, 4, dv2);
}
sheet.setValue(14, 0, "ValidationList Comma Support");
//Validation List Support Comma
sheet.setValue(14, 2, "Amount of money");
let dv3 = new GC.Spread.Sheets.DataValidation.createListValidator("123\\,456,234\\,567,789\\,564");
dv3.inputTitle("Please choose a number:");
dv3.inputMessage("Amount of money");
dv3.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color: "green",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.topRight
});
sheet.setDataValidator(14, 2, dv3);
sheet.setValue(14, 4, "Calculation operators");
let dv4 = new GC.Spread.Sheets.DataValidation.createListValidator("\\,,>,<,*,/");
dv4.inputTitle("Please choose a operator:");
dv4.inputMessage("Calculation operators");
dv4.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: "yellow",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideLeft,
image: "$DEMOROOT$/spread/source/images/apple.jpg"
});
sheet.setDataValidator(14, 4, dv4);
}
let changeHighlightInvalidData = function(e) {
let highlightInvalidData = e.target.checked;
spread.options.highlightInvalidData = highlightInvalidData;
sethighlightInvalidData(highlightInvalidData);
}
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<div className="options-container">
<div className="option-row">
<p>[無効なデータを強調表示]のチェックボックスをクリックして、さまざまな方法で無効なデータを強調表示する方法を確認してください。</p>
<input type="checkbox" id="highlightInvalidData" checked={highlightInvalidData} onChange={e => changeHighlightInvalidData(e)} />
<label htmlFor="highlightInvalidData">無効なデータを強調表示</label>
</div>
</div>
</div>);
}
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import GC from '@mescius/spread-sheets';
import '@mescius/spread-sheets-resources-ja';
GC.Spread.Common.CultureManager.culture("ja-jp");
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
function _getElementById(id) {
return document.getElementById(id);
}
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.state = {highlightInvalidData: true};
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
<div className="options-container">
<div className="option-row">
<p>[無効なデータを強調表示]のチェックボックスをクリックして、さまざまな方法で無効なデータを強調表示する方法を確認してください。</p>
<input type="checkbox" id="highlightInvalidData" checked={this.state.highlightInvalidData} onChange={e => this.changeHighlightInvalidData(e)} />
<label htmlFor="highlightInvalidData">無効なデータを強調表示</label>
</div>
</div>
</div>;
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
this.loadData(spread);
this.setValidator(spread);
spread.resumePaint();
}
loadData(spread) {
let spreadNS = GC.Spread.Sheets;
let sheet = spread.getActiveSheet();
sheet.setRowHeight(3, 40);
sheet.setValue(3, 0, "Shopping Place");
let title = sheet.getCell(3, 0);
title.font("bold 20px arial");
title.vAlign(spreadNS.VerticalAlign.center);
title.backColor("#D1CBC5");
sheet.setColumnWidth(0, 160);
sheet.setColumnWidth(1, 35);
sheet.getRange(3, 0, 3, 1).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), { all: true });
sheet.setValue(4, 0, "Food Shop");
sheet.setValue(5, 0, "Other");
sheet.getCell(4, 0).font("bold 15px arial");
sheet.getCell(5, 0).font("bold 15px arial");
let startRow = 3;
let startCol = 1;
sheet.addSpan(startRow + 0, startCol + 0, 1, 4);
sheet.setRowHeight(startRow + 0, 40);
sheet.setValue(startRow + 0, startCol + 0, "Goods List");
title = sheet.getCell(startRow + 0, startCol + 0);
title.font("bold 30px arial");
title.vAlign(spreadNS.VerticalAlign.center);
title.backColor("#D1CBC5");
sheet.setColumnWidth(startCol + 0, 100);
sheet.setColumnWidth(startCol + 1, 100);
sheet.setColumnWidth(startCol + 2, 100);
sheet.setColumnWidth(startCol + 3, 120);
sheet.getRange(startRow + 0, startCol + 0, 8, 4).setBorder(new spreadNS.LineBorder("Black", spreadNS.LineStyle.thin), { all: true });
sheet.setValue(startRow + 1, startCol + 0, "Name");
sheet.setValue(startRow + 1, startCol + 1, "Category");
sheet.setValue(startRow + 1, startCol + 2, "Price");
sheet.setValue(startRow + 1, startCol + 3, "Shopping Place");
for (let i = 0; i < 4; i++) {
sheet.getCell(startRow + 1, startCol + i).font("bold 15px arial");
}
sheet.setValue(startRow + 2, startCol + 0, "Apple");
sheet.setValue(startRow + 3, startCol + 0, "Potato");
sheet.setValue(startRow + 4, startCol + 0, "Tomato");
sheet.setValue(startRow + 5, startCol + 0, "Sandwich");
sheet.setValue(startRow + 6, startCol + 0, "Hamburger");
sheet.setValue(startRow + 7, startCol + 0, "Grape");
sheet.setValue(startRow + 2, startCol + 1, "Fruit");
sheet.setValue(startRow + 3, startCol + 1, "Vegetable");
sheet.setValue(startRow + 4, startCol + 1, "Vegetable");
sheet.setValue(startRow + 5, startCol + 1, "Food");
sheet.setValue(startRow + 6, startCol + 1, "Food");
sheet.setValue(startRow + 7, startCol + 1, "Fruit");
sheet.setValue(startRow + 2, startCol + 2, 1.00);
sheet.setValue(startRow + 3, startCol + 2, 2.01);
sheet.setValue(startRow + 4, startCol + 2, 3.21);
sheet.setValue(startRow + 5, startCol + 2, 2);
sheet.setValue(startRow + 6, startCol + 2, 2);
sheet.setValue(startRow + 7, startCol + 2, 4);
let myFormatter = new GC.Spread.Formatter.GeneralFormatter("$#,##0.00;[Red] $#,##0.00");
for (let i = 2; i < 8; i++) {
sheet.getCell(startRow + i, startCol + 2).formatter(myFormatter);
}
sheet.setValue(startRow + 2, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 3, startCol + 3, "Other");
sheet.setValue(startRow + 4, startCol + 3, "Other");
sheet.setValue(startRow + 5, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 6, startCol + 3, "Grocery Store");
sheet.setValue(startRow + 7, startCol + 3, "Other");
//set invalid data
sheet.setValue(6, 2, "sss");
sheet.setValue(10, 4, "Sun Store");
}
setValidator(spread) {
let spreadNS = GC.Spread.Sheets;
let sheet = spread.getActiveSheet();
spread.options.highlightInvalidData = true;
//ListValidator
let dv1 = new spreadNS.DataValidation.createListValidator("Fruit,Vegetable,Food");
dv1.inputTitle("Please choose a category:");
dv1.inputMessage("Fruit, Vegetable, Food");
dv1.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: "gold",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideRight,
});
for (let i = 5; i < 11; i++) {
sheet.setDataValidator(i, 2, dv1);
}
//FormulaListValidator
let dv2 = new spreadNS.DataValidation.createFormulaListValidator("$A$5:$A$6")
for (let i = 5; i < 11; i++) {
sheet.setDataValidator(i, 4, dv2);
}
sheet.setValue(14, 0, "ValidationList Comma Support");
//Validation List Support Comma
sheet.setValue(14, 2, "Amount of money");
let dv3 = new GC.Spread.Sheets.DataValidation.createListValidator("123\\,456,234\\,567,789\\,564");
dv3.inputTitle("Please choose a number:");
dv3.inputMessage("Amount of money");
dv3.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color: "green",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.topRight
});
sheet.setDataValidator(14, 2, dv3);
sheet.setValue(14, 4, "Calculation operators");
let dv4 = new GC.Spread.Sheets.DataValidation.createListValidator("\\,,>,<,*,/");
dv4.inputTitle("Please choose a operator:");
dv4.inputMessage("Calculation operators");
dv4.highlightStyle({
type: GC.Spread.Sheets.DataValidation.HighlightType.icon,
color: "yellow",
position: GC.Spread.Sheets.DataValidation.HighlightPosition.outsideLeft,
image: "$DEMOROOT$/spread/source/images/apple.jpg"
});
sheet.setDataValidator(14, 4, dv4);
}
changeHighlightInvalidData(e) {
let highlightInvalidData = e.target.checked;
this.spread.options.highlightInvalidData = highlightInvalidData;
this.setState({highlightInvalidData: highlightInvalidData});
}
}
<!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">
<!-- SystemJS -->
<script src="$DEMOROOT$/ja/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/ja/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 4px;
margin-top: 4px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(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);
