/// <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>
/// Import room spread sheet and display them in form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void importRoomButton_Click(object sender, EventArgs e)
{
using (OpenFileDialog sfdlg = new OpenFileDialog())
{
// file dialog initialization
sfdlg.Title = "Import Excel File";
sfdlg.Filter = "Excel File(*.xls)|*.xls";
sfdlg.RestoreDirectory = true;
//
// initialize the default file name
int hashCode = m_document.GetHashCode();
SheetInfo xlsAndTable = new SheetInfo(String.Empty, String.Empty);
if (CrtlApplication.EventReactor.DocMappedSheetInfo(hashCode, ref xlsAndTable))
{
sfdlg.FileName = xlsAndTable.FileName;
}
//
// import the select
if (DialogResult.OK == sfdlg.ShowDialog())
{
try
{
// create xls data source connector and retrieve data from it
m_dataBaseName = sfdlg.FileName;
XlsDBConnector xlsCon = new XlsDBConnector(m_dataBaseName);
// bind table data to grid view and ComboBox control
tablesComboBox.DataSource = xlsCon.RetrieveAllTables();
// close the connection
xlsCon.Dispose();
}
catch (Exception ex)
{
tablesComboBox.DataSource = null;
MyMessageBox(ex.Message, MessageBoxIcon.Warning);
}
}
}
}
/// <summary>
/// Select one table(work sheet) and display its data to DataGridView control.
/// after selection, generate data table from data source
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tablesComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
// update spread sheet based rooms
sheetDataGridView.DataSource = null;
m_roomTableName = tablesComboBox.SelectedValue as String;
XlsDBConnector xlsCon = null;
try
{
if (null != m_spreadRoomsTable)
{
m_spreadRoomsTable.Clear();
}
// get all rooms table then close this connection immediately
xlsCon = new XlsDBConnector(m_dataBaseName);
// generate room data table from room work sheet.
m_spreadRoomsTable = xlsCon.GenDataTable(m_roomTableName);
newRoomButton.Enabled = (0 == m_spreadRoomsTable.Rows.Count) ? false : true;
// close connection
xlsCon.Dispose();
// update data source of DataGridView
sheetDataGridView.DataSource = new DataView(m_spreadRoomsTable);
}
catch (Exception ex)
{
// close connection and update data source
xlsCon.Dispose();
sheetDataGridView.DataSource = null;
MyMessageBox(ex.Message, MessageBoxIcon.Warning);
return;
}
// update the static s_DocMapDict variable when user changes the Excel and room table
int hashCode = m_document.GetHashCode();
if (CrtlApplication.EventReactor.DocMonitored(hashCode))
{
// update spread sheet to which document is being mapped.
CrtlApplication.EventReactor.UpdateSheeInfo(hashCode, new SheetInfo(m_dataBaseName, m_roomTableName));
// update current mapped room sheet information, only show this when Revit rooms were mapped to Excel sheet.
UpdateRoomMapSheetInfo();
}
}
/// <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 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.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, so we should
// set Revit room's "External Room ID" value to Room.Id 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.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, because the room was added/mapped to spreadsheet,
// and the value of ID column in sheet is just the Room.Id, 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);
}