Asked by:
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.
Thursday, November 15, 2018 8:57 AM
All replies
-
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.
- Edited by Lina-MSFT Friday, November 16, 2018 2:43 AM
Friday, November 16, 2018 2:42 AM -
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.
Monday, November 19, 2018 9:03 AM -
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
- Edited by AndreTrg Monday, November 19, 2018 2:38 PM Adding related question
Monday, November 19, 2018 2:14 PM -
Don't you have any news about my issue?
Thanks,
Andrea
Monday, December 3, 2018 4:47 PM -
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
Friday, December 14, 2018 8:18 AM