Events for manging Row, Cell data and information

[Myles Renaud]

Good afternoon Syncfusion

I have been testing various events to gain a better understanding on how I might leverage them to get control of specific elements in relation to the spreadsheet and the overall dataSource as well as individual cell and row data.

• Is there any Event I can leverage for Cell Click? I see the API Migration that Click is now Select
  • This works for some use cases but the argument is only bringing back the range. This also triggers when navigating with the keyboard.
  • Ideally I would like to get the Cell information on a Click event, similar to the information you get from the queryCellInfo event and spreadsheetObj.getData, as well as other information I am yet to find such as cell and column data types
• Is there a way to identify a specific Cells data to the Datasource?
  • Is this something I need to map manually and find?
• Looking at the Add and Delete Rows API I couldn't find a way to track deleted Rows or Added.
  • I would like to track newly added Rows as well as Deleted for other purposes.
• To set specific columns/cellls to readonly/disabled is there a way to do this without protecting the entire sheet and enabling specific columns and cells.

Ultimately I am trying to find the best approach to get access to each cell and the related information including the columns/headers and types. I have some custom requirements based on column data types as well as other CRUD requirements I would like to implement while maintaining the spreadsheet to use the baseline features included.

Much appreciated!

[Sridhar Alagiri Samy]

Hi Myles Renaud,

Query 1: Is there any Event I can leverage for Cell Click? I see the API Migration that Click is now Select

Query 3: Looking at the Add and Delete Rows API I couldn't find a way to track deleted Rows or Added.

Currently we are checking the possible solution for these requirements in our spreadsheet, and we will update soon.

Query 2: Is there a way to identify a specific Cells data to the Datasource?

You can use our find or findAll method to find the cells that matches with specific cell data.

Please refer to the below UG for more details regarding the find support,

https://ej2.syncfusion.com/angular/documentation/spreadsheet/searching#find

If we misunderstood your requirement, please share the below details which will be helpful for us to validate and provide you a better solution for this query,

• Please elaborate more on this requirement with some examples use cases.

• Whether you want to get the cell details as row data from the spreadsheet.

• How your are binding data into the spreadsheet, whether you are binding as data source using the dataSource property, or importing an Excel file, or loading as JSON data.

Query 4: To set specific columns/cellls to readonly/disabled is there a way to do this without protecting the entire sheet and enabling specific columns and cells.

We have checked your reported query to disable the editing in the specific columns or cells. And you can prevent the edit action for particular cells using the cellEdit event of our Spreadsheet component. The cellEdit will gets triggered when we the cell is being edited. In that, by setting the argument cancel property to true by checking the cell address it will prevent the edit action for the particular cell.

For your convenience, we have prepared the sample and attached along with the code snippet below for your reference,

cellEditHandler(eventArgs) {

        // Get the current editing column index from the cell address.

        const colIndex = getRangeIndexes(eventArgs.address)[1];

        //Check the column Index that you want to disable editing.

        if (this.columns[colIndex] && this.columns[colIndex].readOnly) {

            // Set the cancel property to true to prevent the cell editing.

            eventArgs.cancel = true;

        }

}

Also, we have prevented the UI interactions for modifying the cell value through editing, autofill, cell delete, paste, clear, etc. for the cells that are considered read-only. We can use the beforeCellUpdate event to prevent modifying the cell values.

// This event will be triggered if any properties of the cell model are going to be updated.

const beforeCellUpdateHandler = (eventArgs) => {

        ….

        if (sheetOptions.columns[eventArgs.colIndex].readOnly) {

            if (eventArgs.cell && !isNullOrUndefined(eventArgs.cell.value)) { // Checking whether the cell value is getting changed.

                eventArgs.cancel = true; // By setting this property as true the cell update like paste, autofill, delete, etc. will be prevented.

            }

        }

        ….

};

Stackblitz sample: https://stackblitz.com/edit/angular-zdkjp4?file=src%2Fapp.component.ts

In the above sample, we have defined the sample level of columns collection with the property of readOnly as true or false. We have considered the first two columns as read-only. And we have restricted all the UI interactions for modifying the cell value like editing, autofill, cell delete, paste, clear, etc.

Regards,

Sridhar.

[Janakiraman Sakthivel]

Hi Myles Renaud,

Query 1: Is there any Event I can leverage for Cell Click? I see the API Migration that Click is now Select.

We have validated your requirement, and by using the below shared codes, you can get the clicked cell information, like the queryCellInfo event. In the below shared codes, we have assigned the click event to the sheet panel element in our spreadsheet in the created event. And while clicking on the cells, you can get the required information by using the below codes.

For your convenience, we have shared the code snippets and sample below.

CODE SNIPPET:

created() {

        this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');

        const sheetPanel: HTMLElement = this.spreadsheetObj.element.querySelector('.e-sheet-panel');

        sheetPanel.addEventListener('click', this.clickHandler.bind(this));

    }

    clickHandler(args) {

        let sheet: SheetModel = this.spreadsheetObj.getActiveSheet();

        let selectedCellAddress: string = sheet.activeCell;

        let selectedCellIndexes: number[] = getCellIndexes(selectedCellAddress);

        let selectedCell: CellModel = getCell(selectedCellIndexes[0], selectedCellIndexes[1], sheet);

        let result: object = { cell: selectedCell, address: selectedCellAddress, rowIndex: selectedCellIndexes[0], colIndex: selectedCellIndexes[1] , sheetIndex: sheet.index };

        console.log("Selected Cell Info:", result);

    }

Query 3: Looking at the Add and Delete Rows API I couldn't find a way to track deleted Rows or Added.

You can find the added and deleted row details in the actionComplete event to achieve your requirements. For your convenience, we have shared the code snippets and sample below. In the below shared sample, we have logged the added and deleted rows and columns indexes in the console for your reference.

CODE SNIPPET:

actionComplete(args) {

        if (args.action === "insert") {

            if (args.eventArgs.modelType === "Column") {

                this.insertedColumnsIndexes.push({ "startIndex" : args.eventArgs.index, "endIndex" : (args.eventArgs.index + (args.eventArgs.model.length - 1))});

                console.log("Inserted Columns:", this.insertedColumnsIndexes);

            } else if (args.eventArgs.modelType === "Row") {

                this.insertedRowsIndexes.push({ "startIndex" : args.eventArgs.index, "endIndex" : (args.eventArgs.index + (args.eventArgs.model.length - 1))});

                console.log("Inserted Rows:", this.insertedRowsIndexes);

            }

        } else if (args.action === "delete") {

            if (args.eventArgs.modelType === "Column") {

                this.DeletedColumnsIndexes.push({ "startIndex" : args.eventArgs.startIndex, "endIndex" : args.eventArgs.endIndex });

                console.log("Deleted Columns:", this.DeletedColumnsIndexes);

            } else if (args.eventArgs.modelType === "Row") {

                this.DeletedRowsIndexes.push({ "startIndex" : args.eventArgs.startIndex, "endIndex" : args.eventArgs.endIndex });

                console.log("Deleted Rows:", this.DeletedRowsIndexes);

            }

        }

    }

Sample: https://stackblitz.com/edit/angular-mkvitt-sab9vc?file=src%2Fapp.component.ts

Please get back to us if you need any further clarifications

[Myles Renaud]

Thank you very much for the examples and information, I am working through these at the moment and will Mark and Answer or ask any follow up questions in the coming week.

[Babu Periyasamy]

Hi Myles Renaud,

Thanks for your update. Take your time, we will wait to hear from you.