In this section, we export all parameters of all elements in the Revit model to Excel, sorted by category into different sheets. We create a separate sheet per each category. The first row of the sheet is a header listing the parameter names for the given category. It is followed by a row of values for each element. Note that we need to reference the Excel COM library. There is no native .NET API available for the Microsoft Office products, so we use COM via COM callable wrappers, also known as COM-Interop in .NET.
We implement a new command Lab4_2_ExportParametersToExcel
.
In a first step, we iterate over all elements and sort them into
separate element sets, one for each category:
/// <summary> /// Export all parameters for each model element to Excel, one sheet per category. /// </summary> public class Lab4_2_ExportParametersToExcel : IExternalCommand { public IExternalCommand.Result Execute( ExternalCommandData commandData, ref string message, ElementSet elements ) { Application app = commandData.Application; Document doc = app.ActiveDocument; // extract and group the data from Revit in a dictionary, where // the key is the category name and the value is a list of elements. //Autodesk.Revit.Collections.Map sortedElements = app.Create.NewMap(); Dictionary<string, List<Element>> sortedElements = new Dictionary<string, List<Element>>(); // iterate all non-symbol elements and store in dictionary ElementIterator iter = doc.Elements; while( iter.MoveNext() ) { Element element = iter.Current as Element; if( !(element is Symbol) ) { Category category = element.Category; if( null != category ) { List<Element> elementSet; // If we already have this Key, get its Value (Set) // Otherwise, create the new Key and Value (Set if( sortedElements.ContainsKey( category.Name ) ) { elementSet = sortedElements[category.Name]; } else { elementSet = new List<Element>(); sortedElements.Add( category.Name, elementSet ); } // Add the element to the Set elementSet.Add( element ); } } } // // todo: export parameters // return IExternalCommand.Result.Succeeded; } }
' Exports list of parameters for each model element to Excel, one sheet per category Public Class Lab4_2_ExportParametersToExcel Implements IExternalCommand Public Function Execute( _ ByVal commandData As ExternalCommandData, _ ByRef message As String, _ ByVal elements As ElementSet) _ As IExternalCommand.Result Implements IExternalCommand.Execute Dim revitApp As Revit.Application = commandData.Application Dim doc As Revit.Document = revitApp.ActiveDocument ' First extract and group the data from Revit in a convenient Map class: ' (Key=category name, Val=Set of Elements) '------------------------------------------------------------------------ Dim sortedElements As Autodesk.Revit.Collections.Map = revitApp.Create.NewMap() ' Iterate all non-Symbol elements and store in map Dim iter As IEnumerator = doc.Elements Do While (iter.MoveNext()) ' We look for all non-Symbol Elements which have a Category Dim element As Revit.Element = iter.Current If Not (TypeOf element Is Symbol) Then Dim category As Category = element.Category If Not (category Is Nothing) Then Dim elementSet As ElementSet ' If we already have this Key, get its Value (Set) ' Otherwise, create the new Key and Value (Set If sortedElements.Contains(category.Name) Then elementSet = sortedElements.Item(category.Name) Else elementSet = revitApp.Create.NewElementSet() sortedElements.Insert(category.Name, elementSet) End If ' Add the element to the Set elementSet.Insert(element) End If End If Loop ' ' todo: export parameters ' Return IExternalCommand.Result.Succeeded End Function End Class
At this point you can already compile and link the project, update the Revit.ini file and run and debug the command to analyse the categories and elements collected.
Launch Excel via COM interop and remove the automatically created worksheets, as far as possible ... unfortunately, at least one remains, so we have to adjust the code populating them to account for the one left:
// Launch/Get Excel via COM Interop: X.Application excel = new X.Application(); if( null == excel ) { LabUtils.ErrorMsg( "Failed to get or start Excel." ); return IExternalCommand.Result.Failed; } excel.Visible = true; X.Workbook workbook = excel.Workbooks.Add( Missing.Value ); X.Worksheet worksheet; while( 1 < workbook.Sheets.Count ) // we cannot delete all work sheets, excel requires at least one { worksheet = workbook.Sheets.get_Item( 1 ) as X.Worksheet; worksheet.Delete(); }
' Export parameters ' Launch/Get Excel (via COM Interop) '----------------------------------- ' Use the following line for Excel 2003 (11.0) and similar in the rest of this lab... Dim excel As MsExcel.Application = New MsExcel.ApplicationClass() '... or this for up to Excel 2002 (10.0): 'Dim excel As Excel.Application = New Excel.ApplicationClass() If (excel Is Nothing) Then MsgBox("Failed to get or start Excel!?") Return IExternalCommand.Result.Failed End If excel.Visible = True ' Make it visible "live" to the user ' Add a new work-book and delete the 3 default work-sheets Dim workbook As MsExcel.Workbook = excel.Workbooks.Add() Dim worksheet As MsExcel.Worksheet Do While workbook.Sheets.Count > 1 worksheet = workbook.Sheets.Item(1) worksheet.Delete() Loop
Loop through the element sets for each category. For each category determine all occurring parameters and use these to create the header. Then loop through the elements and export their values one by one:
// Loop all collected Categories and create one worksheet for each except first //KeyCollection List<string> keys = new List<string>( sortedElements.Keys ); keys.Sort(); keys.Reverse(); // the worksheet added last shows up first in the excel tab bool first = true; foreach( string categoryName in keys ) { List<Element> elementSet = sortedElements[categoryName]; // create and name the worksheet if( first ) { worksheet = workbook.Sheets.get_Item( 1 ) as X.Worksheet; first = false; } else { worksheet = excel.Worksheets.Add( Missing.Value, Missing.Value, Missing.Value, Missing.Value ) as X.Worksheet; } worksheet.Name = categoryName; // we could find the list of Parameter names available for ALL the Elements // in this Set, but let's keep it simple and use all parameters encountered: //Autodesk.Revit.Collections.Set allParamNamesEncountered = app.Create.NewSet(); List<string> allParamNamesEncountered = new List<string>(); // loop through all the elements passed to the method foreach( Element el in elementSet ) { ParameterSet parameters = el.Parameters; if( !parameters.IsEmpty ) { // an easier way to loop the parameters than ParameterSetIterator: foreach( Parameter parameter in parameters ) { string name = parameter.Definition.Name; if( !allParamNamesEncountered.Contains( name ) ) { allParamNamesEncountered.Add( name ); } } } } allParamNamesEncountered.Sort(); // add the HEADER row in Bold worksheet.Cells[1, 1] = "ID"; int column = 2; foreach( string paramName in allParamNamesEncountered ) { worksheet.Cells[1, column] = paramName; ++column; } worksheet.get_Range( "A1", "Z1" ).Font.Bold = true; worksheet.get_Range( "A1", "Z1" ).EntireColumn.AutoFit(); int row = 2; foreach( Element elem in elementSet ) { // first column is the element id, which we display as an integer worksheet.Cells[row, 1] = elem.Id.Value; column = 2; foreach( string paramName in allParamNamesEncountered ) { string paramValue; try { paramValue = LabUtils.GetParameterValue( elem.get_Parameter( paramName ) ); } catch( Exception ) { paramValue = "*NA*"; } worksheet.Cells[row, column] = paramValue; ++column; } ++row; } // row } // category = worksheet
' Loop all collected Categories and create one worksheet for each Dim mapIter As Autodesk.Revit.Collections.MapIterator = sortedElements.ForwardIterator Do While (mapIter.MoveNext()) ' retrieve stored category and ElementSet Dim categoryName As String = mapIter.Key Dim elementSet As Autodesk.Revit.ElementSet = mapIter.Current ' create and name the worksheet worksheet = excel.Worksheets.Add() worksheet.Name = categoryName ' we could find the list of Parameter names available for ALL the Elements in this Set, ' but let's keep it simple and use all parameters encountered (will Try-Catch later) Dim allParamNamesEncountered As Autodesk.Revit.Collections.Set = app.Create.NewSet() ' loop through all the elements passed to the method Dim setIter As IEnumerator = elementSet.ForwardIterator Do While (setIter.MoveNext()) Dim el As Autodesk.Revit.Element = setIter.Current Dim parameters As Autodesk.Revit.ParameterSet = el.Parameters If Not (parameters.IsEmpty) Then 'Another way to loop the parameters is via ParameterSetIterator: Dim definitionNames As Autodesk.Revit.Collections.Set = app.Create.NewSet() Dim paramIter As Autodesk.Revit.ParameterSetIterator = parameters.ForwardIterator Do While paramIter.MoveNext() Dim parameter As Autodesk.Revit.Parameter = paramIter.Current Dim name As String = parameter.Definition.Name If Not allParamNamesEncountered.Contains(name) Then allParamNamesEncountered.Insert(name) End If Loop End If Loop ' add the HEADER row in Bold worksheet.Cells(1, 1).Value = "ID" Dim paramName As String Dim column As Integer = 2 For Each paramName In allParamNamesEncountered worksheet.Cells(1, column).Value = paramName excel.Columns(column).EntireColumn.AutoFit() column = column + 1 Next excel.Rows("1").Font.Bold = True ' finally, export a row per each element that belongs to the category Dim elem As Revit.Element Dim row As Integer = 2 For Each elem In elementSet ' first column is the element id (display it as an integer) worksheet.Cells(row, 1).Value = elem.Id.Value ' the other columns are parameter values column = 2 For Each paramName In allParamNamesEncountered Dim paramValue As String Try paramValue = LabUtils.GetParameterValue(elem.Parameter(paramName)) Catch paramValue = "*NA*" End Try worksheet.Cells(row, column).Value = paramValue column = column + 1 Next row = row + 1 Next ' row Loop ' categories (worksheets)
Run and debug the command and observe and discuss the results.
next previous home copyright © 2007-2009 jeremy tammik, autodesk inc. all rights reserved.