0

The SharePoint Excel Web Part client side API ,Ewa (https://msdn.microsoft.com/en-us/library/office/ee589018.aspx) does not seem to have any methods to manipulate an Excel Table (i.e. a ListObject in VBA parlance)

The Ewa API does have a Range object, so if I were to get the Range of the Table, I can make changes to the cells of the table. I can even add a row to the end of the table by simple writing data to the row directly below the table. Excel automatically adds a new Table row. Some example code follows:

Get the table:

// Get a reference to Table named "Table1" in the first Excel Web Part found on the page:
    const ewa = [....].EwaControl.getInstances().getItem(0);
    const wb = ewa.getActiveWorkbook();
    const namedItems = wb.getNamedItems();

    const table = namedItems.getItemByName('Table1');

    if (table.getNamedItemType() !== Ewa.NamedItemType.Table) {
        return;
    }

Get the Range:

Get the Range associated with the table, then create a new range which is a row directly below the table, and then right write to it. This ends up adding a row to the table.

    table.getRefersToRangeAsync((asyncResult) => {
        var rng = asyncResult.getReturnValue();

        const firstRow = rng.getRow() + rng.getRowCount();
        const firstColumn = rng.getColumn();
        const rowCount = 1;
        const columnCount = rng.getColumnCount();
        var sheet = rng.getSheet();

        var rng2 = sheet.getRange(firstRow, firstColumn, rowCount, columnCount);

        var values = new Array(1);

        values[0] = new Array(4);
        values[0][0] = "one";
        values[0][1] = "two";
        values[0][2] = "three";
        values[0][3] = "four";

        rng2.setValuesAsync(values,(asyncResult) => {
            document.getElementById('outputDiv').innerHTML =
                `Succeeded: ${asyncResult.getSucceeded()}`;
        }, null);

    }, table);

But there doesn't seem to be a way to delete a row from the sheet.

Does anyone know of an API to do that?

0

I agree the EWA namespace does not have a way to insert/delete rows or format cells, etc. I don't think Microsoft has updated the EWA namespace for a while.

The newer (and actively supported) JavaScript API for Office v1.1 does have the ability to insert rows: https://msdn.microsoft.com/en-us/library/office/fp142185%28v=office.15%29.aspx?f=255&MSPPError=-2147217396. See the newest Excel add-ins JavaScript API reference capabilities: https://github.com/OfficeDev/office-js-docs/blob/master/excel/excel-add-ins-javascript-reference.md

I hope Microsoft adds the capabilities of the JavaScript API for Office into the EWA namespace because there are key scenarios that it is better for (e.g. embedding on a public website)

  • I can't use the new API because it's designed for Office AddIns. The API si not compatible with Ewa. What's worse, the Ewa API does make calls to the server side, to an internal API which does support it, because the same API is used from the full Excel WebApp. You can see it in the browser's debugger. The method is called something like InsertDeleteRow or something. I asked them to give me the to non minimized version of Ewa, but they can't. – zumalifeguard Dec 15 '15 at 22:44
  • Hopefully that means Microsoft is considering adding new capabilities to EWA. – Andrew Hall Dec 16 '15 at 4:17
  • I have no way to delete rows, but I did develop a hacky/fragile way to hide rows based on cell/formula values. It involves using an Excel filter and jquery to monitor html div values and to navigate through the filter dialogs to update the filter. – Andrew Hall Dec 16 '15 at 4:29
  • Thank you ExcelAnalyst, but what i really need is for the rows to be removed from the table because the table is a data-source to a pivot-report on another sheet. The current workaround I'm planning on is clearing all the data in the rows and then filtering out blank rows in the pivot report. Also take a look at this question about Ewa. stackoverflow.com/questions/33621787/… – zumalifeguard Dec 16 '15 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.