Thursday, March 31, 2011

Kickin it with Excel - Part 1

So you love and Cherish Revit... and are fond of Excel... you want to share model data between Excel and your Revit models... here's step one... exporting data to Excel from a Revit model using the API

Please keep in mind that I can't give away ALL of my secrets, so this one is provided as a means to get you started...

This post assumes that you have 64 Bit Revit and 32 bit Microsoft Excel 2010 installed and have mad skills with a Bow Staff.

First set a reference to the following:



Now all you need is a handy class for opening an Excel document that you can write stuff to. This really doesn't need much explanation and should get you all greased up and ready for Part 2 (TBD):


Imports System.Windows.Forms
Imports System.IO
Imports Microsoft.Office.Interop

Public Class clsUtilityExcel

    Private m_ExcelSession As Excel.Application = New Excel.ApplicationClass

    ''' <summary>
    ''' Bind data to Excel
    ''' </summary>
    ''' <param name="path"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function BindToExcel(ByVal path As String) As Excel.Application
        Dim m_Excel As Excel.Application
        Dim m_WB As Excel.Workbook
        If path.Trim() = "" Then
            Try
                m_WB = m_ExcelSession.Workbooks.Add(Type.Missing)
                While m_ExcelSession.Worksheets.Count > 1
                    Dim m_WS As Excel.Worksheet
                    m_WS = TryCast(m_ExcelSession.Worksheets(1), Excel.Worksheet)
                    m_WS.Delete()
                End While
                m_Excel = m_WB.Application
                m_Excel.Visible = True
                Return m_Excel
            Catch exception As Exception
                MessageBox.Show("Unable to start Excel session with file. " & _
                                vbCr & vbCr & _
                                "System Error Message: " & vbCr & _
                                exception.Message, "Error")
                Return Nothing
            End Try
        Else
            If Not File.Exists(path) Then
                MessageBox.Show("Unable to locate file: " & path & ".", "Error")
                Return Nothing
            End If
            Try
                m_WB = DirectCast(System.Runtime.InteropServices.Marshal.BindToMoniker(path), Excel.Workbook)
                If m_WB.Application.ActiveWorkbook Is Nothing Then
                    Dim m_FileName As String
                    m_FileName = path.Substring(path.LastIndexOf("\") + 1)
                    m_WB.Application.Windows(m_FileName).Visible = True
                End If
                m_Excel = m_WB.Application
                m_Excel.Visible = True
                Return m_Excel
            Catch exception As Exception
                MessageBox.Show("Unable to find or start Excel with file: " & path & ". " & _
                                vbLf & vbLf & "System Error Message: " & vbLf & _
                                exception.Message, "Error")
                Return Nothing
            End Try
        End If
    End Function

    ''' <summary>
    ''' Launch an Excel Session
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function LaunchExcel() As Excel.Application
        Dim m_Worksheet As Excel.Worksheet
        Dim m_Workbook As Excel.Workbook = m_ExcelSession.Workbooks.Add(Type.Missing)
        Try
            If m_ExcelSession Is Nothing Then
                Return Nothing
            End If
            While m_ExcelSession.Worksheets.Count > 1
                m_Worksheet = TryCast(m_ExcelSession.Worksheets(1), Excel.Worksheet)
                m_Worksheet.Delete()
            End While
            Return m_ExcelSession
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function

End Class

I guess you'll have to stay tuned on how the magic is done to get the data from the elements and into the Excel worksheet...