AddCollapsedGroupAttributes ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext, IGraph graph ) { Debug.Assert(workbook != null); Debug.Assert(readWorkbookContext != null); Debug.Assert(graph != null); GroupInfo[] aoGroups; ListObject oEdgeTable, oVertexTable; if ( GroupUtil.TryGetGroups(graph, out aoGroups) && ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Edges, TableNames.Edges, out oEdgeTable) && ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable) ) { AddCollapsedGroupAttributesInternal(workbook, readWorkbookContext, oEdgeTable, oVertexTable, aoGroups); } }
TryGetIDAndDynamicFilterValues ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, String sWorksheetName, String sTableName, out Object [,] oIDColumnValues, out Object [,] oDynamicFilterColumnValues ) { Debug.Assert(oWorkbook != null); Debug.Assert(!String.IsNullOrEmpty(sWorksheetName)); Debug.Assert(!String.IsNullOrEmpty(sTableName)); oIDColumnValues = oDynamicFilterColumnValues = null; ListObject oTable; Range oIDColumnData, oDynamicFilterColumnData; return( ExcelTableUtil.TryGetTable(oWorkbook, sWorksheetName, sTableName, out oTable) && ExcelTableUtil.TryGetTableColumnDataAndValues(oTable, CommonTableColumnNames.ID, out oIDColumnData, out oIDColumnValues) && ExcelTableUtil.TryGetTableColumnDataAndValues(oTable, CommonTableColumnNames.DynamicFilter, out oDynamicFilterColumnData, out oDynamicFilterColumnValues) ); }
//************************************************************************* // Constructor: GroupByVertexAttributeDialog() // /// <overloads> /// Initializes a new instance of the <see /// cref="GroupByVertexAttributeDialog" /> class. /// </overloads> /// /// <param name="workbook"> /// Workbook containing the graph contents. /// </param> //************************************************************************* public GroupByVertexAttributeDialog ( Microsoft.Office.Interop.Excel.Workbook workbook ) { InitializeComponent(); m_oWorkbook = workbook; // Instantiate an object that saves and retrieves the user settings for // this dialog. Note that the object automatically saves the settings // when the form closes. m_oGroupByVertexAttributeDialogUserSettings = new GroupByVertexAttributeDialogUserSettings(this); if (ExcelTableUtil.TryGetTable(m_oWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out m_oVertexTable)) { cbxVertexColumnName.PopulateWithTableColumnNames(m_oVertexTable); } cbxVertexColumnFormat.PopulateWithObjectsAndText( ExcelColumnFormat.Other, "Categories", ExcelColumnFormat.Number, "Numbers", ExcelColumnFormat.Date, "Dates", ExcelColumnFormat.Time, "Times", ExcelColumnFormat.DateAndTime, "Dates with times" ); DoDataExchange(false); AssertValid(); }
TryGetNamesAndValues ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, out Object [,] aoNames, out Object [,] aoValues ) { Debug.Assert(oWorkbook != null); AssertValid(); aoNames = aoValues = null; ListObject oOverallMetricsTable; Range oRange; return( ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.OverallMetrics, TableNames.OverallMetrics, out oOverallMetricsTable) && ExcelTableUtil.TryGetTableColumnDataAndValues( oOverallMetricsTable, OverallMetricsTableColumnNames.Name, out oRange, out aoNames) && ExcelTableUtil.TryGetTableColumnDataAndValues( oOverallMetricsTable, OverallMetricsTableColumnNames.Value, out oRange, out aoValues) ); }
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() )); } }
TryGetPerWorkbookSettingsTable ( out ListObject oPerWorkbookSettingsTable ) { AssertValid(); return(ExcelTableUtil.TryGetTable(m_oWorkbook, WorksheetNames.Miscellaneous, TableNames.PerWorkbookSettings, out oPerWorkbookSettingsTable)); }
GetEdgeTable ( Microsoft.Office.Interop.Excel.Workbook workbook ) { Debug.Assert(workbook != null); AssertValid(); // Get the worksheet that contains edge data. Worksheet oEdgeWorksheet; if (!ExcelUtil.TryGetWorksheet(workbook, WorksheetNames.Edges, out oEdgeWorksheet)) { OnWorkbookFormatError(String.Format( "The workbook must contain a worksheet named \"{0}\" that" + " contains edge data.\r\n\r\n{1}" , WorksheetNames.Edges, ErrorUtil.GetTemplateMessage() )); } // Get the table (ListObject) that contains edge data. ListObject oEdgeTable; if (!ExcelTableUtil.TryGetTable(oEdgeWorksheet, TableNames.Edges, out oEdgeTable)) { OnWorkbookFormatError(String.Format( "The worksheet named \"{0}\" must have a table named \"{1}\"" + " that contains edge data.\r\n\r\n{2}" , WorksheetNames.Edges, TableNames.Edges, ErrorUtil.GetTemplateMessage() )); } // Make sure the vertex name columns exist. GetTableColumnIndex(oEdgeTable, EdgeTableColumnNames.Vertex1Name, true); GetTableColumnIndex(oEdgeTable, EdgeTableColumnNames.Vertex2Name, true); return(oEdgeTable); }
ReadWorksheet ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext, IGraph graph ) { Debug.Assert(workbook != null); Debug.Assert(readWorkbookContext != null); Debug.Assert(graph != null); AssertValid(); // Attempt to get the optional tables that contain group data. ListObject oGroupTable, oGroupVertexTable; if ( ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Groups, TableNames.Groups, out oGroupTable) && ExcelTableUtil.TryGetTable(workbook, WorksheetNames.GroupVertices, TableNames.GroupVertices, out oGroupVertexTable) ) { // The code that reads the tables can handle hidden rows, but not // hidden columns. Temporarily show all hidden columns in the // table. ExcelHiddenColumns oHiddenGroupColumns = ExcelColumnHider.ShowHiddenColumns(oGroupTable); ExcelHiddenColumns oHiddenGroupVertexColumns = ExcelColumnHider.ShowHiddenColumns(oGroupVertexTable); try { ReadGroupTables(oGroupTable, oGroupVertexTable, readWorkbookContext, graph); } finally { ExcelColumnHider.RestoreHiddenColumns(oGroupTable, oHiddenGroupColumns); ExcelColumnHider.RestoreHiddenColumns(oGroupVertexTable, oHiddenGroupVertexColumns); } } }
//************************************************************************* // Constructor: TopNByMetricUserSettingsDialog() // /// <summary> /// Initializes a new instance of the <see /// cref="TopNByMetricUserSettingsDialog" /> class. /// </summary> /// /// <param name="topNByMetricUserSettings"> /// The object being edited. /// </param> /// /// <param name="workbook"> /// Workbook containing the graph contents. /// </param> //************************************************************************* public TopNByMetricUserSettingsDialog ( TopNByMetricUserSettings topNByMetricUserSettings, Microsoft.Office.Interop.Excel.Workbook workbook ) { Debug.Assert(topNByMetricUserSettings != null); Debug.Assert(workbook != null); m_oTopNByMetricUserSettings = topNByMetricUserSettings; m_oWorkbook = workbook; InitializeComponent(); nudN.Minimum = TopNByMetricUserSettings.MinimumN; nudN.Maximum = TopNByMetricUserSettings.MaximumN; // This dialog is hard-coded for now to get only the top vertices from // the vertex worksheet. It can be updated later to get the top items // from any worksheet later, if necessary. If that is done, // TopNByMetrics.ToString() must also be updated. cbxWorksheetName.Items.Add(WorksheetNames.Vertices); cbxItemNameColumnName.Items.Add(VertexTableColumnNames.VertexName); ListObject oVertexTable; if (ExcelTableUtil.TryGetTable(m_oWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable)) { cbxRankedColumnName.PopulateWithTableColumnNames(oVertexTable); } // Instantiate an object that saves and retrieves the user settings for // this dialog. Note that the object automatically saves the settings // when the form closes. m_oTopNByMetricUserSettingsDialogUserSettings = new TopNByMetricUserSettingsDialogUserSettings(this); DoDataExchange(false); AssertValid(); }
GetGroupNamesByVertexName ( Microsoft.Office.Interop.Excel.Workbook workbook, ICollection <String> vertexNames ) { Debug.Assert(workbook != null); Debug.Assert(vertexNames != null); // Store the vertex names in a HashSet for quick lookup. HashSet <String> oVertexNames = new HashSet <String>(vertexNames); HashSet <String> oGroupNames = new HashSet <String>(); ListObject oGroupVertexTable; if (ExcelTableUtil.TryGetTable(workbook, WorksheetNames.GroupVertices, TableNames.GroupVertices, out oGroupVertexTable)) { foreach (ExcelTableReader.ExcelTableRow oRow in (new ExcelTableReader(oGroupVertexTable)).GetRows()) { String sGroupName, sVertexName; if ( oRow.TryGetNonEmptyStringFromCell( GroupVertexTableColumnNames.VertexName, out sVertexName) && oVertexNames.Contains(sVertexName) && oRow.TryGetNonEmptyStringFromCell( GroupVertexTableColumnNames.GroupName, out sGroupName) ) { oGroupNames.Add(sGroupName); } } } return(oGroupNames); }
//************************************************************************* // Constructor: WordMetricUserSettingsDialog() // /// <summary> /// Initializes a new instance of the <see /// cref="WordMetricUserSettingsDialog" /> class. /// </summary> /// /// <param name="wordMetricUserSettings"> /// The object being edited. /// </param> /// /// <param name="workbook"> /// Workbook containing the graph contents. /// </param> //************************************************************************* public WordMetricUserSettingsDialog ( WordMetricUserSettings wordMetricUserSettings, Microsoft.Office.Interop.Excel.Workbook workbook ) { Debug.Assert(wordMetricUserSettings != null); Debug.Assert(workbook != null); m_oWordMetricUserSettings = wordMetricUserSettings; InitializeComponent(); // Populate the column name ComboBoxes with column names from the // workbook. ListObject oTable; if (ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Edges, TableNames.Edges, out oTable)) { cbxEdgeColumnName.PopulateWithTableColumnNames(oTable); } if (ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Vertices, TableNames.Vertices, out oTable)) { cbxVertexColumnName.PopulateWithTableColumnNames(oTable); } // Instantiate an object that saves and retrieves the user settings for // this dialog. Note that the object automatically saves the settings // when the form closes. m_oWordMetricUserSettingsDialogUserSettings = new WordMetricUserSettingsDialogUserSettings(this); DoDataExchange(false); AssertValid(); }
TryGetVertexTableAndVisibleColumnData ( Workbook oWorkbook, out ListObject oVertexTable, out Range oVisibleNameColumnData, out Range oVisibleSubgraphImageColumnData ) { Debug.Assert(oWorkbook != null); oVertexTable = null; oVisibleNameColumnData = null; oVisibleSubgraphImageColumnData = null; Range oNameColumnData, oSubgraphImageColumnData; return( // If the vertex table, the name column data, or the image column // data aren't available, nothing can be done. ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable) && ExcelTableUtil.TryGetTableColumnData(oVertexTable, VertexTableColumnNames.VertexName, out oNameColumnData) && TryGetSubgraphImageColumnData(oVertexTable, out oSubgraphImageColumnData) // Reduce the name and image column data to visible areas only. && ExcelUtil.TryGetVisibleRange(oNameColumnData, out oVisibleNameColumnData) && ExcelUtil.TryGetVisibleRange(oSubgraphImageColumnData, out oVisibleSubgraphImageColumnData) ); }
TryGetEdgeTable ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, out ListObject oEdgeTable ) { Debug.Assert(oWorkbook != null); AssertValid(); // The DataBodyRange test catches the odd case where the user deletes // the first data row of the table. It looks like the row is still // there, but it's not. Continuing with a null DataBodyRange can cause // a variety of problems. return( ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Edges, TableNames.Edges, out oEdgeTable) && oEdgeTable.DataBodyRange != null ); }
//************************************************************************* // Constructor: MergeDuplicateEdgesUserSettingsDialog() // /// <summary> /// Initializes a new instance of the <see /// cref="MergeDuplicateEdgesUserSettingsDialog" /> class. /// </summary> /// /// <param name="mode"> /// Indicates the mode in which the dialog is being used. /// </param> /// /// <param name="mergeDuplicateEdgesUserSettings"> /// The object being edited. /// </param> /// /// <param name="workbook"> /// Workbook containing the graph contents. /// </param> //************************************************************************* public MergeDuplicateEdgesUserSettingsDialog ( DialogMode mode, MergeDuplicateEdgesUserSettings mergeDuplicateEdgesUserSettings, Microsoft.Office.Interop.Excel.Workbook workbook ) { Debug.Assert(mergeDuplicateEdgesUserSettings != null); mergeDuplicateEdgesUserSettings.AssertValid(); Debug.Assert(workbook != null); m_oMergeDuplicateEdgesUserSettings = mergeDuplicateEdgesUserSettings; // Instantiate an object that saves and retrieves the position of this // dialog. Note that the object automatically saves the settings when // the form closes. m_oMergeDuplicateEdgesUserSettingsDialogUserSettings = new MergeDuplicateEdgesUserSettingsDialogUserSettings(this); InitializeComponent(); if (mode == DialogMode.EditOnly) { this.Text += " Options"; } ListObject oEdgeTable; if (ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Edges, TableNames.Edges, out oEdgeTable)) { cbxThirdColumnNameForDuplicateDetection .PopulateWithTableColumnNames(oEdgeTable); } DoDataExchange(false); AssertValid(); }
AutoFillTable ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, AutoFillUserSettings oAutoFillUserSettings, AutoFillWorkbookResults oAutoFillWorkbookResults, String sWorksheetName, String sTableName, AutoFillTableMethod oAutoFillTableMethod ) { Debug.Assert(oWorkbook != null); Debug.Assert(oAutoFillUserSettings != null); Debug.Assert(oAutoFillWorkbookResults != null); Debug.Assert(!String.IsNullOrEmpty(sWorksheetName)); Debug.Assert(!String.IsNullOrEmpty(sTableName)); Debug.Assert(oAutoFillTableMethod != null); ListObject oTable; ExcelHiddenColumns oHiddenColumns; if (ExcelTableUtil.TryGetTable(oWorkbook, sWorksheetName, sTableName, out oTable)) { // The TableColumnMapper class that does the actual autofilling // fills only visible cells. Temporarily show all hidden columns // in the table. oHiddenColumns = ExcelColumnHider.ShowHiddenColumns(oTable); try { oAutoFillTableMethod(oTable, oAutoFillUserSettings, oAutoFillWorkbookResults); } finally { ExcelColumnHider.RestoreHiddenColumns(oTable, oHiddenColumns); } } }
TryGetColumnGroupTable ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, ColumnGroups eColumnGroup, out ListObject oTable ) { Debug.Assert(oWorkbook != null); oTable = null; String sColumnGroup = eColumnGroup.ToString(); if (sColumnGroup.StartsWith("Edge")) { return(ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Edges, TableNames.Edges, out oTable)); } if (sColumnGroup.StartsWith("Vertex")) { return(ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out oTable)); } if (sColumnGroup.StartsWith("GroupEdge")) { return(ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.GroupEdgeMetrics, TableNames.GroupEdgeMetrics, out oTable)); } if (sColumnGroup.StartsWith("Group")) { return(ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Groups, TableNames.Groups, out oTable)); } Debug.Assert(false); return(false); }
GetVertexIDsInGroup ( Microsoft.Office.Interop.Excel.Workbook workbook, String groupName ) { Debug.Assert(workbook != null); Debug.Assert(!String.IsNullOrEmpty(groupName)); ListObject oGroupVertexTable; LinkedList <Int32> oVertexIDsInGroup = new LinkedList <Int32>(); if (ExcelTableUtil.TryGetTable(workbook, WorksheetNames.GroupVertices, TableNames.GroupVertices, out oGroupVertexTable)) { foreach (ExcelTableReader.ExcelTableRow oRow in (new ExcelTableReader(oGroupVertexTable)).GetRows()) { String sGroupName; Int32 iVertexID; if ( oRow.TryGetNonEmptyStringFromCell( GroupVertexTableColumnNames.GroupName, out sGroupName) && sGroupName == groupName && oRow.TryGetInt32FromCell( GroupVertexTableColumnNames.VertexID, out iVertexID) ) { oVertexIDsInGroup.AddLast(iVertexID); } } } return(oVertexIDsInGroup); }
TryCollapseOrExpandAllGroups ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, Boolean bCollapse ) { Debug.Assert(oWorkbook != null); // Store the new collapsed state in the group table. ListObject oGroupTable; if (ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Groups, TableNames.Groups, out oGroupTable)) { ExcelTableUtil.SetVisibleTableColumnData(oGroupTable, GroupTableColumnNames.Collapsed, (new BooleanConverter()).GraphToWorkbook(bCollapse)); return(true); } return(false); }
ReadWorksheet ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext, IGraph graph ) { Debug.Assert(workbook != null); Debug.Assert(readWorkbookContext != null); Debug.Assert(graph != null); AssertValid(); // Attempt to get the optional table that contains vertex data. ListObject oVertexTable; if (ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable)) { // The code that reads the table can handle hidden rows, but not // hidden columns. Temporarily show all hidden columns in the // table. ExcelHiddenColumns oHiddenColumns = ExcelColumnHider.ShowHiddenColumns(oVertexTable); Boolean bLayoutAndZOrderSet; try { ReadVertexTable(oVertexTable, readWorkbookContext, graph, out bLayoutAndZOrderSet); RemoveUnwantedIsolates(readWorkbookContext, graph); } finally { ExcelColumnHider.RestoreHiddenColumns(oVertexTable, oHiddenColumns); } if (bLayoutAndZOrderSet) { // The layout and z-orders were specified for at least one // vertex. The ByMetadataVertexSorter used by // SortableLayoutBase and GraphDrawer requires that if an order // is set on one vertex, it must be set on all vertices. This // isn't required in the Excel template, though, so set a // default order for each vertex that doesn't already specify // one. SetUnspecifiedVertexLayoutAndZOrders(graph); // The layout and z-orders are ignored unless this key is added // to the graph. graph.SetValue( ReservedMetadataKeys.SortableLayoutAndZOrderSet, null); } } }
Open() { Debug.Assert(m_oDynamicFilterSettingsTable == null); AssertValid(); // Get the table that contains the dynamic filter settings. if (!ExcelTableUtil.TryGetTable(m_oWorkbook, WorksheetNames.Miscellaneous, TableNames.DynamicFilterSettings, out m_oDynamicFilterSettingsTable)) { OnWorkbookFormatError(String.Format( "A table that is required to use this feature is missing." + "\r\n\r\n{0}" , ErrorUtil.GetTemplateMessage() )); } foreach (ExcelTableReader.ExcelTableRow oRow in (new ExcelTableReader(m_oDynamicFilterSettingsTable)).GetRows()) { String sTableName, sColumnName; Double dSelectedMinimum, dSelectedMaximum; if ( oRow.TryGetNonEmptyStringFromCell( DynamicFilterSettingsTableColumnNames.TableName, out sTableName) && oRow.TryGetNonEmptyStringFromCell( DynamicFilterSettingsTableColumnNames.ColumnName, out sColumnName) && oRow.TryGetDoubleFromCell( DynamicFilterSettingsTableColumnNames.SelectedMinimum, out dSelectedMinimum) && oRow.TryGetDoubleFromCell( DynamicFilterSettingsTableColumnNames.SelectedMaximum, out dSelectedMaximum) ) { // Create a SettingsForOneFilter object for each filter and // store it in a dictionary. SettingsForOneFilter oSettingsForOneFilter = new SettingsForOneFilter(); oSettingsForOneFilter.SelectedMinimum = (Decimal)dSelectedMinimum; oSettingsForOneFilter.SelectedMaximum = (Decimal)dSelectedMaximum; oSettingsForOneFilter.SelectedMinimumAddress = ExcelUtil.GetRangeAddressAbsolute( oRow.GetRangeForCell( DynamicFilterSettingsTableColumnNames.SelectedMinimum) ); oSettingsForOneFilter.SelectedMaximumAddress = ExcelUtil.GetRangeAddressAbsolute( oRow.GetRangeForCell( DynamicFilterSettingsTableColumnNames.SelectedMaximum) ); m_oDynamicFilterSettingsDictionary.Add( GetDictionaryKey(sTableName, sColumnName), oSettingsForOneFilter); } } }
TryGetTable ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, GraphMetricColumn oGraphMetricColumn, out ListObject oTable ) { Debug.Assert(oWorkbook != null); Debug.Assert(oGraphMetricColumn != null); AssertValid(); oTable = null; String sTableName = oGraphMetricColumn.TableName; if (!ExcelTableUtil.TryGetTable(oWorkbook, oGraphMetricColumn.WorksheetName, sTableName, out oTable)) { // The table couldn't be found. It has to be created. if (sTableName == TableNames.GroupEdgeMetrics) { if (!TryCreateGroupEdgeTable(oWorkbook, out oTable)) { return(false); } } else if (sTableName.StartsWith(TableNames.TopNByMetricsRoot)) { if (!TryCreateStackedTable(oWorkbook, WorksheetNames.TopNByMetrics, oGraphMetricColumn, out oTable)) { return(false); } } else if (sTableName.StartsWith( TableNames.TwitterSearchNetworkTopItemsRoot)) { if (!TryCreateStackedTable(oWorkbook, WorksheetNames.TwitterSearchNetworkTopItems, oGraphMetricColumn, out oTable)) { return(false); } } else if (sTableName == TableNames.WordCounts) { // There is actually just one table on the words worksheet, not // a stack of tables, but that's okay. TryCreateStackedTable() // doesn't care whether it will be called again with another // table for the worksheet. if (!TryCreateStackedTable(oWorkbook, WorksheetNames.WordCounts, oGraphMetricColumn, out oTable)) { return(false); } } else if (sTableName == TableNames.WordPairCounts) { if (!TryCreateStackedTable(oWorkbook, WorksheetNames.WordPairCounts, oGraphMetricColumn, out oTable)) { return(false); } } else { return(false); } } return(true); }
CopyRowsToNewNodeXLWorkbook ( ListObject oSourceTable, Range oSourceTableRangeToCopy, Workbook oNewNodeXLWorkbook ) { Debug.Assert(oSourceTable != null); Debug.Assert(oSourceTableRangeToCopy != null); Debug.Assert(oNewNodeXLWorkbook != null); AssertValid(); String sTableName = oSourceTable.Name; Debug.Assert(oSourceTable.Parent is Worksheet); String sWorksheetName = ((Worksheet)oSourceTable.Parent).Name; ListObject oNewTable; if (!ExcelTableUtil.TryGetTable(oNewNodeXLWorkbook, sWorksheetName, sTableName, out oNewTable)) { throw new ExportWorkbookException( "A table is missing in the new workbook." ); } foreach (ListColumn oSourceColumn in oSourceTable.ListColumns) { // Add the column to the new table if it doesn't already exist. Range oNewColumnData = GetOrAddTableColumn(oSourceColumn, oNewTable); Range oSourceColumnData = oSourceColumn.DataBodyRange; Debug.Assert(oSourceColumnData != null); Debug.Assert(oNewColumnData != null); // Read the source column. Object [,] aoSourceValues = ExcelUtil.GetRangeValues(oSourceColumnData); // Create a collection to hold the specified column cells to be // copied. LinkedList <Object> oNewValues = new LinkedList <Object>(); // Get the cells to be copied. foreach (Range oSourceTableRangeArea in oSourceTableRangeToCopy.Areas) { Int32 iFirstRowOneBased = oSourceTableRangeArea.Row - oSourceColumnData.Row + 1; Int32 iLastRowOneBased = iFirstRowOneBased + oSourceTableRangeArea.Rows.Count - 1; for (Int32 iRowOneBased = iFirstRowOneBased; iRowOneBased <= iLastRowOneBased; iRowOneBased++) { oNewValues.AddLast(aoSourceValues[iRowOneBased, 1]); } } // Copy the cells to the new workbook. Int32 iValuesToCopy = oNewValues.Count; if (iValuesToCopy > 0) { Object [,] aoNewValues = ExcelUtil.GetSingleColumn2DArray(iValuesToCopy); Int32 iNewValueIndexOneBased = 1; foreach (Object oNewValue in oNewValues) { aoNewValues[iNewValueIndexOneBased, 1] = oNewValue; iNewValueIndexOneBased++; } // Note that a cell may contain a string that starts with an // equal sign, which would cause Excel to throw an exception. // Remove any equal signs before copying the cells. ExcelUtil.SetRangeValues(oNewColumnData, aoNewValues, true); } } }
ImportGraph ( IGraph sourceGraph, String [] edgeAttributes, String [] vertexAttributes, Boolean clearTablesFirst, Microsoft.Office.Interop.Excel.Workbook destinationNodeXLWorkbook ) { Debug.Assert(sourceGraph != null); Debug.Assert(destinationNodeXLWorkbook != null); if (clearTablesFirst) { NodeXLWorkbookUtil.ClearAllNodeXLTables(destinationNodeXLWorkbook); } // Get the required table that contains edge data. GetEdgeTable() // throws an exception if the table is missing. EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader(); ListObject oEdgeTable = oEdgeWorksheetReader.GetEdgeTable(destinationNodeXLWorkbook); // Get the required columns. Range oVertex1NameColumnData = null; Range oVertex2NameColumnData = null; if ( !ExcelTableUtil.TryGetTableColumnData(oEdgeTable, EdgeTableColumnNames.Vertex1Name, out oVertex1NameColumnData) || !ExcelTableUtil.TryGetTableColumnData(oEdgeTable, EdgeTableColumnNames.Vertex2Name, out oVertex2NameColumnData) ) { ErrorUtil.OnMissingColumn(); } // Import the edges and their attributes into the workbook. ImportEdges(sourceGraph, edgeAttributes, oEdgeTable, oVertex1NameColumnData, oVertex2NameColumnData, !clearTablesFirst); // Populate the vertex worksheet with the name of each unique vertex in // the edge worksheet. (new VertexWorksheetPopulator()).PopulateVertexWorksheet( destinationNodeXLWorkbook, false); // Get the table that contains vertex data. ListObject oVertexTable; Range oVertexNameColumnData = null; Range oVisibilityColumnData = null; if ( !ExcelTableUtil.TryGetTable(destinationNodeXLWorkbook, WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable) || !ExcelTableUtil.TryGetTableColumnData(oVertexTable, VertexTableColumnNames.VertexName, out oVertexNameColumnData) || !ExcelTableUtil.TryGetTableColumnData(oVertexTable, CommonTableColumnNames.Visibility, out oVisibilityColumnData) ) { ErrorUtil.OnMissingColumn(); } // Import isolated vertices and the attributes for all the graph's // vertices. ImportVertices(sourceGraph, vertexAttributes, oVertexTable, oVertexNameColumnData, oVisibilityColumnData); }
GetDynamicFilterParameters ( Microsoft.Office.Interop.Excel.Workbook workbook, String worksheetName, String tableName ) { Debug.Assert(workbook != null); Debug.Assert(!String.IsNullOrEmpty(worksheetName)); Debug.Assert(!String.IsNullOrEmpty(tableName)); #if false // For testing. return(GetRandomDynamicFilterParameters(tableName)); #endif LinkedList <DynamicFilterParameters> oDynamicFilterParameters = new LinkedList <DynamicFilterParameters>(); // Get the specified table and loop through its columns. ListObject oTable; if (ExcelTableUtil.TryGetTable(workbook, worksheetName, tableName, out oTable)) { Application oApplication = workbook.Application; foreach (ListColumn oColumn in oTable.ListColumns) { if (ColumnShouldBeExcluded(oColumn)) { continue; } ExcelColumnFormat eColumnFormat = ExcelTableUtil.GetTableColumnFormat(oColumn); switch (eColumnFormat) { case ExcelColumnFormat.Number: case ExcelColumnFormat.Date: case ExcelColumnFormat.Time: case ExcelColumnFormat.DateAndTime: // Get the range of values in the column. Double dMinimumCellValue, dMaximumCellValue; if (TryGetNumericRange(worksheetName, oColumn, out dMinimumCellValue, out dMaximumCellValue)) { if (eColumnFormat == ExcelColumnFormat.Number) { oDynamicFilterParameters.AddLast( new NumericFilterParameters(oColumn.Name, dMinimumCellValue, dMaximumCellValue, ExcelTableUtil.GetTableColumnDecimalPlaces( oColumn)) ); } else { oDynamicFilterParameters.AddLast( new DateTimeFilterParameters(oColumn.Name, dMinimumCellValue, dMaximumCellValue, eColumnFormat)); } } break; case ExcelColumnFormat.Other: // Skip the column. break; default: Debug.Assert(false); break; } } } return(oDynamicFilterParameters); }
TryAddSelectedVerticesToGroup ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, Sheet2 oVertexWorksheet ) { Debug.Assert(oWorkbook != null); Debug.Assert(oVertexWorksheet != null); AddSelectedVerticesToGroupDialog oAddSelectedVerticesToGroupDialog = new AddSelectedVerticesToGroupDialog(oWorkbook); if (oAddSelectedVerticesToGroupDialog.ShowDialog() != DialogResult.OK) { return(false); } // First, remove the selected vertices from any groups they belong to. ListObject oGroupTable, oGroupVertexTable; ICollection <String> oSelectedVertexNames; if ( !TryRemoveSelectedVerticesFromGroups(oWorkbook, oVertexWorksheet, out oSelectedVertexNames) || !ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Groups, TableNames.Groups, out oGroupTable) || !ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.GroupVertices, TableNames.GroupVertices, out oGroupVertexTable) ) { return(false); } String sGroupName = oAddSelectedVerticesToGroupDialog.GroupName; if (oAddSelectedVerticesToGroupDialog.IsNewGroup) { // Add the new group to the group table. // // Note that the group table (and the group-vertex table, below) // needs to be activated before being written to. If this isn't // done, the formula written to the group-vertex table below also // mysteriously appears in the vertex table on the vertex // worksheet. // // It's up to the caller to use the ExcelActiveWorksheetRestorer // class to save and restore the active worksheet. ExcelUtil.ActivateWorksheet(oGroupTable); ExcelTableUtil.AddTableRow(oGroupTable, GroupTableColumnNames.Name, sGroupName ); SetVertexAttributesForAllGroups(oGroupTable); } // Add the selected vertices to the group-vertex table. ExcelUtil.ActivateWorksheet(oGroupVertexTable); foreach (String sSelectedVertexName in oSelectedVertexNames) { ExcelTableUtil.AddTableRow(oGroupVertexTable, GroupVertexTableColumnNames.GroupName, sGroupName, GroupVertexTableColumnNames.VertexName, sSelectedVertexName, GroupVertexTableColumnNames.VertexID, GetExcelFormulaForVertexID() ); } return(true); }
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"; } } } }
AddColumnPair ( Microsoft.Office.Interop.Excel.Workbook workbook, String worksheetName, String tableName, String column1NameBase, Double column1WidthChars, String column2NameBase, Double column2WidthChars ) { Debug.Assert(workbook != null); Debug.Assert(!String.IsNullOrEmpty(worksheetName)); Debug.Assert(!String.IsNullOrEmpty(tableName)); Debug.Assert(!String.IsNullOrEmpty(column1NameBase)); Debug.Assert(column1WidthChars == ExcelTableUtil.AutoColumnWidth || column1WidthChars >= 0); Debug.Assert(!String.IsNullOrEmpty(column2NameBase)); Debug.Assert(column2WidthChars == ExcelTableUtil.AutoColumnWidth || column2WidthChars >= 0); AssertValid(); ListObject oTable; if (!ExcelTableUtil.TryGetTable(workbook, worksheetName, tableName, out oTable)) { throw new WorkbookFormatException(String.Format( "To use this feature, there must be a worksheet named \"{0}\"" + " that contains a table named \"{1}\"." + "\r\n\r\n" + "{2}" , worksheetName, tableName, ErrorUtil.GetTemplateMessage() )); } Int32 iMaximumAppendedNumber = Math.Max( GetMaximumAppendedNumber(oTable, column1NameBase), GetMaximumAppendedNumber(oTable, column2NameBase) ); if (iMaximumAppendedNumber != 0) { String sStringToAppend = " " + (iMaximumAppendedNumber + 1).ToString(); column1NameBase += sStringToAppend; column2NameBase += sStringToAppend; } ListColumn oListColumn1, oListColumn2; if ( !ExcelTableUtil.TryAddTableColumn(oTable, column1NameBase, column1WidthChars, null, out oListColumn1) || !ExcelTableUtil.TryAddTableColumn(oTable, column2NameBase, column2WidthChars, null, out oListColumn2) ) { FormUtil.ShowWarning("The columns weren't added."); } ExcelUtil.ActivateWorksheet(oTable); }