/// <summary>
/// Class constructor
/// </summary>
/// <param name="commandData">Revit external command data</param>
public RoomScheduleForm(ExternalCommandData commandData)
{
// UI initialization
InitializeComponent();
// reserve Revit command data and get rooms information,
// and then display rooms information in DataGrideView
m_commandData = commandData;
m_document = m_commandData.Application.ActiveUIDocument.Document;
m_roomData = new RoomsData(commandData.Application.ActiveUIDocument.Document);
// bind levels and phases data to level and phase ComboBox controls
GetAllLevelsAndPhases();
// list all levels and phases
this.levelComboBox.DisplayMember = "Name";
this.levelComboBox.DataSource = m_allLevels;
this.levelComboBox.SelectedIndex = 0;
this.phaseComboBox.DisplayMember = "Name";
this.phaseComboBox.DataSource = m_allPhases;
this.phaseComboBox.SelectedIndex = 0;
// if there is no phase, newRoomButton will be disabled.
if (m_allPhases.Count == 0)
{
newRoomButton.Enabled = false;
}
// check to see whether current Revit document was mapped to spreadsheet.
UpdateRoomMapSheetInfo();
}
/// <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>
/// Clear all values of shared parameters
/// Allow user to create more unplaced rooms and update map relationships between Revit and spreadsheet rooms.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void clearIDButton_Click(object sender, EventArgs e)
{
int nCount = 0;
foreach (Room room in m_roomData.Rooms)
{
Parameter param = null;
bool bExist = RoomsData.ShareParameterExists(room, RoomsData.SharedParam, ref param);
if (bExist && null != param && false == String.IsNullOrEmpty(param.AsString()))
{
param.Set(String.Empty);
nCount++;
}
}
// update Revit rooms display
if (nCount > 0)
{
UpdateFormDisplay(false);
}
}
/// <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>
/// 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>
/// Class constructor
/// </summary>
/// <param name="commandData">Revit external command data</param>
public RoomScheduleForm(ExternalCommandData commandData)
{
// UI initialization
InitializeComponent();
// reserve Revit command data and get rooms information,
// and then display rooms information in DataGrideView
m_commandData = commandData;
m_document = m_commandData.Application.ActiveUIDocument.Document;
m_roomData = new RoomsData(commandData.Application.ActiveUIDocument.Document);
// bind levels and phases data to level and phase ComboBox controls
GetAllLevelsAndPhases();
// list all levels and phases
this.levelComboBox.DisplayMember = "Name";
this.levelComboBox.DataSource = m_allLevels;
this.levelComboBox.SelectedIndex = 0;
this.phaseComboBox.DisplayMember = "Name";
this.phaseComboBox.DataSource = m_allPhases;
this.phaseComboBox.SelectedIndex = 0;
// if there is no phase, newRoomButton will be disabled.
if (m_allPhases.Count == 0)
{
newRoomButton.Enabled = false;
}
// check to see whether current Revit document was mapped to spreadsheet.
UpdateRoomMapSheetInfo();
}
