Excel.Range to convert strings to C #

Question

Using the libraries of interaction between .NET Office, does anyone know a better way to convert back and forth between lines (for example, "A57", "$ L $ 2: $ M: $ 3") and corresponding objects like Excel.Range?

Bonus points if it also works with "named ranges."

Answer 1

Use the Range property of the Worksheet object and pass Type.Missing as the second parameter.

For instance:

 Range range = sheet.get_Range("$L$2:$M:$3", Type.Missing); 

It also supports named ranges.

EDITED

Answer 2

As Slacks said, you can get a range object from a string address with the property of the Range worksheet as worksheet.Range["A3:C30"] . The second argument may be omitted in .NET 4.0. .get_Range() equivalent to .Range[] .

To go the other way, use the range Address : range.Address .

Answer 3

If what you are trying to get is the actual contents of the cell, use the Value2 property. Here is some code that examines the type of cell value 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; 

NTN

Answer 4

To get a string from 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 address.</returns> public static string ToAddressString(this Range range) { return range.Address[true, true, XlReferenceStyle.xlA1, false, null]; } } 

To get 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 may be a bit free, but I prefer to be crystal clear in the names of my methods.