locked
How To/Sample: Clearing out data in a excel binding RRS feed

  • Question

  • I am trying to find out a way to clear out the data from a binding that I created in a excel worksheet in the context of excel 2013 apps. When I try something like this:

    Office.context.document.bindings.getByIdAsync('MyBinding', function (asyncResult) {
            if (asyncResult.status === "succeeded") {
                asyncResult.value.setDataAsync(new Office.TableData(), { coercionType: "table" });
            }
        });

    I get an error in excel like this:

    How do I go about doing something like this? Please advise.

    Thanks,

    Shahnaz.

    Wednesday, October 3, 2012 7:08 PM

Answers

  • it is documented here http://msdn.microsoft.com/en-us/library/fp161172(v=office.15).aspx if you call this method it clears the data values (and not the headers)..

    there is no way to delete the entire table, you need to clear the table headers by sending an empty string, and then calling deleteAllDataValuesAsync.

    Are you still sending the setData operation? that is the one I assume is returning that error..... make sure to remove it and just call deleteAllDataValuesAsync() 

    • Marked as answer by inahs Friday, October 5, 2012 5:18 PM
    Friday, October 5, 2012 3:43 PM

All replies

  • oh Interesting Inahs!, so you are trying to set an empty table data object to clear the data, this is not a way of doing this, there is a method for table bindings (deleteAllDataValuesAsync) designed to clear all data values from a table.

    the error that you are getting (which is a bug on our side, does not apply on your case), happens because if you try to insert data that overlaps existing data in excel, we fail the call with this error message. This sample ovbiously should return either a different error or clear the table. So great feedback !!! thanks so much....

    let me know how it goes with deleteAllDataValuesAsync..

    thanks!-Juan.

    Thursday, October 4, 2012 6:48 PM
  • Thanks for the reply Juan. For some reason, I didn't see this method in the JS API documentation for Office. But, I still have an issue, this method seems to clear out the row data but the header data stays in place and I still get the same error as well. Is it supposed to clear out the entire table? Please clarify.

    Thanks.

    Thursday, October 4, 2012 9:54 PM
  • it is documented here http://msdn.microsoft.com/en-us/library/fp161172(v=office.15).aspx if you call this method it clears the data values (and not the headers)..

    there is no way to delete the entire table, you need to clear the table headers by sending an empty string, and then calling deleteAllDataValuesAsync.

    Are you still sending the setData operation? that is the one I assume is returning that error..... make sure to remove it and just call deleteAllDataValuesAsync() 

    • Marked as answer by inahs Friday, October 5, 2012 5:18 PM
    Friday, October 5, 2012 3:43 PM
  • I was only looking under the binding object documentation :) and didn't dig into the TableBinding object documentation. Thanks for the pointer.

    Coming back to the issue, I apologize. I didn't realize that further down the code, once the data was cleared, I was again trying to write to the binding and that call was the one throwing that error. Not the deleteAllDataValuesAsync call. A miss on my part. But I will try out your suggestion to clear out the headers by sending an empty string first.

    Thanks for your time and help.

    Shahnaz.

    Friday, October 5, 2012 5:18 PM
  • Awesome let me know how it goes!


    Saturday, October 6, 2012 12:07 AM
  • Hi ,

    "there is no way to delete the entire table, you need to clear the table headers by sending an empty string, and then calling deleteAllDataValuesAsync."

    How to clear table headers by sending an empty string using office.js?  i was unable to do this.

    Monday, November 10, 2014 11:30 AM