Form Control

SpreadJS supports form controls like button, spin button, listBox, comboBox, checkBox, option button etc.

Add form control Link form control with cell In the above example, you may have found that the form control can be linked with a cell. When the value of the form control changes, the cell will also change. This can be done using the following code. Form controls with no value such as a button, label, group box, etc. cannot be linked to cells. Bind event You can listen to the buttonClicked or valueChanged event of the form control. UI Behavior Usually, a left mouse button click means interacting with the form control. In order to select the form control for moving or resizing, you can use ctrl + left mouse click or right mouse click.
import * as React from 'react'; import * as ReactDOM from 'react-dom'; import './styles.css'; import { AppFunc } from './app-func'; // import { App } from './app-class'; // 1. Functional Component sample ReactDOM.render(<AppFunc />, document.getElementById('app')); // 2. Class Component sample // ReactDOM.render(<App />, document.getElementById('app'));
import * as React from "react"; import { SpreadSheets, Worksheet } from "@mescius/spread-sheets-react"; import "@mescius/spread-sheets-shapes"; import GC from "@mescius/spread-sheets"; export function AppFunc() { return ( <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={(spread) => initWorkbook(spread)}> <Worksheet></Worksheet> </SpreadSheets> </div> </div> ); } function initWorkbook(workbook) { workbook.suspendPaint(); initSheet1(workbook.getSheet(0)); workbook.resumePaint(); } function initSheet1(sheet) { setData(sheet); setStyle(sheet); setFormControl(sheet); } function setData(sheet) { var departmentData = ["Sales", "Editorial", "Research", "HR", "Finance", "Product", "Marketing", "Purchasing"]; for (var i = 0; i < departmentData.length; i++) { sheet.setValue(i, 12, departmentData[i]); } sheet.setValue(1, 2, "liming"); sheet.setArray(21, 0, [ ["name", "age", "gender", "like", null, "department"], [null, null, null, "basketball", "football", null], ]); sheet.setArray(23, 0, [["=C2", "=C3", '=CHOOSE(A20, "male", "female")', '=IF(C20,"yes","no")', '=IF(D20, "yes", "no")', "=INDEX(M1:M8,B20)"]], true); sheet.setArray(0, 13, [ ["item1", 67], ["item2", 159], ["item3", 177], ["item4", 93], ["item5", 166], ["item6", 91], ["item7", 57], ["item8", 107], ["item9", 92], ["item10", 175], ]); for (var i = 1; i <= 6; i++) { sheet.setFormula(i, 7, "=INDEX(N" + i + ":N10, P1)"); sheet.setFormula(i, 8, "=INDEX(O" + i + ":O10, P1)"); } } function setStyle(sheet) { sheet.defaults.colWidth = 70; sheet.setColumnWidth(5, 80); sheet.getRange(1, 0, 13, 5).backColor("#bdd7ee"); sheet.getRange(21, 0, 2, 6).backColor("#bdd7ee"); sheet.getRange(1, 2, 1, 2).hAlign(1); sheet.getRange(21, 0, 3, 6).hAlign(1); sheet.getRange(21, 0, 3, 6).vAlign(1); sheet.addSpan(1, 2, 1, 2); sheet.addSpan(21, 3, 1, 2); sheet.addSpan(21, 0, 2, 1); sheet.addSpan(21, 1, 2, 1); sheet.addSpan(21, 2, 2, 1); sheet.addSpan(21, 5, 2, 1); sheet.conditionalFormats.addDataBarRule( GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.automin, null, GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.automax, null, "green", [new GC.Spread.Sheets.Range(1, 8, 6, 1)] ); } function setFormControl(sheet) { var shapes = sheet.shapes; var nameLabel = shapes.addFormControl("name", GC.Spread.Sheets.Shapes.FormControlType.label, 84, 20, 55, 20); nameLabel.text("name:"); var ageLabel = shapes.addFormControl("age", GC.Spread.Sheets.Shapes.FormControlType.label, 96, 40, 55, 20); ageLabel.text("age:"); var ageSpinBtn = shapes.addFormControl("spin", GC.Spread.Sheets.Shapes.FormControlType.spinButton, 210, 40, 70, 26); var options = ageSpinBtn.options(); (options.cellLink = "C3"), (options.minValue = 1); options.maxValue = 120; ageSpinBtn.options(options); ageSpinBtn.value(18); var genderGroupBox = shapes.addFormControl("gender", GC.Spread.Sheets.Shapes.FormControlType.groupBox, 70, 80, 210, 60); genderGroupBox.text("gender"); var male = shapes.addFormControl("male", GC.Spread.Sheets.Shapes.FormControlType.optionButton, 90, 105, 90, 20); male.text("male"); male.value(true); var female = shapes.addFormControl("female", GC.Spread.Sheets.Shapes.FormControlType.optionButton, 180, 105, 90, 20); female.text("female"); options = female.options(); (options.cellLink = "A20"), female.options(options); var likeGroupBox = shapes.addFormControl("like", GC.Spread.Sheets.Shapes.FormControlType.groupBox, 70, 160, 210, 60); likeGroupBox.text("like"); var basketball = shapes.addFormControl("basketball", GC.Spread.Sheets.Shapes.FormControlType.checkBox, 90, 185, 90, 20); basketball.text("basketball"); options = basketball.options(); (options.cellLink = "C20"), basketball.options(options); var football = shapes.addFormControl("football", GC.Spread.Sheets.Shapes.FormControlType.checkBox, 180, 185, 90, 20); football.text("football"); options = football.options(); (options.cellLink = "D20"), football.options(options); var departmentLabel = shapes.addFormControl("department label", GC.Spread.Sheets.Shapes.FormControlType.label, 69, 240, 80, 20); departmentLabel.text("department:"); var department = shapes.addFormControl("department", GC.Spread.Sheets.Shapes.FormControlType.comboBox, 150, 240, 130, 20); options = department.options(); options.cellLink = "B20"; options.inputRange = "M1:M8"; options.dropDownLines = 5; department.options(options); department.value(1); var scrollBar = shapes.addFormControl("scroll bar", GC.Spread.Sheets.Shapes.FormControlType.scrollBar, 642, 21, 24, 116); options = scrollBar.options(); options.cellLink = "P1"; options.minValue = 1; options.maxValue = 5; scrollBar.options(options); scrollBar.value(1); var listBox = shapes.addFormControl("listBox", GC.Spread.Sheets.Shapes.FormControlType.listBox, 500, 200, 180, 100); options = listBox.options(); options.cellLink = "B20"; options.inputRange = "M1:M8"; listBox.options(options); var button = shapes.addFormControl("button", GC.Spread.Sheets.Shapes.FormControlType.button, 500, 380, 160, 100); button.text("Click me"); sheet.bind(GC.Spread.Sheets.Events.FormControlButtonClicked, function (s, args) { alert("button clicked..."); }); }
import * as React from "react"; import { SpreadSheets, Worksheet } from "@mescius/spread-sheets-react"; import "@mescius/spread-sheets-shapes"; import GC from "@mescius/spread-sheets"; const Component = React.Component; export class App extends Component { constructor(props) { super(props); this.spread = null; } render() { return ( <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={(spread) => initWorkbook(spread)}> <Worksheet></Worksheet> </SpreadSheets> </div> </div> ); } } function initWorkbook(workbook) { workbook.suspendPaint(); initSheet1(workbook.getSheet(0)); workbook.resumePaint(); } function initSheet1(sheet) { setData(sheet); setStyle(sheet); setFormControl(sheet); } function setData(sheet) { var departmentData = ["Sales", "Editorial", "Research", "HR", "Finance", "Product", "Marketing", "Purchasing"]; for (var i = 0; i < departmentData.length; i++) { sheet.setValue(i, 12, departmentData[i]); } sheet.setValue(1, 2, "liming"); sheet.setArray(21, 0, [ ["name", "age", "gender", "like", null, "department"], [null, null, null, "basketball", "football", null], ]); sheet.setArray(23, 0, [["=C2", "=C3", '=CHOOSE(A20, "male", "female")', '=IF(C20,"yes","no")', '=IF(D20, "yes", "no")', "=INDEX(M1:M8,B20)"]], true); sheet.setArray(0, 13, [ ["item1", 67], ["item2", 159], ["item3", 177], ["item4", 93], ["item5", 166], ["item6", 91], ["item7", 57], ["item8", 107], ["item9", 92], ["item10", 175], ]); for (var i = 1; i <= 6; i++) { sheet.setFormula(i, 7, "=INDEX(N" + i + ":N10, P1)"); sheet.setFormula(i, 8, "=INDEX(O" + i + ":O10, P1)"); } } function setStyle(sheet) { sheet.defaults.colWidth = 70; sheet.setColumnWidth(5, 80); sheet.getRange(1, 0, 13, 5).backColor("#bdd7ee"); sheet.getRange(21, 0, 2, 6).backColor("#bdd7ee"); sheet.getRange(1, 2, 1, 2).hAlign(1); sheet.getRange(21, 0, 3, 6).hAlign(1); sheet.getRange(21, 0, 3, 6).vAlign(1); sheet.addSpan(1, 2, 1, 2); sheet.addSpan(21, 3, 1, 2); sheet.addSpan(21, 0, 2, 1); sheet.addSpan(21, 1, 2, 1); sheet.addSpan(21, 2, 2, 1); sheet.addSpan(21, 5, 2, 1); sheet.conditionalFormats.addDataBarRule( GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.automin, null, GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.automax, null, "green", [new GC.Spread.Sheets.Range(1, 8, 6, 1)] ); } function setFormControl(sheet) { var shapes = sheet.shapes; var nameLabel = shapes.addFormControl("name", GC.Spread.Sheets.Shapes.FormControlType.label, 84, 20, 55, 20); nameLabel.text("name:"); var ageLabel = shapes.addFormControl("age", GC.Spread.Sheets.Shapes.FormControlType.label, 96, 40, 55, 20); ageLabel.text("age:"); var ageSpinBtn = shapes.addFormControl("spin", GC.Spread.Sheets.Shapes.FormControlType.spinButton, 210, 40, 70, 26); var options = ageSpinBtn.options(); (options.cellLink = "C3"), (options.minValue = 1); options.maxValue = 120; ageSpinBtn.options(options); ageSpinBtn.value(18); var genderGroupBox = shapes.addFormControl("gender", GC.Spread.Sheets.Shapes.FormControlType.groupBox, 70, 80, 210, 60); genderGroupBox.text("gender"); var male = shapes.addFormControl("male", GC.Spread.Sheets.Shapes.FormControlType.optionButton, 90, 105, 90, 20); male.text("male"); male.value(true); var female = shapes.addFormControl("female", GC.Spread.Sheets.Shapes.FormControlType.optionButton, 180, 105, 90, 20); female.text("female"); options = female.options(); (options.cellLink = "A20"), female.options(options); var likeGroupBox = shapes.addFormControl("like", GC.Spread.Sheets.Shapes.FormControlType.groupBox, 70, 160, 210, 60); likeGroupBox.text("like"); var basketball = shapes.addFormControl("basketball", GC.Spread.Sheets.Shapes.FormControlType.checkBox, 90, 185, 90, 20); basketball.text("basketball"); options = basketball.options(); (options.cellLink = "C20"), basketball.options(options); var football = shapes.addFormControl("football", GC.Spread.Sheets.Shapes.FormControlType.checkBox, 180, 185, 90, 20); football.text("football"); options = football.options(); (options.cellLink = "D20"), football.options(options); var departmentLabel = shapes.addFormControl("department label", GC.Spread.Sheets.Shapes.FormControlType.label, 69, 240, 80, 20); departmentLabel.text("department:"); var department = shapes.addFormControl("department", GC.Spread.Sheets.Shapes.FormControlType.comboBox, 150, 240, 130, 20); options = department.options(); options.cellLink = "B20"; options.inputRange = "M1:M8"; options.dropDownLines = 5; department.options(options); department.value(1); var scrollBar = shapes.addFormControl("scroll bar", GC.Spread.Sheets.Shapes.FormControlType.scrollBar, 642, 21, 24, 116); options = scrollBar.options(); options.cellLink = "P1"; options.minValue = 1; options.maxValue = 5; scrollBar.options(options); scrollBar.value(1); var listBox = shapes.addFormControl("listBox", GC.Spread.Sheets.Shapes.FormControlType.listBox, 500, 200, 180, 100); options = listBox.options(); options.cellLink = "B20"; options.inputRange = "M1:M8"; listBox.options(options); var button = shapes.addFormControl("button", GC.Spread.Sheets.Shapes.FormControlType.button, 500, 380, 160, 100); button.text("Click me"); sheet.bind(GC.Spread.Sheets.Events.FormControlButtonClicked, function (s, args) { alert("button clicked..."); }); }
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('$DEMOROOT$/en/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: 100%; height: 100%; overflow: hidden; float: left; } input { padding: 4px 6px; } .options-row { font-size: 14px; padding: 5px; margin-top: 10px; } 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; } #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-react': 'npm:@mescius/spread-sheets-react/index.js', '@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'react': 'npm:react/umd/react.production.min.js', 'react-dom': 'npm:react-dom/umd/react-dom.production.min.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);