Excel office-js API-将值插入空白表 [英] Excel office-js API - Insert values to a blank sheet

问题描述

如何将值插入空白表?
我目前知道的插入值的方法是检索范围，然后使用values属性(例如

How can I insert values into a blank sheet?
The way that I currently know of for inserting values is by retrieving the range and then inserting the values using the values property, such as

range.getRange("A1:" + cellBottomRight);
range.load("values");
context.sync().then(function () {
    range.values = twoDimensionalArrayOfValues;
});

通过使用单个函数简单地插入值，而不是先检索范围，是否有更简单的方法?

Is there a simpler way by using a single function to simply insert the values, rather than first retrieving the range?

谢谢！

编辑:
我一直在尝试创建一个新工作表，然后向其插入一个二维数组，该数组的值将从单元格A1开始插入.
到目前为止，我设法做到了以下几点:

EDIT:
I've been trying to create a new sheet and then insert a 2-dimensional array to it, which its values would be inserted starting from the cell A1.
So far, I managed to do the following:

let neeSheet = context.workbook.worksheets.add("New sheet");
newSheet.activate();
newSheet.getRange("A1").values = twoDimensionalArray;
context.sync();

但是没有用.

如何使它工作?
谢谢！

How can I get it to work?
Thanks!

推荐答案

(添加此答案以响应@ avi12在编辑"下的上述问题中添加的新信息)

(Adding this answer in response to the new information that @avi12 added to the question above, under "EDIT")

Philip的答案正确地显示了如何在工作表的单元格A1中插入单个值.为了解决您在(更新的)问题中描述的特定情况，以下是一些代码片段(一个在TypeScript中，另一个在JavaScript中)，展示了如何创建新工作表，然后使用二维数据数组.这里要指出的关键是我正在通过使用 getResizedRange (传入数组的维)来检索范围，因此范围的大小与我所设置的数据集的大小匹配正在插入其中.

Philip's answer above correctly shows how to insert a single value into cell A1 of a worksheet. To address the specific scenario that you've described in your (updated) question, here are some code snippets (one in TypeScript and the other in JavaScript) that show how to create a new worksheet and then add data to the worksheet, using a 2-dimensional array of data. The key thing to point out here is that I'm retrieving the range by using getResizedRange (passing in dimensions of my array), so that the size of the range matches the size of the data set that I'm inserting into it.

注意: :您可以使用脚本实验室(

Note: You can quickly and easily try these snippets yourself by using Script Lab (https://aka.ms/getscriptlab). Simply install the Script Lab add-in (free), then choose "Import" in the navigation menu, and use the following GIST URL: https://gist.github.com/kbrandl/01c4faf352c34286188311c1198f6307.

TypeScript:

async function run_TS() {
    try {
        await Excel.run(async (context) => {

            // define values that will be inserted into new sheet
            let values = [["A1", "B1", "C1"], ["A2", "B2", "C2"]]; 

            // create and activate new sheet
            let sheets = context.workbook.worksheets;
            let newSheet = sheets.add("New sheet TS");
            newSheet.activate();

            // add data to the new sheet
            let range = newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1);
            range.values = values;

            // sync
            await context.sync();

            console.log("Finished with run_TS function");
        });
    }
    catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
    }
}

JavaScript:

function run_JS() {
    Excel.run(function (context) {

        // define values that will be inserted into new sheet
        var values = [["A1", "B1", "C1"], ["A2", "B2", "C2"]];

        // create and activate new sheet
        var sheets = context.workbook.worksheets;
        var newSheet = sheets.add("New sheet JS");
        newSheet.activate();

        // add data to the new sheet
        var range = newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1);
        range.values = values;

        // sync        
        return context.sync()
            .then(function () {
                console.log("Finished with run_JS function");
            });
    })
        .catch(function (error) {
            OfficeHelpers.UI.notify(error);
            OfficeHelpers.Utilities.log(error);
        });
}

这篇关于Excel office-js API-将值插入空白表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！