Use the following code to create a checkbox list cell:
The Checkbox List has same methods with Radio List.
Example:
You can use the mode method to get and set checkbox list mode. You can set "checkbox" or "toggle" to cellType.
You can use the toggleOptions method to get and set the toggle options of a checkbox list. Please refer to GC.Spread.Sheets.CellTypes.IToggleOptions for parameter types.
var spread;
window.onload = function () {
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
initEvent(spread);
readSetting(new GC.Spread.Sheets.CellTypes.CheckBoxList());
};
function initCellStyle(fontSize, foreColor = '#000000', backColor = '#ffffff', bold=true) {
const style = new GC.Spread.Sheets.Style();
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
style.font = `${bold ? 'bold' : 'normal'} ${fontSize}pt Calibri`;
style.foreColor = foreColor;
style.backColor = backColor;
return style;
}
function initLayoutStyle(sheet) {
sheet.setRowCount(11);
sheet.setColumnCount(10);
sheet.options.gridline.showHorizontalGridline = false;
sheet.options.gridline.showVerticalGridline = false;
sheet.setRowHeight(0, 30);
sheet.setRowHeight(1, 50);
sheet.setRowHeight(2, 40);
sheet.setRowHeight(3, 40);
sheet.setRowHeight(4, 40);
sheet.setRowHeight(5, 40);
sheet.setRowHeight(6, 40);
sheet.setRowHeight(7, 40);
sheet.setRowHeight(8, 40);
sheet.setRowHeight(9, 40);
sheet.setRowHeight(10, 30);
sheet.setColumnWidth(0, 30);
sheet.setColumnWidth(1, 110);
sheet.setColumnWidth(2, 100);
sheet.setColumnWidth(3, 100);
sheet.setColumnWidth(4, 100);
sheet.setColumnWidth(5, 100);
sheet.setColumnWidth(6, 100);
sheet.setColumnWidth(7, 100);
sheet.setColumnWidth(8, 100);
sheet.setColumnWidth(9, 30);
sheet.addSpan(1, 1, 1, 8);
sheet.setValue(1, 1, "Inventory List");
sheet.getCell(1, 1).setStyle(initCellStyle(24, "Text 2 -50", "Text 2 40"));
sheet.setValue(2, 1, "Filter Row");
sheet.getCell(2, 1).setStyle(initCellStyle(11, "Text 2 -50", "Text 2 40"));
sheet.addSpan(2, 2, 1, 7);
sheet.getCell(2, 2).setStyle(initCellStyle(11, "Text 2 -50", "Text 2 80"));
sheet.getCell(2, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left);
sheet.setValue(3, 1, "Filter Row");
sheet.getCell(3, 1).setStyle(initCellStyle(11, "Text 2 -50", "Text 2 40"));
sheet.addSpan(3, 2, 1, 7);
sheet.getCell(3, 2).setStyle(initCellStyle(11, "Text 2 -50", "Text 2 80"));
sheet.getCell(3, 2).hAlign(GC.Spread.Sheets.HorizontalAlign.left);
}
function initData(sheet) {
sheet.setValue(4, 1, "Inventory ID");
sheet.setValue(4, 2, "Name");
sheet.setValue(4, 3, "Unit Price");
sheet.setValue(4, 4, "Quantity In Stock");
sheet.setValue(4, 5, "Inventory Value");
sheet.setValue(4, 6, "Reorder Level");
sheet.setValue(4, 7, "Reorder Time In Days");
sheet.setValue(4, 8, "Reorder Quantity");
sheet.setValue(2, 2, ['1', '2', '3', '4', '5']);
sheet.setValue(3, 2, ['1', '2', '3', '4', '5']);
sheet.setValue(5, 1, "IN0001");
sheet.setValue(5, 2, "Item 1");
sheet.setValue(5, 3, "51");
sheet.setValue(5, 4, "25");
sheet.setValue(5, 5, "1275");
sheet.setValue(5, 6, "29");
sheet.setValue(5, 7, "13");
sheet.setValue(5, 8, "50");
sheet.setValue(6, 1, "IN0002");
sheet.setValue(6, 2, "Item 2");
sheet.setValue(6, 3, "93");
sheet.setValue(6, 4, "132");
sheet.setValue(6, 5, "12276");
sheet.setValue(6, 6, "231");
sheet.setValue(6, 7, "4");
sheet.setValue(6, 8, "50");
sheet.setValue(7, 1, "IN0003");
sheet.setValue(7, 2, "Item 3");
sheet.setValue(7, 3, "57");
sheet.setValue(7, 4, "151");
sheet.setValue(7, 5, "8607");
sheet.setValue(7, 6, "114");
sheet.setValue(7, 7, "11");
sheet.setValue(7, 8, "150");
sheet.setValue(8, 1, "IN0004");
sheet.setValue(8, 2, "Item 4");
sheet.setValue(8, 3, "75");
sheet.setValue(8, 4, "62");
sheet.setValue(8, 5, "4650");
sheet.setValue(8, 6, "39");
sheet.setValue(8, 7, "12");
sheet.setValue(8, 8, "50");
sheet.setValue(9, 1, "IN0005");
sheet.setValue(9, 2, "Item 5");
sheet.setValue(9, 3, "100");
sheet.setValue(9, 4, "100");
sheet.setValue(9, 5, "10000");
sheet.setValue(9, 6, "100");
sheet.setValue(9, 7, "10");
sheet.setValue(9, 8, "100");
for(var i = 1; i < 9; i++) {
sheet.getCell(4, i).setStyle(initCellStyle(11, "Accent 1 -75", "Text 2 60"));
for(var j = 5; j < 10; j++) {
sheet.getCell(j, i).setStyle(initCellStyle(11, undefined, "Accent 1 80", false));
}
}
for (let i = 4; i < 10; i++) {
for(var j = 1; j < 10; j++) {
sheet.getCell(i, j).hAlign(GC.Spread.Sheets.HorizontalAlign.left).vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.getCell(i, j).wordWrap(true);
}
}
}
function bindEvent(spread) {
let sheet = spread.getActiveSheet();
var range = new GC.Spread.Sheets.Range(5, 1, 5, 1);
var rowFilter = new GC.Spread.Sheets.Filter.HideRowFilter(range);
sheet.rowFilter(rowFilter);
rowFilter.filterButtonVisible(false);
spread.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
if ((info.row === 2 || info.row === 3) && info.col === 2) {
if (info.row === 2) {
sheet.setValue(3, 2, info.newValue);
} else {
sheet.setValue(2, 2, info.newValue);
}
rowFilter.removeFilterItems(1);
if (info.newValue.length > 0) {
for(var i = 0; i < info.newValue.length; i++) {
var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {
compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo,
expected: 'IN000' + info.newValue[i]
});
rowFilter.addFilterItem(1, condition);
}
}
rowFilter.filter(1);
sheet.invalidateLayout();
sheet.repaint();
}
});
}
function initSpread(spread) {
var sheet = spread.getSheet(0);
sheet.suspendPaint();
bindEvent(spread);
initLayoutStyle(sheet);
var radio = new GC.Spread.Sheets.CellTypes.CheckBoxList();
radio.items([
{ text: "IN0001", value: "1" },
{ text: "IN0002", value: "2" },
{ text: "IN0003", value: "3" },
{ text: "IN0004", value: "4" },
{ text: "IN0005", value: "5" },
]);
sheet.setCellType(2, 2, radio);
sheet.getCell(2, 2).vAlign(GC.Spread.Sheets.VerticalAlign.center);
var toggle = new GC.Spread.Sheets.CellTypes.CheckBoxList();
toggle.items([
{ text: "IN0001", value: "1" },
{ text: "IN0002", value: "2" },
{ text: "IN0003", value: "3" },
{ text: "IN0004", value: "4" },
{ text: "IN0005", value: "5" },
]);
toggle.mode('toggle');
toggle.direction(GC.Spread.Sheets.CellTypes.Direction.vertical);
toggle.toggleOptions({
width: 30,
height: 15,
animationDuration: 300,
trackColorOn: "Text 2 40",
trackColorOff: "Background 2 -10",
sliderColorOn: "Text 2 -50",
sliderColorOff: "Background 1",
});
sheet.setCellType(3, 2, toggle);
sheet.getCell(3, 2).vAlign(GC.Spread.Sheets.VerticalAlign.center);
initData(sheet);
sheet.resumePaint();
};
function initEvent(spread) {
spread.bind(GC.Spread.Sheets.Events.SelectionChanged, function () {
var sheet = spread.getActiveSheet();
var row = sheet.getActiveRowIndex();
var column = sheet.getActiveColumnIndex();
var cellType = sheet.getCellType(row, column);
if (cellType instanceof GC.Spread.Sheets.CellTypes.CheckBoxList) {
readSetting(cellType);
} else {
readSetting(new GC.Spread.Sheets.CellTypes.CheckBoxList());
}
});
_getElementById("apply").addEventListener('click', function () {
applySetting();
});
_getElementById("addItem").addEventListener('click', function () {
var select = _getElementById("items");
var d = new Date();
d.setMonth(d.getMonth() + select.length);
var str = d.getFullYear() + "-" + (d.getMonth() + 1);
_getElementById("items").add(new Option(str, select.length));
});
_getElementById("removeItem").addEventListener('click', function () {
var select = _getElementById("items");
select[select.length - 1].remove();
});
_getElementById("direction-horizontal").addEventListener('change', function () {
refreshMaxCountVisible();
});
_getElementById("direction-vertical").addEventListener('change', function () {
refreshMaxCountVisible();
});
_getElementById("isFlowLayout").addEventListener('change', function () {
refreshMaxCountVisible();
});
_getElementById("boxSize").addEventListener('change', function () {
applySetting();
})
_getElementById("checkBoxListMode").addEventListener('change', function () {
const mode = _getElementById("checkBoxListMode").value;
const cellType = new GC.Spread.Sheets.CellTypes.CheckBoxList();
if(mode === 'toggle') {
cellType.mode('toggle');
} else {
cellType.mode('checkbox');
}
readSetting(cellType);
})
_getElementById("toggleCheckBoxCellAutoSize").addEventListener('change', function (e) {
_getElementById("toggleCheckBoxCellWidth").toggleAttribute('disabled', e.target.checked);
_getElementById("toggleCheckBoxCellHeight").toggleAttribute('disabled', e.target.checked);
})
};
function readSetting(cellType) {
var select = _getElementById("items");
select.options.length = 0;
var items = cellType.items();
for(var i=0; i<items.length; i++){
select.add(new Option(items[i].text, items[i].value));
}
if(cellType.direction() === GC.Spread.Sheets.CellTypes.Direction.horizontal) {
_getElementById("direction-horizontal").checked = true;
} else {
_getElementById("direction-vertical").checked = true;
}
if (cellType.mode() === 'toggle') {
_getElementById("toggleOptions").style.display = 'block';
_getElementById("checkboxListBoxSize").style.display = 'none';
_getElementById("checkBoxListMode").value = 'toggle';
_getElementById("checkboxTextAlign").style.display = 'none';
_getElementById("toggleTextAlign").style.display = 'block';
} else {
_getElementById("toggleOptions").style.display = 'none';
_getElementById("checkboxListBoxSize").style.display = 'block';
_getElementById("checkBoxListMode").value = 'checkbox';
_getElementById("checkboxTextAlign").style.display = 'block';
_getElementById("toggleTextAlign").style.display = 'none';
}
const textAlignId = cellType.mode() === 'toggle' ? 'toggle-textAlign-' : 'textAlign-';
if(cellType.textAlign() === GC.Spread.Sheets.CellTypes.TextAlign.right) {
_getElementById(textAlignId + 'right').checked = true;
} else if(cellType.textAlign() === GC.Spread.Sheets.CellTypes.TextAlign.inside) {
_getElementById(textAlignId + 'inside').checked = true;
} else {
_getElementById(textAlignId + 'left').checked = true;
}
_getElementById("isFlowLayout").checked = cellType.isFlowLayout();
_getElementById("rowCount").value = cellType.maxRowCount();
_getElementById("columnCount").value = cellType.maxColumnCount();
_getElementById("space-horizontal").value = cellType.itemSpacing().horizontal;
_getElementById("space-vertical").value = cellType.itemSpacing().vertical;
_getElementById("boxSize").value = cellType.boxSize();
refreshMaxCountVisible();
}
function refreshMaxCountVisible() {
if (_getElementById("isFlowLayout").checked) {
_getElementById("rowCountDiv").style.display="none";
_getElementById("columnCountDiv").style.display="none";
} else if (_getElementById("direction-vertical").checked) {
_getElementById("rowCountDiv").style.display="block";
_getElementById("columnCountDiv").style.display="none";
} else {
_getElementById("rowCountDiv").style.display="none";
_getElementById("columnCountDiv").style.display="block";
}
}
function applySetting() {
var sheet = spread.getActiveSheet();
var row = sheet.getActiveRowIndex();
var column = sheet.getActiveColumnIndex();
var cellType = new GC.Spread.Sheets.CellTypes.CheckBoxList();
const mode = _getElementById("checkBoxListMode").value;
cellType.mode(mode);
var items = [];
var select = _getElementById("items");
for (var i = 0; i < select.length; i++) {
items.push({ text: select[i].text, value: select[i].value });
}
cellType.items(items);
if (_getElementById("direction-horizontal").checked) {
cellType.direction(GC.Spread.Sheets.CellTypes.Direction.horizontal);
} else {
cellType.direction(GC.Spread.Sheets.CellTypes.Direction.vertical);
}
cellType.isFlowLayout(_getElementById("isFlowLayout").checked);
if (_getElementById("rowCount").value) {
cellType.maxRowCount(_getElementById("rowCount").value);
}
if (_getElementById("columnCount").value) {
cellType.maxColumnCount(_getElementById("columnCount").value);
}
cellType.itemSpacing({
horizontal: _getElementById("space-horizontal").value,
vertical: _getElementById("space-vertical").value
});
if (mode === 'toggle') {
const sliderRadius = _getElementById("toggleCheckBoxCellSliderRadius").value;
const trackRadius = _getElementById("toggleCheckBoxCellTrackRadius").value;
cellType.toggleOptions({
autoSize: _getElementById("toggleCheckBoxCellAutoSize").checked,
width: _getElementById("toggleCheckBoxCellWidth").value - 0,
height: _getElementById("toggleCheckBoxCellHeight").value - 0,
sliderMargin: _getElementById("toggleCheckBoxCellSliderMargin").value - 0,
sliderRadius: sliderRadius === '' ? null : (sliderRadius - 0),
trackRadius: trackRadius === '' ? null : (trackRadius - 0),
sliderColorOn: _getElementById("toggleCheckBoxCellSliderColorOn").value,
sliderColorOff: _getElementById("toggleCheckBoxCellSliderColorOff").value,
trackColorOn: _getElementById("toggleCheckBoxCellTrackColorOn").value,
trackColorOff: _getElementById("toggleCheckBoxCellTrackColorOff").value,
animationDuration: _getElementById("toggleCheckBoxCellAnimationDuration").value - 0,
});
if (_getElementById("toggle-textAlign-right").checked) {
cellType.textAlign(GC.Spread.Sheets.CellTypes.TextAlign.right);
} else if (_getElementById("toggle-textAlign-inside").checked) {
cellType.textAlign(GC.Spread.Sheets.CellTypes.TextAlign.inside);
} else {
cellType.textAlign(GC.Spread.Sheets.CellTypes.TextAlign.left);
}
} else {
if (_getElementById("textAlign-right").checked) {
cellType.textAlign(GC.Spread.Sheets.CellTypes.TextAlign.right);
} else {
cellType.textAlign(GC.Spread.Sheets.CellTypes.TextAlign.left);
}
var boxSizeValue = _getElementById("boxSize").value;
var boxSize = Number(boxSizeValue);
if (isNaN(boxSize)) {
cellType.boxSize(boxSizeValue);
} else {
cellType.boxSize(boxSize);
}
}
sheet.getCell(row, column).cellType(cellType);
}
function _getElementById(id) {
return document.getElementById(id);
}
<!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/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.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 class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="option-row">
<p>Change any options below then press the Set button to apply your changes.</p>
<label>Items:</label>
</div>
<div class="option-row">
<select id="items" size="5"></select>
<input type="button" id="addItem" value="Add" />
<input type="button" id="removeItem" value="Remove" />
</div>
<hr>
<div class="option-row">
<label>Mode:</label>
</div>
<div class="option-row">
<select id="checkBoxListMode">
<option value="checkbox" selected="selected">checkbox</option>
<option value="toggle">toggle</option>
</select>
</div>
<div class="option-row">
<label>Direction:</label>
</div>
<div class="option-row">
<input type="radio" name="direction" id="direction-horizontal" checked="checked" /><label for="direction-horizontal">horizontal</label>
<input type="radio" name="direction" id="direction-vertical" /><label for="direction-vertical">vertical</label>
</div>
<div class="option-row">
<label>Text Align:</label>
</div>
<div id="checkboxTextAlign" class="option-row">
<input type="radio" name="textAlign" id="textAlign-left" checked="checked" /><label for="textAlign-left">left</label>
<input type="radio" name="textAlign" id="textAlign-right" /><label for="textAlign-right">right</label>
</div>
<div id="toggleTextAlign" class="option-row" style="display: none;">
<input type="radio" name="textAlign" id="toggle-textAlign-left" checked="checked" /><label for="toggle-textAlign-left">left</label>
<input type="radio" name="textAlign" id="toggle-textAlign-right" /><label for="toggle-textAlign-right">right</label>
<input type="radio" name="textAlign" id="toggle-textAlign-inside" /><label for="toggle-textAlign-inside">inside</label>
</div>
<hr>
<div class="option-row">
<label for="isFlowLayout">Flow Layout</label>
<input type="checkbox" id="isFlowLayout" checked />
</div>
<div class="option-row" id="rowCountDiv">
<label for="rowCount">Max Row Count:</label>
<input type="text" id="rowCount" value="2" />
</div>
<div class="option-row" id="columnCountDiv">
<label for="columnCount">Max Column Count:</label>
<input type="text" id="columnCount" value="2" />
</div>
<div class="option-row">
<label for="space-horizontal">Item horizontal Spacing:</label>
<input type="text" id="space-horizontal" value="8" />
</div>
<div class="option-row">
<label for="space-vertical">Item vertical Spacing:</label>
<input type="text" id="space-vertical" value="2" />
</div>
<hr>
<div id="checkboxListBoxSize" class="option-row">
<label for="boxSize">Box Size</label>
<input type="text" id="boxSize" class="boxSize"/>
</div>
<div id="toggleOptions" class="option-row" style="display: none;">
<label class="toggle-checkbox-cell-auto-size"><input type="checkbox" id="toggleCheckBoxCellAutoSize" /> Auto Size</label>
<label>Width:</label>
<input type="text" id="toggleCheckBoxCellWidth" value="30" />
<label>Height:</label>
<input type="text" id="toggleCheckBoxCellHeight" value="15" />
<label>Slider Margin:</label>
<input type="text" id="toggleCheckBoxCellSliderMargin" value="2" />
<label>Slider Radius:</label>
<input type="text" id="toggleCheckBoxCellSliderRadius" value="" />
<label>Track Radius:</label>
<input type="text" id="toggleCheckBoxCellTrackRadius" value="" />
<label>Slider Color On:</label>
<input type="text" id="toggleCheckBoxCellSliderColorOn" value="#1565c0" />
<label>Slider Color Off:</label>
<input type="text" id="toggleCheckBoxCellSliderColorOff" value="#ffffff" />
<label>Track Color On:</label>
<input type="text" id="toggleCheckBoxCellTrackColorOn" value="#8cbae8" />
<label>Track Color Off:</label>
<input type="text" id="toggleCheckBoxCellTrackColorOff" value="#9e9e9e" />
<label>Animation Duration:</label>
<input type="text" id="toggleCheckBoxCellAnimationDuration" value="300" />
</div>
<div class="option-row">
<input type="button" id="apply" value="Set" />
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.boxSize{
display: block;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.disabled{
opacity: 0.5;
pointer-events: none;
}
.option-row {
font-size: 14px;
padding: 5px;
}
.option-row {
padding-bottom: 5px;
}
label {
display:inline-block;
}
input{
padding: 4px 8px;
box-sizing: border-box;
}
select{
width: 100%;
}
input[type=checkbox]+label {
display: inline-block;
width: auto;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}