How do I clear a spreadsheet programmatically

How do I clear a spreadsheet programmatically

Posted by: alex.grant1 on 22 March 2024, 3:43 am EST

• 

  alex.grant1

  • Post Options:
  • Link
    Copy

  Posted 22 March 2024, 3:43 am EST

  Hi,

  I have added a custom button to the toolbar ribbon which should clear the active spreadsheet when clicked. Is this possible?

  This is the code I have so far

  export function addResetButton(config) {
    config.commandMap = {
      cmdResetSpreadsheet: {
        title: 'Reset Spreadsheet',
        text: '',
        type: 'button',
        iconClass: 'cmdResetSpreadsheet',
        bigButton: false,
        commandName: 'cmdResetSpreadsheet',
        execute: async () => {
         // add code here to clear active spreadsheet
        },
      },
    };
  }

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 25 March 2024, 4:43 pm EST

  Hi,

  As per my understanding, your requirement is to clear the content of the active sheet in the spread when the button is clicked.

  For this, the worksheet.clear() method could be utilized. This method clears the content(data, style, comment, tag, etc) from the cell range in the sheet and allows to clear specific types of content.

  For more understanding, kindly refer to the attached sample.

  sample: https://jscodemine.grapecity.com/share/8vdGrkU1HkSweiUR0Q-Qog/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Also, if you want to clear all the sheets of the Workbook/spread, the workbook.clearSheets() method can be used.

  References:

  worksheet.clear(): https://developer.mescius.com/spreadjs/api/v17/classes/GC.Spread.Sheets.Worksheet#clear
  
  workbook.clearSheets(): https://developer.mescius.com/spreadjs/api/v17/classes/GC.Spread.Sheets.Workbook#clearsheets
  
  StorageType enum: https://developer.mescius.com/spreadjs/api/v17/enums/GC.Spread.Sheets.StorageType

  Regards

• 

  alex.grant1

  • Post Options:
  • Link
    Copy

  Posted 25 March 2024, 8:03 pm EST

  Based on the links in your reply I Updated my code and added the clearSheets code in the execute async call but it does not work. The sheet does not get cleared and the data remains.

  export function addResetButton(config) {
    config.commandMap = {
      cmdResetSpreadsheet: {
        title: 'Reset Spreadsheet',
        text: '',
        type: 'button',
        iconClass: 'cmdResetSpreadsheet',
        bigButton: false,
        commandName: 'cmdResetSpreadsheet',
        execute: async () => {
          const spread = new GC.Spread.Sheets.Workbook();
          spread.clearSheets();
        },
      },
    };
  }

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 26 March 2024, 4:35 pm EST

  Hi,

  The sheets are not cleared because you are creating a new Workbook instance and clearing sheets on that newly created Workbook instance. You need to perform the clearSheets() method on the existing Workbook instance. Refer to the below code snippet.

  config.commandMap = {
      cmdResetSpreadsheet: {
          title: 'Reset Spreadsheet',
          text: 'Reset Spreadsheet',
          type: 'button',
          // iconClass: 'cmdResetSpreadsheet',
          // bigButton: false,
          commandName: 'cmdResetSpreadsheet',
          execute: async (context) => {
              const workbook = context.getWorkbook();
  
              workbook.suspendPaint();
              workbook.suspendEvent();
              workbook.suspendCalcService();
  
              // Clear all the sheets in the spreadSheet
              workbook.clearSheets();
  
              workbook.resumeCalcService();
              workbook.resumeEvent();
              workbook.resumePaint();
  
              console.log('reset spread', arguments);
          },
      },
  }

  Please note that the workbook.clearSheets() method removes all the sheets from the Workbook instance. If you want to clear only the content of the all the sheets then you can individually clear the content of each sheet as shown in the sample. For more understanding, refer to the sample.

  sample: https://jscodemine.grapecity.com/share/7tHH8NsGCU_YVAkvIcQmaw/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Regards