Creating Calculated Columns in Excel 2007
Summary: Learn how to use the Microsoft Office Excel 2007 object library to programmatically create a table that contains a calculated column.
Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007
Joel Krist, Akona Systems
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.
To illustrate this process, this section walks through six key steps:
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.
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.
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.
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
Declaring Helper Variables
Next, declare the following variables to hold references to the Excel objects that are used in the calculated column creation code.
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
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.
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:
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.
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"
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)
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.
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:
targetSheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange, _ dataRange, , XlYesNoGuess.xlYes)
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.
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()
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:
Size: 6.31 MB
Type: WMV file