Skip to main content Skip to footer

Checkbox in header toggles all checkboxes with SpreadJS

Background:

Using SpreadJS, our JavaScript spreadsheet API, developers can implement a checkbox in the column header to toggle all of the checkboxes that are within that column by using SpreadJS’s CellClick event and the CheckBox cell type.

Steps to Complete:

1. Add checkbox to column header

2. Bind the CellClick event

3. Get ColumnHeader value

Getting Started:

Step 1: Add checkbox to column header

First, we will add a checkbox to the column header to column A like so:

sheet.setCellType( 0, 0, cellType, GC.Spread.Sheets.SheetArea.colHeader);
sheet.setValue(0, 0,"",GC.Spread.Sheets.SheetArea.colHeader);

Then apply the checkBox cell type to all of the cells within column A like so:

sheet.setCellType(-1, 0, cellType, GC.Spread.Sheets.SheetArea.viewport);

Step 2: Bind the CellClick event

Bind the cellClick event to the SpreadJS instance so that when a user checks/unchecks the column header area the event is triggered, apply this like so:      

function initSpread(spread) {
            var sheet = spread.getSheet(0);
            // Step 1: Apply checkbox cell types to col header and cells within the column
            sheet.suspendPaint();
            var cellType = new GC.Spread.Sheets.CellTypes.CheckBox();
            sheet.setCellType(-1, 0, cellType, GC.Spread.Sheets.SheetArea.viewport);
            sheet.setCellType( 0, 0, cellType, GC.Spread.Sheets.SheetArea.colHeader);
            sheet.setValue(0, 0,"",GC.Spread.Sheets.SheetArea.colHeader);
            sheet.resumePaint();
            // Step 2: Bind cell click event to SJS instance
            sheet.bind(GC.Spread.Sheets.Events.CellClick, function (sender, args) {
                
            })
        }

Step 3: Get ColumnHeader value to toggle checkboxes

Within the CellClick event, check if the colHeader that was clicked is the checkbox column:

          // Step 3: get ColumnHeader Value to toggle checkboxes   
          sheet.bind(GC.Spread.Sheets.Events.CellClick, function (sender, args){  
                    if(args.col== '0'){
                        
                    }
            })
        }

With this example, if the user has clicked on column 'A', this is at index 0, get the value of the column header to see what to toggle the rest of the checkboxes to. The value of the check box in the column header will be stored in the check variable:

          // Step 3: get ColumnHeader Value to toggle checkboxes 
          sheet.bind(GC.Spread.Sheets.Events.CellClick, function (sender, args) {
                if(args.sheetArea == GC.Spread.Sheets.SheetArea.colHeader){  
                    if(args.col== '0'){
                        sheet.suspendPaint();
                        // If the value of the column header is == 0 then set the value to 1 is not then set to 0
                        var check = sheet.getValue(0, 0, GC.Spread.Sheets.SheetArea.colHeader) == 0 ? 1 : 0;

                        sheet.resumePaint();
                    }
                }
            })

Finally we will set the value of the checkboxes in that column and the column header check box to the check variable to ensure they are the same and to toggle between checked and unchecked:

function initSpread(spread) {
            var sheet = spread.getSheet(0);
            // Step 1: Apply checkbox cell types to col header and cells within the column
            sheet.suspendPaint();
            var cellType = new GC.Spread.Sheets.CellTypes.CheckBox();
            sheet.setCellType(-1, 0, cellType, GC.Spread.Sheets.SheetArea.viewport);
            sheet.setCellType( 0, 0, cellType, GC.Spread.Sheets.SheetArea.colHeader);
            sheet.setValue(0, 0,"",GC.Spread.Sheets.SheetArea.colHeader);
            sheet.resumePaint();
            
            // Step 2: Bind cell click event to SJS instance
            sheet.bind(GC.Spread.Sheets.Events.CellClick, function (sender, args) {
                if(args.sheetArea == GC.Spread.Sheets.SheetArea.colHeader){
                    // Step 3: get ColumnHeader Value to toggle checkboxes  
                    if(args.col== '0'){
                        // suspend paint
                        sheet.suspendPaint();
                        // use check to toggle; if the value of the column header is == 0 then set the value to 1 is not then set to 0
                        var check = sheet.getValue(0, 0, GC.Spread.Sheets.SheetArea.colHeader) == 0 ? 1 : 0;
                        // Based on the sheet's row count set, the value of the celltype check boxes in the column to the variable checked 
                        for(var r=0; r < sheet.getRowCount(); r++) sheet.setValue( r, 0 ,check,GC.Spread.Sheets.SheetArea.viewport);
                        // Set the value of the column headers check box tot eh check variable
                        sheet.setValue(0, 0, check, GC.Spread.Sheets.SheetArea.colHeader);
                        sheet.resumePaint();
                    }
                }
            })
        }

Outcome:

After applying the above shared code logic, the SpreadJS instance will now show checkboxes in all the cells of column A. When a user clicks on the check box in column A’s header, all the checkboxes within that column with toggle depending on the column headers check box:


Here is a link to a live sample showing this: https://codesandbox.io/s/sjs-js-toggle-checkboxes-from-column-header-x7hte

Mackenzie Albitz