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 an addRows method in Table namespace. 
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.

Does it need 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.                  

Answer 1

Hi AndreTrg,

According to your description, you want to replace the existing row, I'm not sure if my understanding is correct.

>>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: 

Base on my  experience, I'm afraid there is no better solution to achieve it.

For more information, please see the following links:

Add row to existing table in Excel with Javascript API

Adding table rows and columns in JavaScript

>>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? 

Yes, you could to fill these new columns with empty data.

Hopefully it helps you.

Best Regards,

Lina

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

Answer 2

Hi AndreTrg,

 

Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

 

Thank you for understanding. If you have any question, or update, please feel free to let us know.

 

I wish you a happy life!

 

Best Regards,

 

Lina

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

Answer 3

Hello Lina-MSFT, 

thank you for your answer. 

Using Excel Javascript API, I want to create an Excel table with some data, then update the table with new data (which can be less or more rows, compared to the first run). Using ranges or tables, if the user adds another table, or any data under the table created via Excel API, the first table will overlap the second one after some adds / removal data. 

So, I can't dispose tables one below the other (e.g.: the first table starts at cell A1 (and it's 5 rows) and another one at cell A10). 

If you consider it a bug, could you open an issue to the Office-js team on GitHub please? 

---

Another question: why should I use TableBinding rather than the Table API? Both APIs use add/delete methods. 

Best regards, 

AndreaTrg

Answer 4

Don't you have any news about my issue? 

Thanks, 

Andrea

Answer 5

Hi AndreTrg,

 

Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

 

Thank you for understanding. If you have any question, or update, please feel free to let us know.

 

I wish you a happy life!

 

Best Regards,

 

Lina

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

Hello Lina-MSFT, 

do you have any update related to my question? 

Can you forward the question to someone else in case? 

Thank you in advance, 

AndreaTrg