Operating on Excel Cells and Cell Ranges with C#

Operating on Ranges is not just for Oklahoma Veterinarians Anymore

When working with Excel spreadsheets from C#, you first need to specify which cell or cells (cell range) you want to reference before actually doing something with them. There are probably many ways to do this; I will show you how you can reference a single cell first, and then how you can reference multiple cells (a Range).

First, let's assume we've declared variables for the various fundamental parts of an Excel spreadsheet like so:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
. . .
        private ApplicationClass _xlApp;
        private Workbook _xlBook;
        private Sheets _xlSheets;
        private Worksheet _xlSheet;
        . . .

Now that those preliminaries have been handled (no pun intended), we can move on to referencing cells and ranges of cells, and operating on them.

Single-Celled Organisms are Organisms, Too

You can treat a cell as a range of one row, one column, this way:

var annualContractProductsCell = (Range)_xlSheet.Cells[5, 1];

The arguments passed to the Cells are first the row index (5 above) and then the column index (1 above). So, row 5 and column 1 is now stored in annualContractProductsCell. So now we can manipulate that range (even though it's the most basic of ranges, a single cell), like so:

annualContractProductsCell.Interior.Color = XlRgbColor.rgbLightGoldenrodYellow;

Home, Home on the Range

Now let's work on a true range of cells - from a designated row and column starting point to a designated row and column ending point. We do that like this for a range that spans several columns on a single row:

private static readonly int COLUMN_HEADING_ROW = 7;
private static readonly int MONTH1_COL = 3;
private static readonly int MONTH13_COL = 15;
. . .
var monthHeadingsRange = _xlSheet.Range[_xlSheet.Cells[COLUMN_HEADING_ROW, MONTH1_COL], _xlSheet.Cells[COLUMN_HEADING_ROW, MONTH13_COL]];
monthHeadingsRange.Interior.Color = XlRgbColor.rgbSkyBlue;

Here's how we can do it for a range that does the opposite - one that spans several rows over a single column:

private static readonly int DATA_STARTING_ROW = 8;
private static readonly int ITEMDESC_COL = 1;
private int _lastRowAdded; // this is incremented as rows are programmatically added to the spreadsheet
. . .
var descriptionColRange = _xlSheet.Range[_xlSheet.Cells[DATA_STARTING_ROW, ITEMDESC_COL], _xlSheet.Cells[_lastRowAdded, ITEMDESC_COL]];
descriptionColRange.Interior.Color = XlRgbColor.rgbLightGreen;

Be Kind to Platypi

What, though, if you don't want to use one of the predefined colors, such as XlRgbColor's rgbLightGoldenrodYellow, rgbSkyBlue, and rgbLightGreen shown above (wondrous as they are), but rather a custom one of your own? Never fear, this is also possible. First, define such colors like so:

public static Color PLATYPUS_GREEN = Color.FromArgb(70, 128, 0);
public static Color PLATYPUS_ORANGE = Color.FromArgb(208, 102, 7);
public static Color PLATYPUS_BLUE = Color.FromArgb(0, 128, 128);
public static Color PLATYPUS_YELLOW = Color.FromArgb(255, 163, 0);
public static Color PLATYPUS_PURPLE = Color.FromArgb(47, 40, 73);

With those in place, you can now replace code like this:

descriptionColRange.Interior.Color = XlRgbColor.rgbLightGreen;

...with code like this:

descriptionColRange.Interior.Color = ColorTranslator.ToOle(PLATYPUS_GREEN);

Caveat Developtor

Note: You will need to add a Reference to Microsoft.Office.Interop.Excel.dll to your project for this code to work. I am using version 15.0.0.0 in my .NET 4.5 project (Runtime Version v2.0.50727)