Excel AddIn - Adding rows to an existing table

Question

I'm having some troubles while using the Javascript Excel API to create an Excel AddIn.

First issue: Adding rows to an existing table with the Excel Js library: I create a table and add some rows; then the user can update table content with new data coming from a REST service (so resulting table rows can change: increase / decrease, or be the same).

tl;dr; I need to replace table rows with new ones.

That seems pretty simple: there's a addRows method in Table namespace (reference). But this won't work as expected: if the table already contains rows new ones will be added to the end, not replacing the existing ones.

Here the code:

const currentWorksheet = context.workbook.worksheets.getItemOrNullObject(
  "Sheet1"
)
let excelTable = currentWorksheet.tables.getItemOrNullObject(tableName)
if (excelTable.isNullObject) {
  excelTable = currentWorksheet.tables.add(tableRange, true /* hasHeaders */)
  excelTable.name = tableName
  excelTable.getHeaderRowRange().values = [excelHeaders]
  excelTable.rows.add(null, excelData)
} else {
  excelTable.rows.add(0, excelData)
}

I also tried to delete old rows, then adding new ones.

if (!excelTable.isNullObject) {
  for (let i = tableRows - 1; i >= 0; i--) {
    // Deletes all table rows
    excelTable.rows.items[i].delete()
  }

  excelTable.rows.add(0, excelData)
}

But .. it works fine only if there isn't content below the columns of the table (no functions, other tables and so on).

I tried another method: using ranges. The first time I create the table, next ones I delete all rows, get the range of new data and insert the values:

if (excelTable.isNullObject) {
  excelTable = currentWorksheet.tables.add(tableRange, true /* hasHeaders */)
  excelTable.name = tableName
  excelTable.getHeaderRowRange().values = [excelHeaders]
  excelTable.rows.add(null, excelData)
} else {
  let actualRange, newDataRange
  const tableRows = excelTable.rows.items.length
  const tableColumns = excelTable.columns.items.length
  const dataRows = excelData.length
  const dataColumns = excelData[0].length

  actualRange = excelTable.getDataBodyRange()

  for (let i = tableRows - 1; i >= 0; i--) {
    // Deletes all table rows
    excelTable.rows.items[i].delete()
  }

  newDataRange = actualRange.getAbsoluteResizedRange(dataRows, tableColumns)

  newDataRange.values = excelData
}

But there are still drawbacks with this solution.

It needs to be so hard to add/edit/remove rows in an Excel table?

Second issue: Using the same table, if the user decides to add some 'extra' columns (with a formula based on table values e.g.), do I need to fill this new columns with null data?

const tableColumns = excelTable.columns.items.length
const dataRows = excelData.length
const dataColumns = excelData[0].length

if (tableColumns > dataColumns) {
  let diff = tableColumns - dataColumns

  for (let i = 0; i < diff; i++) {
    for (let j = 0; j < dataRows; j++) {
      excelData[j].push(null)
    }
  }
}

Excel API can't handle this scenario?

Please, could you help me?

Thank you in advance.

Does it expose Resize method and then write new data to that? Should overwrite existing. — QHarr
@QHarr No, there are only 2 options: you can delete() the table or add() / delete() single rows — ATrogolo
I think the ranges option above is your best option (create the table, next time delete all rows, get the range of new data and insert the values). It's a bit clumsy I agree, but gets the job done. You mentioned there are drawbacks with that approach - any other concerns beyond the fact it's a bit clumsy? — Doug Mahugh
Thank you @DougMahugh! Yes, it's very clumsy, but it isn't the only issue: if I have another table under the one that I'm updating, the table will move up or down related to rows that I'm adding / removing. After several data reload the lower table will overlap the first one. Any suggestion related to this problem? — ATrogolo
@Michael Zlatkovsky: do you have any suggestion about my issue? — ATrogolo

XuanZhouMSFT XuanZhouMSFT · Accepted Answer · 2018-11-21T11:57:14

Thanks for your reporting. Add table row API is just adding row to the table rows, not replace it. What's more. I can't repro the issue with delete rows. Can you show me more details?

Excel AddIn - Adding rows to an existing table

1 Answers