excel 如何在office js中清除表格样式

7gyucuyw  于 2022-12-20  发布在  其他
关注(0)|答案(1)|浏览(162)

我已经使用Office JS创建了一个表。当它在Excel工作表中显示时,默认情况下它是以**“蓝色,表格样式中等2”格式部署的。我不希望在Excel表格中有任何样式。我读过一些问题的答案,但在该答案中,他们建议“白色,表格样式浅1”
有没有办法在excel表格中设置
无样式或者有没有办法清除**表格的样式?
我的代码如下。

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();
});

这是默认应用的样式。

我想在excel表这种类型。

smdncfj3

smdncfj31#

TLDR; Tbl_Obj.style = "TableStyleLight1"是我能找到的最接近的。看起来有一种方法可以删除表格样式,但我想不出来。

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

除非有原因,否则不要使用表格。只使用范围。

直接将数据输入到表格对象比写入范围要慢。您应该始终从范围开始,并且仅在需要时才转换为表格。
我不知道如何设置为“无样式”,但再次,见上文。下面的代码只是使用了一个范围,并有你想要的样式,这看起来只是excel默认。
参见--〉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();

一个更好的方法IMO将使用对象数组如下:
Example/docs --〉(虽然再次使用表格,但您可以看到)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();

相关问题