Clearing data in the spreadsheet

[Adi Biton]

Hi,

I'm trying to load a content to the spreadsheet by this way:

spreadsheet.openFromJson

({file:{sheets: [{

    name: 'Car Sales Report',

    columns: [

      { width: 180 }, { width: 130 }, { width: 130 }, { width: 180 },

      { width: 130 }, { width: 120 }

    ]

  }]}});

However, I get the following error:

ej2.min.js:1 Uncaught TypeError: Cannot read properties of undefined (reading 'sheets')

    at e.updateModel (ej2.min.js:1:19608440)

    at e.fetchSuccess (ej2.min.js:1:19607949)

    at e.open (ej2.min.js:1:19606424)

    at e.notify (ej2.min.js:1:770567)

    at t.notify (ej2.min.js:1:876687)

    at t.openFromJson (ej2.min.js:1:19991327)

Could you please tell me what's wrong in the accepted json?

Second, I'm trying to clear the data in the spreadsheet. I was trying to use the function clear(). however, it expects to get the parameter "range", but the range can be changed from time to time according to the loaded file, and I did not find a way to send the coulmn count an the row count (that if I have them then I could simply send them to the function clear())

Therefore, I thought to use the function openFromJson and send an empty json, but this results in the error above. 

Could you please suggest the best way to implement it?

Thanks,

Adi

[Babu Periyasamy]

Hi Adi Biton,

Getting error while using openFromJson method:

We reviewed your query along with the provided code snippets. After examining your code, we observed that you are directly passing the sheets array to the file option in the `openFromJson` method, which is causing the issue. To resolve this, the openFromJson method requires the Workbook object to be passed to the file option, as shown in the screenshot below.

 

For your convenience, we have prepared the Javascript Spreadsheet sample in which we have opened the Workbook object using the openFromJson method and attached the sample below for your reference,

Sample link: https://stackblitz.com/edit/vqhtf3-e8j2yx?file=index.js

For more information regarding the JSON structure in Spreadsheet, please refer the below documentation,

https://ej2.syncfusion.com/javascript/documentation/spreadsheet/how-to/create-a-object-structure

Clear data by passing range.

We have reviewed your query about obtaining a range based on data in the Spreadsheet to use with the `clear` method. To retrieve the data range, you can use the usedRange property of each sheet, which provides the used row and column indexes. With these indexes, you can construct the range address using the `getRangeAddress` utility method.

For your convenience, we have prepared a sample that demonstrates clearing data based on the usedRange property using the clear method, triggered by a button labeled `Clear data`. The sample and a code snippet are attached below for your reference.

Code snippet:

 

<button id="clearDataButton">Clear data</button>

 

function clearData() {

        // Get the active sheet.

        const sheet = spreadsheet.getActiveSheet();

        // Get the used range of the active sheet.

        const usedRange = sheet.usedRange;

   

        // Convert the used range to a cell address format using getRangeAddress.

        const usedRangeAddress = ej.spreadsheet.getRangeAddress([0, 0, usedRange.rowIndex, usedRange.colIndex]);

   

        // Clear the data within the used range.

        spreadsheet.clear({type: 'Clear All', range: `${sheet.name}!${usedRangeAddress}` });

        //Reset the select range to default.

        spreadsheet.selectRange('A1:A1');

    }

   

    // Add event listener for button click.

    document.getElementById('clearDataButton').addEventListener('click', clearData);

Sample link: https://stackblitz.com/edit/vqhtf3-es75nf?file=index.js

For more information, please refer the below documentation,

https://ej2.syncfusion.com/javascript/documentation/spreadsheet/cell-range#clear

Kindly check the above details and get back to us if you need further clarifications.

[Adi Biton]

Thank you for your response.

I implemented the code as per your suggestion. However, in certain scenarios, the last command (spreadsheet.selectRange('A1:A1');) throws the following error:

ej2.min.js:1 Uncaught TypeError: Cannot read properties of undefined (reading 'toString')

    at e.updateCell (ej2.min.js:1:20083953)

    at e.updateEditedValue (ej2.min.js:1:20082908)

    at e.endEdit (ej2.min.js:1:20088686)

    at e.performEditOperation (ej2.min.js:1:20062944)

    at e.notify (ej2.min.js:1:770567)

    at t.notify (ej2.min.js:1:876687)

    at t.selectRange (ej2.min.js:1:20769220)

For instance, if I add a chart, then write something in any cell, and subsequently clear the data, the spreadsheet becomes unresponsive, the chart is not deleted, and I am unable to perform any further actions.

Could you please explain why this issue occurs and suggest how I might resolve it?

Thank you,

[Dinakar Manickam]

Hi Adi Biton,

We have reviewed your reported query, and based on your query, we suspect that the script error occurs when you attempt to clear data from the spreadsheet while another cell is in editing mode.

To resolve this issue, we addressed the script error by invoking the endEdit() method of the spreadsheet, ensuring that it exits editing mode before performing the clear operation. Additionally, we would like to inform you that the clear() method only removes formats, content, and hyperlinks from the spreadsheet.

After performing the clear operation, we deleted the charts from the sheet by iterating over the chartCollection using the deleteChart method of our spreadsheet component.

For your reference, we have modified the previously shared sample and included the updated code snippet along with a video demonstration below.

Sample Link: https://stackblitz.com/edit/vqhtf3-huwsmb?file=index.js

Note: We have highlighted the changes in the code snippet below for your reference.

Code Snippet:

  function clearData() {

        // To know if the spreadsheet is in editing state.

        if(spreadsheet.isEdit){

            spreadsheet.endEdit();

        }

        // Get the active sheet.

        const sheet = spreadsheet.getActiveSheet();

        // Get the used range of the active sheet.

        const usedRange = sheet.usedRange;

        // Convert the used range to a cell address format using getRangeAddress.

        const usedRangeAddress = ej.spreadsheet.getRangeAddress([0, 0, usedRange.rowIndex, usedRange.colIndex]);

 

        // Clear the data within the used range.

        spreadsheet.clear({type: 'Clear All', range: `${sheet.name}!${usedRangeAddress}` });

        //Reset the select range to default.

        spreadsheet.selectRange('A1:A1');

 

        // Delete the charts from the current sheet.

        const chartsToRemove = spreadsheet.chartColl.filter(chart => chart.range.startsWith(sheet.name));

        chartsToRemove.forEach(chart => {

            spreadsheet.deleteChart(chart.id);

        });

   

    }

 

Video Demonstration: Please refer to the attachment below.

Kindly check the above details and feel free to contact us for further clarifications.

Regards,
Dinakar M

Attachment: Forum__195043_a6bf013c.zip

[Adi Biton]

Hi,

Thank you for your response.

I understand that each object added to the spreadsheet, such as a chart, needs to be cleared individually.

However, is there an alternative method to clear the entire spreadsheet, such as programmatically loading an empty spreadsheet?

Thanks,

[Adi Biton]

Hi,

I would like to know if you have an alternative method to clear the entire spreadsheet.

Thank you in advance

[Janakiraman Sakthivel]

Hi Adi Biton,

We recommend using the refresh() method with the parameter isNew set to true to create a new workbook in the spreadsheet. This will help you achieve your requirement of clearing the entire spreadsheet, similar to loading an empty spreadsheet.

Note: We kindly inform you that when creating a new workbook using the refresh() method as mentioned above, the old sheets will be deleted, and a new sheet will be rendered with the default name 'Sheet1'.

For your reference, we have included a button labeled "Refresh" in the sample. When clicking this button, the refresh() method is called with the parameter isNew set to true. A sample and the corresponding code snippet are shared below for your reference.

Sample: J9phmk (forked) - StackBlitz

CODE SNIPPET:

<button id="refresh">Refresh</button>

 

function refreshSpreadsheet() {

    spreadsheet.refresh(true); // Assign "true" to create a new workbook in the spreadsheet.

}

 

When maintaining multiple sheets, if you want to remove only the data from the active sheet while preserving the data in the remaining sheets and keeping all sheets with their original names, you can achieve this using the code snippets shared below, as demonstrated in the sample provided.

Sample: J9phmk (forked) - StackBlitz

CODE SNIPPET:

function refreshSpreadsheet() {

    // Get the active sheet.

    let sheets = [];

    const activeSheetIdx = spreadsheet.activeSheetIndex; // Preserve the active sheet index to maintain the active sheet properly

    Object.assign(sheets, spreadsheet.sheets);

    const sheetsLength = sheets.length;

 

    spreadsheet.refresh(true); // Assign the value "true" to the parameter isNew to create a new workbook in the spreadsheet.

 

    setTimeout(function () {

        for (var idx = sheetsLength - 1; idx >= 0; idx--) {

            // Insert the new empty sheet with the name of your previously active sheet

            if (activeSheetIdx === idx) {

                spreadsheet.insertSheet([{ name: sheets[idx].name }]);

            } else {

                // Insert the remaining sheets from the previously maintained sheets model, except the active sheet, to maintain as they were maintained.

                spreadsheet.insertSheet([sheets[idx]]);

            }

        }

        setTimeout(function () {

            // Delete the sheet that is rendered when calling the refresh() method.

            if (sheetsLength < spreadsheet.sheets.length) {

                spreadsheet.delete(sheetsLength, sheetsLength, 'Sheet');

            }

            // Set the active sheet of the spreadsheet to maintain the active sheet properly.

            spreadsheet.activeSheetIndex = activeSheetIdx; // Restore the active sheet index

            spreadsheet.dataBind();

        });

    });

}

 

In the above sample, we preserved the previously maintained sheets model in the local variable sheets and stored the active sheet index in the variable activeSheetIdx before calling the refresh() method. After creating the new workbook, you can insert a new empty sheet with the name of the previously maintained active sheet using the insertSheet() method to achieve the requirement of clearing the data from the active sheet.

To maintain the remaining sheets, we suggest inserting the remaining sheets from the previously maintained sheets model into the new workbook using the insertSheet() method. This ensures they are preserved with their original data.

Finally, we recommend deleting the default sheet that is rendered when calling the refresh() method using the delete() method.

Kindly check the samples and details shared above on your end and get back to us if you need further clarification regarding this. We hope this helps.

Regards,
Janakiraman S.