GetTableColumnIndex
(
ListObject oTable,
String sColumnName,
Boolean bColumnIsRequired
)
{
Debug.Assert(oTable != null);
Debug.Assert(!String.IsNullOrEmpty(sColumnName));
AssertValid();
ListColumn oColumn;
if (ExcelTableUtil.TryGetTableColumn(oTable, sColumnName, out oColumn))
{
return(oColumn.Index);
}
if (bColumnIsRequired)
{
OnWorkbookFormatError(String.Format(
"The table named \"{0}\" must have a column named \"{1}.\""
+ "\r\n\r\n{2}"
,
oTable.Name,
sColumnName,
ErrorUtil.GetTemplateMessage()
));
}
return(NoSuchColumn);
}
cbxVertexColumnName_SelectedIndexChanged
(
object sender,
EventArgs e
)
{
AssertValid();
String sVertexColumnName = cbxVertexColumnName.Text;
ListColumn oVertexColumn;
if (
String.IsNullOrEmpty(sVertexColumnName)
||
m_oVertexTable == null
||
!ExcelTableUtil.TryGetTableColumn(m_oVertexTable,
sVertexColumnName, out oVertexColumn)
)
{
return;
}
cbxVertexColumnFormat.SelectedValue =
ExcelTableUtil.GetTableColumnFormat(oVertexColumn);
EnableControls();
}
GetRequiredTables
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
out ListObject oEdgeTable,
out ListObject oVertexTable
)
{
Debug.Assert(oWorkbook != null);
AssertValid();
// Get the required table that contains edge data. GetEdgeTable()
// checks for the required vertex name columns.
EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader();
oEdgeTable = oEdgeWorksheetReader.GetEdgeTable(oWorkbook);
// Normally, the vertex table isn't required, but to avoid having to
// create the table in code if it's missing, require it here.
if (ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oVertexTable))
{
// Make sure the vertex name column exists.
ListColumn oColumn;
if (!ExcelTableUtil.TryGetTableColumn(oVertexTable,
VertexTableColumnNames.VertexName, out oColumn))
{
oVertexTable = null;
}
}
else
{
oVertexTable = null;
}
if (oVertexTable == null)
{
throw new WorkbookFormatException(String.Format(
"To use this feature, there must be a worksheet named \"{0}\""
+ " that contains a table named \"{1}\", and that table must"
+ " contain a column named \"{2}\"."
+ "\r\n\r\n"
+ "{3}"
,
WorksheetNames.Vertices,
TableNames.Vertices,
VertexTableColumnNames.VertexName,
ErrorUtil.GetTemplateMessage()
));
}
}
GetAttributeColumn
(
ListObject oTable,
String sAttribute,
out ListColumn oAttributeColumn,
out Range oAttributeColumnData
)
{
Debug.Assert(oTable != null);
Debug.Assert(!String.IsNullOrEmpty(sAttribute));
if (!ExcelTableUtil.TryGetTableColumn(oTable, sAttribute,
out oAttributeColumn))
{
if (!ExcelTableUtil.TryAddTableColumn(oTable, sAttribute,
ExcelTableUtil.AutoColumnWidth, null, out oAttributeColumn))
{
goto CannotGetColumn;
}
// Wrap the text in the new column's header.
ExcelTableUtil.WrapTableColumnHeader(oAttributeColumn);
// This sometimes wraps a single-word header. Fix it.
oAttributeColumn.Range.EntireColumn.AutoFit();
}
if (ExcelTableUtil.TryGetTableColumnData(oAttributeColumn,
out oAttributeColumnData))
{
// Success.
return;
}
CannotGetColumn:
throw new WorkbookFormatException(
"The " + sAttribute + " column couldn't be added."
);
}
RestoreHiddenColumns
(
ListObject table,
ExcelHiddenColumns excelHiddenColumns
)
{
Debug.Assert(table != null);
Debug.Assert(excelHiddenColumns != null);
foreach (String sColumnName in excelHiddenColumns)
{
ListColumn oColumn;
if (ExcelTableUtil.TryGetTableColumn(table, sColumnName,
out oColumn))
{
ShowOrHideColumn(oColumn, false);
}
}
}
ShowOrHideColumn
(
ListObject table,
String columnName,
Boolean show
)
{
Debug.Assert(table != null);
Debug.Assert(!String.IsNullOrEmpty(columnName));
Boolean bColumnWasVisible = false;
ListColumn oColumn;
if (ExcelTableUtil.TryGetTableColumn(table, columnName, out oColumn))
{
bColumnWasVisible = ShowOrHideColumn(oColumn, show);
}
return(bColumnWasVisible);
}
TryGetRequiredColumnInformation
(
GraphMetricColumn oGraphMetricColumn,
ListObject oTable,
out Range oVisibleColumnData
)
{
Debug.Assert(oGraphMetricColumn != null);
Debug.Assert(oTable != null);
AssertValid();
oVisibleColumnData = null;
// Add the specified column if it's not already present.
String sColumnName = oGraphMetricColumn.ColumnName;
String sColumnStyle = oGraphMetricColumn.Style;
Microsoft.Office.Interop.Excel.ListColumn oColumn;
if (
!ExcelTableUtil.TryGetTableColumn(oTable, sColumnName, out oColumn)
&&
!ExcelTableUtil.TryAddTableColumn(oTable, sColumnName,
oGraphMetricColumn.ColumnWidthChars, sColumnStyle, out oColumn)
)
{
// Give up.
return(false);
}
Range oColumnData;
if (!ExcelTableUtil.TryGetTableColumnData(oTable, sColumnName,
out oColumnData))
{
return(false);
}
ExcelUtil.SetRangeStyle(oColumnData, sColumnStyle);
String sNumberFormat = oGraphMetricColumn.NumberFormat;
if (sNumberFormat != null)
{
oColumnData.NumberFormat = sNumberFormat;
}
// Wrapping text makes Range.set_Value() very slow, so turn it off.
oColumn.Range.WrapText = false;
// But wrap the text in the column's header.
ExcelTableUtil.WrapTableColumnHeader(oColumn);
// Get the visible range.
if (!ExcelUtil.TryGetVisibleRange(oColumnData,
out oVisibleColumnData))
{
return(false);
}
return(true);
}
FillVertexTable
(
ListObject oVertexTable,
HashSet <String> oUniqueVertexNames
)
{
Debug.Assert(oVertexTable != null);
Debug.Assert(oUniqueVertexNames != null);
AssertValid();
// There may already be some vertex names in the table. For each
// existing name, remove the name from the HashSet.
Int32 iExistingRows = 0;
Range oVertexNameRange;
if (ExcelTableUtil.TryGetTableColumnData(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameRange))
{
iExistingRows = oVertexNameRange.Rows.Count;
// Read the vertex names all at once.
Object [,] aoVertexNameValues =
ExcelUtil.GetRangeValues(oVertexNameRange);
// Loop through the vertices.
for (Int32 iRowOneBased = 1; iRowOneBased <= iExistingRows;
iRowOneBased++)
{
// Get the vertex name and remove it from the HashSet.
String sVertexName;
if (ExcelUtil.TryGetNonEmptyStringFromCell(
aoVertexNameValues, iRowOneBased, 1, out sVertexName))
{
oUniqueVertexNames.Remove(sVertexName);
}
}
}
// Now create an array for the vertices that remain in the HashSet.
// These are vertices that were in the edge table but not the vertex
// table.
Int32 iRowsToAdd = oUniqueVertexNames.Count;
if (iRowsToAdd == 0)
{
return;
}
String [,] asAddedVertexNameValues = new String [iRowsToAdd, 1];
Int32 iIndex = 0;
foreach (String sUniqueVertexName in oUniqueVertexNames)
{
asAddedVertexNameValues[iIndex, 0] = sUniqueVertexName;
iIndex++;
}
// The table may be empty or contain empty rows. If so, the remaining
// vertices should be appended after the last non-empty row.
Int32 iLastNonEmptyRowOneBased;
Range oDataBodyRange = oVertexTable.DataBodyRange;
if (
oDataBodyRange == null
||
!ExcelUtil.TryGetLastNonEmptyRow(oDataBodyRange,
out iLastNonEmptyRowOneBased)
)
{
// There were no non-empty data rows in the table. Use an offset
// of 1 from the header row instead.
oDataBodyRange = oVertexTable.HeaderRowRange;
iExistingRows = 1;
}
else
{
iExistingRows = iLastNonEmptyRowOneBased - oDataBodyRange.Row + 1;
}
// Get the index of the vertex name column.
ListColumn oVertexNameColumn;
if (!ExcelTableUtil.TryGetTableColumn(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameColumn))
{
// This can't happen, because GetRequiredTables() has
// verified that the column exists.
Debug.Assert(false);
}
Int32 iVertexNameColumnIndexOneBased = oVertexNameColumn.Index;
Debug.Assert(oVertexTable.Parent is Worksheet);
Worksheet oVertexWorksheet = (Worksheet)oVertexTable.Parent;
Range oAddedVertexNameRange = oVertexWorksheet.get_Range(
(Range)oDataBodyRange.Cells[iExistingRows + 1,
iVertexNameColumnIndexOneBased],
(Range)oDataBodyRange.Cells[iExistingRows + iRowsToAdd,
iVertexNameColumnIndexOneBased]
);
oAddedVertexNameRange.set_Value(
Missing.Value, asAddedVertexNameValues);
}
ReadGroupTables
(
ListObject oGroupTable,
ListObject oGroupVertexTable,
ReadWorkbookContext oReadWorkbookContext,
IGraph oGraph
)
{
Debug.Assert(oGroupTable != null);
Debug.Assert(oGroupVertexTable != null);
Debug.Assert(oReadWorkbookContext != null);
Debug.Assert(oGraph != null);
AssertValid();
// If a required column is missing, do nothing.
ListColumn oColumn;
if (
!ExcelTableUtil.TryGetTableColumn(oGroupTable,
GroupTableColumnNames.Name, out oColumn)
||
!ExcelTableUtil.TryGetTableColumn(oGroupTable,
GroupTableColumnNames.VertexColor, out oColumn)
||
!ExcelTableUtil.TryGetTableColumn(oGroupTable,
GroupTableColumnNames.VertexShape, out oColumn)
||
!ExcelTableUtil.TryGetTableColumn(oGroupVertexTable,
GroupVertexTableColumnNames.GroupName, out oColumn)
||
!ExcelTableUtil.TryGetTableColumn(oGroupVertexTable,
GroupVertexTableColumnNames.VertexName, out oColumn)
)
{
return;
}
// These are the names of the groups that should be skipped or hidden.
HashSet <String> oSkippedGroupNames = new HashSet <String>();
HashSet <String> oHiddenGroupNames = new HashSet <String>();
// Create a dictionary from the group table. The key is the group name
// and the value is an ExcelTemplateGroupInfo object for the group.
Dictionary <String, ExcelTemplateGroupInfo> oGroupNameDictionary =
ReadGroupTable(oGroupTable, oReadWorkbookContext,
oSkippedGroupNames, oHiddenGroupNames);
// Read the group vertex table and set the color and shape of each
// group vertex in the graph.
ReadGroupVertexTable(oGroupVertexTable, oReadWorkbookContext,
oGroupNameDictionary, oGraph);
// Now that the groups and the vertices they contain are known, skip
// and hide those groups that should be skipped or hidden.
SkipAndHideGroups(oReadWorkbookContext, oGroupNameDictionary,
oSkippedGroupNames, oHiddenGroupNames, oGraph);
if (oGroupNameDictionary.Count > 0)
{
// Save the group information on the graph.
Debug.Assert(oGroupNameDictionary.Values is
ICollection <ExcelTemplateGroupInfo>);
oGraph.SetValue(ReservedMetadataKeys.GroupInfo,
oGroupNameDictionary.Values.ToArray());
}
}
UpdateWorkbook
(
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(workbook != null);
Worksheet oWorksheet;
ListObject oTable;
ListColumn oColumn;
// Rename the Radius column on the Vertices worksheet to Size.
if (ExcelTableUtil.TryGetTable(workbook, "Vertices", "Vertices",
out oTable)
&&
ExcelTableUtil.TryGetTableColumn(oTable, "Radius", out oColumn)
)
{
oColumn.Name = "Size";
}
// Rename the Clusters worksheet to Groups.
if (ExcelUtil.TryGetWorksheet(workbook, "Clusters", out oWorksheet))
{
oWorksheet.Name = "Groups";
if (ExcelTableUtil.TryGetTable(oWorksheet, "Clusters", out oTable))
{
oTable.Name = "Groups";
if (ExcelTableUtil.TryGetTableColumn(oTable, "Cluster",
out oColumn))
{
oColumn.Name = "Group";
}
}
}
// Rename the Cluster Vertices worksheet to Group Vertices.
if (ExcelUtil.TryGetWorksheet(workbook, "Cluster Vertices",
out oWorksheet))
{
oWorksheet.Name = "Group Vertices";
if (ExcelTableUtil.TryGetTable(oWorksheet, "ClusterVertices",
out oTable))
{
oTable.Name = "GroupVertices";
if (ExcelTableUtil.TryGetTableColumn(oTable, "Cluster",
out oColumn))
{
oColumn.Name = "Group";
}
}
}
// Add a Label column to the Groups worksheet.
if (ExcelTableUtil.TryGetTable(workbook,
WorksheetNames.Groups, TableNames.Groups, out oTable))
{
ExcelTableUtil.TryGetOrAddTableColumn(oTable,
GroupTableColumnNames.Label, ExcelTableUtil.AutoColumnWidth,
CellStyleNames.Label, out oColumn);
}
// Rename the Metric column on the Overall Metrics worksheet to Graph
// Metric.
if (ExcelUtil.TryGetWorksheet(workbook, "Overall Metrics",
out oWorksheet))
{
if (ExcelTableUtil.TryGetTable(oWorksheet, "OverallMetrics",
out oTable))
{
if (ExcelTableUtil.TryGetTableColumn(oTable, "Metric",
out oColumn))
{
oColumn.Name = "Graph Metric";
}
}
}
}
GetColumnPairNames
(
ListObject table,
String column1NameBase,
String column2NameBase
)
{
Debug.Assert(table != null);
AssertValid();
List <KeyValuePair <String, String> > oColumnPairNames =
new List <KeyValuePair <String, String> >();
Regex oRegex = GetColumnNameRegex(column1NameBase);
// Loop through all the columns looking for a column name based on
// column1NameBase. If such a column is found, look for the
// corresponding column name based on column2NameBase.
foreach (ListColumn oColumn1 in table.ListColumns)
{
String sColumn1Name = oColumn1.Name;
ListColumn oColumn2 = null;
if (String.IsNullOrEmpty(sColumn1Name))
{
continue;
}
// Look for an exact match.
if (sColumn1Name == column1NameBase)
{
// Found column 1. Look for column 2.
if (!ExcelTableUtil.TryGetTableColumn(table, column2NameBase,
out oColumn2))
{
oColumn2 = null;
}
}
else
{
// Look for a "starts with" match.
Match oMatch = oRegex.Match(sColumn1Name);
Int32 iAppendedNumber;
if (
oMatch.Success
&&
Int32.TryParse(
oMatch.Groups[AppendedNumberGroupName].Value,
out iAppendedNumber)
)
{
// Found column 1. Look for column 2.
if (!ExcelTableUtil.TryGetTableColumn(table,
column2NameBase + " " + iAppendedNumber.ToString(),
out oColumn2))
{
oColumn2 = null;
}
}
}
if (oColumn2 != null)
{
oColumnPairNames.Add(new KeyValuePair <String, String>(
oColumn1.Name, oColumn2.Name));
}
}
return(oColumnPairNames);
}