public static bool Update(string path, string worksheetName, DelimitedFileTable delimitedFileTable, int headerIndex = 1, int headerCount = 0, ClearOption clearOption = ClearOption.None)
{
if (string.IsNullOrEmpty(path) || string.IsNullOrWhiteSpace(worksheetName) || delimitedFileTable == null)
return false;
Application application = null;
bool screenUpdating = false;
bool displayStatusBar = false;
bool enableEvents = false;
bool result = false;
try
{
application = new Application();
application.DisplayAlerts = false;
application.Visible = false;
screenUpdating = application.ScreenUpdating;
displayStatusBar = application.DisplayStatusBar;
enableEvents = application.EnableEvents;
application.ScreenUpdating = false;
application.DisplayStatusBar = false;
application.EnableEvents = false;
Workbook workbook = null;
if (System.IO.File.Exists(path))
workbook = application.Workbooks.Open(path);
else
workbook = application.Workbooks.Add();
result = Update(workbook, worksheetName, delimitedFileTable, headerIndex, headerCount, clearOption);
if (result)
workbook.SaveAs(path);
workbook.Close(false);
}
catch (Exception exception)
{
result = false;
}
finally
{
if (application != null)
{
application.ScreenUpdating = screenUpdating;
application.DisplayStatusBar = displayStatusBar;
application.EnableEvents = enableEvents;
application.Quit();
application.Dispose();
}
}
return result;
}
public static bool Update(this Workbook workbook, string worksheetName, DelimitedFileTable delimitedFileTable, int headerIndex = 1, int headerCount = 0, ClearOption clearOption = ClearOption.None)
{
if (workbook == null || string.IsNullOrWhiteSpace(worksheetName) || delimitedFileTable == null)
return false;
Worksheet worksheet = workbook.Worksheet(worksheetName);
if (worksheet == null)
{
worksheet = workbook.Worksheets.Add() as Worksheet;
if (worksheet != null)
worksheet.Name = worksheetName;
}
return Update(worksheet, delimitedFileTable, headerIndex, headerCount, clearOption);
}
protected override void TrySolveInstance(IGH_DataAccess dataAccess)
{
int index;
index = Params.IndexOfInputParam("_run");
bool run = false;
if (index == -1 || !dataAccess.GetData(index, ref run) || !run)
{
return;
}
DelimitedFileTable delimitedFileTable = Analytical.Revit.Query.DefaultToolsParameterMap();
if (delimitedFileTable == null)
{
return;
}
Analytical.Revit.Tool tool = Analytical.Revit.Tool.Undefined;
index = Params.IndexOfInputParam("_tool");
if (index == -1 || !dataAccess.GetData(index, ref tool) || tool == Analytical.Revit.Tool.Undefined)
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Invalid data");
return;
}
Document document = RhinoInside.Revit.Revit.ActiveDBDocument;
StartTransaction(document);
List <SpatialElement> spatialElements = null;
if (Analytical.Revit.Modify.CopySpatialElementParameters(document, tool))
{
spatialElements = new FilteredElementCollector(document).OfCategory(BuiltInCategory.OST_MEPSpaces).Cast <SpatialElement>().ToList();
}
index = Params.IndexOfOutputParam("elements");
if (index != -1)
{
dataAccess.SetDataList(index, spatialElements);
}
}
public static bool Update(this Worksheet worksheet, DelimitedFileTable delimitedFileTable, int headerIndex = 1, int headerCount = 0, ClearOption clearOption = ClearOption.None)
{
if (worksheet == null)
return false;
int dataRowIndex = headerIndex + headerCount + 1;
Range range_1 = null;
Range range_2 = null;
int lastRowIndex = worksheet.Cells.SpecialCells(NetOffice.ExcelApi.Enums.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
int lastColumnIndex = worksheet.Cells.SpecialCells(NetOffice.ExcelApi.Enums.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
if (clearOption == ClearOption.Data)
{
range_1 = worksheet.Cells[dataRowIndex, 1];
range_2 = worksheet.Cells[lastRowIndex, lastColumnIndex];
worksheet.Range(range_1, range_2).Clear();
}
else if(clearOption == ClearOption.All)
{
worksheet.UsedRange.Clear();
}
range_1 = worksheet.Cells[headerIndex, 1];
range_2 = worksheet.Cells[headerIndex, lastColumnIndex];
object[,] values = worksheet.Range(range_1, range_2).Value as object[,];
for(int i = values.GetLowerBound(1); i <= values.GetUpperBound(1); i++)
{
string name = values[1, i]?.ToString();
if (string.IsNullOrEmpty(name))
continue;
int index = delimitedFileTable.GetColumnIndex(name);
if (index == -1)
continue;
object[,] values_Column = delimitedFileTable.GetColumnValues(index).Transpose();
range_1 = worksheet.Cells[dataRowIndex, i];
range_2 = worksheet.Cells[dataRowIndex + delimitedFileTable.RowCount - 1, i];
worksheet.Range(range_1, range_2).Value = values_Column;
if(clearOption == ClearOption.Column)
{
int firstRowIndex = dataRowIndex + delimitedFileTable.RowCount;
if(firstRowIndex < lastColumnIndex)
{
range_1 = worksheet.Cells[firstRowIndex, i];
range_2 = worksheet.Cells[lastRowIndex, i];
worksheet.Range(range_1, range_2).Clear();
}
}
}
return true;
}
/// <summary>
/// This is the method that actually does the work.
/// </summary>
/// <param name="dataAccess">
/// The DA object is used to retrieve from inputs and store in outputs.
/// </param>
protected override void SolveInstance(IGH_DataAccess dataAccess)
{
int index;
bool run = false;
index = Params.IndexOfInputParam("_run_");
if (index == -1 || !dataAccess.GetData(index, ref run) || !run)
{
return;
}
string path = null;
index = Params.IndexOfInputParam("_path");
if (index == -1 || !dataAccess.GetData(index, ref path) || string.IsNullOrEmpty(path))
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Invalid data");
return;
}
if (!System.IO.File.Exists(path))
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "File does not exists");
return;
}
string worksheetName = null;
index = Params.IndexOfInputParam("_worksheetName");
if (index == -1 || !dataAccess.GetData(index, ref worksheetName) || string.IsNullOrEmpty(worksheetName))
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Invalid data");
return;
}
object[,] values = Core.Excel.Query.Values(path, worksheetName);
if (values == null)
{
return;
}
int names_Index = 1;
index = Params.IndexOfInputParam("_namesIndex_");
if (index != -1)
{
dataAccess.GetData(index, ref names_Index);
}
int headerCount = 0;
index = Params.IndexOfInputParam("_headerCount_");
if (index != -1)
{
dataAccess.GetData(index, ref headerCount);
}
DelimitedFileTable delimitedFileTable = new DelimitedFileTable(values, names_Index, headerCount);
index = Params.IndexOfOutputParam("DelimitedFileTable");
if (index != -1)
{
dataAccess.SetData(index, new GooDelimitedFileTable(delimitedFileTable));
}
}
public static bool CopySpatialElementParameters(this Document document, Tool tool)
{
if (document == null || tool == Tool.Undefined)
{
return(false);
}
DelimitedFileTable delimitedFileTable = Query.DefaultToolsParameterMap();
if (delimitedFileTable == null)
{
return(false);
}
string source = null;
switch (tool)
{
case Tool.EnergyPlus:
source = "E";
break;
case Tool.IES:
source = "IES";
break;
case Tool.TAS:
source = "TAS";
break;
case Tool.Other:
source = "X";
break;
}
int index_Source = delimitedFileTable.GetColumnIndex(source);
int index_Destination = delimitedFileTable.GetColumnIndex("Generic");
if (index_Source == -1 || index_Destination == -1)
{
return(false);
}
Dictionary <string, string> dictionary = new Dictionary <string, string>();
for (int i = 0; i < delimitedFileTable.RowCount; i++)
{
string name_Source = delimitedFileTable[i, index_Source]?.ToString();
if (string.IsNullOrWhiteSpace(name_Source))
{
continue;
}
string name_Destination = delimitedFileTable[i, index_Destination]?.ToString();
if (string.IsNullOrWhiteSpace(name_Destination))
{
continue;
}
dictionary[name_Destination] = name_Source;
}
if (dictionary != null || dictionary.Count != 0)
{
List <SpatialElement> spatialElements = new FilteredElementCollector(document).OfCategory(BuiltInCategory.OST_MEPSpaces).Cast <SpatialElement>().ToList();
if (spatialElements != null && spatialElements.Count != 0)
{
foreach (SpatialElement spatialElement in spatialElements)
{
if (spatialElement == null)
{
continue;
}
foreach (KeyValuePair <string, string> keyValuePair in dictionary)
{
Core.Revit.Modify.CopyValue(spatialElement.LookupParameter(keyValuePair.Value), spatialElement.LookupParameter(keyValuePair.Key));
}
}
}
}
return(true);
}
/// <summary>
/// This is the method that actually does the work.
/// </summary>
/// <param name="dataAccess">
/// The DA object is used to retrieve from inputs and store in outputs.
/// </param>
protected override void SolveInstance(IGH_DataAccess dataAccess)
{
int index = -1;
bool run = false;
index = Params.IndexOfInputParam("_run_");
if (index == -1 || !dataAccess.GetData(index, ref run) || !run)
{
dataAccess.SetData(0, false);
return;
}
string path = null;
index = Params.IndexOfInputParam("_path");
if (index == -1 || !dataAccess.GetData(index, ref path) || string.IsNullOrEmpty(path))
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Invalid data");
return;
}
if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(path)))
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Directory does not exists");
return;
}
string worksheetName = null;
index = Params.IndexOfInputParam("_worksheetName");
if (index == -1 || !dataAccess.GetData(index, ref worksheetName) || string.IsNullOrEmpty(worksheetName))
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Invalid data");
return;
}
DelimitedFileTable delimitedFileTable = null;
index = Params.IndexOfInputParam("_delimitedFileTable");
if (index == -1 || !dataAccess.GetData(index, ref delimitedFileTable))
{
AddRuntimeMessage(GH_RuntimeMessageLevel.Error, "Invalid data");
return;
}
int headerIndex = 1;
index = Params.IndexOfInputParam("_headerIndex_");
if (index != -1)
{
dataAccess.GetData(index, ref headerIndex);
}
int headerCount = 1;
index = Params.IndexOfInputParam("_headerCount_");
if (index != -1)
{
dataAccess.GetData(index, ref headerCount);
}
Core.Excel.ClearOption clearOption = Core.Excel.ClearOption.Data;
index = Params.IndexOfInputParam("_clearOption_");
if (index != -1)
{
string clearOptionString = null;
if (dataAccess.GetData(index, ref clearOptionString))
{
Enum.TryParse(clearOptionString, out clearOption);
}
}
bool result = Core.Excel.Modify.Update(path, worksheetName, delimitedFileTable, headerIndex, headerCount, clearOption);
//Wait 2 sek
System.Threading.Thread.Sleep(1000);
index = Params.IndexOfOutputParam("Successful");
if (index != -1)
{
dataAccess.SetData(index, result);
}
}
