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.                  


    Monday, January 7, 2019 4:23 PM