revit二次开发API示例 RoomSchedule房间的创建和修改Excel 数据的导入和导出

应用程序名称：RoomSchedule

Revit平台：Architecture

Revit版本：2011.0

首次发布于：2008.2

编程语言：C#

技能水平：高

类别：房间/空间

类型：ExternalCommand, ExternalApplication

主题：房间的创建和修改；Excel 数据的导入和导出

概述：

本示例演示了如何检索电子表格数据、如何创建未放置房间以及如何使用与所映射的房间相关的数据更新电子表格数据

相关类：

Autodesk.Revit.UI.IExternalCommand

Autodesk.Revit.DB.Document

Autodesk.Revit.DB.Architecture.Room

Autodesk.Revit.DB.Level

Autodesk.Revit.DB.Phase

Autodesk.Revit.DB.Category

Autodesk.Revit.DB.Parameters.BuildInParameter

Autodesk.Revit.DB.Parameters.Definition

Autodesk.Revit.DB.Parameters.DefinitionGroup

Autodesk.Revit.DB.Parameters.InstanceBinding

Autodesk.Revit.DB.Events.DocumentSaving

Autodesk.Revit.DB.Events.DocumentSavingAs

Autodesk.Revit.DB.Events.DocumentClosed

System.Data.OleDb.OleDbConnection

System.Data.OleDb.OleDbCommand

System.Data.DataTable

项目文件:

Command.cs

这个文件包含了类Command，继承了IExternalCommand接口并实现了Execute方法，这个类会弹出房间计划表单。

CtrlApplication.cs

这个文件包含类CtrlApplication，实现了IExternalApplication接口，这个类会在OnStartup方法中订阅DocumentSaving/SavingAs事件，在OnShutdown方法中取消这些事件的订阅。

在OnStartup方法中，会订阅。 

RoomScheduleForm.cs

这个文件包含一个Form类，其中包含两个DataGridView控件和三个ComboBox控件。电子表格和可用的房间、楼层和阶段的数据将显示在这些控件中。  

RoomsData.cs

这个文件将用于从当前Revit项目中检索可用的房间、楼层和阶段数据，并生成用于在DataGridView控件中显示的房间DataTable数据。 

XlsDBConnector.cs

这个文件将用于连接到Excel电子表格文件(.xls)，检索可用的表并生成用于在DataGridView控件中显示的DataTable数据；此外，这个类将用于更新电子表格数据或将新数据插入到电子表格中。 

EventsReactor.cs

这个文件定义了类EventsReactor，其中包含了三个委托方法，用于DocumentSaving/SavingAs/Closing事件，当这些事件被触发时，委托方法会相应地进行更新。

EventsReactor类还包括实现电子表格和Revit房间更新的方法，更新时，相关的日志信息将被记录在日志文件(RoomSchedule.log)中，供您以后查看。

描述：

这个示例主要使用OleDb.OleDbConnection、OleDb.OleDbCommand以及相关的Revit类来将基于电子表格的房间计划表与Revit房间同步。这个示例实现了两个功能：从电子表格中导入房间计划表以及使用Revit数据更新电子表格中的房间面积字段。

- 使用.NET的OleDb.OleDbConnection、OleDbCommand、DataTable类处理.xls文件（读取、更新）。

- 要在指定阶段创建房间，使用Creation.Document.NewRoom(Phase)方法。

- 要获取所需的元素，使用Document.get_Elements(Filter)方法。

- 要获取房间的参数，使用Room.get_Parameter(String paramName)方法。

- 要获取每个PlanTopology中的所有房间，使用PlanTopology.Rooms属性。

- 订阅和实现了控制应用程序级别的DocumenSaving/DocumentSavedAs/DocumentClosed的相关功能。

- DefinitionFile、DefinitionGroup、Definition和InstanceBinding类将用于为房间创建共享参数。

指示：

1.将提供的.addin文件复制到示例文件夹下的Revit安装文件夹中，并为dll指定完整路径。

2.在运行此示例之前，请确保Excel文件有一个工作表，该工作表必须在第一行包含“ID”、“Room Area”、“Room Name”、“Room Number”、“Room Comments”列，ID和Area列应为数值，其他列应为文本值，并且电子表格应是可写的。在项目文件夹下有一个名为RoomSchedule.xls的Excel文件，您可以使用该文件作为数据定义的示例，或直接在以下步骤中导入此文件。

3.直接运行此示例，"房间计划表"表单将弹出，所有可用的楼层和阶段将分别列在ComboBox控件中。

4.点击“导入Excel...”按钮将允许您选择一个电子表格文件。此示例将检索此工作表文件中的所有可用工作表，并将它们列在ComboBox控件中。

5.选择定义房间的工作表，然后这个工作表中的所有房间数据将显示在DataGridView控件中。

6.选择一个阶段，然后单击“创建未放置房间”将根据电子表格房间数据创建未放置的房间(新的未放置房间的属性将由电子表格数据中的相关列设置)。将添加一个名为“External Room ID”的共享参数到房间类别中，并将参数值映射到电子表格房间的ID中。在房间创建后也会订阅Document Save、SaveAs事件。

7.选择“显示所有房间”将显示当前项目中所有可用的房间(已放置和未放置的房间)，选择一个楼层将过滤所有在选择的楼层中的房间。

8.退出此示例，单击Revit基本选项卡中的“房间”，选择上述步骤中创建的房间并放置它们。单击保存/另存为命令来保存项目：在保存对话框之后，这个示例将通过映射和放置的房间的实际值更新工作表的“Room Area”、“Room Name”、“Room Number”和“Room Comments”列数据。您可以打开日志文件(RoomSchedule.log)查看更新的详细信息。

9.如果有用户手动创建和放置的新房间，则在调用保存或另存为时，这些房间将添加到电子表格中，如果房间没有评论，则电子表格中的“Room Comments”列将设置为“<Added from Revit>”。此外，新房间的“External Room ID”参数将在添加后设置为房间的ID值。

10.在创建未放置房间时，新房间的名称、编号和注释属性将自动由房间表中的“Room Name”、“Room Number”和“Room Comments”列值设置。

11.如果电子表格中的房间已经由Revit房间映射过了，则新建将被跳过(某些消息将弹出)，因为不允许有多个Revit房间映射到同一个电子表格房间。单击“清除External Room ID”按钮将清除现有映射(所有共享参数的值将被设置为null)，并允许用户创建更多的未放置房间。

注意：

1. 由于没有64位Jet OLE DB提供程序，因此该样本不支持64位Revit。

2. 由于Excel ISAM驱动程序的限制，如果Excel文件的名称、编号或注释列中既包含数字数据又包含文本数据，请将数字数据格式化为文本类型，方法是在数据前面添加单引号作为前缀。文本格式将使样本的读取和更新操作正常工作。再次提醒，通常手动或通过API创建的房间都将具有文本名称，如果Revit房间没有注释值，则注释列将填充文本“<null>”，因此请确保名称和注释列中没有数字数据。

3. 房间的名称和注释不应包含单引号（'），因为这会与SQL查询语句冲突。

源代码：

完整的源代码请加入QQ群649037449，在群文件中下载RevitSDK.exe，解压后在文件夹中搜索本文中应用程序名称即可获得完整源码

CtrlApplication.cs

EventsReactor.cs

//
// (C) Copyright 2003-2019 by Autodesk, Inc.
//
// Permission to use, copy, modify, and distribute this software in
// object code form for any purpose and without fee is hereby granted,
// provided that the above copyright notice appears in all copies and
// that both that copyright notice and the limited warranty and
// restricted rights notice below appear in all supporting
// documentation.
//
// AUTODESK PROVIDES THIS PROGRAM "AS IS" AND WITH ALL FAULTS.
// AUTODESK SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTY OF
// MERCHANTABILITY OR FITNESS FOR A PARTICULAR USE. AUTODESK, INC.
// DOES NOT WARRANT THAT THE OPERATION OF THE PROGRAM WILL BE
// UNINTERRUPTED OR ERROR FREE.
//
// Use, duplication, or disclosure by the U.S. Government is subject to
// restrictions set forth in FAR 52.227-19 (Commercial Computer
// Software - Restricted Rights) and DFAR 252.227-7013(c)(1)(ii)
// (Rights in Technical Data and Computer Software), as applicable.
//

using System;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;

using Autodesk.Revit;
using Autodesk.Revit.DB;
using Autodesk.Revit.DB.Events;
using Autodesk.Revit.DB.Architecture;
using System.IO;

namespace Revit.SDK.Samples.RoomSchedule
{
/// <summary>
/// One struct defines the content of mapped Excel spreadsheet: the full name of this file and the sheet.
/// Only the opened sheet is reserved by this struct.
/// </summary>
public class SheetInfo
{
#region Class Member Variables
/// <summary>
/// Excel file name, it's full path
/// </summary>
string m_fileName;

/// <summary>
/// Sheet table within excel file, it's opened by sample
/// </summary>
string m_sheetName;
#endregion

#region Class Public Methods
/// <summary>
/// Ctor method
/// </summary>
/// <param name="fileName">Full path name file.</param>
/// <param name="sheetName">The sheet name of spreadsheet which was opened.</param>
public SheetInfo(String fileName, String sheetName)
{
m_fileName = fileName;
m_sheetName = sheetName;
}

/// <summary>
/// Get or set the full name of spreadsheet file
/// </summary>
public string FileName
{
get { return m_fileName; }
set { m_fileName = value; }
}

/// <summary>
/// Get or set the sheet name within the spreadsheet, the sheet name was mapped by Revit rooms.
/// </summary>
public string SheetName
{
get { return m_sheetName; }
set { m_sheetName = value; }
}
#endregion
}

/// <summary>
/// Class consists of delegate methods of DocumentSaving/SavingAs and DocumentClosing events.
/// These delegates will be raised once document is about to be saved or closed.
/// But, delegate will update mapped spreadsheet only when user created rooms for current document.
/// (That's, user clicks the button "Create Unplaced Rooms" and new rooms was created successfully).
/// Otherwise, these events handler methods won't do any update even if they were raised.
/// </summary>
public sealed class EventsReactor : IDisposable
{
#region Class Global Static Variables
/// <summary>
/// Array of documents' hash code and mapped Excel file and opened table.
/// The mapped excel and its table will be updated when events DocumentSave/SaveAs are raised.
/// The update occurs only when new room was created according to excel spreadsheet.
/// </summary>
private Dictionary<int, SheetInfo> m_docMapDict = new Dictionary<int, SheetInfo>();

/// <summary>
/// Specified log file name
/// </summary>
private String m_logFile;

/// <summary>
/// Logging writer used to write logging to log specified log file.
/// It's not recommended to access m_logWriter and call it's method, because maybe it's not initialized yet.
/// Please call DumpLog to dump related logging
/// </summary>
private StreamWriter m_logWriter;
#endregion

#region Class Public Implementations
/// <summary>
/// This class will dump information to log file to tell user what happened
/// </summary>
/// <param name="logFile"></param>
public EventsReactor(String logFile)
{
m_logFile = logFile;
}

/// <summary>
/// Release the file handling
/// </summary>
public void Dispose()
{
if (null != m_logWriter)
{
// close the stream
m_logWriter.Flush();
m_logWriter.Close();
m_logWriter = null;
GC.SuppressFinalize(this);
}
}

/// <summary>
/// Finalizer, we need to ensure the file stream was closed
/// This destructor will run only if the Dispose method does not get called.
/// </summary>
~EventsReactor()
{
Dispose();
}

/// <summary>
/// Delegate for document save as event, it will update spreadsheet if document was mapped to spreadsheet.
/// </summary>
/// <param name="sender">Event sender.</param>
/// <param name="e">EventArgs of this event.</param>
public void DocumentSavingAs(object sender, DocumentSavingAsEventArgs e)
{
DumpLog("Raised DocumentSavingAs -> Document: " + Path.GetFileNameWithoutExtension(e.Document.Title));
UpdateMappedSpreadsheet(e.Document);
}

/// <summary>
/// Delegate for document save event, it will update spreadsheet if document was mapped to spreadsheet.
/// </summary>
/// <param name="sender">Event sender.</param>
/// <param name="e">EventArgs of this event.</param>
public void DocumentSaving(object sender, DocumentSavingEventArgs e)
{
DumpLog("Raised DocumentSaving -> Document: " + Path.GetFileNameWithoutExtension(e.Document.Title));
UpdateMappedSpreadsheet(e.Document);
}

/// <summary>
/// Removed the document which was closed, event reactor doesn't need to monitor this document any more.
/// DocumentId is designed to identify one document, it's equal to hash code of this document.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void DocumentClosed(object sender, DocumentClosedEventArgs e)
{
DumpLog("Raised DocumentClosed.");
m_docMapDict.Remove(e.DocumentId);
}

/// <summary>
/// Check if document is monitored by this event reactor
/// </summary>
/// <param name="docHashcode">Hashcode of document.</param>
/// <returns></returns>
public bool DocMonitored(int docHashcode)
{
return m_docMapDict.ContainsKey(docHashcode);
}

/// <summary>
/// Get the sheet information of document.
/// </summary>
/// <param name="hashCode">The hash code of document.</param>
/// <param name="sheetInfo">The mapped spread file and sheet information.</param>
/// <returns>Indicates whether find the spread sheet mapped by this document.
/// True if mapped spreadsheet information found, else false.</returns>
public bool DocMappedSheetInfo(int hashCode, ref SheetInfo sheetInfo)
{
if(!DocMonitored(hashCode))
{
return false;
}
else
{
return m_docMapDict.TryGetValue(hashCode, out sheetInfo);
}
}

/// <summary>
/// Update or reset the sheet information to which document is being mapped.
/// </summary>
/// <param name="hashCode">Hash code of document used as key to find mapped spreadsheet.</param>
/// <param name="newSheetInfo">New value for spreadsheet.</param>
public void UpdateSheeInfo(int hashCode, SheetInfo newSheetInfo)
{
if(!DocMonitored(hashCode))
{
m_docMapDict.Add(hashCode, newSheetInfo);
}
else
{
m_docMapDict.Remove(hashCode);
m_docMapDict.Add(hashCode, newSheetInfo);
}
}
#endregion

#region Class Implementations
/// <summary>
/// Update mapped spread sheet when document is about to be saved or saved as
/// This method will update spread sheet room data([Area] column) with actual area value of mapped Revit Room.
/// or add Revit room to spreadsheet if it is not mapped to room of spreadsheet. /// </summary>
/// <param name="activeDocument">Current active document.</param>
private void UpdateMappedSpreadsheet(Document activeDocument)
{
// Programming Routines:
//
// 1: Update spreadsheet when:
// a: there is room work sheet table;
// b: there is rooms data;
// c: shared parameter exists;
// 2: Skip update and insert operations for below rooms:
// a: the rooms are not placed or located;
// b: the rooms whose shared parameter(defined by sample) are not retrieved,
// some rooms maybe don't have shared parameter at all, despite user create for Rooms category.
// 3: Update spreadsheet rooms values by Revit room actual values.
// a: if shared parameter exists(is not null), update row by using this parameter's value;
// b: if shared parameter doesn't exist (is null), update row by Id value of room, which will avoid the duplicate
// ID columns occur in spreadsheet.
// 4: Insert Revit rooms data to spreadsheet if:
// a: failed to update values of rooms (maybe there no matched ID value in spread sheet rows).
//

#region Check Whether Update Spreadsheet Data
//
// check which table to be updated.
SheetInfo mappedXlsAndTable;
bool hasValue = m_docMapDict.TryGetValue(activeDocument.GetHashCode(), out mappedXlsAndTable);
if (!hasValue || null == mappedXlsAndTable ||
String.IsNullOrEmpty(mappedXlsAndTable.FileName) || String.IsNullOrEmpty(mappedXlsAndTable.SheetName))
{
DumpLog("This document isn't mapped to spreadsheet yet.");
return;
}

// retrieve all rooms in project(maybe there are new rooms created manually by user)
RoomsData roomData = new RoomsData(activeDocument);
if (roomData.Rooms.Count <= 0)
{
DumpLog("This document doesn't have any room yet.");
return;
}
#endregion

// create a connection and update values of spread sheet
int updatedRows = 0; // number of rows which were updated
int newRows = 0; // number of rows which were added into spread sheet
XlsDBConnector dbConnector = new XlsDBConnector(mappedXlsAndTable.FileName);

// check whether there is room table.
// get all available rooms in current document once more
int stepNo = -1;
DumpLog(System.Environment.NewLine + "Start to update spreadsheet room......");
foreach (Room room in roomData.Rooms)
{
// check Whether We Update This Room
stepNo++;
double roomArea = 0.0f;
String externalId = String.Empty;
if (!ValidateRevitRoom(activeDocument, room, ref roomArea, ref externalId))
{
DumpLog(String.Format("#{0}--> Room:{1} was skipped.", stepNo, room.Number));
continue;
}

// try to update
try
{

#region Update Spreadsheet Room
// flag used to indicate whether update is successful
bool bUpdateFailed = false; // reserve whether this room updated successfully.
// if room comment is empty, use <null> for mapped room, use <Added from Revit> for not mapped room in spread sheet.
bool bCommnetIsNull = false;

// get comments of room
String comments;
Parameter param = room.get_Parameter(BuiltInParameter.ALL_MODEL_INSTANCE_COMMENTS);
comments = (null != param) ? (param.AsString()) : ("");
if (String.IsNullOrEmpty(comments))
{
// this room doesn't have comment value
bCommnetIsNull = true;
// use <null> for room with empty comment by default when updating spread sheet
comments = "<null>";
}

// create update SQL clause,
// when filtering row to be updated, use Room.Id.IntegerValue if "External Room ID" is null.
String updateStr = String.Format(
"Update [{0}$] SET [{1}] = '{2}', [{3}] = '{4}', [{5}] = '{6}', [{7}] = '{8:N3}' Where [{9}] = {10}",
mappedXlsAndTable.SheetName, // mapped table name
RoomsData.RoomName, room.Name,
RoomsData.RoomNumber, room.Number,
RoomsData.RoomComments, comments,
RoomsData.RoomArea, roomArea,
RoomsData.RoomID, String.IsNullOrEmpty(externalId) ? room.Id.IntegerValue.ToString() : externalId);

// execute the command and check the size of updated rows
int afftectedRows = dbConnector.ExecuteCommnand(updateStr);
if (afftectedRows == 0)
{
bUpdateFailed = true;
}
else
{
// count how many rows were updated
DumpLog(String.Format("#{0}--> {1}", stepNo, updateStr));
updatedRows += afftectedRows;

// if "External Room ID" is null but update successfully, which means:
// in spreadsheet there is existing row whose "ID" value equals to room.Id.IntegerValue, so we should
// set Revit room's "External Room ID" value to Room.Id.IntegerValue for consistence after update .
if (String.IsNullOrEmpty(externalId))
{
SetExternalRoomIdToRoomId(room);
}
}
#endregion

#region Insert Revit Room

// Add this new room to spread sheet if fail to update spreadsheet
if (bUpdateFailed)
{
// try to insert this new room to spread sheet, some rules:
// a: if the "External Room ID" exists, set ID column to this external id value,
// if the "External Room ID" doesn't exist, use the actual Revit room id as the ID column value.
// b: use comments in room if room's description exists,
// else, use constant string: "<Added from Revit>" for Comments column in spreadsheet.

String insertStr =
String.Format("Insert Into [{0}$] ([{1}], [{2}], [{3}], [{4}], [{5}]) Values('{6}', '{7}', '{8}', '{9}', '{10:N3}')",
mappedXlsAndTable.SheetName, // mapped table name
RoomsData.RoomID, RoomsData.RoomComments, RoomsData.RoomName, RoomsData.RoomNumber, RoomsData.RoomArea,
(String.IsNullOrEmpty(externalId)) ? (room.Id.IntegerValue.ToString()) : (externalId), // Room id
(bCommnetIsNull || String.IsNullOrEmpty(comments)) ? ("<Added from Revit>") : (comments),
room.Name, room.Number, roomArea);

// try to insert it
afftectedRows = dbConnector.ExecuteCommnand(insertStr);
if (afftectedRows != 0)
{
// remember the number of new rows
String succeedMsg = String.Format("#{0}--> Succeeded to insert spreadsheet Room - Name:{1}, Number:{2}, Area:{3:N3}",
stepNo, room.Name, room.Number, roomArea);
DumpLog(succeedMsg);
newRows += afftectedRows;

// if the Revit room doesn't have external id value(may be a room created manually)
// set its "External Room ID" value to Room.Id.IntegerValue, because the room was added/mapped to spreadsheet,
// and the value of ID column in sheet is just the Room.Id.IntegerValue, we should keep this consistence.
if (String.IsNullOrEmpty(externalId))
{
SetExternalRoomIdToRoomId(room);
}
}
else
{
DumpLog(String.Format("#{0}--> Failed: {1}", stepNo, insertStr));
}
}
#endregion
}
catch (Exception ex)
{
// close the connection
DumpLog(String.Format("#{0}--> Exception: {1}", stepNo, ex.Message));
dbConnector.Dispose();
RoomScheduleForm.MyMessageBox(ex.Message, MessageBoxIcon.Warning);
return;
}
}
// close the connection
dbConnector.Dispose();

// output the affected result message
String sumMsg = String.Format("{0}:[{1}]: {2} rows were updated and {3} rows were added into successfully.",
Path.GetFileName(mappedXlsAndTable.FileName), mappedXlsAndTable.SheetName, updatedRows, newRows);
DumpLog(sumMsg);
DumpLog("Finish updating spreadsheet room." + System.Environment.NewLine);
}

/// <summary>
/// Check to see if we need to update spreadsheet data according to this Revit room.
/// We don't need to update spreadsheet rooms if Revit room:
/// . Which is one unplaced room.
/// . The room has area which is zero.
/// . Special room which doesn't have custom shared parameter at all.
/// </summary>
/// <param name="activeDocument">Current active document.</param>
/// <param name="roomObj">Room object to be checked.</param>
/// <param name="roomArea">Room area of this Revit room.</param>
/// <param name="externalId">The value of custom shared parameter of this room.</param>
/// <returns>Indicates whether it succeeded to get room area and shared parameter value.</returns>
private static bool ValidateRevitRoom(Document activeDocument, Room room, ref double roomArea, ref String externalId)
{
roomArea = 0.0f;
externalId = String.Empty;
if (null == room.Location || null == activeDocument.GetElement(room.LevelId))
{
return false;
}

// get Area of room, if converting to double value fails, skip this.
// if the area is zero to less than zero, skip the update too
try
{
// get area without unit, then converting it to double will be ok.
String areaStr = RoomsData.GetProperty(activeDocument, room, BuiltInParameter.ROOM_AREA, false);
roomArea = Double.Parse(areaStr);
if (roomArea <= double.Epsilon)
{
return false;
}
}
catch
{
// parse double value failed, continue the loop
return false;
}

// get the shared parameter value of room
Parameter externalIdSharedParam = null;
bool bExist = RoomsData.ShareParameterExists(room, RoomsData.SharedParam, ref externalIdSharedParam);
if (false == bExist || null == externalIdSharedParam)
{
return false;
}
else
{
externalId = externalIdSharedParam.AsString();
}
return true;
}

/// <summary>
/// Set shared parameter (whose name is "External Room ID") value to Room.Id.IntegerValue
/// </summary>
/// <param name="room">The room used to get the room which to be updated</param>
private static bool SetExternalRoomIdToRoomId(Room room)
{
try
{
Parameter shareParam = room.LookupParameter(RoomsData.SharedParam);
if (null != shareParam)
{
return shareParam.Set(room.Id.IntegerValue.ToString());
}
}
catch
{
// none
}
return false;
}

/// <summary>
/// Dump log file now
/// </summary>
private void DumpLog(String strLog)
{
// Create writer only when there is dump
if(null == m_logWriter) {
if (File.Exists(m_logFile))
{
File.Delete(m_logFile);
}
m_logWriter = new StreamWriter(m_logFile);
m_logWriter.AutoFlush = true;
}
//
// dump log now
m_logWriter.WriteLine(strLog);
}
#endregion
}
}

RoomsData.cs

//
// (C) Copyright 2003-2019 by Autodesk, Inc.
//
// Permission to use, copy, modify, and distribute this software in
// object code form for any purpose and without fee is hereby granted,
// provided that the above copyright notice appears in all copies and
// that both that copyright notice and the limited warranty and
// restricted rights notice below appear in all supporting
// documentation.
//
// AUTODESK PROVIDES THIS PROGRAM "AS IS" AND WITH ALL FAULTS.
// AUTODESK SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTY OF
// MERCHANTABILITY OR FITNESS FOR A PARTICULAR USE. AUTODESK, INC.
// DOES NOT WARRANT THAT THE OPERATION OF THE PROGRAM WILL BE
// UNINTERRUPTED OR ERROR FREE.
//
// Use, duplication, or disclosure by the U.S. Government is subject to
// restrictions set forth in FAR 52.227-19 (Commercial Computer
// Software - Restricted Rights) and DFAR 252.227-7013(c)(1)(ii)
// (Rights in Technical Data and Computer Software), as applicable.
//

using System;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;

using Autodesk.Revit;
using Autodesk.Revit.DB;
using Autodesk.Revit.DB.Architecture;

namespace Revit.SDK.Samples.RoomSchedule
{
/// <summary>
/// Iterates through the rooms in the project and get the information of all the rooms
/// </summary>
public class RoomsData
{
#region Class Constant Variables
/// <summary>
/// Constant name for RoomID, this column must exist in first row.
/// </summary>
public const String RoomID = "ID";

/// <summary>
/// Constant name for room area, this column must exist in first row
/// </summary>
public const String RoomArea = "Room Area";

/// <summary>
/// Constant named for room name, this column must exist in first row
/// </summary>
public const String RoomName = "Room Name";

/// <summary>
/// Constant name for room number, this column must exist in first row
/// </summary>
public const String RoomNumber = "Room Number";

/// <summary>
/// Constant name for room number, this column must exist in first row
/// </summary>
public const String RoomComments = "Room Comments";

/// <summary>
/// Constant name for shared parameter,
/// the mapped room id of spread sheet will saved in this parameter.
/// </summary>
public const String SharedParam = "External Room ID";
#endregion

#region Class Member Variables
/// <summary>
/// Active document to which this RoomsData instance belongs
/// </summary>
Document m_activeDocument;

/// <summary>
/// a list to store all rooms in the project
/// </summary>
List<Room> m_rooms = new List<Room>();

/// <summary>
/// parameters which will be displayed in DataGridView
/// </summary>
List<BuiltInParameter> m_parameters = new List<BuiltInParameter>();

/// <summary>
/// a list to store column names of Rooms
/// </summary>
List<String> m_columnNames = new List<string>();
#endregion

#region Class Properties
/// <summary>
/// A list of all the rooms in the project
/// </summary>
public ReadOnlyCollection<Room> Rooms
{
get
{
return new ReadOnlyCollection<Room>(m_rooms);
}
}
#endregion

#region Class Constructor Method
/// <summary>
/// Constructor
/// </summary>
/// <param name="activeDocument">Revit project.</param>
public RoomsData(Document activeDocument)
{
m_activeDocument = activeDocument;

// initialize the output parameters
InitializeParameters();

// get all the rooms in the project
GetAllRooms(activeDocument);
}
#endregion

#region Class Public Methods
/// <summary>
/// Update rooms data after room creation happens in Revit
/// </summary>
public void UpdateRoomsData()
{
// clear all rooms and re-retrieve data from Revit
m_rooms.Clear();
GetAllRooms(m_activeDocument);
}

/// <summary>
/// Get all parameters to be displayed in DataGridView.
/// </summary>
/// <param name="specifiedParams">all parameters specified by user.</param>
public void UpdateParameters(ReadOnlyCollection<BuiltInParameter> specifiedParams)
{
// if there is no instance, parameter setting is not allowed
if (m_rooms.Count <= 0)
{
throw new Exception("No element instance to set parameters");
}

// clear old parameters data
m_parameters.Clear();
m_columnNames.Clear();

// get column names of room by specified parameters
Room firstRoom = m_rooms[0];
foreach (BuiltInParameter param in specifiedParams)
{
// add this parameter
m_parameters.Add(param);

// store all specified parameter names of room.
Parameter toomPara = firstRoom.get_Parameter(param);
m_columnNames.Add(toomPara.Definition.Name);
}
}

/// <summary>
/// Generate all rooms which are located in specified level.
/// A DataTable data object will be generated after this method call.
/// </summary>
/// <param name="level">the specified level to retrieve rooms</param>
/// <returns>DataTable generated from rooms</returns>
public DataTable GenRoomsDataTable(Level level)
{
// get all Rooms information and generate a DataTable
if (m_rooms.Count == 0)
{
return null;
}

// generate columns by all parameters
DataTable newTable = new DataTable();
foreach (String col in m_columnNames)
{
DataColumn column = new DataColumn();
column.ColumnName = col;
column.ReadOnly = true;
column.DataType = System.Type.GetType("System.String");
newTable.Columns.Add(column);
}

// add constant column: External Room ID
DataColumn constantCol = new DataColumn();
constantCol.ColumnName = SharedParam;
constantCol.ReadOnly = true;
constantCol.DataType = System.Type.GetType("System.String");
newTable.Columns.Add(constantCol);

// filter rooms by level
foreach (Room room in m_rooms)
{
// check whether room is located at specified level
if ((null == level) || (m_activeDocument.GetElement(room.LevelId) != null && room.LevelId.IntegerValue == level.Id.IntegerValue))
{
DataRow dataRow = newTable.NewRow();
for (int i = 0; i < m_parameters.Count; i++)
{
dataRow[i] = GetProperty(m_activeDocument, room, m_parameters[i], true);
}

// add constant column value: External Room ID
Parameter param = null;
bool bExist = ShareParameterExists(room, SharedParam, ref param);
if (bExist && null != param && false == String.IsNullOrEmpty(param.AsString()))
{
dataRow[m_parameters.Count] = param.AsString();
}
else
{
dataRow[m_parameters.Count] = "<null>";
}

// add this row
newTable.Rows.Add(dataRow);
}
}

return newTable;
}

/// <summary>
/// Get the room property value according the parameter name
/// </summary>
/// <param name="activeDoc">Current active document.</param>
/// <param name="room">an instance of room class</param>
/// <param name="paraEnum">the parameter used to get parameter value</param>
/// <param name="useValue">convert parameter to value type or not.
/// if true, the value of parameter will be with unit.
/// if false, the value of parameter will be without unit.</param>
/// <returns>the string value of property specified by shared parameter</returns>
public static String GetProperty(Document activeDoc, Room room, BuiltInParameter paraEnum, bool useValue)
{
String propertyValue = null; //the value of parameter

// Assuming the build in parameter is legal for room.
// if the room is not placed, some properties are not available, i.g. Level name, Area ...
// trying to retrieve them will throw exception;
// however some parameters are available, e.g.: name, number
Parameter param;
try
{
param = room.get_Parameter(paraEnum);
}
catch (Exception)
{
// throwing exception for this parameter is acceptable if it's a unplaced room
if (null == room.Location)
{
propertyValue = "Not Placed";
return propertyValue;
}
else
{
throw new Exception("Illegal built in parameter.");
}
}

// get the parameter via the built in parameter
if (null == param)
{
return "";
}

// get the parameter's storage type and convert parameter to string
StorageType storageType = param.StorageType;
switch (storageType)
{
case StorageType.Integer:
int iVal = param.AsInteger();
propertyValue = iVal.ToString();
break;
case StorageType.String:
propertyValue = param.AsString();
break;
case StorageType.Double:
// AsValueString will make the return string with unit, it's appreciated.
if (useValue)
{
propertyValue = param.AsValueString();
}
else
{
propertyValue = param.AsDouble().ToString();
}
break;
case StorageType.ElementId:
Autodesk.Revit.DB.ElementId elemId = param.AsElementId();
Element elem = activeDoc.GetElement(elemId);
propertyValue = elem.Name;
break;
default:
propertyValue = param.AsString();
break;
}

return propertyValue;
}

/// <summary>
/// Check to see whether specified parameter exists in room object.
/// </summary>
/// <param name="roomObj">Room object used to get parameter</param>
/// <param name="paramName">parameter name to be checked</param>
/// <param name="sharedParam">shared parameter returned</param>
/// <returns>true, the parameter exists; false, the parameter doesn't exist</returns>
public static bool ShareParameterExists(Room roomObj, String paramName, ref Parameter sharedParam)
{
// get the parameter
try
{
sharedParam = roomObj.LookupParameter(paramName);
}
catch
{
}
return (null != sharedParam);
}
#endregion

#region Class Implementation
/// <summary>
/// Get all rooms in current Revit project
/// </summary>
private void GetAllRooms(Document activeDoc)
{
// get all room elements
// try to find all rooms in the project and add to the list
RoomFilter filter = new RoomFilter();
FilteredElementCollector collector = new FilteredElementCollector(activeDoc);
m_rooms = collector.WherePasses(filter).ToElements().Cast<Room>().ToList<Room>();
// sort rooms by number
m_rooms.Sort(CompRoomByNumber);
}

/// <summary>
/// Sort the rooms by number
/// </summary>
/// <param name="room1"></param>
/// <param name="room2"></param>
/// <returns></returns>
private static int CompRoomByNumber(Room room1, Room room2)
{
if (null == room1 || null == room2)
{
return -1;
}
return room1.Number.CompareTo(room2.Number);
}

/// <summary>
/// Initialize the parameters displayed in DataGridView control
/// </summary>
private void InitializeParameters()
{
// Room name
m_parameters.Add(BuiltInParameter.ROOM_NAME);
m_columnNames.Add("Name");

// Room Number
m_parameters.Add(BuiltInParameter.ROOM_NUMBER);
m_columnNames.Add("Number");

// Room Area
m_parameters.Add(BuiltInParameter.ROOM_AREA);
m_columnNames.Add("Area");

// Room Comments
m_parameters.Add(BuiltInParameter.ALL_MODEL_INSTANCE_COMMENTS);
m_columnNames.Add("Comments");

// Level
m_parameters.Add(BuiltInParameter.LEVEL_NAME);
m_columnNames.Add("Level");

// Phase
m_parameters.Add(BuiltInParameter.ROOM_PHASE);
m_columnNames.Add("Phase");
}
#endregion
}
}

XlsDBConnector.cs

//
// (C) Copyright 2003-2019 by Autodesk, Inc.
//
// Permission to use, copy, modify, and distribute this software in
// object code form for any purpose and without fee is hereby granted,
// provided that the above copyright notice appears in all copies and
// that both that copyright notice and the limited warranty and
// restricted rights notice below appear in all supporting
// documentation.
//
// AUTODESK PROVIDES THIS PROGRAM "AS IS" AND WITH ALL FAULTS.
// AUTODESK SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTY OF
// MERCHANTABILITY OR FITNESS FOR A PARTICULAR USE. AUTODESK, INC.
// DOES NOT WARRANT THAT THE OPERATION OF THE PROGRAM WILL BE
// UNINTERRUPTED OR ERROR FREE.
//
// Use, duplication, or disclosure by the U.S. Government is subject to
// restrictions set forth in FAR 52.227-19 (Commercial Computer
// Software - Restricted Rights) and DFAR 252.227-7013(c)(1)(ii)
// (Rights in Technical Data and Computer Software), as applicable.
//

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Revit.SDK.Samples.RoomSchedule
{
/// <summary>
/// An integrated class to connect .xls data source, retrieve / update data
/// </summary>
class XlsDBConnector : IDisposable
{
#region Class Memeber Variables
// The connection created
private System.Data.OleDb.OleDbConnection m_objConn;

// One command for this connection
private OleDbCommand m_command;

// The connection string
private String m_connectStr;

// All available tables(work sheets) in xls data source
private List<String> m_tables = new List<String>();
#endregion

#region Class Constructor & Destructor
/// <summary>
/// Class constructor, to retrieve data from .xls data source
/// </summary>
/// <param name="strXlsFile">The .xls file to be connected.
/// This file should exist and it can be writable.</param>
public XlsDBConnector(String strXlsFile)
{
// Validate the specified
if (!ValidateFile(strXlsFile)) {
throw new ArgumentException("The specified file doesn't exists or has readonly attribute.", strXlsFile);
}

// establish a connection to the data source.
m_connectStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \"" + strXlsFile +
"\"; Extended Properties = \"Excel 8.0;HDR=YES;\"";

// create the .xls connection
m_objConn = new System.Data.OleDb.OleDbConnection(m_connectStr);
m_objConn.Open();
}

/// <summary>
/// Close the OleDb connection
/// </summary>
public void Dispose()
{
if (null != m_objConn)
{
// close the OleDbConnection
m_objConn.Close();
m_objConn = null;
GC.SuppressFinalize(this);
}
}

/// <summary>
/// Finalizer, we need to ensure the connection was closed
/// This destructor will run only if the Dispose method does not get called.
/// </summary>
~XlsDBConnector()
{
Dispose();
}
#endregion

#region Class Member Methods
/// <summary>
/// Get all available table names from .xls data source
/// </summary>
public List<String> RetrieveAllTables()
{
// clear the old tables list firstly
m_tables.Clear();

// get all table names from data source
DataTable schemaTable = m_objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
m_tables.Add(schemaTable.Rows[i].ItemArray[2].ToString().TrimEnd('$'));
}

return m_tables;
}

/// <summary>
/// Generate a DataTable data from xls data source, by a specified table name
/// </summary>
/// <param name="tableName">Table name to be retrieved </param>
/// <returns>The generated DataTable from work sheet</returns>
public DataTable GenDataTable(String tableName)
{
// Get all data via command and then fill data to table
string strCom = "Select * From [" + tableName + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, m_objConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[" + tableName + "$]");

try
{
// check to see whether the constant columns(defined in RoomsData class) exist in spread sheet.
// These columns are necessary when updating spread sheet

// define a flag variable to remember whether column is found
// duplicate column is not allowed in spreadsheet
bool[] bHasColumn = new bool[5];
Array.Clear(bHasColumn, 0, 5); // clear the variable to false

// five constant columns which must exist and to be checked
String[] constantNames = { RoomsData.RoomID, RoomsData.RoomName,
RoomsData.RoomNumber, RoomsData.RoomArea, RoomsData.RoomComments };

// remember all duplicate columns, used to pop up error message
String duplicateColumns = String.Empty;
for (int i = 0; i < myDataSet.Tables[0].Columns.Count; i++)
{
// get each column and check it
String columnName = myDataSet.Tables[0].Columns[i].ColumnName;

// check whether there are expected columns one by one
for (int col = 0; col < bHasColumn.Length; col++)
{
bool bDupliate = CheckSameColName(columnName, constantNames[col]);
if (bDupliate)
{
if (false == bHasColumn[col])
{
bHasColumn[col] = true;
}
else
{
// this column is duplicate, reserve it
duplicateColumns += String.Format("[{0}], ", constantNames[col]);
}
}
}
}

// check to see whether there are duplicate columns
if (duplicateColumns.Length > 0)
{
// duplicate columns are not allowed
String message = String.Format("There are duplicate column(s) in the spread sheet: {0}.", duplicateColumns);
throw new Exception(message);
}

// check whether all required columns are there.
String missingColumns = String.Empty; // reserve all column names which are missing.
for (int col = 0; col < bHasColumn.Length; col++)
{
if (bHasColumn[col] == false)
{
missingColumns += String.Format("[{0}], ", constantNames[col]);
}
}

// check to see whether any required columns are missing.
if (missingColumns.Length != 0)
{
// some columns are missing, pop up these column names
String message = String.Format("Required columns are missing: {0}.", missingColumns);
throw new Exception(message);
}

// if no exception occurs, return the table of dataset directly
return myDataSet.Tables[0];
}
catch (Exception ex)
{
// throw exception
throw new Exception(ex.Message);
}
}

/// <summary>
/// Execute SQL command, such as: update and insert
/// </summary>
/// <param name="strCmd">command to be executed</param>
/// <returns>the number of rows affected by this command</returns>
public int ExecuteCommnand(String strCmd)
{
try
{
if (null == m_command)
{
m_command = m_objConn.CreateCommand();
}
m_command.CommandText = strCmd;
return m_command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.ToString() + strCmd);
}
}
#endregion

#region Class Implementation
/// <summary>
/// This method will validate and update attributes the specified file.
/// The file should exist and it should have writable attribute.
/// If it's readonly, this method will try to set the attribute to writable.
/// </summary>
/// <param name="strFile"></param>
/// <returns></returns>
private bool ValidateFile(String strFile)
{
// exists check
if(!File.Exists(strFile)) {
return false;
}
//
// writable attribute set
File.SetAttributes(strFile, FileAttributes.Normal);
return (FileAttributes.Normal == File.GetAttributes(strFile));
}
/// <summary>
/// Check if two columns names are the same
/// </summary>
/// <param name="baseName">first name</param>
/// <param name="compName">second name</param>
/// <returns>true, the two names are same; false, they are different.</returns>
private static bool CheckSameColName(String baseName, String compName)
{
if (String.Compare(baseName, compName) == 0)
{
return true;
}
else
{
return false;
}
}
#endregion
};
}