CODEWITHSUNDEEP
javascriptexcel-2013office-addinsoffice-js
Mikael Johansson
Mikael Johansson

Reputation: 359

Clear a sheet in Excel 2013 with office add-ins

I have an office add-in that generates a report (tables). The second time the user is running it I want to clear that sheet.

I have been trying around and have read through the documentation but I cant find a way to do this.

Have anyone found a solution to solve something like this?

Upvotes: 2

Views: 1562

Answers (2)

gogosweb
gogosweb

Reputation: 101

I know it is an old post but you can use the below function to clear your generates report tables on Excel 2013.

    DeleteTableDataOnExcel2013(tableID, callback) {
        Office.context.document.goToByIdAsync(tableID, Office.GoToType.Binding, function (asyncResult) {
            Office.context.document.bindings.getByIdAsync(tableID, function (getByIdAsyncResult) {
                if (getByIdAsyncResult.status === Office.AsyncResultStatus.Succeeded) {
                    const binding = getByIdAsyncResult.value;
                    binding.clearFormatsAsync();
                    binding.setFormatsAsync({ cellFormat: { cells: { row: Office.Table.Headers }, format: { backgroundColor: '#FFFFFF', fontColor: '#000000', fontFamily: 'Verdana', fontSize: 12, wrapping: true, fontStyle: 'normal', width: 8.43, height: 15 } } }, function (formatResult) {
                        binding.deleteAllDataValuesAsync();
                        Office.context.document.bindings.releaseByIdAsync(tableID, function (releaseAsyncResult) {
                            callback();
                        });
                    });
                } else {
                    callback();
                }
            });
        });
    }

Upvotes: 0

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

Use sheet.getRange().clear() method.

Example: 

Excel.run(function (ctx) {
    var sheet = ctx.workbook.worksheets.getActiveWorksheet();
    sheet.getRange().clear();
    return ctx.sync();
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

Upvotes: 4

Related Questions