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); }