none
Excel API - Is there a way to add table to workbook at an exact cell without it saving room for the table header row? RRS feed

  • Question

  • Hi,

    I am trying to output a table at an exact cell location without a table header but the API's all seem to leave room for a header even if I specify tableHeader: false.  So if I am trying to put table data without a header at cell C3, the APIs actually put the data starting at C4 and with a blank row at C3.

    I have tried the following with coercionType = table and  "tableHeader: false" in options

    Office.context.document.setSelectedDataAsync(tableData, options, ...

    Office.select("bindings#123").setDataAsync(tableData, options, ...

    I have a workaround to insert a dummy row, add the table, convert it to a range, and then delete the dummy row. Ugly and I lose the table binding.

    Thanks,

    Jim

    Tuesday, May 10, 2016 12:45 PM

Answers

  • Hi Jim,

    Thanks the detail information for this issue.

    And yes, the code would leave the blank space as you mentioned above. Another workaround is that move the selection to the one cell above and then create the table using code above.

    Here is the code for your reference:

    function createTableFromLastLine() {
            Excel.run(function (ctx) {
                var selectedRange = ctx.workbook.getSelectedRange();
                var temRange = selectedRange.getOffsetRange(-1, 0);
                temRange.select();
                return ctx.sync().then(function () {
                    
                    createTable();
                });
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
        }
    
        function createTable() {
            var tableData = new Office.TableData();
            //tableData.headers = [['header1']];
            tableData.rows = [['row1'], ['row2'], ['row3']];
         
            
            Office.context.document.setSelectedDataAsync(tableData, { coercionType: Office.CoercionType.Table, tableOptions: { headerRow: false } }, function (asyncResult) {
            
            })
        }

    There is a limitation in this workaround that we are not able to create the table start from first row. To fix this issue, we can create the table without the headers and delete the line if it is start from second line. Here is the modifying code for your reference:

     function createTableFromLastLine() {
            Excel.run(function (ctx) {
                var selectedRange = ctx.workbook.getSelectedRange();
                var temRange = selectedRange.getOffsetRange(-1, 0);
                temRange.select();
                return ctx.sync().then(function () {
                    
                    createTable();
                });
            }).catch(function (error) {
                createTable();
                deleteTheLine();
                
            });
        }
    
        function deleteTheLine() {
            Excel.run(function (ctx) {
                var selectedRange = ctx.workbook.getSelectedRange();
                var temRange = selectedRange.getOffsetRange(-1, 0);
                var temRow = temRange.getRow(0);
                temRow.delete();
                return ctx.sync().then(function () {
                   
                });
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
        }
    
     function createTable() {
            var tableData = new Office.TableData();
            //tableData.headers = [['header1']];
            tableData.rows = [['row1'], ['row2'], ['row3']];
         
            
            Office.context.document.setSelectedDataAsync(tableData, { coercionType: Office.CoercionType.Table, tableOptions: { headerRow: false } }, function (asyncResult) {
            
            })
        }

    And if you want the Excel add-in API to support create the table without headers and the table range start from the select cell, you can try to submit the feedback from here.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, May 13, 2016 6:19 AM
    Moderator

All replies

  • Hi Jim,

    >>Excel API - Is there a way to add table to workbook at an exact cell without it saving room for the table header row?

    Yes. Here is an example works well for me:

       function createTableOld() {
            var tableData = new Office.TableData();
            //tableData.headers = [['header1']];
            tableData.rows = [['row1'], ['row2'], ['row3']];
                         Office.context.document.setSelectedDataAsync(tableData, { coercionType: Office.CoercionType.Table, tableOptions: { headerRow: false } }, function (asyncResult) {
            
            })
        }

    Please let me know whether it works for you.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 11, 2016 6:20 AM
    Moderator
  • Hi Fei,

    Thanks for the try but your code still inserts a blank row above the data (at least in my version of Excel).

    If I select cell A1 then call the function the data starts at row 2 in the sheet.

    A1: <blank>

    A2: row1

    A3: row2

    A4: row3

    Any other thoughts on this?

    Thanks

    Jim

    Wednesday, May 11, 2016 1:12 PM
  • Hi Jim,

    Thanks the detail information for this issue.

    And yes, the code would leave the blank space as you mentioned above. Another workaround is that move the selection to the one cell above and then create the table using code above.

    Here is the code for your reference:

    function createTableFromLastLine() {
            Excel.run(function (ctx) {
                var selectedRange = ctx.workbook.getSelectedRange();
                var temRange = selectedRange.getOffsetRange(-1, 0);
                temRange.select();
                return ctx.sync().then(function () {
                    
                    createTable();
                });
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
        }
    
        function createTable() {
            var tableData = new Office.TableData();
            //tableData.headers = [['header1']];
            tableData.rows = [['row1'], ['row2'], ['row3']];
         
            
            Office.context.document.setSelectedDataAsync(tableData, { coercionType: Office.CoercionType.Table, tableOptions: { headerRow: false } }, function (asyncResult) {
            
            })
        }

    There is a limitation in this workaround that we are not able to create the table start from first row. To fix this issue, we can create the table without the headers and delete the line if it is start from second line. Here is the modifying code for your reference:

     function createTableFromLastLine() {
            Excel.run(function (ctx) {
                var selectedRange = ctx.workbook.getSelectedRange();
                var temRange = selectedRange.getOffsetRange(-1, 0);
                temRange.select();
                return ctx.sync().then(function () {
                    
                    createTable();
                });
            }).catch(function (error) {
                createTable();
                deleteTheLine();
                
            });
        }
    
        function deleteTheLine() {
            Excel.run(function (ctx) {
                var selectedRange = ctx.workbook.getSelectedRange();
                var temRange = selectedRange.getOffsetRange(-1, 0);
                var temRow = temRange.getRow(0);
                temRow.delete();
                return ctx.sync().then(function () {
                   
                });
            }).catch(function (error) {
                console.log("Error: " + error);
                if (error instanceof OfficeExtension.Error) {
                    console.log("Debug info: " + JSON.stringify(error.debugInfo));
                }
            });
        }
    
     function createTable() {
            var tableData = new Office.TableData();
            //tableData.headers = [['header1']];
            tableData.rows = [['row1'], ['row2'], ['row3']];
         
            
            Office.context.document.setSelectedDataAsync(tableData, { coercionType: Office.CoercionType.Table, tableOptions: { headerRow: false } }, function (asyncResult) {
            
            })
        }

    And if you want the Excel add-in API to support create the table without headers and the table range start from the select cell, you can try to submit the feedback from here.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, May 13, 2016 6:19 AM
    Moderator
  • Fei,

    Thanks for the response.  I also have code to delete the line above which I will use.

    I probably won't submit to user voice on this since right now but it would be nice to have.

    Jim

    Tuesday, May 17, 2016 5:33 PM