Using .NET's Office interop libraries, does anybody know the best way to convert back and forth between strings (eg "A57", "$L$2:$M:$3") and corresponding objects of type Excel.Range?
Bonus points if it also works with "named ranges".
1
-
Not quite sure what you mean here... are "A57", "$L$2:$M$3" strings containing cell reference information that you want to resolve to an Excel.Range, or do you mean something else?code4life– code4life2010-04-09 18:45:35 +00:00Commented Apr 9, 2010 at 18:45
Add a comment
|
4 Answers
Use the Range property of a Worksheet object, and pass Type.Missing as the second parameter.
For example:
Range range = sheet.get_Range("$L$2:$M:$3", Type.Missing);
This also supports named ranges.
4 Comments
mcoolbeth
This is what I want, though I am interested in the inverse operation(Range to string) as well.
SLaks
Cast the Range's
Name property to a Name object and look at the properties.mcoolbeth
Can you think of any reason for Range not to appear among the visible properties of a Worksheet in Visual Studio?
mcoolbeth
It won't build if I type "Range" myself. I see get_Range, but the arguments/behaviour are not well documented.
To get a string from a Range:
/// <summary>
/// Extensions to the Range class.
/// </summary>
public static class RangeExtensions
{
/// <summary>
/// Returns the range as a string indicating its address.
/// </summary>
/// <param name="range">The range to convert to a string.</param>
/// <returns>A string indicating the range's address.</returns>
public static string ToAddressString(this Range range)
{
return range.Address[true, true, XlReferenceStyle.xlA1, false, null];
}
}
To get a Range from a string:
public class ExcelUtil
{
/// <summary>
/// Converts the given address string on the given sheet to a Range object.
/// </summary>
/// <param name="sheet">The worksheet.</param>
/// <param name="addressString">The address string to convert.</param>
/// <returns>The range.</returns>
public static Range RangeFromAddresssString(Worksheet sheet, string addressString)
{
return sheet.Range[addressString];
}
}
The second method might be a little gratuitous, but I prefer being crystal clear in my method names.
Comments
As SLaks said, you can get a range object from a string address with the worksheet's Range property like worksheet.Range["A3:C30"]. The second argument can be omitted in .NET 4.0. .get_Range() is equivalent to .Range[].
To go the other way, use the range objects's Address property like this: range.Address.
1 Comment
super_seabass
In c# it appears that you have to use range.get_Address() and supply the arguments as outlined here: msdn.microsoft.com/en-us/library/…
If what you are trying to get is the actual contents of the cell, use the Value2 property. Here's some code that examines the cell value type and does different things accordingly.
Excel.Range cell = (Excel.Range)sheet.UsedRange[row, col];
if (cell.Value2 != null)
{
switch (Type.GetTypeCode(cell.Value2.GetType()))
{
case TypeCode.String:
string formula = cell.Value2;
break;
case TypeCode.Double:
double amt = (Double)cell.Value2;
break;
}
}
cell.Value2 = amt + someotheramt;
HTH
Comments
