Excel office-js:合并2个或更多单元格 [英] Excel office-js: Merge 2 or more cells

问题描述

我需要合并2个单元格并在合并的单元格中有一个值.
我的(失败的)尝试:

#1

Excel.run(function (context) {
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").values = values;
    newSheet.getRange("A1:B1").merge = true;
});

#2

Excel.run(function (context) {
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").values = values;
    newSheet.getRange("A1").getResizedRange(0, 1).merge = true;
});

我在哪里做错了?

编辑:标记的答案是正确的，不是因为其主要内容-而是因为 Kim Brandl 的后续答复-其中，我的解决方案使用API​​ 1.1-但merge()仅可在解决方案

下面是一个代码片段，它创建一个新工作表，向工作表中添加2行数据，然后合并单元格A1和B1.

Excel.run(function (context) {

    // create new sheet, add 2 rows of data
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "Row 2", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1).values = values;

    // merge cells A1 and B1 
    var myRange = newSheet.getRange("A1:B1");
    myRange.merge();

    return context.sync();
})
    .catch(function (error) {
        console.log('error: ' + error);
        if (error instanceof OfficeExtension.Error) {
            console.log('Debug info: ' + JSON.stringify(error.debugInfo));
        }
    });

这是在Excel中运行此代码的结果:

注意以下几点:

• 如果您要合并的范围内的多个单元格包含值，则只有该范围内第一个单元格(即范围内最左上角的单元格)中的值得以保留-全部随后的单元格值将被清除.例如，上面示例中的工作表最初在单元格B1中包含一个值(行2")，但是如上面的屏幕截图所示，每当单元格B1与单元格A1合并时，该值就会被清除.

• 您只需要一个context.sync() －由于您只是将数据写入工作表并合并单元格，因此这些命令可以排队并最后使用单个sync()执行.

p>

• 始终包括错误处理(就像我对catch语句所做的那样)很重要，以确保在您不知情的情况下事情不会悄然失败.

更新(添加有关需求集的信息):

更多信息-好像range.merge()已作为Requirement Set 1.2的一部分添加到Excel JavaScript API中.我建议您确认您正在运行的Office版本/平台支持Requirement Set 1.2(或更高版本)-.

I need to merge 2 cells and have a value in the merged cell.
My (failed) attempts:

#1

Excel.run(function (context) {
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").values = values;
    newSheet.getRange("A1:B1").merge = true;
});

#2

Excel.run(function (context) {
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").values = values;
    newSheet.getRange("A1").getResizedRange(0, 1).merge = true;
});

Where did I do wrong?

EDIT: The marked answer is correct not because its main content - but because Kim Brandl's later reply - in which, my solution is using API 1.1 - but the merge() can only work in API 1.2.

Thanks a lot!

解决方案

Here's a code snippet that creates a new sheet, adds 2 rows of data to the sheet, and then merges cells A1 and B1.

Excel.run(function (context) {

    // create new sheet, add 2 rows of data
    const newSheet = context.workbook.worksheets.add("New sheet 1");
    newSheet.activate();
    const values = [
        ["Row 1", "Row 2", "Row 1"],
        ["Row 2", "Row 2", "Row 2"]
    ];
    newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1).values = values;

    // merge cells A1 and B1 
    var myRange = newSheet.getRange("A1:B1");
    myRange.merge();

    return context.sync();
})
    .catch(function (error) {
        console.log('error: ' + error);
        if (error instanceof OfficeExtension.Error) {
            console.log('Debug info: ' + JSON.stringify(error.debugInfo));
        }
    });

Here's the result of running this code in Excel:

Note the following:

• If multiple cells within the range that you're merging contain values, then only the value in the first cell of the range (i.e., the upper-left-most cell in the range) is persisted -- all subsequent cell values are wiped-out. For example, the sheet in the example above initially contained a value in cell B1 ("Row 2") -- but as the screenshot above shows, this value is wiped-out whenever cell B1 is merged with cell A1.

• You only need a single context.sync() -- since you're just writing data to the sheet and merging cells, those commands can be queued up and executed with a single sync() at the end.

• It's important to always include error handling (as I've done with the catch statement), to ensure that things don't silently fail without you knowing it.

Update (adding info about Requirement Sets):

One additional bit of info -- looks like range.merge() was added to the Excel JavaScript API as part of Requirement Set 1.2. I'd suggest you verify that you're running a version/platform of Office that supports Requirement Set 1.2 (or higher) -- https://dev.office.com/reference/add-ins/requirement-sets/excel-api-requirement-sets. For information about how to get the latest version of Office, see this topic in the Office Add-ins docs.

这篇关于Excel office-js:合并2个或更多单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！