//public QLViewScheduleResolve(ViewSchedule aViewSchedule, Field queryFieldForViewScheduleData)
//{
// id = aViewSchedule.Id.ToString();
// name = aViewSchedule.Name;
// if (queryFieldForViewScheduleData != null)
// {
// try
// {
// qlViewScheduleData = ResolverEntry.aRevitTask.Run<QLViewScheduleData>(app =>
// {
// ////https://thebuildingcoder.typepad.com/blog/2012/05/the-schedule-api-and-access-to-schedule-data.html
// TableData table = aViewSchedule.GetTableData();
// var rowCount = aViewSchedule.GetTableData().GetSectionData(SectionType.Body).NumberOfRows;
// var colCount = aViewSchedule.GetTableData().GetSectionData(SectionType.Body).NumberOfColumns;
// QLViewScheduleData data = new QLViewScheduleData();
// data.headers = new List<string>();
// data.rows = new List<QLViewScheduleRow>();
// for (int i = 0; i < rowCount; i++)
// {
// QLViewScheduleRow newRow = new QLViewScheduleRow();
// newRow.id = i.ToString();
// newRow.cells = new List<string>();
// for (int j = 0; j < colCount; j++)
// {
// var cellString = aViewSchedule.GetCellText(SectionType.Body, i, j);
// if (i == 0)
// {
// data.headers.Add(cellString);
// }
// else
// {
// newRow.cells.Add(cellString);
// }
// }
// if (i == 0)
// {
// i++; // skip 2nd row - it's always blank
// }
// else
// {
// data.rows.Add(newRow);
// }
// }
// return data;
// }).Result;
// }
// catch (Exception ex)
// {
// var m = ex.Message;
// }
// }
//}
public QLViewScheduleResolve(ViewSchedule aViewSchedule, object aFieldOrContext)
{
id = aViewSchedule.Id.ToString();
name = aViewSchedule.Name;
var queryFieldForViewScheduleData = GraphQlHelpers.GetFieldFromFieldOrContext(aFieldOrContext, "qlViewScheduleData");
if (queryFieldForViewScheduleData != null)
{
try
{
qlViewScheduleData = ResolverEntry.aRevitTask.Run <QLViewScheduleData>(app =>
{
////https://thebuildingcoder.typepad.com/blog/2012/05/the-schedule-api-and-access-to-schedule-data.html
TableData table = aViewSchedule.GetTableData();
var rowCount = aViewSchedule.GetTableData().GetSectionData(SectionType.Body).NumberOfRows;
var colCount = aViewSchedule.GetTableData().GetSectionData(SectionType.Body).NumberOfColumns;
QLViewScheduleData data = new QLViewScheduleData();
data.headers = new List <string>();
data.rows = new List <QLViewScheduleRow>();
for (int i = 0; i < rowCount; i++)
{
QLViewScheduleRow newRow = new QLViewScheduleRow();
newRow.id = i.ToString();
newRow.cells = new List <string>();
for (int j = 0; j < colCount; j++)
{
var cellString = aViewSchedule.GetCellText(SectionType.Body, i, j);
if (i == 0)
{
data.headers.Add(cellString);
}
else
{
newRow.cells.Add(cellString);
}
}
if (i == 0)
{
i++; // skip 2nd row - it's always blank
}
else
{
data.rows.Add(newRow);
}
}
return(data);
}).Result;
}
catch (Exception ex)
{
var m = ex.Message;
}
}
}
private bool IsEmpty(ElementId schdId)
{
if (schdId != null)
{
ViewSchedule vs = m_doc
.GetElement(schdId) as ViewSchedule;
TableSectionData tableData = vs.GetTableData()
.GetSectionData(SectionType.Body);
bool showHeaders = vs.Definition.ShowHeaders;
System.Diagnostics.Trace.Write(
string.Format("LastRowNumber = {0}", tableData.LastRowNumber));
if (tableData.LastRowNumber == -1)
{
return(true);
}
else if (showHeaders &&
tableData.LastRowNumber == 0)
{
return(true);
}
return(false);
}
throw new Exception("Schedule id is null.");
}
public void AddString(ViewSchedule schedule)
{
TableData calTabledata = schedule.GetTableData();
TableSectionData tsd = calTabledata.GetSectionData(SectionType.Body);
tsd.InsertRow(tsd.FirstRowNumber + 1);
}
public double GetScheduleQuantity(ViewSchedule schedule)
{
TableData table = schedule.GetTableData();
TableSectionData section = table.GetSectionData(SectionType.Body);
if (section.NumberOfRows > 0)
{
for (int col = section.NumberOfColumns - 1; col >= 0; col--)
{
string strValue = schedule.GetCellText(SectionType.Body, section.NumberOfRows - 1, col);
if (strValue.Length > 0)
{
if (strValue.Contains(" "))
{
strValue = strValue.Remove(strValue.IndexOf(" "));
if (double.TryParse(strValue, out double value))
{
return(value);
}
}
else if (double.TryParse(strValue, out double value))
{
return(value);
}
}
}
}
return(0.0);
}
public string GetScheduleUnit(ViewSchedule schedule)
{
TableData table = schedule.GetTableData();
TableSectionData section = table.GetSectionData(SectionType.Body);
if (section.NumberOfRows > 0)
{
for (int col = section.NumberOfColumns - 1; col >= 0; col--)
{
string unit = schedule.GetCellText(SectionType.Body, section.NumberOfRows - 1, col);
if (unit.Length > 0)
{
if (unit.Contains(" "))
{
return(unit.Remove(0, unit.IndexOf(" ") + 1));
}
else
{
return("kg");
}
}
}
}
return("");
}
public MainWindow(UIDocument _uidoc, ViewSchedule _viewSched)
{
InitializeComponent();
uidoc = _uidoc;
doc = uidoc.Document;
viewSched = _viewSched;
// Placeholder sheets print as blank pages, we don't want them
sheetList = new FilteredElementCollector(doc);
sheetList.OfClass(typeof(ViewSheet))
.Cast <ViewSheet>()
.Where <ViewSheet>(i => !i.IsPlaceholder);
viewSet = new ViewSet();
// Read the data from the schedule
TableData table = viewSched.GetTableData();
TableSectionData section = table.GetSectionData(SectionType.Body);
nRows = section.NumberOfRows;
nCols = section.NumberOfColumns;
SetNameBox.Text = viewSched.Name;
// This is to select which column the sheet numbers are
foreach (int num in Enumerable.Range(1, nCols))
{
Cbx.Items.Add(num);
}
Cbx.SelectedIndex = 0;
}
internal void SetNewTitle(IMultitableSchedule multiSchd)
{
if (multiSchd is AssemblySchedule && multiSchd.Subschedules[0].ShowTitle)
{
ViewSchedule vs = m_doc.GetElement(multiSchd.Subschedules[0].Id)
as ViewSchedule;
string newTitle = "Спецификация элементов " +
multiSchd.FiltersValues[MultischeduleParameters.ASSEMBLY_MARK];
vs.GetTableData()
.GetSectionData(SectionType.Header)
.SetCellText(vs.GetTableData().GetSectionData(SectionType.Header).FirstRowNumber,
vs.GetTableData().GetSectionData(SectionType.Header).FirstColumnNumber,
newTitle);
}
}
public int GetLastRowNumber(ViewSchedule schedule)
{
TableData calTabledata = schedule.GetTableData();
TableSectionData tsd = calTabledata.GetSectionData(SectionType.Body);
int lastRowNumber = tsd.LastRowNumber;
return(lastRowNumber);
}
private void HideEmptyColumns(ElementId id)
{
if (id == ElementId.InvalidElementId)
{
return;
}
ViewSchedule viewSchd = m_doc.GetElement(id)
as ViewSchedule;
ScheduleDefinition schdDef = viewSchd.Definition;
TableSectionData tableData =
viewSchd.GetTableData().GetSectionData(SectionType.Body);
Func <ScheduleFieldId, bool> filterHiddenFields = delegate(ScheduleFieldId fId)
{
if (schdDef.GetField(fId).IsHidden)
{
return(false);
}
else
{
return(true);
}
};
IList <ScheduleFieldId> visibleFields = schdDef
.GetFieldOrder()
.Where(filterHiddenFields)
.ToList();
StringBuilder strBld = new StringBuilder();
for (int i = tableData.FirstColumnNumber + 2; i <= tableData.LastColumnNumber; ++i)
{
double sum = 0;
for (int j = tableData.FirstRowNumber + 1; j <= tableData.LastRowNumber; ++j)
{
string cellContent = tableData.GetCellText(j, i);
strBld.AppendFormat("({0}, {1}) = {2}\n", i, j, cellContent);
double cellValue;
if (Double.TryParse(cellContent, out cellValue))
{
sum += cellValue;
}
}
// if the current column holds no value, then have it hidden
if (sum == 0)
{
ScheduleField field = schdDef.GetField(visibleFields[i]);
field.IsHidden = true;
}
}
System.Diagnostics.Trace.Write(strBld.ToString());
}
public static void AddScheduleKey(Document document, ViewSchedule schedule)
{
var transaction = new Transaction(document, "Add Row");
transaction.Start();
var tableData = schedule.GetTableData();
var section = tableData.GetSectionData(SectionType.Body);
section.InsertRow(section.FirstRowNumber);
transaction.Commit();
}
internal bool IsEmpty()
{
if (m_createdScheduleId != null)
{
ViewSchedule vs = m_doc.GetElement(m_createdScheduleId) as ViewSchedule;
TableSectionData tableData = vs.GetTableData().GetSectionData(SectionType.Body);
if (tableData.LastRowNumber == -1)
{
return(true);
}
return(false);
}
throw new Exception("Schedule id is null.");
}
private static void Check(ViewSchedule schedule)
{
using (Transaction t = new Transaction(schedule.Document, "spelling"))
{
t.Start();
foreach (SectionType sectionType in Enum.GetValues(typeof(SectionType)))
{
TableSectionData section = schedule.GetTableData().GetSectionData(sectionType);
if (section != null && section.IsValidObject)
{
Check(section);
}
}
t.Commit();
}
}
void ChangeTitle(ViewSchedule vs, string assemblyName)
{
ScheduleDefinition sd = vs.Definition;
TableData td = vs.GetTableData();
TableSectionData tsd = td.GetSectionData(SectionType.Header);
string oldValue = tsd.GetCellText(tsd.FirstRowNumber, tsd.FirstColumnNumber);
Tracer.Write($"oldValue={oldValue}");
string newValue = string.Empty;
for (int i = 0; i < oldValue.Split(' ').Length - 1; i++)
{
newValue += oldValue.Split(' ')[i] + " ";
}
newValue += assemblyName;
Tracer.Write($"newValue={newValue}");
tsd.SetCellText(tsd.FirstRowNumber, tsd.FirstColumnNumber, newValue);
}
public static bool HasParameter(this ViewSchedule schedule, Parameter parameter)
{
var bodyData = schedule.GetTableData().GetSectionData(SectionType.Body);
var count = bodyData.NumberOfColumns;
for (var i = 0; i < count; i++)
{
var field = schedule.Definition.GetField(i);
var id = field.ParameterId;
if (id == parameter.Id)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Converts revit detail view to a DataTable.
/// </summary>
/// <param name="view"></param>
/// <param name="doc"></param>
/// <returns></returns>
public static DataTable ToDataTable(this ViewSchedule view, Document doc)
{
if (view is null)
{
throw new ArgumentNullException(nameof(view));
}
if (doc is null)
{
throw new ArgumentNullException(nameof(doc));
}
var table = view.GetTableData();
var body = table.GetSectionData(SectionType.Body);
var colNum = body.NumberOfColumns;
var rowNum = body.NumberOfRows;
var result = new DataTable {
TableName = view.GetCellText(SectionType.Header, 0, 0)
};
for (var i = 0; i < colNum; i++)
{
result.Columns.Add();
}
for (var i = 0; i < rowNum; i++)
{
var row = result.NewRow();
for (var j = 0; j < colNum; j++)
{
row[j] = view.GetCellText(SectionType.Body, i, j);
}
result.Rows.Add(row);
}
return(result);
}
internal void HideZeroFieldsMaterialTakeOff()
{
ViewSchedule viewSchedule = m_doc.GetElement(m_createdScheduleId) as ViewSchedule;
ScheduleDefinition scheduleDef = viewSchedule.Definition;
TableData tableData = viewSchedule.GetTableData();
TableSectionData tableSecData = tableData.GetSectionData(SectionType.Body);
List <ScheduleField> visibleFields = scheduleDef.GetFieldOrder().Select(id => scheduleDef.GetField(id)).Where(f => !f.IsHidden).ToList();
System.Text.StringBuilder strBld = new System.Text.StringBuilder();
using (Transaction t = new Transaction(viewSchedule.Document))
{
t.Start("Hide Zeroed Fields");
for (int i = tableSecData.FirstColumnNumber; i <= tableSecData.LastColumnNumber; ++i)
{
if (tableSecData.GetCellText(tableSecData.FirstRowNumber + 5, i) == "0")
{
double total = 0;
for (int j = tableSecData.FirstRowNumber + 5; j <= tableSecData.LastRowNumber; ++j)
{
strBld.AppendFormat("{0} = {1}; column = {2}\n",
tableSecData.GetCellText(tableSecData.FirstRowNumber + 4, i), tableSecData.GetCellText(j, i), i);
double tmp;
if (Double.TryParse(tableSecData.GetCellText(j, i), out tmp))
{
total += tmp;
}
}
if (total == 0)
{
visibleFields[i].IsHidden = true;
}
}
}
t.Commit();
}
Tracer.Write(strBld.ToString());
}
private Dictionary <string, FamilyInstance> Sortdic(Dictionary <string, FamilyInstance> dic, ViewSchedule viewSchedule)
{
Dictionary <string, FamilyInstance> dic2 = new Dictionary <string, FamilyInstance>();
var keys = dic.Keys.ToList();
List <string> listtexts = new List <string>();
List <string> Newtexts = new List <string>();
TableData tableData = viewSchedule.GetTableData();
TableSectionData sectionData = tableData.GetSectionData(SectionType.Body);
int numberOfRows = sectionData.NumberOfRows;
int numberOfColumns = sectionData.NumberOfColumns;
//for (int i = 0; i < numberOfRows; i++)
//{
// for (int j = 0; j < numberOfColumns; j++)
// {
// string cellText = viewSchedule.GetCellText(SectionType.Body, i, j);
// listtexts.Add(cellText);
// }
//}
for (int i = 0; i < numberOfRows; i++)
{
string cellText = viewSchedule.GetCellText(SectionType.Body, i, 0);
listtexts.Add(cellText);
}
foreach (var item in listtexts)
{
foreach (var item2 in keys)
{
if (item.Equals(item2))
{
Newtexts.Add(item);
}
}
}
foreach (var item in Newtexts)
{
dic2.Add(item, dic[item]);
}
return(dic2);
}
/// <summary>
/// The getScheduleData
/// </summary>
/// <param name="doc">The doc<see cref="Document"/></param>
public void getScheduleData(Document doc)
{
FilteredElementCollector collector = new FilteredElementCollector(doc);
IList <Element> collection = collector.OfClass(typeof(ViewSchedule)).ToElements();
String prompt = "ScheduleData :";
prompt += Environment.NewLine;
foreach (Element e in collection)
{
ViewSchedule viewSchedule = e as ViewSchedule;
TableData table = viewSchedule.GetTableData();
TableSectionData section = table.GetSectionData(SectionType.Body);
int nRows = section.NumberOfRows;
int nColumns = section.NumberOfColumns;
if (nRows > 1)
{
//valueData.Add(viewSchedule.Name);
List <List <string> > scheduleData = new List <List <string> >();
for (int i = 0; i < nRows; i++)
{
List <string> rowData = new List <string>();
for (int j = 0; j < nColumns; j++)
{
rowData.Add(viewSchedule.GetCellText(SectionType.Body, i, j));
}
scheduleData.Add(rowData);
}
List <string> columnData = scheduleData[0];
scheduleData.RemoveAt(0);
DataMapping(columnData, scheduleData);
}
}
}
public List <List <string> > getScheduleData(Document doc, ViewSchedule viewSchedule)
{
TableData table = viewSchedule.GetTableData();
TableSectionData section = table.GetSectionData(SectionType.Body);
int nRows = section.NumberOfRows;
int nColumns = section.NumberOfColumns;
List <List <string> > scheduleData = new List <List <string> >();
if (viewSchedule.IsTitleblockRevisionSchedule)
{
scheduleData.Add(new List <string>()
{
"HISTÓRICO DE REVISÕES"
});
}
else
{
scheduleData.Add(new List <string>()
{
viewSchedule.Name
});
}
for (int i = 0; i < nRows; i++)
{
List <string> rowData = new List <string>();
for (int j = 0; j < nColumns; j++)
{
rowData.Add(viewSchedule.GetCellText(SectionType.Body, i, j));
}
scheduleData.Add(rowData);
}
return(scheduleData);
}
/// <summary>
/// Получаем спецификацию
/// </summary>
private void GetSchedule()
{
// Получаем выбранный элемент
var selection = _uidoc
.Selection
.GetElementIds()
.Select(i => _doc.GetElement(i))
.FirstOrDefault();
// Если элемент является спецификацией запишем спецификацию
if (selection is ScheduleSheetInstance)
{
_scheduleSheetInstance = selection as ScheduleSheetInstance;
_viewSchedule = (ViewSchedule)_doc.GetElement(_scheduleSheetInstance.ScheduleId);
// Проверка является ли спецификация ведомостью деталей
if (!_scheduleSheetInstance.Name.Contains("Ведомость деталей"))
{
TaskDialog.Show("Ошибка", "Выбрана не ведомость деталей");
return;
}
}
// Если элемент яляется группой то расскидаем значения
else if (selection is Group)
{
var listElement = ((Group)selection)
.GetMemberIds()
.Select(i => _doc.GetElement(i))
.ToList();
_scheduleSheetInstance = listElement
.Where(i => i is ScheduleSheetInstance)
.Cast <ScheduleSheetInstance>()
.ToList()
.FirstOrDefault();
if (_scheduleSheetInstance == null)
{
TaskDialog.Show("Ошибка", "В группе нет спецификации");
return;
}
// Проверка является ли спецификация ведомостью деталей
if (!_scheduleSheetInstance.Name.Contains("Ведомость деталей"))
{
TaskDialog.Show("Ошибка", "Выбрана не ведомость деталей");
return;
}
_viewSchedule = (ViewSchedule)_doc.GetElement(_scheduleSheetInstance.ScheduleId);
_annotationSymbolList = listElement
.Where(i => i is AnnotationSymbol)
.Cast <AnnotationSymbol>()
.ToList();
}
else
{
TaskDialog.Show("Ошибка", "Не выбрана спецификация или группа со спецификацией");
return;
}
// Получаем объект TableSectionData
_tsd = _viewSchedule.GetTableData().GetSectionData(SectionType.Body);
// Получаем баундингБокс
_boundingBox = _scheduleSheetInstance.get_BoundingBox(_doc.ActiveView);
// Получаем представление спецификации
_scheduleDefinition = _viewSchedule.Definition;
}
private static void CreateInstance(ViewSchedule v)
{
TableSectionData body = v.GetTableData().GetSectionData(SectionType.Body);
body.InsertRow(body.LastRowNumber + 1);
}
private static void CopyToAdsk(ViewSchedule vs, IReadOnlyCollection <IOperation> operations)
{
var tData = vs.GetTableData();
var tsDada = tData.GetSectionData(SectionType.Body);
if (operations.Count == 0)
{
return;
}
TransactionManager.CreateTransactionGroup(_doc, "Копирование параметров", () =>
{
for (var rInd = 0; rInd < tsDada.NumberOfRows; rInd++)
{
foreach (var operation in operations)
{
var elements = RevitFunctions.GetElementsOnRow(_doc, vs, rInd);
if (elements == null)
{
continue;
}
switch (operation.Name)
{
case Operation.CopyString:
if (operation is CopyStringOperation stringOperation)
{
CopyStringValues(stringOperation, tsDada, rInd, elements);
}
break;
case Operation.CopyInteger:
if (operation is CopyIntegerOperation integerOperation)
{
CopyIntegerValues(integerOperation, tsDada, rInd, elements);
}
break;
case Operation.CopyLength:
if (operation is CopyLengthOperation lengthOperation)
{
CopyLengthValues(lengthOperation, elements);
}
break;
case Operation.CopyArea:
if (operation is CopyAreaOperation areaOperation)
{
CopyAreaValues(areaOperation, elements);
}
break;
case Operation.CopyVolume:
if (operation is CopyVolumeOperation volumeOperation)
{
CopyVolumeValues(volumeOperation, elements);
}
break;
case Operation.CopyMass:
if (operation is CopyMassOperation massOperation)
{
CopyMassValues(massOperation, tsDada, rInd, elements);
}
break;
case Operation.CopyTemperature:
if (operation is CopyTemperatureOperation temperatureOperation)
{
CopyTemperatureValues(temperatureOperation, elements);
}
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
});
}
public void ModifySchedule(Document doc, ElementId scheduleId, string file, string worksheet, string transactionMsg, int pathType, bool contentOnly)
{
// Set the Pathtype
PathType pt = (PathType)pathType;
// Clear the current schedule of data/formatting and then rebuild it.
ViewSchedule sched = null;
_doc = doc;
try
{
Element schedElem = doc.GetElement(scheduleId);
sched = schedElem as ViewSchedule;
}
catch (Exception ex)
{
TaskDialog.Show("Error", ex.Message);
}
if (sched != null)
{
Transaction trans = new Transaction(doc, transactionMsg);
trans.Start();
if (!contentOnly)
{
// Get the header body to create the necessary rows and columns
TableSectionData headerData = sched.GetTableData().GetSectionData(SectionType.Header);
int rowCount = headerData.NumberOfRows;
int columnCount = headerData.NumberOfColumns;
for (int i = 1; i < columnCount; i++)
{
try
{
headerData.RemoveColumn(1);
}
catch { }
}
for (int i = 1; i < rowCount; i++)
{
try
{
headerData.RemoveRow(1);
}
catch { }
}
// Make sure the name is up to date
sched.Name = worksheet;
}
// Add the new schedule data in
AddScheduleData(file, sched, doc, pt, contentOnly);
trans.Commit();
}
else
{
TaskDialog errorDialog = new TaskDialog("Error")
{
MainInstruction = $"Schedule ({worksheet}) could not be found. Remove from update list?",
CommonButtons = TaskDialogCommonButtons.No | TaskDialogCommonButtons.Yes,
DefaultButton = TaskDialogResult.Yes
};
TaskDialogResult result = errorDialog.Show();
if (result == TaskDialogResult.Yes)
{
try
{
// Find an Excel File
// Get the schema
Schema schema = Schema.Lookup(schemaGUID);
//Entity entity = null;
DataStorage ds = SchemaManager.GetDataStorage(_doc);
ExcelScheduleEntity schedEntity = ds.GetEntity <ExcelScheduleEntity>();
if (schedEntity != null)
{
int index = -1;
for (int i = 0; i < schedEntity.ScheduleId.Count; i++)
{
if (schedEntity.ScheduleId[i].IntegerValue == scheduleId.IntegerValue)
{
index = i;
break;
}
}
if (index != -1)
{
using (Transaction trans = new Transaction(doc, "Remove Schedule Excel Link Data"))
{
trans.Start();
// Check if there are more linked items than the one we found
if (schedEntity.ScheduleId.Count > 1)
{
// Cull the index
schedEntity.ScheduleId.RemoveAt(index);
schedEntity.DateTime.RemoveAt(index);
schedEntity.ExcelFilePath.RemoveAt(index);
schedEntity.PathType.RemoveAt(index);
schedEntity.WorksheetName.RemoveAt(index);
// Set the entity back to the DS
ds.SetEntity(schedEntity);
}
// If we only have one item and we're removing it, just delete the DataStorage and entity
else
{
ds.DeleteEntity <ExcelScheduleEntity>();
doc.Delete(ds.Id);
}
trans.Commit();
}
}
}
}
catch (Exception ex)
{
TaskDialog.Show("Error", ex.Message);
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
/// <param name="sched"></param>
/// <param name="doc"></param>
/// <param name="pt"></param>
/// <param name="contentOnly"></param>
public void AddScheduleData(string filePath, ViewSchedule sched, Document doc, PathType pt, bool contentOnly)
{
string docPath;
if (doc.IsWorkshared)
{
docPath = ModelPathUtils.ConvertModelPathToUserVisiblePath(doc.GetWorksharingCentralModelPath());
}
else
{
docPath = doc.PathName;
}
string fullPath;
if (pt == PathType.Absolute)
{
fullPath = filePath;
}
else
{
fullPath = PathExchange.GetFullPath(filePath, docPath);
}
// Get the file path
excelFilePath = fullPath;
if (!File.Exists(excelFilePath))
{
return;
}
// read the Excel file and create the schedule
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath);
Excel.Sheets worksheets = workbook.Worksheets;
worksheet = null;
foreach (Excel.Worksheet ws in worksheets)
{
if (ws.Name.Trim() == sched.Name.Trim())
{
worksheet = ws;
}
}
if (worksheet == null)
{
return;
}
//TaskDialog.Show("Test", "Worksheet found");
// Find the ThinLine linestyle
CategoryNameMap lineSubCats = doc.Settings.Categories.get_Item(BuiltInCategory.OST_Lines).SubCategories;
ElementId thinLineStyle = new ElementId(-1);
ElementId hairlineStyle = new ElementId(-1);
ElementId thinStyle = new ElementId(-1);
ElementId mediumStyle = new ElementId(-1);
ElementId thickStyle = new ElementId(-1);
foreach (Category style in lineSubCats)
{
if (style.Name == "Thin Lines")
{
thinLineStyle = style.Id;
}
if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.hairlineInt)
{
hairlineStyle = style.Id;
}
else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thinInt)
{
thinStyle = style.Id;
}
else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.mediumInt)
{
mediumStyle = style.Id;
}
else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thickInt)
{
thickStyle = style.Id;
}
}
if (hairlineStyle.IntegerValue == -1)
{
hairlineStyle = thinLineStyle;
}
if (thinStyle.IntegerValue == -1)
{
thinStyle = thinLineStyle;
}
if (mediumStyle.IntegerValue == -1)
{
mediumStyle = thinLineStyle;
}
if (thickStyle.IntegerValue == -1)
{
thickStyle = thinLineStyle;
}
// Find out how many rows and columns we need in the schedule
Excel.Range rng = ActualUsedRange(worksheet);
Excel.Range range = rng;
int rowCount = range.Rows.Count;
int columnCount = range.Columns.Count;
// Get the schedule body to set the overall width
TableSectionData bodyData = sched.GetTableData().GetSectionData(SectionType.Body);
if (!contentOnly)
{
double schedWidth = range.Columns.Width;
try
{
bodyData.SetColumnWidth(0, (schedWidth * pointWidthInches) / 12);
}
catch { }
}
// Get the header body to create the necessary rows and columns
TableSectionData headerData = sched.GetTableData().GetSectionData(SectionType.Header);
if (!contentOnly)
{
//TaskDialog.Show("Test: ", "Row Count: " + rowCount.ToString() + "\nColumn Count: " + columnCount.ToString());
for (int i = 0; i < columnCount - 1; i++)
{
headerData.InsertColumn(1);
}
for (int i = 0; i < rowCount - 1; i++)
{
headerData.InsertRow(1);
}
for (int i = 1; i <= headerData.NumberOfColumns; i++)
{
try
{
Excel.Range cell = worksheet.Cells[1, i];
headerData.SetColumnWidth(i - 1, (cell.Width * pointWidthInches) / 12);
}
catch { }
}
for (int i = 1; i <= headerData.NumberOfRows; i++)
{
try
{
Excel.Range cell = worksheet.Cells[i, 1];
headerData.SetRowHeight(i - 1, (cell.Height * pointWidthInches) / 12);
}
catch { }
}
}
List <TableMergedCell> mergedCells = new List <TableMergedCell>();
int errorCount = 0;
for (int i = 1; i <= headerData.NumberOfRows; i++) // Iterate through rows of worksheet data
{
for (int j = 1; j <= headerData.NumberOfColumns; j++) // Iterate through columns of worksheet data
{
// Get the current cell in the worksheet grid
Excel.Range cell = worksheet.Cells[i, j];
// If adjusting the formatting or adding content is not necessary,
// just update the text content. This is via a UI switch.
if (contentOnly)
{
try
{
headerData.SetCellText(i - 1, j - 1, cell.Text);
continue;
}
catch {
errorCount++;
continue;
}
}
Excel.Font font = cell.Font;
Excel.DisplayFormat dispFormat = cell.DisplayFormat;
TableCellStyle cellStyle = new TableCellStyle();
TableCellStyleOverrideOptions styleOverride = cellStyle.GetCellStyleOverrideOptions();
Excel.Border topEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeTop];
Excel.Border bottomEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeBottom];
Excel.Border leftEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeLeft];
Excel.Border rightEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeRight];
// Determine Bottom Edge Line Style
if (bottomEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderBottomLineStyle = new ElementId(-1);
}
else
{
switch (bottomEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderBottomLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderBottomLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderBottomLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderBottomLineStyle = thickStyle;
break;
}
}
// Determine Top Edge Line Style
if (topEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderTopLineStyle = new ElementId(-1);
}
else
{
switch (topEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderTopLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderTopLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderTopLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderTopLineStyle = thickStyle;
break;
}
}
// Determine Left Edge Line Style
if (leftEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderLeftLineStyle = new ElementId(-1);
}
else
{
switch (leftEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderLeftLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderLeftLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderLeftLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderLeftLineStyle = thickStyle;
break;
}
}
// Determine Right Edge Line Style
if (rightEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
{
cellStyle.BorderRightLineStyle = new ElementId(-1);
}
else
{
switch (rightEdge.Weight)
{
case (int)Excel.XlBorderWeight.xlHairline:
cellStyle.BorderRightLineStyle = hairlineStyle;
break;
case (int)Excel.XlBorderWeight.xlThin:
cellStyle.BorderRightLineStyle = thinStyle;
break;
case (int)Excel.XlBorderWeight.xlMedium:
cellStyle.BorderRightLineStyle = mediumStyle;
break;
case (int)Excel.XlBorderWeight.xlThick:
cellStyle.BorderRightLineStyle = thickStyle;
break;
}
}
// Border Styles are always overridden
styleOverride.BorderBottomLineStyle = true;
styleOverride.BorderTopLineStyle = true;
styleOverride.BorderLeftLineStyle = true;
styleOverride.BorderRightLineStyle = true;
if (styleOverride.BorderBottomLineStyle || styleOverride.BorderTopLineStyle ||
styleOverride.BorderLeftLineStyle || styleOverride.BorderRightLineStyle)
{
styleOverride.BorderLineStyle = true;
}
// Get Background color and font name
System.Drawing.Color backGroundColor = System.Drawing.ColorTranslator.FromOle((int)cell.Interior.Color);
cellStyle.BackgroundColor = new Color(backGroundColor.R, backGroundColor.G, backGroundColor.B);
styleOverride.BackgroundColor = true;
cellStyle.FontName = cell.Font.Name;
styleOverride.Font = true;
// Determine Horizontal Alignment
// If its not set to left, right or center, do not modify
switch (dispFormat.HorizontalAlignment)
{
case (int)Excel.XlHAlign.xlHAlignLeft:
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left;
styleOverride.HorizontalAlignment = true;
break;
case (int)Excel.XlHAlign.xlHAlignRight:
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right;
styleOverride.HorizontalAlignment = true;
break;
case (int)Excel.XlHAlign.xlHAlignGeneral: // No specific style assigned
// Check if it's a number which is typically right aligned
if (double.TryParse(cell.Text, out double alignTest))
{
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right;
styleOverride.HorizontalAlignment = true;
}
else // Assume text and left align it
{
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left;
styleOverride.HorizontalAlignment = true;
}
break;
case (int)Excel.XlHAlign.xlHAlignCenter:
cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Center;
styleOverride.HorizontalAlignment = true;
break;
}
// Get the vertical alignment of the cell
switch (dispFormat.VerticalAlignment)
{
case (int)Excel.XlVAlign.xlVAlignBottom:
cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Bottom;
styleOverride.VerticalAlignment = true;
break;
case (int)Excel.XlVAlign.xlVAlignTop:
cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Top;
styleOverride.VerticalAlignment = true;
break;
default:
cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Middle;
styleOverride.VerticalAlignment = true;
break;
}
switch (dispFormat.Orientation)
{
case (int)Excel.XlOrientation.xlUpward:
cellStyle.TextOrientation = 9;
styleOverride.TextOrientation = true;
break;
case (int)Excel.XlOrientation.xlDownward:
cellStyle.TextOrientation = -9;
styleOverride.TextOrientation = true;
break;
case (int)Excel.XlOrientation.xlVertical:
cellStyle.TextOrientation = 9;
styleOverride.TextOrientation = true;
break;
default:
int rotation = (int)cell.Orientation;
if (rotation != (int)Excel.XlOrientation.xlHorizontal)
{
cellStyle.TextOrientation = rotation;
styleOverride.TextOrientation = true;
}
break;
}
// Determine Text Size
double textSize = Convert.ToDouble(font.Size);
//double newTextSize = (textSize / 72) / 12;
cellStyle.TextSize = textSize;
styleOverride.FontSize = true;
// Determine Font Color
System.Drawing.Color fontColor = System.Drawing.ColorTranslator.FromOle((int)font.Color);
cellStyle.TextColor = new Color(fontColor.R, fontColor.G, fontColor.B);
styleOverride.FontColor = true;
// NOTES: Bold is a bool
// Italic is a bool
// Underline is an int
cellStyle.IsFontBold = (bool)font.Bold;
cellStyle.IsFontItalic = (bool)font.Italic;
cellStyle.IsFontUnderline = (int)font.Underline == 2;
styleOverride.Bold = true;
styleOverride.Italics = true;
styleOverride.Underline = true;
cellStyle.SetCellStyleOverrideOptions(styleOverride);
if (cell.MergeCells == true)
{
TableMergedCell tmc = new TableMergedCell()
{
Left = j - 1,
Right = cell.MergeArea.Columns.Count - 1,
Top = i - 1,
Bottom = (i - 1) + cell.MergeArea.Rows.Count - 1
};
// Check to see if the cell is already merged...
bool alreadyMerged = false;
foreach (TableMergedCell mergedCell in mergedCells)
{
bool left = false;
bool right = false;
bool top = false;
bool bottom = false;
if (i - 1 >= mergedCell.Top)
{
top = true;
}
if (i - 1 <= mergedCell.Bottom)
{
bottom = true;
}
if (j - 1 >= mergedCell.Left)
{
left = true;
}
if (j - 1 <= mergedCell.Right)
{
right = true;
}
//TaskDialog.Show("MergedCell", string.Format("Top: {0}\nBottom: {1}\nLeft: {2}\nRight: {3}\ni-1: {4}\nj-1: {5}", mergedCell.Top, mergedCell.Bottom, mergedCell.Left, mergedCell.Right, i - 1, j - 1));
if (top && bottom && left && right)
{
alreadyMerged = true;
break;
}
}
if (!alreadyMerged)
{
try
{
headerData.MergeCells(tmc);
headerData.SetCellText(i - 1, j - 1, cell.Text);
headerData.SetCellStyle(i - 1, j - 1, cellStyle);
j += cell.MergeArea.Columns.Count - 1;
mergedCells.Add(tmc);
// TaskDialog.Show("Test", string.Format("This cell [{0},{1}] is merged.\nMerged Area: [{2},{3}]", cell.Row - 1, cell.Column - 1, cell.MergeArea.Rows.Count.ToString(), cell.MergeArea.Columns.Count.ToString()));
}
catch
{
}
}
}
else
{
//TaskDialog.Show("Non Merged", string.Format("This cell is not merged with any others [{0}, {1}]", i - 1, j - 1));
try
{
headerData.SetCellText(i - 1, j - 1, cell.Text);
headerData.SetCellStyle(i - 1, j - 1, cellStyle);
}
catch { }
}
}
}
if (errorCount > 0)
{
TaskDialog.Show("Warning", "Error reloading content for " + errorCount.ToString() + " cells.\n\nConsider unchecking the \"Content Only\" checkbox and reloading the schedule to force it to rebuild.");
}
// Write the Schema to the project
Schema schema = null;
try
{
schema = Schema.Lookup(schemaGUID);
}
catch { }
ModifySchemaData(schema, sched.Id);
workbook.Close(false);
Marshal.ReleaseComObject(worksheets);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
}
/// <summary>
/// Получение сортированных элементов из спецификации с не установленной галочкой "Для каждого экземпляра"
/// </summary>
/// <param name="viewSchedule">Вид спецификации</param>
/// <param name="elements">Элементы, полученные с этого вида</param>
private IEnumerable <RowData <int> > GetSortedElementsFromNotItemizedScheduleBySignalValue(
ViewSchedule viewSchedule, List <Element> elements)
{
var sortedElements = new List <RowData <int> >();
var builtInParameter = GetBuiltInParameterForElements(elements);
if (builtInParameter == null)
{
return(sortedElements);
}
// запоминаю начальные значения в параметре
var cachedParameterValues = new Dictionary <Element, string>();
elements.ForEach(e =>
{
var parameter = e.get_Parameter(builtInParameter.Value);
cachedParameterValues.Add(e, parameter.AsString());
});
const string signalValue = "$Filled$";
var columnNumber = -1;
var startRowNumber = -1;
bool fieldAlreadyAdded;
using (var transaction = new SubTransaction(viewSchedule.Document))
{
transaction.Start();
// всем элементам записываем в комментарий сигнальное значение в виде двух специальных символов
elements.ForEach(e =>
{
if (e.GroupId != ElementId.InvalidElementId)
{
(e.Document.GetElement(e.GroupId) as Group)?.UngroupMembers();
}
var parameter = e.get_Parameter(builtInParameter.Value);
parameter.Set(signalValue);
});
// К спецификации добавляем поле. Код из справки, за исключением try {} catch{}
AddFieldToSchedule(viewSchedule, builtInParameter.Value, out fieldAlreadyAdded);
// в зависимости от количества строк в таблице сразу заполняю коллекцию "болванками" и
// нахожу номер нужной колонки и первой строки
var sectionData = viewSchedule.GetTableData().GetSectionData(SectionType.Body);
var rowNumber = 0;
for (var r = sectionData.FirstRowNumber; r <= sectionData.LastRowNumber; r++)
{
for (var c = sectionData.FirstColumnNumber; c <= sectionData.LastColumnNumber; c++)
{
var cellValue = viewSchedule.GetCellText(SectionType.Body, r, c);
if (cellValue.Contains(signalValue))
{
rowNumber++;
sortedElements.Add(new RowData <int>(rowNumber));
if (startRowNumber == -1)
{
startRowNumber = r;
}
if (columnNumber == -1)
{
columnNumber = c;
}
break;
}
}
}
transaction.Commit();
}
// теперь выполняю итерацию по всем элементам
for (var index = 0; index < elements.Count; index++)
{
var element = elements[index];
using (var transaction = new SubTransaction(viewSchedule.Document))
{
transaction.Start();
if (index != 0)
{
var parameter = elements[index - 1].get_Parameter(builtInParameter.Value);
// возвращаю элементу значение в параметр
parameter.Set(signalValue);
}
{
// элементу стираю второй символ. Первый символ нужен, чтобы идентифицировать ячейку
var parameter = element.get_Parameter(builtInParameter.Value);
parameter.Set(string.Empty);
}
// регенерирую таблицу, чтобы обновить представление
viewSchedule.RefreshData();
viewSchedule.Document.Regenerate();
transaction.Commit();
}
// теперь смотрю какая ячейка погасла
var sectionData = viewSchedule.GetTableData().GetSectionData(SectionType.Body);
var rowNumber = 0;
for (var r = startRowNumber; r <= sectionData.LastRowNumber; r++)
{
rowNumber++;
var cellValue = viewSchedule.GetCellText(SectionType.Body, r, columnNumber);
/*
* Начиная с версии 2022 в ячейках с разными значениями пишется <варианты>. Проверять буду по скобкам
*/
if (string.IsNullOrEmpty(cellValue) || (cellValue.StartsWith("<") && cellValue.EndsWith(">")))
{
var elInRow = sortedElements.FirstOrDefault(e => e.RowNumber == rowNumber);
elInRow?.Items.Add(element.Id.IntegerValue);
break;
}
}
}
// восстанавливаю начальные значения параметров
using (var transaction = new SubTransaction(viewSchedule.Document))
{
transaction.Start();
foreach (var pair in cachedParameterValues)
{
var parameter = pair.Key.get_Parameter(builtInParameter.Value);
parameter.Set(pair.Value);
}
// если поле не было добавлено изначально, то нужно его удалить
if (!fieldAlreadyAdded)
{
RemoveFieldFromSchedule(viewSchedule, builtInParameter.Value);
}
transaction.Commit();
}
return(sortedElements);
}
/// <summary>
/// Получение сортированных элементов из спецификации с не установленной галочкой "Для каждого экземпляра"
/// </summary>
/// <remarks>Алгоритм с удалением строк</remarks>
/// <param name="viewSchedule">Вид спецификации</param>
private IEnumerable <RowData <int> > GetSortedElementsFromNotItemizedScheduleByDeleteRows(
ViewSchedule viewSchedule)
{
var sortedElements = new List <RowData <int> >();
var allElements = new FilteredElementCollector(viewSchedule.Document, viewSchedule.Id)
.Where(e => e.IsValidObject && e.Category != null && e.Category.CategoryType != CategoryType.AnalyticalModel)
.Select(e => e.Id.IntegerValue)
.ToList();
var sectionData = viewSchedule.GetTableData().GetSectionData(SectionType.Body);
var rowNumber = 0;
var rowToRemove = sectionData.FirstRowNumber;
var rc = sectionData.LastRowNumber - sectionData.FirstRowNumber;
var sr = sectionData.FirstRowNumber;
for (var r = sr; r <= rc; r++)
{
using (var t = new SubTransaction(viewSchedule.Document))
{
t.Start();
try
{
sectionData.RemoveRow(rowToRemove);
rowNumber++;
var elements = new FilteredElementCollector(viewSchedule.Document, viewSchedule.Id)
.Where(e => e.IsValidObject && e.Category != null && e.Category.CategoryType != CategoryType.AnalyticalModel)
.ToDictionary(e => e.Id.IntegerValue, e => e);
var elementsInRow = new RowData <int>(rowNumber);
var iterateCount = allElements.Count - elements.Count;
var iterateIndex = 0;
var keysToRemove = new List <int>();
foreach (var id in allElements)
{
if (!elements.ContainsKey(id))
{
elementsInRow.Items.Add(id);
keysToRemove.Add(id);
iterateIndex++;
}
if (iterateIndex == iterateCount)
{
break;
}
}
foreach (var key in keysToRemove)
{
allElements.Remove(key);
}
sortedElements.Add(elementsInRow);
}
catch (Exception exception)
{
Debug.Print($"Remove row {r}: {exception.Message}");
rowToRemove++;
}
t.Commit();
}
}
return(sortedElements);
}
/// <summary>
/// Получение сортированных элементов из спецификации с установленной галочкой "Для каждого экземпляра"
/// </summary>
/// <param name="viewSchedule">Вид спецификации</param>
/// <param name="elements">Элементы, полученные с этого вида</param>
private List <Element> GetSortedElementsFromItemizedSchedule(ViewSchedule viewSchedule, List <Element> elements)
{
var sortedElements = new List <Element>();
var builtInParameter = GetBuiltInParameterForElements(elements);
if (builtInParameter == null)
{
return(sortedElements);
}
using (var transaction = new SubTransaction(viewSchedule.Document))
{
transaction.Start();
// Разделитель
const string separator = "$ElementId=";
// Записываем Id всех элементов в параметр "Комментарий"
elements.ForEach(e =>
{
if (e.GroupId != ElementId.InvalidElementId)
{
(e.Document.GetElement(e.GroupId) as Group)?.UngroupMembers();
}
var parameter = e.get_Parameter(builtInParameter.Value);
parameter.Set(parameter.AsString() + separator + e.Id.IntegerValue);
});
// К спецификации добавляем поле. Код из справки, за исключением try {} catch{}
AddFieldToSchedule(viewSchedule, builtInParameter.Value, out _);
// Ну и сама магия - просто читаем получившуюся спецификацию по ячейкам и получаем
// элементы уже в том порядке, в котором мы их видим в спецификации
var sectionData = viewSchedule.GetTableData().GetSectionData(SectionType.Body);
for (var r = sectionData.FirstRowNumber; r <= sectionData.LastRowNumber; r++)
{
for (var c = sectionData.FirstColumnNumber; c <= sectionData.LastColumnNumber; c++)
{
var cellValue = viewSchedule.GetCellText(SectionType.Body, r, c);
if (cellValue.Contains(separator))
{
var idStr = cellValue.Split(separator.ToCharArray()).Last();
if (!string.IsNullOrEmpty(idStr))
{
// Делаем устойчивым к ошибкам - при наличии ошибок все равно код завершится,
// а я буду знать о возможных проблемах. На мой взгляд лучше, чем полное прерывание метода
try
{
sortedElements.Add(viewSchedule.Document.GetElement(new ElementId(Convert.ToInt32(idStr))));
}
catch (Exception exception)
{
ExceptionBox.Show(exception);
}
}
}
}
}
// Откатываем транзакцию
transaction.RollBack();
}
return(sortedElements);
}
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
{
Document doc = commandData.Application.ActiveUIDocument.Document;
// Variables to store user input
List <int> listIds;
string desktopPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
// Variables to store log
var schedSuccess = new List <string>();
var schedFail = new List <string>();
// Prompt window to collect user input
using (BrowserCheckboxes customWindow = new BrowserCheckboxes(commandData))
{
customWindow.ShowDialog();
listIds = customWindow.listIds;
}
// Check there are schedules selected
if (listIds != null)
{
using (BrowserWindow browserWindow = new BrowserWindow())
{
browserWindow.ShowDialog();
// Variables
string fullPath = browserWindow.selectedPath;
// CHeck that path is not empty and path is a folder
if (fullPath == null || !Directory.Exists(fullPath))
{
TaskDialog.Show("Warning", "No folder has been selected");
return(Result.Cancelled);
}
else
{
// Loop through each selected schedule
foreach (int id in listIds)
{
// Extract data from schedule
ViewSchedule sched = doc.GetElement(new ElementId(id)) as ViewSchedule;
TableData tD = sched.GetTableData();
TableSectionData sectionData = tD.GetSectionData(SectionType.Body);
int numbRows = sectionData.NumberOfRows;
int numbCols = sectionData.NumberOfColumns;
// Name of the file
string excelPath = fullPath + @"\" + sched.Name + ".xlsx";
// Create excel file
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet excelSheet = (SXSSFSheet)workbook.CreateSheet(sched.Name);
excelSheet.SetRandomAccessWindowSize(100);
//Create a header row
IRow row = excelSheet.CreateRow(0);
// Define format for cells
var fontStyle = workbook.CreateFont();
fontStyle.IsBold = true;
fontStyle.FontHeightInPoints = 12;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(fontStyle);
// Write to excel
using (var fs = new FileStream(excelPath, FileMode.Create, FileAccess.Write))
{
// Write content
for (int i = 0; i < numbRows; i++)
{
row = excelSheet.CreateRow(i);
for (int j = 0; j < numbCols; j++)
{
string content = sched.GetCellText(SectionType.Body, i, j);
var cell = row.CreateCell(j);
cell.SetCellValue(content);
if (i == 0)
{
cell.CellStyle = titleStyle;
}
}
}
// Size columns
excelSheet.TrackAllColumnsForAutoSizing();
for (int i = 0; i < numbCols; i++)
{
excelSheet.AutoSizeColumn(i);
}
excelSheet.UntrackAllColumnsForAutoSizing();
// Write to file
try
{
workbook.Write(fs);
// Log success export schedule name
schedSuccess.Add(sched.Name);
}
catch
{
schedFail.Add(sched.Name);
}
}
}
TaskDialog.Show("Success", "The following schedules have been exported: " +
string.Join("\n", schedSuccess.ToArray()));
return(Result.Succeeded);
}
}
}
return(Result.Cancelled);
}
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
{
Document doc = commandData.Application.ActiveUIDocument.Document;
ViewSchedule vs = commandData.Application.ActiveUIDocument.ActiveView as ViewSchedule;
ScheduleDefinition sdef = null;
if (vs == null)
{
Selection sel = commandData.Application.ActiveUIDocument.Selection;
if (sel.GetElementIds().Count == 0)
{
return(Result.Failed);
}
ScheduleSheetInstance ssi = doc.GetElement(sel.GetElementIds().First()) as ScheduleSheetInstance;
if (ssi == null)
{
return(Result.Failed);
}
if (!ssi.Name.Contains("ВРС"))
{
return(Result.Failed);
}
vs = doc.GetElement(ssi.ScheduleId) as ViewSchedule;
}
sdef = vs.Definition;
int firstWeightCell = 0;
int startHiddenFields = 0;
int borderCell = 9999;
//определяю первую и последнюю ячейку с массой
for (int i = 0; i < sdef.GetFieldCount(); i++)
{
ScheduleField sfield = sdef.GetField(i);
string cellName = sfield.GetName();
if (firstWeightCell == 0)
{
if (char.IsNumber(cellName[0]))
{
firstWeightCell = i;
}
else
{
if (sfield.IsHidden)
{
startHiddenFields++;
}
}
}
if (cellName.StartsWith("="))
{
borderCell = i;
break;
}
}
using (Transaction t = new Transaction(doc))
{
t.Start("Отобразить все ячейки");
for (int i = firstWeightCell; i < borderCell; i++)
{
ScheduleField sfield = sdef.GetField(i);
string cellName = sfield.GetName();
sfield.IsHidden = false;
}
doc.Regenerate();
TableData tdata = vs.GetTableData();
TableSectionData tsd = tdata.GetSectionData(SectionType.Body);
int firstRownumber = tsd.FirstRowNumber;
int lastRowNumber = tsd.LastRowNumber;
int rowsCount = lastRowNumber - firstRownumber;
for (int i = firstWeightCell; i < borderCell; i++)
{
ScheduleField sfield = sdef.GetField(i);
string cellName = sfield.GetName();
List <string> values = new List <string>();
for (int j = firstRownumber; j <= lastRowNumber; j++)
{
string cellText = tsd.GetCellText(j, i - startHiddenFields);
values.Add(cellText);
}
bool checkOnlyTextAndZeros = OnlyTextAndZeros(values);
if (checkOnlyTextAndZeros)
{
sfield.IsHidden = true;
}
}
t.Commit();
}
return(Result.Succeeded);
}
private RevitDataTable GetRevitDataTable(ViewSchedule schedule)
{
ScheduleDefinition scheduleDefinition = schedule.Definition;
TableSectionData bodyData = schedule.GetTableData().GetSectionData(SectionType.Body);
TableSectionData headerData = schedule.GetTableData().GetSectionData(SectionType.Header);
RevitDataTable dataTable = new RevitDataTable(schedule.Name);
RevitDataSection dataSection = new RevitDataSection("Без названия");
dataTable.Sections.Add(dataSection);
// заголовки
int start_i;
if (scheduleDefinition.ShowHeaders)
{
RevitDataRow header = new RevitDataRow(0);
for (int col = 0; col < bodyData.NumberOfColumns; col++)
{
RevitDataCell dataCell = new RevitDataCell(
schedule.GetCellText(SectionType.Body, 0, col),
bodyData.GetCellType(0, col),
bodyData.GetCellParamId(col));
header.Cells.Add(dataCell);
}
start_i = 1;
dataTable.Header = header;
}
else
{
start_i = 0;
}
//ищем секции
for (int row = start_i; row < bodyData.NumberOfRows; row++)
{
if (bodyData.GetCellType(row, 0) == CellType.Text &&
bodyData.GetMergedCell(row, 0).Right == bodyData.LastColumnNumber)
{
string header = bodyData.GetCellText(row, 0);
header = string.IsNullOrEmpty(header) ? "Без названия" : header;
dataSection = new RevitDataSection(header);
dataTable.Sections.Add(dataSection);
continue;
}
RevitDataRow dataRow = new RevitDataRow(row);
for (int col = 0; col < bodyData.NumberOfColumns; col++)
{
RevitDataCell dataCell = new RevitDataCell(
schedule.GetCellText(SectionType.Body, row, col),
bodyData.GetCellType(row, col),
bodyData.GetCellParamId(col));
dataRow.Cells.Add(dataCell);
}
dataSection.Rows.Add(dataRow);
}
if (dataTable["Без названия"].Rows.Count == 0)
{
dataTable.Sections.Remove(dataTable["Без названия"]);
}
return(dataTable);
}