Named Ranges in Excel OpenXML

Question

I am trying to create named ranges in Excel with OpenXML. I am able to add a DefinedName in the DefinedNames collection, but that does not seem to do anything. I noticed a place in the ExtendedFileProperties where the names of ranges are being saved, a structure called "TitlesOfParts". I have tried adding an entry in there but that causes excel to throw an error and the named range is not created. Here is the code I am using:

public void AddNamedRange(string pNamedRangeRef, string pNamedRangeName)
    {
        DefinedName _definedName = new DefinedName() { Name = pNamedRangeName, Text = pNamedRangeRef };
        _workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.DefinedNames>().First().Append(_definedName);
        DocumentFormat.OpenXml.VariantTypes.VTLPSTR _t = new DocumentFormat.OpenXml.VariantTypes.VTLPSTR() { Text = pNamedRangeName };
        _spreadsheet.ExtendedFilePropertiesPart.Properties.TitlesOfParts.VTVector.Append(_t);
        _spreadsheet.ExtendedFilePropertiesPart.Properties.TitlesOfParts.VTVector.Size++;
    }

"I am able to add a DefinedName in the DefinedNames collection, but that does not seem to do anything" - that should be all you have to do - In what way does it not do anything? When you save the document and open it in excel, does your named range appear in the dropdown list? If you open the xlsx as a zip file, can you find your named range in workbook.xml? — Ben, Nov 11 '11 at 13:09
I bet you're failing to qualify the values properly. Your pNamedRangeName should look something like "myrangename" and pNamedRangeRef should look like "Sheet1!$A$1". — Chris Rae, Dec 16 '11 at 20:31

score 2 · Answer 1 · answered Jan 16 '12 at 22:34

Using the Open XML SDK 2.0 Productivity Tool for Microsoft Office, to define a global/workbook-wide named range is pretty easy:

DefinedNames definedNamesCol = new DefinedNames();    //Create the collection
DefinedName definedName = new DefinedName()
    { Name = "test", Text="Sheet1!$B$2:$B$4" };       // Create a new range
definedNamesCol.Append(definedName);                  // Add it to the collection

workbook.Append(definedNamesCol);                     // Add collection to the workbook

user3464110 · Answer 2 · 2016-09-28T10:14:15.990

The below code did the trick for me. After this I was able to see the name ranges in excel also.

var wbPart = document.WorkbookPart;
 Workbook workbook = wbPart.Workbook;
 DefinedName definedName1 = new DefinedName { Name = "ColumnRange",Text = "Sheet1!$A$1:$I$1"};
DefinedName definedName2 = new DefinedName { Name = "RowRange", Text = "Sheet1!$A$1:$A$15"};
if (workbook.DefinedNames == null)
            {
                DefinedNames definedNames1 = new DefinedNames();
                definedNames1.Append(definedName1);
                definedNames1.Append(definedName2);
                workbook.DefinedNames = definedNames1;
            }

score 0 · Answer 3 · answered Sep 28 '22 at 12:49

'vb.net
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Dim WB As SpreadsheetDocument = SpreadsheetDocument.Create("C:\NewFile.xmlx", SpreadsheetDocumentType.Workbook)
Dim WBP As WorkbookPart = WB.AddWorkbookPart

Dim dn As DefinedName = New DefinedName()
    dn.Name = "test"
    dn.Text = "XFW_PLP_CalcPlan!A5:$I$1"
    
Dim dns As DefinedNames = New DefinedNames()
    dns.Append(dn)

WBP.Workbook.Append(dns)

Named Ranges in Excel OpenXML

3 Answers3