Working with Excel cell values, formulas and formatting: C# code samples

If you look at the title of this article, it almost sums up what Microsoft Excel is. Without cells, values and formulas, you just cannot get much done in Excel. Of course everything is contained within worksheets and workbooks and you can do so much more with Excel, but at the heart of it all lies cells.

In this article we’ll take a closer look at manipulating Excel cells in your Add-in Express based Excel add-ins. There are a number of ways to reference cells in Excel. Keep in mind though that the Excel object model does not have any object called Cell, you must always use the Range object.

• Retrieving all cells
• Retrieving multiple cells
• Retrieving selected cells
• Setting Excel cell formulas
• Displaying the Insert Function Dialog
• Formatting cells

Retrieving all cells

The Worksheet object contains a Cells property that is a Range object that comprises a reference to all the cells in the Worksheet. You can get a reference to a Worksheet object by either using the ActiveSheet or WorkSheets properties of the Excel Application.

The following C# code retrieves a reference to the Cells property and displays the number of rows and columns in the active work sheet:

Excel._Workbook workBook = null;
Excel._Worksheet workSheet = null;
Excel.Range cellsRange = null;
Excel.Range columnRange = null;
Excel.Range rowRange = null;
int numberOfColumns = 0;
int numberOfRows = 0;
 
try
{
    workBook = ExcelApp.ActiveWorkbook;                
    workSheet = ExcelApp.ActiveSheet as Excel._Worksheet;
    cellsRange = workSheet.Cells;
    columnRange = cellsRange.Columns;
    rowRange = cellsRange.Rows;
    numberOfColumns = columnRange.Count;
    numberOfRows = rowRange.Count;
    MessageBox.Show(String.Format(
        "There are {0} columns and {1} rows in the Worksheet",
        numberOfColumns, numberOfRows));                
}
finally
{
    if (rowRange != null) Marshal.ReleaseComObject(rowRange);
    if (columnRange != null) Marshal.ReleaseComObject(columnRange);
    if (cellsRange != null) Marshal.ReleaseComObject(cellsRange);
    if (workSheet != null) Marshal.ReleaseComObject(workSheet);
    if (workBook != null) Marshal.ReleaseComObject(workBook);
}

Getting multiple cells

You can retrieve multiple Excel cells by either specifying the row and columns number for the Range object or you can use the Range objects’ get_Range method. This method allows you to retrieve a Range object by specifying the address of the cells e.g. A1:H9. The code listing is as follows:

Excel._Workbook workBook = null;
Excel._Worksheet workSheet = null;
Excel.Range allCellsRange = null;
Excel.Range firstCellRange = null;
Excel.Range secondCellRange = null;
 
try
{
    workBook = ExcelApp.ActiveWorkbook;
    workSheet = ExcelApp.ActiveSheet as Excel._Worksheet;
    allCellsRange = workSheet.Cells;
    firstCellRange = allCellsRange[3, 1] as Excel.Range;
    firstCellRange.Value = "First Range Value";
 
    secondCellRange = allCellsRange.get_Range("C2:F7");
    secondCellRange.Value = "Second Range Value";
}
finally
{
    if (secondCellRange != null) Marshal.ReleaseComObject(secondCellRange);
    if (firstCellRange != null) Marshal.ReleaseComObject(firstCellRange);
    if (allCellsRange != null) Marshal.ReleaseComObject(allCellsRange);
    if (workSheet != null) Marshal.ReleaseComObject(workSheet);
    if (workBook != null) Marshal.ReleaseComObject(workBook);
}

As you can see in the above code listing, to set the value of multiple cells, you need to set the Value property. You might also notice a Value2 property, which is an interesting one and reading more about this property on MSDN yielded the following piece of information, which is good to know:

“The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type.” Find more on MSDN.

Retrieving selected cells

So far we’ve only discussed retrieving a collection of cells programmatically, next, let’s look at how you can get a reference to the Range of cells the user has selected. Do this by using the Selection property of the Excel Application object. Be careful though, the selection is not always a Range object, the user could have selected a chart or an image. Add the following code:

Excel.Range selectedCellsRange = null;
Excel.Range columnRange = null;
Excel.Range rowRange = null;
int numberOfColumns = 0;
int numberOfRows = 0;
 
try
{
    if (ExcelApp.Selection is Excel.Range)
    {
        selectedCellsRange = ExcelApp.Selection as Excel.Range;
        columnRange = selectedCellsRange.Columns;
        rowRange = selectedCellsRange.Rows;
        numberOfColumns = columnRange.Count;
        numberOfRows = rowRange.Count;
        MessageBox.Show(String.Format(
            "You've selected {0} columns and {1} rows. The selection address is {2}",
            numberOfColumns, numberOfRows, selectedCellsRange.Address));
    }
 
}
finally
{
    if (rowRange != null) Marshal.ReleaseComObject(rowRange);
    if (columnRange != null) Marshal.ReleaseComObject(columnRange);
    if (selectedCellsRange != null) Marshal.ReleaseComObject(selectedCellsRange);
}

You will notice that in the above code we used the Address property of the Range object. This will return a string containing the selected cells’ addresses e.g. $A$1:$D$3

Setting Excel cell formulas

Let’s take what we’ve learned so far and use it to set cell formulas. You’ll need to get the Range object for the cells which formula you wish to set and then set its Formula property:

Excel.Range selectedRange = null;
try
{
    selectedRange = ExcelApp.Selection as Excel.Range;
    selectedRange.Formula = "=SUM(A1:A7)";
}
finally
{
    if (selectedRange != null) Marshal.ReleaseComObject(selectedRange);
}

Displaying the Insert Function dialog

If you want the user to choose which Excel function they want to insert, you can show the Insert Function dialog programmatically by using the Excel Application objects’ Dialogs collection.

Excel.Dialogs dialogs = null;
Excel.Dialog insertFunctionDialog = null;
 
try
{
    dialogs = ExcelApp.Dialogs;
    insertFunctionDialog = dialogs[Excel.XlBuiltInDialog.xlDialogFunctionWizard];
    insertFunctionDialog.Show();
}
finally
{
    if (insertFunctionDialog != null) Marshal.ReleaseComObject(insertFunctionDialog);
    if (dialogs != null) Marshal.ReleaseComObject(dialogs);
}

Formatting cells

Setting cell number formats, font, foreground and background colors are done in a similar fashion as setting cell values. You first need to get a reference to the Excel Range object which formatting you want to change. In the following code, we get a reference to the selected cells and set their number format to a custom format, the font to bold, foreground color to white and the background color to blue.

Excel.Range selectedRange = null;
Excel.Font font = null;
Excel.Interior interior = null;
 
try
{
    selectedRange = ExcelApp.Selection as Excel.Range;
    selectedRange.NumberFormat = "$#,##0.00_);($#,##0.00)";
    font = selectedRange.Font;
    font.Bold = true;
    font.Color = System.Drawing.Color.White;
    interior = selectedRange.Interior;
    interior.Pattern = Excel.Constants.xlSolid;
    interior.Color = System.Drawing.Color.DarkBlue;
}
finally
{
    if (interior != null) Marshal.ReleaseComObject(interior);
    if (font != null) Marshal.ReleaseComObject(font);
    if (selectedRange != null) Marshal.ReleaseComObject(selectedRange);
}

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Excel Cell Format add-in for Excel (C#)

Excel add-in development in Visual Studio for beginners:

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 8: Working with Excel charts
• Part 9: Working with Excel tables and ranges
• Part 10: Importing data from SQL databases and other sources to Excel
• Part 11: Working with Excel pivot tables: VB.NET code examples