Snippets - Tables

link - docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-tables


Tables - Creating

Excel.run(function (context) { 
   const currentWsh = context.workbook.worksheets.getActiveWorksheet()
// const context.workbook.tables.add('Sheet1!A1:E7', true);
   const myTable = currentWsh.tables.add("A1:D1",
                                        true /*has Headers*/ );
   myTable.getHeaderRowRange().Values =
      [ [ "col1, "col2", "col3", "col4"] ];
   myTable.rows.add
      (null /*add at the end*/,
      [ [ "one", "11", "one-1", "1-1"],
      [ [ "two", "22", "two-2", "2-2"],
      [ [ "three", "33", "three-3", "3-3"],
   ]);
   myTable.columns.getItemAt(3).getRange().numberFormat = [['@']];
   myTable.getRange().format.autofit.Columns();
   myTable.getRange().format.autofit.Rows();

                return context.sync();
}).catch(function (error) {
   console.log(error);
});

Tables - Reading

Excel.run(function (context) { 
                var tableRows = context.workbook.tables.getItemAt(0).rows.load("values");
                return context.sync().then(function () {
                                for (var i = 0; i < tableRows.items.length; i++) {
                                                console.log(tableRows.items[i].values);
                                }
                                console.log("done");
                });
}).catch(function (error) {
   console.log(error);
});

Tables - Add Row

Excel.run(function (context) { 
                context.workbook.tables.getItem('Table1').rows.add(3, [[1,2,3,4,5]]);
                return context.sync();
}).catch(function (error) {
   console.log(error);
});

Tables - Format Row

Excel.run(function (context) { 
                var range = context.workbook.tables.getItem('Table1').rows.getItemAt(1).getRange();
                range.format.fill.color = "#00AA00";
                return context.sync();
}).catch(function (error) {
   console.log(error);
});

Tables - Delete Row

Excel.run(function (context) { 
                context.workbook.tables.getItem('Table1').rows.getItemAt(3).delete();
                return context.sync();
}).catch(function (error) {
   console.log(error);
});

Tables - Read All

Excel.run(function (context) { 
                var tables = context.workbook.tables.load("name");
                return context.sync().then(function() {
                                for (var i = 0; i < tables.items.length; i++)
                                {
                                                console.log(tables.items[i].name);
                                }
                                console.log("done");
                });
}).catch(function (error) {
   console.log(error);
});

Table - Writing

Create a TableData object.

var myTable = new Office.TableData(); 
myTable.headers = ["First Name","Last Name","Grade"];
myTable.rows = [["Lisa","Simpson","A"], ["Bart","Simpson","C"],
               ["Homer","Simpson","B"]];

// Set the myTable in the document.
Office.context.document.setSelectedDataAsync(myTable,
                                             function (asyncResult) {
if (asyncResult.status == "failed") {
showMessage("Action failed with error: " + asyncResult.error.message);
}
});

Be aware that a table can only be written into the spreadsheet if no other data in the surrounding cells will be overwritten.


Table - Reading

Read a table from the document and display its content.

Office.context.document.getSelectedDataAsync("table", 
                                             function (asyncResult) {
  if (asyncResult.status == "failed") {
    showMessage("Action failed with error: " + asyncResult.error.message);
  }
  else {
    showMessage("Headers: " + asyncResult.value.headers + " Rows: " +
    asyncResult.value.rows);
  }
});

The following example uses the getSelectedDataAsync method to read the currently selected table. The CoercionType.Table parameter specifies the expected data type. The callback function returns an object containing the data currently selected by the user, which can be accessed by the asyncResult.value property.


Table - Updating Row

Create a new table

var myTable = new Office.TableData(); 
myTable.headers = ["City", "State"];
myTable.rows = [["Phoenix","AZ"],["Raleigh","NC"], ["Nashville","TN"],
               ["Boston","MA"]];

//Write myTable to the document
Office.context.document.setSelectedDataAsync(myTable,
                                             function (asyncResult) {
if (asyncResult.status == "failed") {
showMessage("Action failed with error: " + asyncResult.error.message);
}
});

Filtered - Visible Rows

'create a table binding
Office.context.document.bindings.addFromNamedItemAsync
   ("Table1", "table", {id : "myTableBinding1"
      }, function(asyncResult){
         //handle any errors
      });

Office.select("Bindings#MyTableBinding1").getDataAsync({
   coercionType : "table",
   filterType : "onlyVisible"
}, function (asyncResult) {
   var values = (asyncResult.value.rows);
});

This is the new way to do it

var table = tables.getItemAt(0); 
var visibleView = table.getRange().getVisibleView();
context.load(visibleView);
context.sync() then (function () {
   var values = visibleView.values;
});



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext