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?
