private static List <List <string> > AggregateData(RevisionData selected,
RevOrderMgr om, DataItems.DataEnum field)
{
// rows x columns
List <List <string> > rawTableData = FormatTableData(selected, om);
List <List <string> > finalTableData = new List <List <string> >();
int newRow = 0;
int maxRawRows = rawTableData.Count;
finalTableData.Add(rawTableData[0].Clone());
for (int i = 1; i < rawTableData.Count; i++)
{
bool result = true;
foreach (DataItems.DataEnum item in om.SortOrder.Columns)
{
if (item.Equals(field))
{
continue;
}
if (!rawTableData[i - 1][item.DataIdx].
Equals(rawTableData[i][item.DataIdx]))
{
finalTableData.Add(rawTableData[i].Clone());
newRow++;
result = false;
break;
}
}
if (result)
{
if (!finalTableData[newRow][field.DataIdx]
.Equals(rawTableData[i][field.DataIdx]))
{
finalTableData[newRow][field.DataIdx] +=
nl + rawTableData[i][field.DataIdx];
}
}
}
Console.WriteLine("raw table data");
ListTableData(rawTableData);
Console.Write(nl);
Console.WriteLine("final table data");
ListTableData(finalTableData);
return(finalTableData);
}
public static bool ExportToExcel(RevisionData selected,
RevOrderMgr om)
{
List <List <string> > data =
AggregateData(selected, om, REV_SORT_ITEM_DESC);
int row = TITLE_ROW;
string outFile = SetUpOutputFile(Setg.TemplatePathAndFileName,
Setg.ExcelPathAndFileName);
X.Application excel = new X.Application();
if (excel == null)
{
return(false);
}
X.Workbook wb = excel.Workbooks.Open(outFile);
X.Worksheet wsData =
wb.Sheets[Setg.ExcelDataWorksheetName] as X.Worksheet;
if (wsData == null)
{
return(false);
}
excel.Visible = false;
ExportColumnTitles(wsData, row, om);
row++;
// startRow row count
X.Range range = GetRange(wsData, row, data.Count,
// startCol colCount
1, om.ColumnOrder.Count);
FormatDataCells(range);
ExportToExcel(data, row, wsData, om);
AdjustColumnWidthInRange(range.Columns, 1.5);
X.Worksheet wsPivot =
wb.Sheets[Setg.ExcelPivotWorksheetName] as X.Worksheet;
X.PivotTable pivotTable =
(X.PivotTable)wsPivot.PivotTables(Setg.ExcelPivotTableName);
pivotTable.RefreshTable();
excel.Visible = true;
return(true);
}
private static void ExportToExcel(RevisionData selected,
int startRow, X.Worksheet ws, RevOrderMgr om)
{
int row = startRow;
foreach (RevisionDataFields rdf in selected.GetEnumerable())
{
int col = 1;
foreach (DataItems.DataEnum d in om.ColumnOrder.Iterate())
{
ExportAnItem(rdf[d.DataIdx], d, row, col++, ws);
}
row++;
}
}
private static List <List <string> > FormatTableData(RevisionData selected,
RevOrderMgr om)
{
// rows x columns
List <List <string> > tableData = new List <List <string> >();
foreach (RevisionDataFields rdf in selected.GetEnumerable())
{
List <string> rowData = new List <string>(new string[selected.Count]);
foreach (DataItems.DataEnum d in om.ColumnOrder.itemize())
{
rowData[d.DataIdx] = (string.Format(d.Display.FormatString,
rdf[d.DataIdx] ?? ""));
}
tableData.Add(rowData);
}
return(tableData);
}
public static RevisionData Scan()
{
RevisionData data = new RevisionData();
dynamic[] Items;
Items = new dynamic[]
{
// order and translation order
"False", // 0 => 0 // selected
"1", // 1 => 1 // seq
"1", // 2 => 3a // alt id
".00", // 3 => 3b // type code
".00.00", // 4 => 3c // disc code
"BULLETIN 001", // 5 => 4 // delta title
"CS000", // 6 => 5 // sht num
"Hidden", // 7 => 6 // visibility
"1", // 8 => 2 // rev id
"BULLETIN 001", // 9 => 7 // block title
"1/1/2018", //10 => 8 // date
"pcc", //11 => 9 // basis
"rev desc 1-cs000-000-1 this description has been made extra "
+ "long in order to test what happens when a description is "
+ "very long. I've made this description into two"
+ "sentences to test this also", //12 => 10 // desc
"-1", //13 => 11 // tag elem id
"209594" //14 => 12 // cloud elem id
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"1",
"1",
".00",
".00.00",
"BULLETIN 001",
"CS000",
"Hidden",
"1",
"BULLETIN 001",
"1/1/2018",
"pcc",
"rev desc 1-cs000-000-2",
"-1",
"209594",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"1",
"1",
".00",
".00.00",
"BULLETIN 001",
"CS000",
"Hidden",
"1",
"BULLETIN 001",
"1/1/2018",
"rfi",
"rev desc 1-cs000-000-3",
"-1",
"209594",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"1",
"1",
".00",
".00.00",
"BULLETIN 001",
"CS100",
"Hidden",
"1",
"BULLETIN 001",
"1/1/2018",
"owner rev",
"rev comment 1",
"-1",
"209594",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"2",
"1",
".10",
".00.00",
"ASI 007",
"CS000",
"TagVisible",
"",
"ASI 007 (BULLETIN 001)",
"1/7/2018",
"owner rev",
"rev desc 1-cs000-007-1",
"-1",
"213234",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"2",
"1",
".10",
".00.00",
"ASI 007",
"CS000",
"TagVisible",
"",
"ASI 007 (BULLETIN 001)",
"1/7/2018",
"pcc",
"rev desc 1-cs000-007-2",
"-1",
"213234",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"2",
"1",
".10",
".00.00",
"ASI 007",
"CS000",
"TagVisible",
"",
"ASI 007 (BULLETIN 001)",
"1/7/2018",
"owner rev",
"rev desc 1-cs000-007-1",
"-1",
"213234",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"3",
"1",
".10",
".00.00",
"ASI 008",
"CS000",
"CloudAndTagVisible",
"",
"ASI 008 (BULLETIN 001)",
"1/8/2018",
"owner rev",
"desc 001-008-000",
"-1",
"214040",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"1A A201",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"rfi",
"rev desc 1A A201 -006-013-1",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"1A A201",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"pcc",
"rev desc 1A A201 -006-013-1",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"1A A202",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"rfi",
"rev desc 1A A202 -006-013-1",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"1A A202",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"owner rev",
"rev desc 1A A202 -006-013-2",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"1B A201",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"pcc",
"rev desc 1B A201 -006-013-1",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"1B A201",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"owner rev",
"rev desc 1B A201 -006-013-2",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"AA A2.20-201.10",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"pcc",
"Revision description ASI 013-01",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
Items = new dynamic[]
{
"False",
"11",
"6",
".10",
".07.00",
"ASI 013",
"AA A2.20-201.10",
"CloudAndTagVisible",
"",
"ASI 013 (BULLETIN 006)",
"6/2/2018",
"owner rev",
"Revision description ASI 013-02",
"-1",
"226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 006
Items = new dynamic[]
{
"False",
"9",
"6",
".00",
".00.00",
"BULLETIN 006",
"CS000",
"CloudAndTagVisible",
"6",
"BULLETIN 006",
"6/1/2018",
"owner rev",
"rev desc 1-cs000-006-xx1",
"-1",
"225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - CS000 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "CS000", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-cs000-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - CS000 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "CS000", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-cs000-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - CS000 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "CS000", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-cs000-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - CS000 - description 2
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "CS000", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 2-cs000-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A201 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A201", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-1A A201-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A201 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A201", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-1A A201-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A201 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A201", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-1A A201-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A201 - description 2
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A201", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 2-1A A201-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A202 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A202", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-1A A202-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A202 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A202", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-1A A202-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A202 - description 1
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A202", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 1-1A A202-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 - 1A A202 - description 2
Items = new dynamic[]
{
"False", "10", "7", ".00", ".00.00", "BULLETIN 007", "1A A202", "CloudAndTagVisible", "7", "BULLETIN 007", "7/1/2018", "owner rev", "rev desc 2-1A A202-007-xx1", "-1", "225501",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-201.10 - description 1
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-201.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-01", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-201.10 - description 1
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-201.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-01", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-201.10 - description 2
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-201.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-02", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-201.10 - description 3
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-201.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-03", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-202.10 - description 1
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-202.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-01", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-202.10 - description 2
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-202.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-02", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-202.10 - description 3
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-202.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-03", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-202.10 - description 4
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-202.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-04", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-202.10 - description 5
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-202.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-05", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-202.10 - description 5
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-202.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-05", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / ASI 107 - sheet AA A2.20-202.10 - description 5
Items = new dynamic[]
{
"False", "11", "7", ".10", ".07.00", "ASI 107", "AA A2.20-202.10", "CloudAndTagVisible", "", "ASI 107 (BULLETIN 007)", "7/2/2018", "owner change", "Revision description RFI 107-05", "-1", "226131",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / RFI 701 - sheet 1A A201 - description 1
Items = new dynamic[]
{
"False", "12", "7", ".20", ".07.00", "RFI 701", "1A A201", "TagVisible", "", "RFI 701 (BULLETIN 007)", "7/3/2018", "owner rev", "rev desc rfi 701", "-1", "227383",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / RFI 701 - sheet 1A A202 - description 1
Items = new dynamic[]
{
"False", "12", "7", ".20", ".07.00", "RFI 701", "1A A202", "TagVisible", "", "RFI 701 (BULLETIN 007)", "7/3/2018", "pcc", "rev desc rfi 701", "-1", "227383",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / RFI 701 - sheet 1A A202 - description 1
Items = new dynamic[]
{
"False", "12", "7", ".20", ".07.00", "RFI 701", "1A A202", "TagVisible", "", "RFI 701 (BULLETIN 007)", "7/3/2018", "rfi", "rev desc rfi 701", "-1", "227383",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / RFI 701 - sheet 1B A201 - description 1
Items = new dynamic[]
{
"False", "12", "7", ".20", ".07.00", "RFI 701", "1B A201", "TagVisible", "", "RFI 701 (BULLETIN 007)", "7/3/2018", "rfi", "rev desc rfi 701", "-1", "226383",
};
data.Add(MakeRevDataItem(Items));
// bulletin 007 / RFI 701 - sheet AA A2.20-201.10 - description 1
Items = new dynamic[]
{
"False", "12", "7", ".20", ".07.00", "RFI 701", "AA A2.20-201.10", "TagVisible", "", "RFI 701 (BULLETIN 007)", "7/3/2018", "rfi", "rev desc rfi 701", "-1", "227383",
};
data.Add(MakeRevDataItem(Items));
return(data);
}
public static void ClearSelected()
{
_selected = new RevisionData();
}
// prep for changing selected
public static void SetPreSelectedToSelected()
{
_preSelected = _selected.Clone();
}
public static void ResetSelected()
{
_selected = _masterRevData.Clone();
}
public static void GetRevisions()
{
_masterRevData = RevitRevisions.Read();
ResetPreSelected();
ClearSelected();
}