javascript - How to name and reference an Excel range using office.js - Stack Overflow

admin管理员组

文章数量:1023744

I'm developing an Excel add-in using office.js library, and need to create some named ranges to track and access later.

However according to the API documentation:

NamedItemCollection: .md

NamedItem: .md

There is no method for creating named items.

Is there a way to name and reference an Excel range using office.js? Thanks.

I'm developing an Excel add-in using office.js library, and need to create some named ranges to track and access later.

However according to the API documentation:

NamedItemCollection: https://github./OfficeDev/office-js-docs/blob/master/reference/excel/nameditemcollection.md

NamedItem: https://github./OfficeDev/office-js-docs/blob/master/reference/excel/nameditem.md

There is no method for creating named items.

Is there a way to name and reference an Excel range using office.js? Thanks.

• javascript
• ms-office
• office365
• office-js

Share Improve this question Follow edited Jul 20, 2016 at 19:04 Michael Saunders 2,66811 gold badge1414 silver badges2121 bronze badges asked Jul 19, 2016 at 22:16 Liang GuoLiang Guo 3311 silver badge55 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 4

There isn't a way to name a range in Excel through Office.js. Only the user can name a range, through the Excel UI.

The good news is that there's a different way to acplish the same goal:

If you want to maintain a reference to a range, there's no need to name it; it already has a name like Sheet1!A1:B10.

If you want to maintain a reference to a range even if the user adds/deletes rows/columns before it or inside it, use a Binding:

var myBindings = Office.context.document.bindings;
var myAddress = "Sheet1!A1:B10";
myBindings.addFromNamedItemAsync(myAddress, "matrix", {id:"myBind"}, function(result){});

And then when you want to access the range later, you can:

Excel.run(function (ctx) { 
    var foundBinding = ctx.workbook.bindings.getItem("myBind");
    var myRange = foundBinding.getRange();
    myRange.load('values');
    return ctx.sync().then(function() {
        console.log(myRange.values);
    });
});

-Michael Saunders, PM for Office add-ins

there is the Excel.NamedItemCollection which provides an add() method that generates Named references

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

  async function addNameToHeader() {
    await Excel.run(async(context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const headerRange = sheet.getRange("A1:D1");

        sheet.names.add("ExpensesHeader", headerRange);
        const namedItems = sheet.names.load("name, type");

        await context.sync();
    });
}

async function formatRangeByName() {
    await Excel.run(async(context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const headerRowName = sheet.names.getItemOrNullObject("ExpensesHeader");
        headerRowName.load();
        await context.sync();

        if (headerRowName.value) {
            const headerRange = headerRowName.getRange();
            headerRange.format.fill.color = "red";
        } else {
            console.log("No named item created for the range.");
        }

        await context.sync();
    });
}

I'm developing an Excel add-in using office.js library, and need to create some named ranges to track and access later.

However according to the API documentation:

NamedItemCollection: .md

NamedItem: .md

There is no method for creating named items.

Is there a way to name and reference an Excel range using office.js? Thanks.

I'm developing an Excel add-in using office.js library, and need to create some named ranges to track and access later.

However according to the API documentation:

NamedItemCollection: https://github./OfficeDev/office-js-docs/blob/master/reference/excel/nameditemcollection.md

NamedItem: https://github./OfficeDev/office-js-docs/blob/master/reference/excel/nameditem.md

There is no method for creating named items.

Is there a way to name and reference an Excel range using office.js? Thanks.

• javascript
• ms-office
• office365
• office-js

Share Improve this question Follow edited Jul 20, 2016 at 19:04 Michael Saunders 2,66811 gold badge1414 silver badges2121 bronze badges asked Jul 19, 2016 at 22:16 Liang GuoLiang Guo 3311 silver badge55 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 4

There isn't a way to name a range in Excel through Office.js. Only the user can name a range, through the Excel UI.

The good news is that there's a different way to acplish the same goal:

If you want to maintain a reference to a range, there's no need to name it; it already has a name like Sheet1!A1:B10.

If you want to maintain a reference to a range even if the user adds/deletes rows/columns before it or inside it, use a Binding:

var myBindings = Office.context.document.bindings;
var myAddress = "Sheet1!A1:B10";
myBindings.addFromNamedItemAsync(myAddress, "matrix", {id:"myBind"}, function(result){});

And then when you want to access the range later, you can:

Excel.run(function (ctx) { 
    var foundBinding = ctx.workbook.bindings.getItem("myBind");
    var myRange = foundBinding.getRange();
    myRange.load('values');
    return ctx.sync().then(function() {
        console.log(myRange.values);
    });
});

-Michael Saunders, PM for Office add-ins

there is the Excel.NamedItemCollection which provides an add() method that generates Named references

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

  async function addNameToHeader() {
    await Excel.run(async(context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const headerRange = sheet.getRange("A1:D1");

        sheet.names.add("ExpensesHeader", headerRange);
        const namedItems = sheet.names.load("name, type");

        await context.sync();
    });
}

async function formatRangeByName() {
    await Excel.run(async(context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        const headerRowName = sheet.names.getItemOrNullObject("ExpensesHeader");
        headerRowName.load();
        await context.sync();

        if (headerRowName.value) {
            const headerRange = headerRowName.getRange();
            headerRange.format.fill.color = "red";
        } else {
            console.log("No named item created for the range.");
        }

        await context.sync();
    });
}

本文标签： javascriptHow to name and reference an Excel range using officejsStack Overflow