Creating a Master Detail View in Excel 2007

  • Article

Summary: Learn how to use structured references programmatically to implement a master-detail view in Microsoft Office Excel 2007. (20 printed pages)

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007

Joel Krist, iSoftStone

March 2009

Overview

Microsoft Office Excel 2007 introduced the concept of structured references to make it easier to create formulas that work with data in tables. Structured references define a syntax such that formulas can adjust automatically as table rows and columns are added and deleted. You use the syntax when you want to reference table cells whose formulas reference table data.

Master-detail views give users a way to select an item (the "master"), and then view the related details. For example, given a particular customer and that customer's orders, you would expect the master part of the view to display basic customer details, such as name and address, and the detail part of the view to display the customer's orders.

 See It Creating a master-detail view in Excel 2007 video

Watch the Video

Length: 07:42 | Size: 10.40 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the sample code

To illustrate how to create a master-detail view in Excel 2007 programmatically, this section walks through the following steps:

  1. Creating a Windows console application solution in Visual Studio 2008.

  2. Adding a reference to the Excel 2007 Primary Interop Assembly.

  3. Importing the Excel 2007 Primary Interop Assembly namespace.

  4. Adding the sample code to the solution.

Creating a Windows Console Application in Visual Studio 2008

This visual how-to topic uses a Windows console application to provide the framework for the sample code. However, you could use the same approach that is illustrated here with other application types as well.

To create a Windows Console Application in Visual Studio 2008

  1. Start Microsoft Visual Studio 2008.

  2. Select File | New | Project from the menu.

  3. In the New Project dialog box select the Visual Basic Windows or Visual C# Windows type in the Project types pane.

  4. Select Console Application in the Templates pane.

  5. Name the project MasterDetailView and choose to create a new solution.

    Figure 1. Create new solution in the New Project dialog box

    Create new Solution in the New Project dialog box

     

  6. Click OK to create the solution.

Adding a reference to the Excel 2007 Primary Interop Assembly

This visual how-to topic uses the Excel 2007 Primary Interop Assembly (PIA) to create and manipulate an Excel workbook. The Excel 2007 Primary Interop Assembly exposes the Excel 2007 object model to managed code and is installed with Excel 2007. It is also available for download as part of the 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies.

To add a Reference to the Excel 2007 Primary Interop Assembly

  1. Select Visual Studio Project | Add Reference from the menu.

  2. Select version 12.0.0.0 of the Microsoft.Office.Interop.Excel component and then click OK to add the reference.

    Figure 2. Add the reference to the 12.0.0.0 interop assembly

    Add the reference to the 12.0.0.0 interop assembly

Importing the Excel 2007 Primary Interop Assembly Namespace

After you import the Excel 2007 PIA namespace, your code can work with the objects and the types that are defined in the namespace without having to specify the fully qualified namespace path each time.

For a Visual Basic project, add the following line to the top of the Module1.vb source file.

Imports Microsoft.Office.Interop.Excel

For a C# project, add the following line to the top of the Program.cs source file below the other using statements generated by Visual Studio.

using Microsoft.Office.Interop.Excel;

Adding the Sample Code to the Solution

For a Visual Basic project, replace the contents of the Module1 module in the Module1.vb source file with the following code.

Sub Main()
    ' Declare variables that hold references to Excel objects.
    Dim excelApplication As ApplicationClass = Nothing
    Dim excelWorkBook As Workbook = Nothing
    Dim customerSheet As Worksheet = Nothing
    Dim orderSheet As Worksheet = Nothing
    Dim reportSheet As Worksheet = Nothing
    Dim customerTable As ListObject = Nothing
    Dim orderTable As ListObject = Nothing
    Dim currentRange As Range = Nothing

    ' Declare workbook and worksheet name variables.
    Dim workBookName As String = "C:\Temp\MasterDetailView.xlsx"
    Dim customerSheetName As String = "Customer"
    Dim orderSheetName As String = "Order"
    Dim reportSheetName As String = "OrderReport"

    Try
        ' Create an instance of Excel
        excelApplication = New ApplicationClass()

        ' Create a workbook and set up the default sheets.
        excelWorkBook = excelApplication.Workbooks.Add()
        customerSheet = excelWorkBook.Worksheets(1)
        customerSheet.Name = customerSheetName
        orderSheet = excelWorkBook.Worksheets(2)
        orderSheet.Name = orderSheetName
        reportSheet = excelWorkBook.Worksheets(3)
        reportSheet.Name = reportSheetName

        ' Add Data to the customer and order sheet.
        AddCustomerData(customerSheet, customerTable)
        AddOrderData(orderSheet, orderTable)

        ' Set up Reporting Sheet.
        ' Initialize control for selecting a customer.
        SetCellValue(reportSheet, "B1", "Customer")

        Dim customerNames As Name = _
            reportSheet.Names.Add("CustomerNames", _
                customerTable.ListColumns(1).DataBodyRange, _
                True, , , , , , , , )

        Dim customerSelection As Name = _
            reportSheet.Names.Add("CustomerSelection", _
                reportSheet.Range("C1", "C1"), _
                True, , , , , , , , )

        customerSelection.RefersToRange.Validation.Add( _
            XlDVType.xlValidateList, _
            XlDVAlertStyle.xlValidAlertStop, , _
            "=CustomerNames", )

        customerSelection.RefersToRange.Value2 = "Company A"

        ' Set customer selection as the active cell.
        reportSheet.Select()
        customerSelection.RefersToRange.Select()

        ' Customer Information Display.
        SetCellValue(reportSheet, "B3", "Customer Info")
        SetCellValue(reportSheet, "B4", "Name")
        SetCellValue(reportSheet, "C4", _
            "=VLOOKUP(CustomerSelection, " & _
            "CustomerData, MATCH(B4, " & _
            "CustomerData[#Headers], 0), " & _
            "FALSE)")
        SetCellValue(reportSheet, "B5", "Phone")
        SetCellValue(reportSheet, "C5", _
            "=VLOOKUP(CustomerSelection, " & _
            "CustomerData, MATCH(B5, " & _
            "CustomerData[#Headers], 0), " & _
            "FALSE)")
        SetCellValue(reportSheet, "B6", "Address")
        SetCellValue(reportSheet, "C6", _
            "=VLOOKUP(CustomerSelection, " & _
            "CustomerData, MATCH(B6, " & _
            "CustomerData[#Headers], 0), " & _
            "FALSE)")
        SetCellValue(reportSheet, "B7", "City")
        SetCellValue(reportSheet, "C7", _
            "=VLOOKUP(CustomerSelection, " & _
            "CustomerData, MATCH(B7, " & _
            "CustomerData[#Headers], 0), " & _
            "FALSE)")
        SetCellValue(reportSheet, "B8", "State")
        SetCellValue(reportSheet, "C8", _
            "=VLOOKUP(CustomerSelection, " & _
            "CustomerData, MATCH(B8, " & _
            "CustomerData[#Headers], 0), " & _
            "FALSE)")

        ' Orders Information Display.
        SetCellValue(reportSheet, "B10", "Order Info")

        ' Header Values
        SetCellValue(reportSheet.Range("B11", "B12"), _
                     "Order Number")
        SetCellValue(reportSheet.Range("C11", "C12"), _
                     "Date")
        SetCellValue(reportSheet.Range("D11", "D12"), _
                     "Amount")
        SetCellValue(reportSheet.Range("E11", "E12"), _
                     "Shipping")
        SetCellValue(reportSheet.Range("F11", "F12"), _
                     "Total")
        SetCellValue(reportSheet.Range("G11", "G12"), _
                     "Status")

        ' Set the formulas on the first data row.
        currentRange = reportSheet.Range("B12", "B12")
        currentRange.FormulaArray = _
            "=IFERROR(INDEX(OrderData, " & _
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " & _
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " & _
            "ROW(1:1)), " & _
            "MATCH(""Order Number"", OrderData[#Headers],0)),"""")"

        currentRange = reportSheet.Range("C12", "C12")
        currentRange.FormulaArray = _
            "=IFERROR(INDEX(OrderData, " & _
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " & _
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " & _
            "ROW(1:1)), " & _
            "MATCH(""Date"", OrderData[#Headers],0)),"""")"

        currentRange = reportSheet.Range("D12", "D12")
        currentRange.FormulaArray = _
            "=IFERROR(INDEX(OrderData, " & _
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " & _
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " & _
            "ROW(1:1)), " & _
            "MATCH(""Amount"", OrderData[#Headers],0)),"""")"

        currentRange = reportSheet.Range("E12", "E12")
        currentRange.FormulaArray = _
            "=IFERROR(INDEX(OrderData, " & _
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " & _
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " & _
            "ROW(1:1)), " & _
            "MATCH(""Shipping"", OrderData[#Headers],0)),"""")"

        currentRange = reportSheet.Range("F12", "F12")
        currentRange.FormulaArray = _
            "=IFERROR(INDEX(OrderData, " & _
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " & _
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " & _
            "ROW(1:1)), MATCH(""Total"", OrderData[#Headers],0)),"""")"

        currentRange = reportSheet.Range("G12", "G12")
        currentRange.FormulaArray = _
            "=IFERROR(INDEX(OrderData, " & _
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " & _
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " & _
            "ROW(1:1)), " & _
            "MATCH(""Status"", OrderData[#Headers],0)),"""")"

        ' Then copy all formulas down.
        currentRange = reportSheet.Range("B12", "G12")
        currentRange.Copy(reportSheet.Range("B13", "G33"))

        ' Save the workbook.
        excelWorkBook.SaveAs(workBookName, , , , , , _
            XlSaveAsAccessMode.xlNoChange, , , , , )
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    Finally
        ' Release the references to the Excel objects.
        customerSheet = Nothing
        orderSheet = Nothing
        reportSheet = Nothing
        customerTable = Nothing
        orderTable = Nothing

        ' Close the Workbook object.
        If Not excelWorkBook Is Nothing Then
            excelWorkBook = Nothing
        End If

        ' Close the ApplicationClass object.
        If Not excelApplication Is Nothing Then
            excelApplication.Quit()
            excelApplication = Nothing
        End If

        ' Garbage collection
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try

End Sub

' Helper method to set a value on a single cell.
Sub SetCellValue(ByRef cell As Range, ByVal value As Object)
    cell.Value = value
End Sub

' Helper method to set a value on a single cell in a specific
' worksheet.
Sub SetCellValue(ByRef targetSheet As Worksheet, _
                 ByRef cell As String, ByVal value As Object)
    targetSheet.Range(cell).Value = value
End Sub

' Helper method to add data to the Customer worksheet.
Sub AddCustomerData(ByRef sheet As Worksheet, _
                    ByRef table As ListObject)

    Dim currentRow As ListRow = Nothing

    ' Initialize the CustomerData table.
    table = sheet.ListObjects.Add( _
    XlListObjectSourceType.xlSrcRange, , , XlYesNoGuess.xlYes, "A1")
    table.Name = "CustomerData"

    ' Set up the table headers.
    SetCellValue(table.HeaderRowRange(1, 1), "Name")
    SetCellValue(table.HeaderRowRange(1, 2), "Phone")
    SetCellValue(table.HeaderRowRange(1, 3), "Address")
    SetCellValue(table.HeaderRowRange(1, 4), "City")
    SetCellValue(table.HeaderRowRange(1, 5), "State")

    ' Add the data to the table.
    currentRow = table.ListRows.Add()

    ' First insert inserts two data rows, removes the extra one.
    table.DataBodyRange.Rows(2).Delete()
    SetCellValue(currentRow.Range(1, 1), "Company A")
    SetCellValue(currentRow.Range(1, 2), "(206)111-1111")
    SetCellValue(currentRow.Range(1, 3), "123 1st Street")
    SetCellValue(currentRow.Range(1, 4), "Seattle")

    SetCellValue(currentRow.Range(1, 5), "WA")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "Company B")
    SetCellValue(currentRow.Range(1, 2), "(425)222-2222")
    SetCellValue(currentRow.Range(1, 3), "456 2st Avenue")
    SetCellValue(currentRow.Range(1, 4), "Bellevue")
    SetCellValue(currentRow.Range(1, 5), "WA")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "Company C")
    SetCellValue(currentRow.Range(1, 2), "(509)333-3333")
    SetCellValue(currentRow.Range(1, 3), "789 3rd Street")
    SetCellValue(currentRow.Range(1, 4), "Richland")
    SetCellValue(currentRow.Range(1, 5), "WA")

    ' Release Excel objects
    currentRow = Nothing
End Sub

' Helper method to add data to the Order worksheet.
Sub AddOrderData(ByRef sheet As Worksheet, ByRef table As ListObject)
    Dim currentRow As ListRow = Nothing

    ' Initialize the OrderData table.
    table = sheet.ListObjects.Add( _
    XlListObjectSourceType.xlSrcRange, , , XlYesNoGuess.xlYes, "A1")
    table.Name = "OrderData"

    ' Set up the table headers.
    SetCellValue(table.HeaderRowRange(1, 1), "Order Number")
    SetCellValue(table.HeaderRowRange(1, 2), "Date")
    SetCellValue(table.HeaderRowRange(1, 3), "Customer")
    SetCellValue(table.HeaderRowRange(1, 4), "Amount")
    SetCellValue(table.HeaderRowRange(1, 5), "Shipping")
    SetCellValue(table.HeaderRowRange(1, 6), "Status")

    ' Add the data to the table.
    currentRow = table.ListRows.Add(System.Type.Missing)

    ' First insert inserts two data rows, removes the extra one.
    table.DataBodyRange.Rows(2).Delete()
    SetCellValue(currentRow.Range(1, 1), "1111")
    SetCellValue(currentRow.Range(1, 2), "1/15/2009")
    SetCellValue(currentRow.Range(1, 3), "Company A")
    SetCellValue(currentRow.Range(1, 4), "100.00")
    SetCellValue(currentRow.Range(1, 5), "5.00")
    SetCellValue(currentRow.Range(1, 6), "Closed")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "1112")
    SetCellValue(currentRow.Range(1, 2), "2/12/2009")
    SetCellValue(currentRow.Range(1, 3), "Company A")
    SetCellValue(currentRow.Range(1, 4), "200.00")
    SetCellValue(currentRow.Range(1, 5), "20.00")
    SetCellValue(currentRow.Range(1, 6), "Closed")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "1113")
    SetCellValue(currentRow.Range(1, 2), "3/17/2009")
    SetCellValue(currentRow.Range(1, 3), "Company A")
    SetCellValue(currentRow.Range(1, 4), "150.00")
    SetCellValue(currentRow.Range(1, 5), "10.00")
    SetCellValue(currentRow.Range(1, 6), "New")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "2221")
    SetCellValue(currentRow.Range(1, 2), "12/11/2008")
    SetCellValue(currentRow.Range(1, 3), "Company B")
    SetCellValue(currentRow.Range(1, 4), "15.00")
    SetCellValue(currentRow.Range(1, 5), "10.00")
    SetCellValue(currentRow.Range(1, 6), "Closed")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "2222")
    SetCellValue(currentRow.Range(1, 2), "12/12/2008")
    SetCellValue(currentRow.Range(1, 3), "Company B")
    SetCellValue(currentRow.Range(1, 4), "20.00")
    SetCellValue(currentRow.Range(1, 5), "25.00")
    SetCellValue(currentRow.Range(1, 6), "Closed")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "3331")
    SetCellValue(currentRow.Range(1, 2), "03/05/2009")
    SetCellValue(currentRow.Range(1, 3), "Company C")
    SetCellValue(currentRow.Range(1, 4), "2000.00")
    SetCellValue(currentRow.Range(1, 5), "300.00")
    SetCellValue(currentRow.Range(1, 6), "Shipped")

    currentRow = table.ListRows.Add()
    SetCellValue(currentRow.Range(1, 1), "3332")
    SetCellValue(currentRow.Range(1, 2), "03/14/2009")
    SetCellValue(currentRow.Range(1, 3), "Company C")
    SetCellValue(currentRow.Range(1, 4), "430")
    SetCellValue(currentRow.Range(1, 5), "44.00")
    SetCellValue(currentRow.Range(1, 6), "New")

    ' Add a totalAmount column.
    ' Formula will be automatically filled into existing rows.
    Dim totalColumn As ListColumn = table.ListColumns.Add(6)
    totalColumn.Name = "Total"

    Dim range As Range = totalColumn.DataBodyRange(1, 1)
    range.Value2 = "=[Shipping]+[Amount]"

    ' Release Excel objects
    currentRow = Nothing
    totalColumn = Nothing
End Sub

For a C# project, replace the contents of the Program class in the Program.cs source file with the following code.

// Declare a variable to aid with passing optional method parameters.
static object useDefault = Type.Missing;

static void Main(string[] args)
{
    // Declare variables that hold references to Excel objects.
    ApplicationClass excelApplication = null;
    Workbook excelWorkBook = null;
    Worksheet customerSheet = null;
    Worksheet orderSheet = null;
    Worksheet reportSheet = null;
    ListObject customerTable = null;
    ListObject orderTable = null;
    Range currentRange = null;

    // Declare workbook and worksheet name variables.
    string workBookName = @"C:\Temp\MasterDetailView.xlsx";
    string customerSheetName = @"Customer";
    string orderSheetName = @"Order";
    string reportSheetName = @"OrderReport";

    try
    {
        // Create an instance of Excel
        excelApplication = new ApplicationClass();

        // Create a workbook and set up the default sheets.
        excelWorkBook = excelApplication.Workbooks.Add(useDefault);
        customerSheet = (Worksheet)(excelWorkBook.Worksheets[1]);
        customerSheet.Name = customerSheetName;
        orderSheet = (Worksheet)(excelWorkBook.Worksheets[2]);
        orderSheet.Name = orderSheetName;
        reportSheet = (Worksheet)(excelWorkBook.Worksheets[3]);
        reportSheet.Name = reportSheetName;

        // Add Data to the customer and order sheet.
        AddCustomerData(customerSheet, ref customerTable);
        AddOrderData(orderSheet, ref orderTable);

        // Set up Reporting Sheet.
        // Initialize control for selecting a customer.
        SetCellValue(reportSheet, "B1", "Customer");
        Name customerNames =
            reportSheet.Names.Add("CustomerNames",
            customerTable.ListColumns[1].DataBodyRange,
            true, useDefault, useDefault, useDefault, useDefault,
            useDefault, useDefault, useDefault, useDefault);
        
        Name customerSelection = 
            reportSheet.Names.Add("CustomerSelection", 
            reportSheet.get_Range("C1", "C1"), true, useDefault, 
            useDefault, useDefault, useDefault, useDefault, 
            useDefault, useDefault, useDefault);

        customerSelection.RefersToRange.Validation.Add(
            XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop,
            useDefault, "=CustomerNames", useDefault);
        customerSelection.RefersToRange.Value2 = "Company A";

        // Set customer selection as the active cell.
        reportSheet.Select(useDefault);
        customerSelection.RefersToRange.Select();

        // Customer Information Display.
        SetCellValue(reportSheet, "B3", "Customer Info");
        SetCellValue(reportSheet, "B4", "Name");
        SetCellValue(reportSheet, "C4",
            "=VLOOKUP(CustomerSelection, CustomerData, " +
            "MATCH(B4, CustomerData[#Headers], 0), FALSE)");
        SetCellValue(reportSheet, "B5", "Phone");
        SetCellValue(reportSheet, "C5", 
            "=VLOOKUP(CustomerSelection, CustomerData, " +
            "MATCH(B5, CustomerData[#Headers], 0), FALSE)");
        SetCellValue(reportSheet, "B6", "Address");
        SetCellValue(reportSheet, "C6", 
            "=VLOOKUP(CustomerSelection, CustomerData, " +
            "MATCH(B6, CustomerData[#Headers], 0), FALSE)");
        SetCellValue(reportSheet, "B7", "City");
        SetCellValue(reportSheet, "C7", 
            "=VLOOKUP(CustomerSelection, CustomerData, " +
            "MATCH(B7, CustomerData[#Headers], 0), FALSE)");
        SetCellValue(reportSheet, "B8", "State");
        SetCellValue(reportSheet, "C8", 
            "=VLOOKUP(CustomerSelection, CustomerData, " +
            "MATCH(B8, CustomerData[#Headers], 0), FALSE)");

        // Orders Information Display.
        SetCellValue(reportSheet, "B10", "Order Info");

        // Header Values
        SetCellValue(reportSheet.get_Range("B11", "B12"),
            "Order Number");
        SetCellValue(reportSheet.get_Range("C11", "C12"), 
            "Date");
        SetCellValue(reportSheet.get_Range("D11", "D12"), 
            "Amount");
        SetCellValue(reportSheet.get_Range("E11", "E12"), 
            "Shipping");
        SetCellValue(reportSheet.get_Range("F11", "F12"), 
            "Total");
        SetCellValue(reportSheet.get_Range("G11", "G12"), 
            "Status");

        // Set the formulas on the first data row.
        currentRange = reportSheet.get_Range("B12", "B12");
        currentRange.FormulaArray = "=IFERROR(INDEX(OrderData, " +
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " +
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " +
            "ROW(1:1)), " +
            "MATCH(\"Order Number\", OrderData[#Headers],0)),\"\")";

        currentRange = reportSheet.get_Range("C12", "C12");
        currentRange.FormulaArray = "=IFERROR(INDEX(OrderData, " +
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " +
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " +
            "ROW(1:1)), " +
            "MATCH(\"Date\", OrderData[#Headers],0)),\"\")";

        currentRange = reportSheet.get_Range("D12", "D12");
        currentRange.FormulaArray = "=IFERROR(INDEX(OrderData, " +
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " +
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " +
            "ROW(1:1)), " +
            "MATCH(\"Amount\", OrderData[#Headers],0)),\"\")";
        
        currentRange = reportSheet.get_Range("E12", "E12");
        currentRange.FormulaArray = "=IFERROR(INDEX(OrderData, " +
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " +
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " +
            "ROW(1:1)), " +
            "MATCH(\"Shipping\", OrderData[#Headers],0)),\"\")";
        
        currentRange = reportSheet.get_Range("F12", "F12");
        currentRange.FormulaArray = "=IFERROR(INDEX(OrderData, " +
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " +
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " +
            "ROW(1:1)), " +
            "MATCH(\"Total\", OrderData[#Headers],0)),\"\")";
        
        currentRange = reportSheet.get_Range("G12", "G12");
        currentRange.FormulaArray = "=IFERROR(INDEX(OrderData, " +
            "SMALL(IF(OrderData[Customer]=CustomerSelection, " +
            "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " +
            "ROW(1:1)), " +
            "MATCH(\"Status\", OrderData[#Headers],0)),\"\")";

        // Then copy all formulas down.
        currentRange = reportSheet.get_Range("B12", "G12");
        currentRange.Copy(reportSheet.get_Range("B13", "G33"));

        // Save the workbook.
        excelWorkBook.SaveAs(workBookName, useDefault, useDefault, 
            useDefault, useDefault, useDefault, 
            XlSaveAsAccessMode.xlNoChange, useDefault, useDefault, 
            useDefault, useDefault, useDefault);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        // Release the references to the Excel objects.
        customerSheet = null;
        orderSheet = null;
        reportSheet = null;
        customerTable = null;
        orderTable = null;

        // Close the Workbook object.
        if (excelWorkBook != null)
            excelWorkBook = null;

        // Close the ApplicationClass object.
        if (excelApplication != null)
        {
            excelApplication.Quit();
            excelApplication = null;
        }

        // Garbage collection
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

/// <summary>
/// Helper Method to set a value on a single cell in a table.
/// </summary>
static void SetCellValue(Range cell, object value)
{
    cell.set_Value(XlRangeValueDataType.xlRangeValueDefault, value);
}

/// <summary>
/// Helper Method to set a value on a single cell in a specific
/// WorkSheet.
/// </summary>
static void SetCellValue(Worksheet targetSheet, string cell,
    object value)
{
    targetSheet.get_Range(cell, useDefault).set_Value(
        XlRangeValueDataType.xlRangeValueDefault, value);
}

private static void AddCustomerData(Worksheet sheet,
    ref ListObject table)
{
    ListRow currentRow = null;

    // Initialize the CustomerData table.
    table = sheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
        useDefault, useDefault, XlYesNoGuess.xlYes, "A1");
    table.Name = "CustomerData";

    // Set up the table headers.
    SetCellValue(((Range)table.HeaderRowRange[1, 1]), "Name");
    SetCellValue(((Range)table.HeaderRowRange[1, 2]), "Phone");
    SetCellValue(((Range)table.HeaderRowRange[1, 3]), "Address");
    SetCellValue(((Range)table.HeaderRowRange[1, 4]), "City");
    SetCellValue(((Range)table.HeaderRowRange[1, 5]), "State");

    // Add the data to the table.
    currentRow = table.ListRows.Add(useDefault);

    // First insert inserts two data rows, removes the extra one.
    ((Range)table.DataBodyRange.Rows[2,
        useDefault]).Delete(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "Company A");
    SetCellValue((Range)currentRow.Range[1, 2], "(206)111-1111");
    SetCellValue((Range)currentRow.Range[1, 3], "123 1st Street");
    SetCellValue((Range)currentRow.Range[1, 4], "Seattle");
    SetCellValue((Range)currentRow.Range[1, 5], "WA");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "Company B");
    SetCellValue((Range)currentRow.Range[1, 2], "(425)222-2222");
    SetCellValue((Range)currentRow.Range[1, 3], "456 2st Avenue");
    SetCellValue((Range)currentRow.Range[1, 4], "Bellevue");
    SetCellValue((Range)currentRow.Range[1, 5], "WA");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "Company C");
    SetCellValue((Range)currentRow.Range[1, 2], "(509)333-3333");
    SetCellValue((Range)currentRow.Range[1, 3], "789 3rd Street");
    SetCellValue((Range)currentRow.Range[1, 4], "Richland");
    SetCellValue((Range)currentRow.Range[1, 5], "WA");

    // Release Excel objects
    currentRow = null;
}

private static void AddOrderData(Worksheet sheet, ref ListObject table)
{
    ListRow currentRow = null;

    // Initialize the OrderData table.
    table = sheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
        useDefault, useDefault, XlYesNoGuess.xlYes, "A1");
    table.Name = "OrderData";

    // Set up the table headers.
    SetCellValue(((Range)table.HeaderRowRange[1, 1]), "Order Number");
    SetCellValue(((Range)table.HeaderRowRange[1, 2]), "Date");
    SetCellValue(((Range)table.HeaderRowRange[1, 3]), "Customer");
    SetCellValue(((Range)table.HeaderRowRange[1, 4]), "Amount");
    SetCellValue(((Range)table.HeaderRowRange[1, 5]), "Shipping");
    SetCellValue(((Range)table.HeaderRowRange[1, 6]), "Status");

    // Add the data to the table.
    currentRow = table.ListRows.Add(useDefault);

    // First insert inserts two data rows, removes the extra one.
    ((Range)table.DataBodyRange.Rows[2,
        useDefault]).Delete(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "1111");
    SetCellValue((Range)currentRow.Range[1, 2], "1/15/2009");
    SetCellValue((Range)currentRow.Range[1, 3], "Company A");
    SetCellValue((Range)currentRow.Range[1, 4], "100.00");
    SetCellValue((Range)currentRow.Range[1, 5], "5.00");
    SetCellValue((Range)currentRow.Range[1, 6], "Closed");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "1112");
    SetCellValue((Range)currentRow.Range[1, 2], "2/12/2009");
    SetCellValue((Range)currentRow.Range[1, 3], "Company A");
    SetCellValue((Range)currentRow.Range[1, 4], "200.00");
    SetCellValue((Range)currentRow.Range[1, 5], "20.00");
    SetCellValue((Range)currentRow.Range[1, 6], "Closed");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "1113");
    SetCellValue((Range)currentRow.Range[1, 2], "3/17/2009");
    SetCellValue((Range)currentRow.Range[1, 3], "Company A");
    SetCellValue((Range)currentRow.Range[1, 4], "150.00");
    SetCellValue((Range)currentRow.Range[1, 5], "10.00");
    SetCellValue((Range)currentRow.Range[1, 6], "New");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "2221");
    SetCellValue((Range)currentRow.Range[1, 2], "12/11/2008");
    SetCellValue((Range)currentRow.Range[1, 3], "Company B");
    SetCellValue((Range)currentRow.Range[1, 4], "15.00");
    SetCellValue((Range)currentRow.Range[1, 5], "10.00");
    SetCellValue((Range)currentRow.Range[1, 6], "Closed");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "2222");
    SetCellValue((Range)currentRow.Range[1, 2], "12/12/2008");
    SetCellValue((Range)currentRow.Range[1, 3], "Company B");
    SetCellValue((Range)currentRow.Range[1, 4], "20.00");
    SetCellValue((Range)currentRow.Range[1, 5], "25.00");
    SetCellValue((Range)currentRow.Range[1, 6], "Closed");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "3331");
    SetCellValue((Range)currentRow.Range[1, 2], "03/05/2009");
    SetCellValue((Range)currentRow.Range[1, 3], "Company C");
    SetCellValue((Range)currentRow.Range[1, 4], "2000.00");
    SetCellValue((Range)currentRow.Range[1, 5], "300.00");
    SetCellValue((Range)currentRow.Range[1, 6], "Shipped");

    currentRow = table.ListRows.Add(useDefault);
    SetCellValue((Range)currentRow.Range[1, 1], "3332");
    SetCellValue((Range)currentRow.Range[1, 2], "03/14/2009");
    SetCellValue((Range)currentRow.Range[1, 3], "Company C");
    SetCellValue((Range)currentRow.Range[1, 4], "430");
    SetCellValue((Range)currentRow.Range[1, 5], "44.00");
    SetCellValue((Range)currentRow.Range[1, 6], "New");

    // Add a totalAmount column.
    // Formula will be automatically filled into existing rows.
    ListColumn totalColumn = table.ListColumns.Add(6);
    totalColumn.Name = "Total";
    ((Range)totalColumn.DataBodyRange[1, 1]).Value2 = 
        "=[Shipping]+[Amount]";

    // Release Excel objects
    currentRow = null;
    totalColumn = null;
}

When you build and run the solution, it creates an Excel workbook named MasterDetailView.xlsx that is located in the C:\Temp folder. To change the name or the location of the workbook, modify the workBookName variable in the sample code.

The MasterDetailView.xlsx workbook has three worksheets - Customer, Order, and OrderReport.

The Customer worksheet contains customer data in a table named CustomerData.

Figure 3. Customer worksheet displays customer data

Customer worksheet displays customer data

 

The Order worksheet contains order data in a table named OrderData.

Figure 4. Order worksheet displays data about the order

Order worksheet displays data about the order

 

The OrderReport worksheet contains the master-detail view of the customer and order data. Select an item in the Customer dropdown to display the customer data in the Customer Info (master) region of the worksheet and to display the associated order data in the Order Info (detail) region of the worksheet.

Figure 5. OrderReport worksheet displays the master view

OrderReport worksheet displays the master view

Read It

Structured references allow formulas that reference data in tables to be automatically adjusted as table rows and columns are added and deleted. Column specifiers that are derived from column headers can be used to reference column data. In addition, structured references define special item specifiers that allow formulas to reference specific parts of a table, such as a header or totals row.

The sample code in the Code It section of this visual how-to topic uses two helper methods, AddCustomerData and AddOrderData, to populate two worksheets with the customer and order data displayed in the master-detail report worksheet. Both methods create tables by using the WorkSheet.ListObjects.Add method.

Sub AddCustomerData(ByRef sheet As Worksheet, _
                    ByRef table As ListObject)

    Dim currentRow As ListRow = Nothing
    ' Initialize the CustomerData table.
    table = sheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,, _
    , XlYesNoGuess.xlYes, "A1")

    table.Name = "CustomerData"
    …

private static void AddCustomerData(Worksheet sheet,
    ref ListObject table)
{
    ListRow currentRow = null;

    // Initialize the CustomerData table.
    table = sheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
        useDefault, useDefault, XlYesNoGuess.xlYes, "A1");

    table.Name = "CustomerData";
    …

The master-detail view is created in the Main method or Sub. The code creates a dropdown list of customers that is based on the list of customers in the Customers worksheet. Users can then select the customer that they want to filter on from that list.

' Initialize control that is used to select a customer.
SetCellValue(reportSheet, "B1", "Customer")

Dim customerNames As Name = _
    reportSheet.Names.Add("CustomerNames", _
        customerTable.ListColumns(1).DataBodyRange, _
        True, , , , , , , , )

Dim customerSelection As Name = _
    reportSheet.Names.Add("CustomerSelection", _
        reportSheet.Range("C1", "C1"), _
        True, , , , , , , , )

customerSelection.RefersToRange.Validation.Add( _
    XlDVType.xlValidateList, _
    XlDVAlertStyle.xlValidAlertStop, , _
    "=CustomerNames", )
…

// Initialize control that is used to select a customer.
SetCellValue(reportSheet, "B1", "Customer");
Name customerNames =
    reportSheet.Names.Add("CustomerNames",
    customerTable.ListColumns[1].DataBodyRange,
    true, useDefault, useDefault, useDefault, useDefault,
    useDefault, useDefault, useDefault, useDefault);

Name customerSelection = 
    reportSheet.Names.Add("CustomerSelection", 
    reportSheet.get_Range("C1", "C1"), true, useDefault, 
    useDefault, useDefault, useDefault, useDefault, 
    useDefault, useDefault, useDefault);

customerSelection.RefersToRange.Validation.Add(
    XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop,
    useDefault, "=CustomerNames", useDefault);
…

The code then creates the master part of the master-detail view by inserting formulas into cells that are in the reporting worksheet. Those formulas use the VLOOKUP and MATCH functions as well as structured references that use the #Headers special item specifier. The CustomerSelection named object is referenced in the VLOOKUP function so that the master customer information can be updated as users select different customer filters.

' Master Customer Information Display.
SetCellValue(reportSheet, "B3", "Customer Info")
SetCellValue(reportSheet, "B4", "Name")
SetCellValue(reportSheet, "C4", _
    "=VLOOKUP(CustomerSelection, " & _
    "CustomerData, MATCH(B4, " & _
    "CustomerData[#Headers], 0), " & _
    "FALSE)")
SetCellValue(reportSheet, "B5", "Phone")
SetCellValue(reportSheet, "C5", _
    "=VLOOKUP(CustomerSelection, " & _
    "CustomerData, MATCH(B5, " & _
    "CustomerData[#Headers], 0), " & _
    "FALSE)")
SetCellValue(reportSheet, "B6", "Address")
SetCellValue(reportSheet, "C6", _
    "=VLOOKUP(CustomerSelection, " & _
    "CustomerData, MATCH(B6, " & _
    "CustomerData[#Headers], 0), " & _
    "FALSE)")
SetCellValue(reportSheet, "B7", "City")
SetCellValue(reportSheet, "C7", _
    "=VLOOKUP(CustomerSelection, " & _
    "CustomerData, MATCH(B7, " & _
    "CustomerData[#Headers], 0), " & _
    "FALSE)")
SetCellValue(reportSheet, "B8", "State")
SetCellValue(reportSheet, "C8", _
    "=VLOOKUP(CustomerSelection, " & _
    "CustomerData, MATCH(B8, " & _
    "CustomerData[#Headers], 0), " & _
    "FALSE)")
…

// Master Customer Information Display.
SetCellValue(reportSheet, "B3", "Customer Info");
SetCellValue(reportSheet, "B4", "Name");
SetCellValue(reportSheet, "C4",
    "=VLOOKUP(CustomerSelection, CustomerData, " +
    "MATCH(B4, CustomerData[#Headers], 0), FALSE)");
SetCellValue(reportSheet, "B5", "Phone");
SetCellValue(reportSheet, "C5", 
    "=VLOOKUP(CustomerSelection, CustomerData, " +
    "MATCH(B5, CustomerData[#Headers], 0), FALSE)");
SetCellValue(reportSheet, "B6", "Address");
SetCellValue(reportSheet, "C6", 
    "=VLOOKUP(CustomerSelection, CustomerData, " +
    "MATCH(B6, CustomerData[#Headers], 0), FALSE)");
SetCellValue(reportSheet, "B7", "City");
SetCellValue(reportSheet, "C7", 
    "=VLOOKUP(CustomerSelection, CustomerData, " +
    "MATCH(B7, CustomerData[#Headers], 0), FALSE)");
SetCellValue(reportSheet, "B8", "State");
SetCellValue(reportSheet, "C8", 
    "=VLOOKUP(CustomerSelection, CustomerData, " +
    "MATCH(B8, CustomerData[#Headers], 0), FALSE)");
…

The detail part of the master-detail view is created by using a combination of the INDEX, SMALL, ROW, and MATCH functions and structured references. As with the formulas for the master part of the view, the formulas for the detail part reference the CustomerSelection named object with the VLOOKUP function so that the order detail information can be updated as users select different customer filters.

' Set the formulas on the first data row.
currentRange = reportSheet.Range("B12", "B12")
currentRange.FormulaArray = _
    "=IFERROR(INDEX(OrderData, " & _
    "SMALL(IF(OrderData[Customer]=CustomerSelection, " & _
    "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " & _
    "ROW(1:1)), " & _
    "MATCH(""Order Number"", OrderData[#Headers],0)),"""")"
…

// Set the formulas on the first data row.
currentRange = reportSheet.get_Range("B12", "B12");
currentRange.FormulaArray = "=IFERROR(INDEX(OrderData, " +
    "SMALL(IF(OrderData[Customer]=CustomerSelection, " +
    "ROW(OrderData[Customer])-ROW(OrderData[#Headers])), " +
    "ROW(1:1)), " +
    "MATCH(\"Order Number\", OrderData[#Headers],0)),\"\")";
…

For a detailed explanation of the formulas that are used to create the master-detail functionality shown here, read the Creating a Master-Detail View in Excel blog entry by Dan Battagin, a Program Manager on the Excel team.

Explore It