How to remove the border for table in excel using office.js api

Question

I have created a table using office api, and i dont want to have any style on it. by default when table is created using office api, i see default table style is applied, i just want plain data.

to workaround that 1. I tried to change the border color and range fill area using some color to look like it doesn't have any style.

I have two questions to ask. 1. How to remove default style on excel table when created using office.js apis 2. if 1st is not possible then how to give no border to the range,

I tried using Hairline and Thin border as described in https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/rangeborder.md

but looks like Hairline is not working, Hairline and Thin gives same border

        Excel.run(function (ctx) { 
        var sheetName = "Sheet1";
        var rangeAddress = "A1:F8";
        var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
  range.format.borders.getItem('InsideHorizontal').weight = "Hairline";
                range.format.borders.getItem('InsideVertical').weight = "Hairline";
                range.format.borders.getItem('EdgeBottom').weight = "Hairline";
                range.format.borders.getItem('EdgeLeft').weight = "Hairline";
                range.format.borders.getItem('EdgeRight').weight = "Hairline";
                range.format.borders.getItem('EdgeTop').weight = "Hairline";

                //range.format.borders.getItem('InsideHorizontal').style = "No Border";
                //range.format.borders.getItem('InsideVertical').style = 'No Border';
                //range.format.borders.getItem('EdgeBottom').style = 'No Border';
                //range.format.borders.getItem('EdgeLeft').style = 'No Border';
                //range.format.borders.getItem('EdgeRight').style = 'No Border';
                //range.format.borders.getItem('EdgeTop').style = 'No Border';

                range.format.borders.getItem('InsideHorizontal').color = 'Gray';
                range.format.borders.getItem('InsideVertical').color = 'Gray';
                range.format.borders.getItem('EdgeBottom').color = 'Gray';
                range.format.borders.getItem('EdgeLeft').color = 'Gray';
                range.format.borders.getItem('EdgeRight').color = 'Gray';
                range.format.borders.getItem('EdgeTop').color = 'Gray';        return ctx.sync(); 
    }).catch(function(error) {
            console.log("Error: " + error);
            if (error instanceof OfficeExtension.Error) {
                console.log("Debug info: " + JSON.stringify(error.debugInfo));
            }
        });

My code look something similar to this.

Please guide me on how to remove border from table

Do you really need a table? The performance is worse when writing to table, just write to a range. — FreeSoftwareServers, Apr 19 '22 at 05:24

Marc LaFleur · Answer 1 · 2016-06-13T17:07:43.087

2

You need to set style to 'None' rather than 'No Border'.

range.format.borders.getItem('InsideHorizontal').style = "None";
range.format.borders.getItem('InsideVertical').style = 'None';
range.format.borders.getItem('EdgeBottom').style = 'None';
range.format.borders.getItem('EdgeLeft').style = 'None';
range.format.borders.getItem('EdgeRight').style = 'None';
range.format.borders.getItem('EdgeTop').style = 'None';

The possible values are documented at RangeBorder object (JavaScript API for Excel)

Edit: The above only applies to the newer Excel API. When working with classic table bindings, there is an alternative method. There is a walkthough of this process available at http://dev.office.com/docs/add-ins/excel/format-tables-in-add-ins-for-excel.

edited Jun 13 '16 at 17:07

answered Jun 10 '16 at 21:39

Marc LaFleur

31,987
4
37
63

that did not worked, btw range i have is from table, is that causing a problem ? – shyam_ Jun 10 '16 at 23:08
Yes, the code above only applies to the newer Excel API. For classic 1.1 table bindings the process is a bit different. There is a walkthrough at http://dev.office.com/docs/add-ins/excel/format-tables-in-add-ins-for-excel – Marc LaFleur Jun 11 '16 at 19:03
hi, I used newer api to create the table and then used getDataBodyRange() (https://github.com/OfficeDev/office-js-docs/blob/master/reference/excel/table.md#getdatabodyrange) to get the range. and then then applied the style. looks like for partial range in the table style, its not working, otherwise it will work..i am guessing – shyam_ Jun 14 '16 at 00:50

How to remove the border for table in excel using office.js api

1 Answers1