none
Excel AddIn - Adding rows to an existing table RRS feed

  • 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