NamedRange in xlsx

[Joel Palmer]

The code I'm trying to get away from looks through a name collection to find a specific NamedRange in a workbook.  Then, I populate the NamedRange with a value.  This is a great feature because the creator can put a named range anywhere and move it around at any point.  My code doesn't care where it is... it just populates the value.  Can you tell me what options I have with SpreadProcessing?  If an Excel Workbook has a named range can I get at it with SpreadProcessing?

Excel Code:

/// <summary>
/// Get a Named Range from the given workbook.
/// </summary>
/// <param name="workbook"></param>
/// <param name="rangeName"></param>
/// <returns></returns>
public Range GetNamedRange(
    Workbook workbook,
    string rangeName)
{
    ExceptionHelper.TestNull(workbook, "workbook parameter");
    ExceptionHelper.TestStringValue(rangeName, "rangeName parameter");

    Range result = null;

    Names names = workbook.Names;
    foreach (Name name in names)
    {
        string[] nme = name.NameLocal.Split("!".ToCharArray());

        if (nme[nme.Length-1] == rangeName)
        {
            result = name.RefersToRange;
            break;
        }
    }

    return result;
}

[Joel Palmer]

This was never answered but I would appreciate a reply.  I have made head-way but am frustrated with the lack of documentation on how to interact with named ranges.

Typically, my named ranges only refer to 1 cell or a group of merged cells.  Because there isn't a clear path between the Named Range and a Cell object, I have the following code to parse the ReferesTo values.  In my opinion, I should be able to get a CellSelection directly from the name.RefersTo value.  Instead, I finally get the coordinates and create a CellSelection using this convoluted bit of code. 

If getting the CellSelection this way isn't bad enough, when I finally submit the value it doesn't work.  I put the value into a merged cell (1 row with 3 columns) and the v1 variable shows that the value is set when I view it in debug mode.  However, when I open the document after running the code, the value is not there.  However, the following line puts the same value in the columnIndex + 20 line and it shows up successfully in that location. 

Is there a limitation around using Merged Cells?

foreach (var name in workbook.Names)
{
    if (name.Name == nvp.Name)
    {
        string[] v = name.RefersTo.Split("!".ToCharArray());

        foreach (Worksheet sheet in workbook.Sheets)
        {
            if (sheet.Name.ToUpper() == v[0].Replace("=", String.Empty).ToUpper())
            {
                string[] r = v[1].Split("$".ToCharArray(),
                    StringSplitOptions.RemoveEmptyEntries);
                int columnIndex = ColumnIndexFromReference(r[0]);
                int rowIndex = int.Parse(r[1]);

                CellSelection selection = sheet.Cells[rowIndex, columnIndex];
                selection.SetValue(nvp.Value);
                RangePropertyValue<ICellValue> v1 = selection.GetValue();

                sheet.Cells[rowIndex, columnIndex + 20].SetValue(String.Format(@"{0}{1}{2}", nvp.Value, rowIndex, columnIndex));
            }
        }
    }
}

​

[Joel Palmer]

Doh!  Excel always referenced the columns using a "1 base array".  You are doing it using a 0 base.  Column A is Index 0 where it was previously a 1.  This means I was putting the value into the center of a merged range instead of at the beginning.  That is a huge change... I like it, but that changes logic.

Please don't ignore my previous post, however.  I need a better way to get from a Name Range RefersTo value over to a Cell reference that I can enter values.

[Anna]

Hi Joel,

The RefersTo value is designed to be a string and not a range, because it is not necessary a reference to a range or an index. It is possible for the RefersTo property to be "=1+1" and in this case the value of the named range will be 2. This is useful if you have one or more cells which you want to have a certain value. You can create a name "myName" with RefersTo = 100. Then you can populate some cells with "=myName" and they will all have value 100. Then if you'd like to change the value you only change the name.

In view of this, can you tell me a bit more about your scenario and what role the named ranges play in it? Would it be suitable to apply the above approach instead of looking for the RefersTo range?

Regards,
Anna
Telerik

 

 

[Joel Palmer]

Our customers love Excel because it is "something they know" and they can define reports themselves.  However, they expect us to populate data to it.  So, we narrowly define named ranges to represent the placement of where they want the data.  We've found NamedRanges to be the way to go because they transcend worksheets; they belong to the entire workbook.  We can populate the value where they want it by just looking for a named range.  So, we set up a mapping that says "this value from X goes to this named range" and it works like a reporting tool that our customers can define.

I haven't used NamedRange beyond this application so I wasn't aware that it could represent a calculation.  It seems to me that a NamedRangeType would pull a lot of the ambiguity away from what it represents.  Having a type that represents a Cell, CellBlock, Calculation, etc would be a valuable filtering mechanism.

So, I get to assume that the NamedRange that I'm looking for always represents the cell.  I just need to take a jump from the Name.RefersTo over to identify the coordinates of that Cell.  If its a CellBlock, I would expect to get the Top/Left coordinate of the block.

As you can see in my code, I Split the Name.RefersTo value to identify the Worksheet and convert the Column Letter over to represent the ColumnIndex in order to make this happen.

[Anna]

Hi,

Thanks for the additional details.

This use of the named ranges stretches a little the original way in which they were meant to be used and in this case, it is natural to have to include some extra code. However, I don't see a reason why the approach wouldn't work. I believe what you've written should do the job nicely if you provide for the case when the name is not a reference to a range, which we already discussed is a possible scenario.

Since you mentioned that your named ranges refer to a single cell or a group of merged cells, you can use the TryConvertCellNameToIndex() method of the NameConverter class. With this change your code would look similar to this:

string givenName = "test";
string givenValue = "testValue";

foreach (var name in workbook.Names)
{
    if (name.Name == givenName)
    {
        string[] v = name.RefersTo.Split("!".ToCharArray());

        foreach (Worksheet sheet in workbook.Sheets)
        {
            if (sheet.Name.ToUpper() == v[0].Replace("=", String.Empty).ToUpper())
            {
                string rangeName = v[1];
                string firstIndexName = rangeName.Split(":".ToCharArray())[0];

                int rowIndex;
                int columnIndex;
                bool isRowAbsolute;
                bool isColumnAbsolute;
                bool nameRefersToIndex = NameConverter.TryConvertCellNameToIndex(firstIndexName, out isRowAbsolute, out rowIndex, out isColumnAbsolute, out columnIndex);

                if (nameRefersToIndex)
                {
                    sheet.Cells[rowIndex, columnIndex].SetValue(givenValue);
                }
            }
        }
    }
}

I hope this will help. Please, let me know if you still have any concerns.


Regards,
Anna
Telerik

 

 

[Joel Palmer]

Awesome.  I didn't know about the name converter.  I'll take a look.