Creating Calculated Columns in Excel 2007

Microsoft Office Excel 2007 offers improved support for working with tables. One enhancement, called calculated columns, is a new feature that makes it easier to work with formulas within the context of a table.

This visual how-to topic shows how to use the Microsoft Excel 12.0 Object Library to programmatically create a table that contains a calculated column.

Download the Code Sample

To illustrate this process, this section walks through six key steps:

1. Creating a Microsoft Windows Console Application project in Microsoft Visual Studio 2005.

2. Adding a reference to the Excel 12.0 Object Library to the project.

3. Importing the Excel primary interop assembly namespace.

4. Creating a data insertion helper method.

5. Declaring helper variables.

6. Implementing the calculated column creation code.

Creating a Windows Console Application in Visual Studio 2005

In this procedure, you create a Windows console application. However, the example code is not specific to Windows console applications and could be used in a variety of application types.

To create a project in Visual Studio 2005

Adding a Reference to the Excel 12.0 Object Library

You must add a reference to the Microsoft Excel 12.0 Object Library to the Visual Studio project to enable you to program against the Excel object model.

To add a reference

Importing the Excel Primary Interop Namespace

You must import the Microsoft.Office.Interop.Excel namespace to allow the objects that are defined in the namespace to be used without having to specify the fully qualified namespace path.

To import the namespace, open the Program.cs or Module1.vb source file and add the following line to the top of the source file.

Note

In a Microsoft Visual Basic project, add the code to the very top of the source file, above the Module statement. In a Microsoft Visual C# project, add the code before the namespace statement and right after the default using statements.

Imports Microsoft.Office.Interop.Excel

using Microsoft.Office.Interop.Excel;

Creating a Data Insertion Helper Method

Creating a helper method makes it easier to add table data to a worksheet. Add the following method to the project’s source file.

Note

In a Visual Basic project, add the code right after the Module statement, before Sub Main(). In a C# project, add the code in the Program class definition, before the Main() function.

Sub SetCellValue(ByRef targetSheet as WorkSheet, _
    ByRef Cell as String, ByRef Value as Object)
    targetSheet.Range(Cell).Value = Value
End Sub

static void SetCellValue(Worksheet targetSheet, string Cell,
    object Value)
{
    targetSheet.get_Range(
        Cell, Cell).set_Value(XlRangeValueDataType.xlRangeValueDefault, 
        Value);
}

Declaring Helper Variables

Next, declare the following variables to hold references to the Excel objects that are used in the calculated column creation code.

Note

In a Visual Basic project, add the variable declarations in Sub Main(). In a C# project, add them in the Main() function.

Dim excelApplication As ApplicationClass = Nothing
Dim newWorkbook As Workbook = Nothing
Dim targetSheet As Worksheet = Nothing
Dim dataRange As Range = Nothing

ApplicationClass excelApplication = null;
Workbook newWorkbook = null;
Worksheet targetSheet = null;
Range dataRange = null;

Next, declare variables that help to make the parameters passed to methods used in the calculated column creation code easier to read. In this case, the paramWorkbookPath variable is used to specify the path and file name of the Excel workbook to be created. The paramMissing variable is used when you call methods that accept optional parameters.

Replace the <Path> placeholder in the paramWorkbookPath variable with a path to a folder that will contain the Excel workbook that will be created.

Dim paramWorkbookPath As String = "<Path>\Calculated Columns.xlsx"

string paramWorkbookPath = @"<Path>\Calculated Columns.xlsx";
object paramMissing = Type.Missing;

Note that optional parameters are only optional when using Visual Basic. You must specify a value for optional parameters when using C#. If you use Type.Missing as the value for an optional parameter, the method being called is alerted that the parameter is not specified and that the method should use the parameter's default value.

Implementing the Calculated Column Creation Code

Next, add the calculated column creation code. This code does the following:

1. Starts an instance of Excel.

2. Creates a new workbook and adds the “Calculated Columns” worksheet to it.

3. Inserts table data into the worksheet using the data insertion helper method that you created earlier.

4. Creates a table with a calculated column.

5. Saves the workbook and closes Excel.

For a Visual Basic project place the following code within Sub Main() after the variable declarations added earlier. For a C# project place the following code within the Main() function after the variable declarations that you added earlier.

To start an instance of Excel

First, create an instance of the top-level ApplicationClass object, which is required to manipulate the Excel object model.

excelApplication = New ApplicationClass()

excelApplication = new ApplicationClass();

To create a workbook

Next, add the code to create a workbook that contains one worksheet named "Calculated Columns."

newWorkbook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
targetSheet = newWorkbook.Worksheets(1)
targetSheet.Name = "Calculated Columns"

newWorkbook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
targetSheet = (Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Calculated Columns";

To insert table data into the worksheet

Now, use the SetCellValue helper method created earlier to insert data for the table into the target worksheet. The inserted data used in this example is located in cells A1:D5 and looks like this:

SetCellValue(targetSheet, "A1", "Fruit")
SetCellValue(targetSheet, "B1", "Jan.")
SetCellValue(targetSheet, "C1", "Feb.")
SetCellValue(targetSheet, "D1", "Mar.")
SetCellValue(targetSheet, "E1", "Total")

SetCellValue(targetSheet, "A2", "Apples")
SetCellValue(targetSheet, "A3", "Oranges")
SetCellValue(targetSheet, "A4", "Pears")

SetCellValue(targetSheet, "B2", 5)
SetCellValue(targetSheet, "B3", 2)
SetCellValue(targetSheet, "B4", 3)

SetCellValue(targetSheet, "C2", 3)
SetCellValue(targetSheet, "C3", 3)
SetCellValue(targetSheet, "C4", 3)

SetCellValue(targetSheet, "D2", 4)
SetCellValue(targetSheet, "D3", 3)
SetCellValue(targetSheet, "D4", 2)

SetCellValue(targetSheet, "A1", "Fruit");
SetCellValue(targetSheet, "B1", "Jan.");
SetCellValue(targetSheet, "C1", "Feb.");
SetCellValue(targetSheet, "D1", "Mar.");
SetCellValue(targetSheet, "E1", "Total");
                
SetCellValue(targetSheet, "A2", "Apples");
SetCellValue(targetSheet, "A3", "Oranges");
SetCellValue(targetSheet, "A4", "Pears");
                
SetCellValue(targetSheet, "B2", 5);
SetCellValue(targetSheet, "B3", 2);
SetCellValue(targetSheet, "B4", 3);

SetCellValue(targetSheet, "C2", 3);
SetCellValue(targetSheet, "C3", 3);
SetCellValue(targetSheet, "C4", 3);

SetCellValue(targetSheet, "D2", 4);
SetCellValue(targetSheet, "D3", 3);
SetCellValue(targetSheet, "D4", 2);

To create a table with a calculated column

Finally, add the following code to get a Range object for the worksheet cells holding the table data. This Range object is used when creating the table.

dataRange = targetSheet.Range("A1", "E4")

dataRange = targetSheet.get_Range("A1", "E4");

Use the Add method of the target worksheet's ListObjects collection to create a table using the cells represented by the Range object created earlier. The ListObjects.Add method accepts the following parameters:

• SourceType. Specifies the type of the source data for the table. The code in the following example uses the XlListObjectSourceType.xlSrcRange value to specify that the data for the table is located in the Range object passed as the second parameter.

• Source. The Range object that represents the data source.

• LinkSource. Indicates whether the table is linked to an external data source. This parameter must be omitted if the SourceType parameter is xlSrcRange.

• TableStyleName. An XlYesNoGuess constant that indicates whether the data range for the table has column labels. If the source range does not contain headers, Excel automatically generates headers.

• Destination. A Range object that specifies a single-cell reference as the destination for the upper-left corner of the table. This parameter is ignored if the SourceType parameter is xlSrcRange.

targetSheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, _
    dataRange, , XlYesNoGuess.xlYes)

targetSheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
    dataRange, paramMissing, XlYesNoGuess.xlYes, paramMissing);

Now, add a formula in cell E2 that sums the values in cells B2:D2. The calculated columns functionality in Excel will cause the same formula to be automatically extended to the other rows in column D.

SetCellValue(targetSheet, "E2", "=sum(B2:D2)")

SetCellValue(targetSheet, "E2", "=sum(B2:D2)");

To save the workbook

To finish the process, you save the workbook and exit Excel. In the following C# code, the XlSaveAsAccessMode.xlNoChange value is passed as the seventh parameter to the Workbook.SaveAs method. This is done instead of using the paramMissing variable because the SaveAs method takes an XlSaveAsAccessMode enumerated type rather than an object type for this parameter. For this exercise, the actual value from the XlSaveAsAccessMode enumeration that is passed to the SaveAs method is not important.

After saving the workbook, the code closes it, closes the application objects, and releases references to the underlying Excel COM objects. This lets Excel unload from memory. For more information about releasing COM objects when you use managed code, see Chapter 2: Basics of Office Interoperability (Part 2 of 3) from the book, Microsoft .NET Development for Microsoft Office.

newWorkbook.SaveAs(paramWorkbookPath)

' Release the references to the Excel objects.

dataRange = Nothing
targetSheet = Nothing

' Close and release the Workbook object.

If Not newWorkbook Is Nothing Then
    newWorkbook.Close(False)
    newWorkbook = Nothing
End If

' Quit Excel and release the ApplicationClass object.

If Not excelApplication Is Nothing Then
    excelApplication.Quit()
    excelApplication = Nothing
End If

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing,
    paramMissing, paramMissing, paramMissing,
    XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, 
    paramMissing, paramMissing, paramMissing);

// Release the references to the Excel objects.

dataRange = null;
targetSheet = null;

// Close and release the Workbook object.

if (newWorkbook != null)
{
    newWorkbook.Close(false, paramMissing, paramMissing);
    newWorkbook = null;
}

// Quit Excel and release the ApplicationClass object.

if (excelApplication != null)
{
    excelApplication.Quit();
    excelApplication = null;
}

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

Office Excel 2007 offers improved support for working with tables. Calculated columns make it easier to work with formulas within the context of a table. A calculated column uses a single formula that adjusts for each row. It automatically expands to include additional rows so that the formula is immediately extended to those rows.

With calculated columns, you only have to enter a formula one time, removing the need to use the Fill or Copy commands. You can add a formula into a table cell and Excel automatically extends and adjusts it for each row in the table. You can enter additional formulas into a calculated column as exceptions, and Excel notifies you of any inconsistencies so that they can be resolved, if necessary.

You can find other changes to the table-related functionality in Excel 2007 on Microsoft Office Online in What's new in Microsoft Office Excel 2007.

This visual how to explores how to programmatically create a table that contains a calculated column in Excel 2007 through these key steps:

1. Creating a Windows Console Application project in Visual Studio 2005.

2. Adding a reference to the Excel 12.0 Object Library to the project. This marks the project as making use of the Excel 12.0 Object Library.

3. Importing the Microsoft.Office.Interop.Excel namespace. This allows the code to use the classes and types exposed as part of the Microsoft.Office.Interop.Excel namespace without having to specify the fully qualified namespace path.

4. Creating a data insertion helper method. This method makes it easier to insert data for the table into the worksheet.

5. Declaring helper variables to hold references to the Excel objects used in the calculated column creation code.

6. Creating an instance of the Excel ApplicationClass object. This is the top-most class in the Excel object model hierarchy and is the starting point for working with the other classes in the object model.

7. Creating a workbook and adding a worksheet to it. The code shows how to create a workbook with a single worksheet in it.

8. Inserting table data into the worksheet. The sample code shows how to call the data insertion helper method to add the data for the table to the worksheet.

9. Implementing the table and calculated column creation code. The sample code adds a table to the worksheet. It illustrates how to use the Worksheet.ListObjects.Add method to add a table to the worksheet and how to add a formula to a cell, causing the formula to be automatically extended to the other rows in the column.

10. Saving the workbook, exiting Excel, and releasing the references to its COM objects so that Excel can unload from memory.

Watch the Video

Length: 00:06:30

Size: 6.31 MB

Type: WMV file

• Excel Developer Portal

• Excel 2007 Resource Center

• Office Developer Center

• Understanding Optional Parameters in COM Interop

• Excel ListObjects.Add Method

• Blog: Microsoft Excel 2007