C# / VSTO – Using Excel Ranges

Using Visual Studio Tools for Office (VSTO) with Excel lets you use C# (or your other favorite .NET language to leverage the power of Excel. But you will have to learn how the syntax for the Excel object model as opposed to just using VBA syntax.

This post shows you how to use Excel ranges with C#.

Excel Ranges in C#:

Syntax for Getting Excel Ranges in C#

Here are a couple of ways to do it:

int iRow = 1;

// If you're in the code for a particular sheet, you can use the 'this' object like this:
this.Range["A" + iRow];

// If you're in the code page (.cs file) for an ActionPane, et al, you can use the Globals like this:
Globals.Sheet1.Range["A" + iRow];
Globals.Sheet1.Range["A" + iRow + ":D33"];  //  multiple cell range

Globals.Sheet1.Range[Globals.Sheet1.Cells[1,1], Globals.Sheet1.Cells[iRow, 10]];  //  multiple cell range using just column #'s and row #'s

// Here's a loop for accessing single cell's range with a column number and row number
Excel.Range rng = (Excel.Range)Globals.Sheet1.Cells[1,100];

int iCol = 5;
iRow = 10;
Excel.Range rng = (Excel.Range)this.Cells[iRow, iCol];

//Get a set of Rows
Excel.Range f =  this.Rows["1:22", missing];

// Get a set of Columns
// Well, for some reason I couldn't get this.Columns[missing, "1:5"] to work...ideas anyone?

Use .Value2 for Strings

To access or set the value of a row to a string field, use the .value2 property of the range object.

this.Range["A1"].Value2 = "Hello World";
string mystring = this.Range["A1"].Value2;

Use .Value for Numeric Values

To access or set the value of a row to a numeric field, use the .Value property of the range object.

this.Range["A1"].Value = 42.0;
double mystring = this.Range["A1"].Value2;

How to Check if a Cell is Null

long q2 = this.Range["D" + i].Value == null ? 0 : (long)this.Range["D" + i].Value;

Excel Special Cells

Excel also has a whole slew of special cells you can use too…for instance:

// Gets the Row of the last cell used...note: sometimes the xlCellTypeLastCell gets out of whack if it contained a value but the value has been deleted.  So, use with care.
this.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

Summary

If you can do it with Excel, you can pretty much do it with VSTO, but it might take some tinkering to get the objects and syntax right.

Hope this helps!
Byron

, ,

Comments are closed.

Powered by WordPress. Designed by Woo Themes