How to clear style of table in office js

Question

I have created a table using office JS. When it's displayed in the excel sheet by default it's deployed in the "Blue, Table Style Medium 2" format. I don't want any style in the excel table. I had read some answer of question but in that answer, they suggest "White, Table Style Light 1".

Is there is any way to set no style in excel table or there is any way to clear the style of table?

My code is below.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";

    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    expensesTable.rows.add(null /*add rows to the end of the table*/, [
        ["1/1/2017", "The Phone Company", "Communications", "$120"],
        ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
        ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
        ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
        ["1/11/2017", "Bellows College", "Education", "$350"],
        ["1/15/2017", "Trey Research", "Other", "$135"],
        ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    await context.sync();
});

This is by default style is applied.

I want this type of table in excel sheet.

Answer 1

TLDR; Tbl_Obj.style = "TableStyleLight1" is the closest I could get. There looks to be a way to delete table style, but I couldn't figure it out.

https://learn.microsoft.com/en-us/javascript/api/excel/excel.tablestyle?view=excel-js-preview

Don't use tables at all unless you have a reason. Just use a range.

Inputting data directly into a table object is slower then writing to a range. You should always start with a range and only convert to a table if you have a need.

I don't know how to set to "No Style", but again, see above. The below code just uses a range and has the style you want, which looks to just be excel default.

See --> https://learn.microsoft.com/en-us/office/dev/add-ins/excel/performance#importing-data-into-tables

var vals_arr_of_arrs = [
    ["Date", "Merchant", "Category", "Amount"],
    ["1/1/2017", "The Phone Company", "Communications", "$120"],
    ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
    ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
    ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
    ["1/11/2017", "Bellows College", "Education", "$350"],
    ["1/15/2017", "Trey Research", "Other", "$135"],
    ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
]

var ws = context.workbook.worksheets.getActiveWorksheet();
var rng = ws.getRangeByIndexes(0, 0, vals_arr_of_arrs.length, vals_arr_of_arrs[0].length)
rng.values = vals_arr_of_arrs
//rng.format.fill.clear();

//var Tbl_Obj = ws.tables.add(rng, true);
//Tbl_Obj.style = "TableStyleMedium9"
//Tbl_Obj.convertToRange();

ws.autoFilter.apply(rng)

rng.format.autofitColumns();
rng.format.autofitRows();

//rng.format.fill.clear();

A better method IMO would be to use Array of Objects like below:

Example/docs --> (Using tables again though, but you can see) https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-tables#import-json-data-into-a-table

var vals_arr_of_objs = [
    {
        "DATE": "1/1/2017",
        "MERCHANT": "The Phone Company",
        "CATEGORY": "Communications",
        "AMOUNT": "$120"
    },
    {
        "DATE": "1/1/2017",
        "MERCHANT": "Southridge Video",
        "CATEGORY": "Entertainment",
        "AMOUNT": "$40"
    }
];


var Keys_Arr = Object.keys(vals_arr_of_objs[0])
const MappedArrays = vals_arr_of_objs
    .map(o => Keys_Arr
        .map(h => o.hasOwnProperty(h) ? o[h] : null)
)
MappedArrays.unshift(Keys_Arr)
var ws = context.workbook.worksheets.getActiveWorksheet();
var rng = ws.getRangeByIndexes(0, 0, MappedArrays.length, Keys_Arr.length)
rng.values = MappedArrays
//rng.format.fill.clear();

//var Tbl_Obj = ws.tables.add(rng, true);
//Tbl_Obj.style = "TableStyleMedium9"
//Tbl_Obj.convertToRange();

ws.autoFilter.apply(rng)

rng.format.autofitColumns();
rng.format.autofitRows();