How to create a checkbox column

[John]

My spreadsheet has about 100 rows of data and I'd like to have a checkbox column for each row.  The spreadsheet currently contains a "Y" or "N" in the column, but any values would work for me.  How can I convert this column to display a checkbox rather than the Y/N?  

Additional questions:

While browsing through this forum, I came across a thread by another user that referenced this example:  http://jsplayground.syncfusion.com/jsmc2fst  This example uses version 17.4, while I am using 19.2.  I noticed quite a few differences.  The ribbon menu in 17.4 has many more items than what I see in 19.2 (e.g. AutoSum, Format Painter, Comments, etc.)  Are those no longer available in19.2?

The dropdown column in his example used XLCellType.addCellTypes() to achieve his design.  I don't see that anywhere in the API documentation for 19.2.  Has that been deprecated?

[Gayathri KarunaiAnandam]

Hi John,

We have checked your requirement. We can achieve your requirement by using cell Template. Please check the below code.

[TS]:

let spreadsheet: Spreadsheet = new Spreadsheet({

  sheets: [

    {

      name: 'Car Sales Report',

      ranges: [

        { dataSource: (dataSource as any).defaultData },

        {

          template: '<div><input type="checkbox"  />',

          address: 'C2:C30',

        },

      ],

    },

  ],

  created: (): void => {

    //Applies cell and number formatting to specified range of the active sheet

    spreadsheet.cellFormat(

      { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },

      'A1:F1'

    );

    spreadsheet.numberFormat('$#,##0.00', 'F2:F31');

    var i;

    var index = getRangeIndexes('C2:C31'); // to get the index

    // // for rows iteration

    for (i = index[0]; i < index[2]; i++) spreadsheet.setRowHeight(35, i, 1);

  },

});

 

//Render initialized Spreadsheet component

spreadsheet.appendTo('#spreadsheet');

 

For your reference, please check the below sample.

Sample: https://stackblitz.com/edit/ac69sp?file=index.ts

Please get back to u, if you need further assistance.

Regards,

Gayathri K

[John]

OK, this does display a check box input in the column for each row, as I asked.  But, since the data currently has "Y" and "N" values, how do I initially set the correct value for each check box?  And second, how do I update the data if when the user clicks on the checkboxes.

For example if my data source was like below, how would I make the check the boxes for the "IsUsed" column for rows 1 & 3 initially?   And the last question, can I push the template into the ranges array after the spreadsheet has been created?  Or must I set it on creati

"defaultData": [
       {
            "Customer Name": "Romona Heaslip",
            "Model": "Taurus",
            "IsUsed": "Y",
            "Amount": "8529.22"
        },
        {
            "Customer Name": "Clare Batterton",
            "Model": "Sparrow",
            "IsUsed": "N",
            "Amount": "17866.19"
        },
        {
            "Customer Name": "Eamon Traise",
            "Model": "Grand Cherokee",
            "IsUsed": "Y",
            "Amount": "13853.09"
        }];

[Gayathri KarunaiAnandam]

Hi John,

Query 1:  the data currently has "Y" and "N" values, how do I initially set the correct value for each check box?  And second, how do I update the data if when the user clicks on the checkboxes.

We can set checkbox checked property by using the datasource in beforeCellRender event. We can update changed value in checkbox change event.

Query 2: can I push the template into the ranges array after the spreadsheet has been created?  Or must I set it on creation.

We can render template for spreadsheet after creation. We have prepared a sample in which the checkbox will render when the button is clicked. Please check the code snippet.

Code:

beforeCellRender: (args): void => {

    var elem = args.element.firstElementChild;

 

    let Index = parseInt(args.address.substr(1));

 

    if (args.address.charAt() == 'C' && Index < 9) {

      let value = spreadsheet.sheets[0].rows[Index - 1].cells[2].value;

      let ischeck = value == 'Y' ? true : false;

      new CheckBox(

        {

          value: value,

          checked: ischeck,

          change: function (e: any) {

            var range = getRangeIndexes(spreadsheet.sheets[0].activeCell);

            var checkVal = getCell(range[0], range[1], spreadsheet.sheets[0]);

            checkVal.value = checkVal.value == 'Y' ? 'N' : 'Y';

          },

        },

        elem

      );

    }

  },

});

let button = new Button({});

button.appendTo('#infobtn');

button.element.onclick = (): void => {

  spreadsheet.sheets[0].ranges = [

    { dataSource: (dataSource as any).defaultData },

    {

      template: '<div><input type="checkbox"  />',

      address: 'C2:C8',

    },

  ];

  spreadsheet.refresh();

};

 

 

For your reference, please check the below sample link.

Sample: https://stackblitz.com/edit/9ywfyz?file=index.ts

Please check and get back to us, if you need further assistance.

Regards,

Gayathri K