ReadWorkbook ( Microsoft.Office.Interop.Excel.Workbook oWorkbook ) { Debug.Assert(oWorkbook != null); WorkbookReader oWorkbookReader = new WorkbookReader(); ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); // Read all columns in the vertex worksheet and store the cell values // as metadata on the graph's vertices. oReadWorkbookContext.ReadAllEdgeAndVertexColumns = true; // GroupsToGraphMetricColumnsConverter.Convert() copies the ID column // on the vertex worksheet to the group-vertex worksheet, so make sure // that the vertex ID column is filled in. oReadWorkbookContext.FillIDColumns = true; return(oWorkbookReader.ReadWorkbook(oWorkbook, oReadWorkbookContext)); }
ReadWorkbook ( Microsoft.Office.Interop.Excel.Workbook oWorkbook, GraphMetricUserSettings oGraphMetricUserSettings ) { Debug.Assert(oWorkbook != null); Debug.Assert(oGraphMetricUserSettings != null); AssertValid(); ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.FillIDColumns = true; oReadWorkbookContext.PopulateVertexWorksheet = true; if ((oGraphMetricUserSettings.GraphMetricsToCalculate & GraphMetrics.GroupMetrics) != 0) { oReadWorkbookContext.ReadGroups = true; oReadWorkbookContext.SaveGroupVertices = true; } WorkbookReader oWorkbookReader = new WorkbookReader(); return(oWorkbookReader.ReadWorkbook( oWorkbook, oReadWorkbookContext)); }
ReadWorksheet ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext, IGraph graph ) { Debug.Assert(workbook != null); Debug.Assert(readWorkbookContext != null); Debug.Assert(graph != null); AssertValid(); // Get the required table that contains edge data, then add the edges // and vertices in the table to the graph. ListObject oEdgeTable = GetEdgeTable(workbook); // 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(oEdgeTable); try { ReadEdgeTable(oEdgeTable, readWorkbookContext, graph); } finally { ExcelColumnHider.RestoreHiddenColumns(oEdgeTable, oHiddenColumns); } }
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 ( !ExcelUtil.TryGetTableColumn(oGroupTable, GroupTableColumnNames.Name, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupTable, GroupTableColumnNames.VertexColor, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupTable, GroupTableColumnNames.VertexShape, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupVertexTable, GroupVertexTableColumnNames.GroupName, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupVertexTable, GroupVertexTableColumnNames.VertexName, out oColumn) ) { return; } // Create a dictionary from the group table. The key is the group name // and the value is a GroupInformation object for the group. Dictionary <String, GroupInformation> oGroupNameDictionary = ReadGroupTable(oGroupTable, oReadWorkbookContext); // Read the group vertex table and set the color and shape of each // group vertex in the graph. ReadGroupVertexTable(oGroupVertexTable, oReadWorkbookContext, oGroupNameDictionary, oGraph); // Save the group information on the graph. Debug.Assert(oGroupNameDictionary.Values is ICollection <GroupInformation>); oGraph.SetValue(ReservedMetadataKeys.GroupInformation, oGroupNameDictionary.Values); }
ReadWorkbook ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext ) { Debug.Assert(readWorkbookContext != null); Debug.Assert(workbook != null); AssertValid(); IGraph oGraph = null; // Turn off screen updating, for two reasons: // // 1. Reading the workbook can involve writing to edge and vertex ID // columns, which can be slow when updating is turned on. // // 2. Any hidden columns get temporarily shown as each worksheet is // read. Application oApplication = workbook.Application; Boolean bOldScreenUpdating = oApplication.ScreenUpdating; oApplication.ScreenUpdating = false; try { oGraph = ReadWorkbookInternal(workbook, readWorkbookContext); } catch (WorkbookFormatException oWorkbookFormatException) { Range oRangeToSelect = oWorkbookFormatException.RangeToSelect; if (oRangeToSelect != null) { // The user may have hidden the column group containing the // error. Make sure the column is visible. oRangeToSelect.EntireColumn.Hidden = false; } throw oWorkbookFormatException; } finally { oApplication.ScreenUpdating = bOldScreenUpdating; } return(oGraph); }
ReadClusterTables ( ListObject oClusterTable, ListObject oClusterVertexTable, ReadWorkbookContext oReadWorkbookContext, IGraph oGraph ) { Debug.Assert(oClusterTable != null); Debug.Assert(oClusterVertexTable != null); Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oGraph != null); AssertValid(); // If a required column is missing, do nothing. ListColumn oColumn; if ( !ExcelUtil.TryGetTableColumn(oClusterTable, ClusterTableColumnNames.Name, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterTable, ClusterTableColumnNames.VertexColor, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterTable, ClusterTableColumnNames.VertexShape, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterVertexTable, ClusterVertexTableColumnNames.ClusterName, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterVertexTable, ClusterVertexTableColumnNames.VertexName, out oColumn) ) { return; } // Create a dictionary from the cluster table. The key is the cluster // name and the value is a ClusterInformation object for the cluster. Dictionary <String, ClusterInformation> oClusterNameDictionary = ReadClusterTable(oClusterTable, oReadWorkbookContext); // Read the cluster vertex table and add the cluster vertex information // to the graph. ReadClusterVertexTable(oClusterVertexTable, oClusterNameDictionary, oReadWorkbookContext.VertexNameDictionary, oGraph); }
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 ( ExcelUtil.TryGetTable(workbook, WorksheetNames.Groups, TableNames.Groups, out oGroupTable) && ExcelUtil.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); } } }
ReadWorkbook ( Microsoft.Office.Interop.Excel.Workbook oWorkbook ) { Debug.Assert(oWorkbook != null); AssertValid(); ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); WorkbookReader oWorkbookReader = new WorkbookReader(); // Convert the workbook contents to a Graph object. return(oWorkbookReader.ReadWorkbook( oWorkbook, oReadWorkbookContext)); }
ReadWorksheet ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext, IGraph graph ) { Debug.Assert(workbook != null); Debug.Assert(readWorkbookContext != null); Debug.Assert(graph != null); AssertValid(); Nullable <Color> oBackColor = this.BackColor; if (oBackColor.HasValue) { graph.SetValue(ReservedMetadataKeys.GraphBackColor, oBackColor.Value); // (Note that if there is no per-workbook background color, the // GraphDrawer.BackColor property will be used instead.) } String sBackgroundImageUri = this.BackgroundImageUri; if (!String.IsNullOrEmpty(sBackgroundImageUri)) { System.Windows.Media.ImageSource oImage = (new WpfImageUtil()). GetImageSynchronousIgnoreDpi(sBackgroundImageUri); graph.SetValue(ReservedMetadataKeys.GraphBackgroundImage, oImage); // (Note that if there is no per-workbook background image, no // background image will be drawn.) } }
//************************************************************************* // Method: ReadClusterTables() // /// <summary> /// Reads the cluster tables and add the contents to a graph. /// </summary> /// /// <param name="oClusterTable"> /// Table that contains the cluster data. /// </param> /// /// <param name="oClusterVertexTable"> /// Table that contains the cluster vertex data. /// </param> /// /// <param name="oReadWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <param name="oGraph"> /// Graph to add cluster data to. /// </param> //************************************************************************* protected void ReadClusterTables( ListObject oClusterTable, ListObject oClusterVertexTable, ReadWorkbookContext oReadWorkbookContext, IGraph oGraph ) { Debug.Assert(oClusterTable != null); Debug.Assert(oClusterVertexTable != null); Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oGraph != null); AssertValid(); // If a required column is missing, do nothing. ListColumn oColumn; if ( !ExcelUtil.TryGetTableColumn(oClusterTable, ClusterTableColumnNames.Name, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterTable, ClusterTableColumnNames.VertexColor, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterTable, ClusterTableColumnNames.VertexShape, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterVertexTable, ClusterVertexTableColumnNames.ClusterName, out oColumn) || !ExcelUtil.TryGetTableColumn(oClusterVertexTable, ClusterVertexTableColumnNames.VertexName, out oColumn) ) { return; } // Create a dictionary from the cluster table. The key is the cluster // name and the value is a ClusterInformation object for the cluster. Dictionary<String, ClusterInformation> oClusterNameDictionary = ReadClusterTable(oClusterTable, oReadWorkbookContext); // Read the cluster vertex table and add the cluster vertex information // to the graph. ReadClusterVertexTable(oClusterVertexTable, oClusterNameDictionary, oReadWorkbookContext.VertexNameDictionary, oGraph); }
ReadClusterTable ( ListObject oClusterTable, ReadWorkbookContext oReadWorkbookContext ) { Debug.Assert(oClusterTable != null); Debug.Assert(oReadWorkbookContext != null); AssertValid(); Dictionary <String, ClusterInformation> oClusterNameDictionary = new Dictionary <String, ClusterInformation>(); ColorConverter2 oColorConverter2 = oReadWorkbookContext.ColorConverter2; ExcelTableReader oExcelTableReader = new ExcelTableReader(oClusterTable); foreach (ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows()) { // Get the cluster information. String sClusterName; Color oVertexColor; VertexShape eVertexShape; if ( !oRow.TryGetNonEmptyStringFromCell( ClusterTableColumnNames.Name, out sClusterName) || !TryGetColor(oRow, ClusterTableColumnNames.VertexColor, oColorConverter2, out oVertexColor) || !TryGetVertexShape(oRow, ClusterTableColumnNames.VertexShape, out eVertexShape) ) { continue; } // Add the cluster information to the dictionary. ClusterInformation oClusterInformation = new ClusterInformation(); oClusterInformation.VertexColor = oVertexColor; oClusterInformation.VertexShape = eVertexShape; try { oClusterNameDictionary.Add( sClusterName, oClusterInformation); } catch (ArgumentException) { Range oInvalidCell = oRow.GetRangeForCell( ClusterTableColumnNames.Name); OnWorkbookFormatError(String.Format( "The cell {0} contains a duplicate cluster name. There" + " can't be two rows with the same cluster name." , ExcelUtil.GetRangeAddress(oInvalidCell) ), oInvalidCell ); } } return(oClusterNameDictionary); }
//************************************************************************* // Method: ReadClusterTable() // /// <summary> /// Reads the cluster table. /// </summary> /// /// <param name="oClusterTable"> /// The cluster table. /// </param> /// /// <param name="oReadWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <returns> /// A dictionary. The key is the cluster name and the value is a /// ClusterInformation object for the cluster. /// </returns> //************************************************************************* protected Dictionary<String, ClusterInformation> ReadClusterTable( ListObject oClusterTable, ReadWorkbookContext oReadWorkbookContext ) { Debug.Assert(oClusterTable != null); Debug.Assert(oReadWorkbookContext != null); AssertValid(); Dictionary<String, ClusterInformation> oClusterNameDictionary = new Dictionary<String, ClusterInformation>(); ColorConverter2 oColorConverter2 = oReadWorkbookContext.ColorConverter2; ExcelTableReader oExcelTableReader = new ExcelTableReader(oClusterTable); foreach ( ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows() ) { // Get the cluster information. String sClusterName; Color oVertexColor; VertexShape eVertexShape; if ( !oRow.TryGetNonEmptyStringFromCell( ClusterTableColumnNames.Name, out sClusterName) || !TryGetColor(oRow, ClusterTableColumnNames.VertexColor, oColorConverter2, out oVertexColor) || !TryGetVertexShape(oRow, ClusterTableColumnNames.VertexShape, out eVertexShape) ) { continue; } // Add the cluster information to the dictionary. ClusterInformation oClusterInformation = new ClusterInformation(); oClusterInformation.VertexColor = oVertexColor; oClusterInformation.VertexShape = eVertexShape; try { oClusterNameDictionary.Add( sClusterName, oClusterInformation); } catch (ArgumentException) { Range oInvalidCell = oRow.GetRangeForCell( ClusterTableColumnNames.Name); OnWorkbookFormatError( String.Format( "The cell {0} contains a duplicate cluster name. There" + " can't be two rows with the same cluster name." , ExcelUtil.GetRangeAddress(oInvalidCell) ), oInvalidCell ); } } return (oClusterNameDictionary); }
public void SetUp() { m_oReadWorkbookContext = new ReadWorkbookContext(); }
//************************************************************************* // Method: ReadWorkbook() // /// <summary> /// Creates a NodeXL graph from the contents of an Excel workbook. /// </summary> /// /// <param name="workbook"> /// Workbook containing the graph data. /// </param> /// /// <param name="readWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <returns> /// A new graph. /// </returns> /// /// <remarks> /// If <paramref name="workbook" /> contains valid graph data, a new <see /// cref="IGraph" /> is created from the workbook contents and returned. /// Otherwise, a <see cref="WorkbookFormatException" /> is thrown. /// </remarks> //************************************************************************* public IGraph ReadWorkbook( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext ) { Debug.Assert(readWorkbookContext != null); Debug.Assert(workbook != null); AssertValid(); IGraph oGraph = null; // Turn off screen updating, for two reasons: // // 1. Reading the workbook can involve writing to edge and vertex ID // columns, which can be slow when updating is turned on. // // 2. Any hidden columns get temporarily shown as each worksheet is // read. Application oApplication = workbook.Application; Boolean bOldScreenUpdating = oApplication.ScreenUpdating; oApplication.ScreenUpdating = false; try { oGraph = ReadWorkbookInternal(workbook, readWorkbookContext); } catch (WorkbookFormatException oWorkbookFormatException) { Range oRangeToSelect = oWorkbookFormatException.RangeToSelect; if (oRangeToSelect != null) { // The user may have hidden the column group containing the // error. Make sure the column is visible. oRangeToSelect.EntireColumn.Hidden = false; } throw oWorkbookFormatException; } finally { oApplication.ScreenUpdating = bOldScreenUpdating; } return (oGraph); }
ReadGroupVertexTable ( ListObject oGroupVertexTable, ReadWorkbookContext oReadWorkbookContext, Dictionary <String, GroupInformation> oGroupNameDictionary, IGraph oGraph ) { Debug.Assert(oGroupVertexTable != null); Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oGroupNameDictionary != null); Debug.Assert(oGraph != null); AssertValid(); Dictionary <String, IVertex> oVertexNameDictionary = oReadWorkbookContext.VertexNameDictionary; ExcelTableReader oExcelTableReader = new ExcelTableReader(oGroupVertexTable); foreach (ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows()) { // Get the group vertex information from the row. String sGroupName, sVertexName; if ( !oRow.TryGetNonEmptyStringFromCell( GroupVertexTableColumnNames.GroupName, out sGroupName) || !oRow.TryGetNonEmptyStringFromCell( GroupVertexTableColumnNames.VertexName, out sVertexName) ) { continue; } // Get the group information for the vertex and store the group // information in the vertex. GroupInformation oGroupInformation; IVertex oVertex; if ( !oGroupNameDictionary.TryGetValue(sGroupName, out oGroupInformation) || !oVertexNameDictionary.TryGetValue(sVertexName, out oVertex) ) { continue; } oVertex.SetValue(ReservedMetadataKeys.PerColor, oGroupInformation.VertexColor); oVertex.SetValue(ReservedMetadataKeys.PerVertexShape, oGroupInformation.VertexShape); if (oReadWorkbookContext.SaveGroupVertices) { Debug.Assert(oGroupInformation.Vertices != null); oGroupInformation.Vertices.Add(oVertex); } } }
//************************************************************************* // Method: ReadWorkbook() // /// <summary> /// Transfers data from the workbook to the NodeXLControl and optionally /// lays out the graph. /// </summary> /// /// <param name="bLayOutGraph"> /// true to lay out the graph. /// </param> //************************************************************************* protected void ReadWorkbook( Boolean bLayOutGraph ) { AssertValid(); if (oNodeXLControl.IsLayingOutGraph) { return; } if ( !this.NonEmptyWorkbookRead && this.LayoutIsNull && !ShowLayoutTypeIsNullNotification() ) { return; } // This is in case another open workbook has modified the user // settings. GeneralUserSettings oGeneralUserSettings = new GeneralUserSettings(); ApplyGeneralUserSettings(oGeneralUserSettings); ApplyLayoutUserSettings( new LayoutUserSettings() ); ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.IgnoreVertexLocations = false; oReadWorkbookContext.GraphRectangle = this.GraphRectangle; oReadWorkbookContext.FillIDColumns = true; oReadWorkbookContext.ReadGroups = m_oRibbon.ReadGroups; oReadWorkbookContext.ReadVertexLabels = m_oRibbon.ReadVertexLabels; oReadWorkbookContext.ReadEdgeLabels = m_oRibbon.ReadEdgeLabels; oReadWorkbookContext.ReadVertexImages = true; oReadWorkbookContext.DefaultVertexImageSize = oGeneralUserSettings.VertexImageSize; oReadWorkbookContext.DefaultVertexShape = oGeneralUserSettings.VertexShape; // Populate the vertex worksheet. This isn't strictly necessary, but // it does enable the vertex worksheet to be updated when the user // edits vertex attributes in the NodeXL graph. (If the vertex // worksheet is missing, vertex attributes can still be edited in the // graph; the edits just won't get saved in the workbook.) oReadWorkbookContext.PopulateVertexWorksheet = true; WorkbookReader oWorkbookReader = new WorkbookReader(); m_oEdgeRowIDDictionary = null; m_oVertexRowIDDictionary = null; EnableGraphControls(false); try { // Read the workbook into a Graph object. IGraph oGraph = oWorkbookReader.ReadWorkbook( m_oWorkbook, oReadWorkbookContext); // Save the edge and vertex dictionaries that were created by // WorkbookReader. m_oEdgeRowIDDictionary = oReadWorkbookContext.EdgeRowIDDictionary; m_oVertexRowIDDictionary = oReadWorkbookContext.VertexRowIDDictionary; // Load the NodeXLControl with the resulting graph. oNodeXLControl.Graph = oGraph; // Collapse any groups that are supposed to be collapsed. CollapseOrExpandGroups(GetGroupNamesToCollapse(oGraph), true, false); // Enable tooltips only if tooltips were specified in the workbook. oNodeXLControl.ShowVertexToolTips = oGraph.ContainsKey( ReservedMetadataKeys.ToolTipSet); // If the dynamic filter dialog is open, read the dynamic filter // columns it filled in. if (m_oDynamicFilterDialog != null) { ReadDynamicFilterColumns(false); ReadFilteredAlpha(false); } oNodeXLControl.DrawGraph(bLayOutGraph); PerWorkbookSettings oPerWorkbookSettings = this.PerWorkbookSettings; UpdateAutoFillResultsLegend(oPerWorkbookSettings); UpdateDynamicFiltersLegend(); UpdateAxes(oPerWorkbookSettings); } catch (Exception oException) { // If exceptions aren't caught here, Excel consumes them without // indicating that anything is wrong. This can result in the graph // controls remaining disabled, among other problems. ErrorUtil.OnException(oException); } finally { EnableGraphControls(true); } // Change the button text to indicate that if any of the buttons is // clicked again, the graph will be read again. tsbReadWorkbook.Text = msiContextReadWorkbook.Text = m_oRibbon.ReadWorkbookButtonText = "Refresh Graph"; }
ReadVertexTable ( ListObject oVertexTable, ReadWorkbookContext oReadWorkbookContext, IGraph oGraph, out Boolean bLayoutOrderSet, out Boolean bToolTipSet ) { Debug.Assert(oVertexTable != null); Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oGraph != null); AssertValid(); bLayoutOrderSet = bToolTipSet = false; if (GetTableColumnIndex(oVertexTable, VertexTableColumnNames.VertexName, false) == NoSuchColumn) { // Nothing can be done without vertex names. return; } Boolean bReadAllEdgeAndVertexColumns = oReadWorkbookContext.ReadAllEdgeAndVertexColumns; if (oReadWorkbookContext.FillIDColumns) { FillIDColumn(oVertexTable); } // Get the names of all the column pairs that are used to add custom // menu items to the vertex context menu in the graph. TableColumnAdder oTableColumnAdder = new TableColumnAdder(); ICollection <KeyValuePair <String, String> > aoCustomMenuItemPairNames = oTableColumnAdder.GetColumnPairNames(oVertexTable, VertexTableColumnNames.CustomMenuItemTextBase, VertexTableColumnNames.CustomMenuItemActionBase); IVertexCollection oVertices = oGraph.Vertices; Dictionary <String, IVertex> oVertexNameDictionary = oReadWorkbookContext.VertexNameDictionary; Dictionary <Int32, IIdentityProvider> oEdgeRowIDDictionary = oReadWorkbookContext.EdgeRowIDDictionary; BooleanConverter oBooleanConverter = oReadWorkbookContext.BooleanConverter; VertexVisibilityConverter oVertexVisibilityConverter = new VertexVisibilityConverter(); VertexLabelPositionConverter oVertexLabelPositionConverter = new VertexLabelPositionConverter(); ExcelTableReader oExcelTableReader = new ExcelTableReader(oVertexTable); HashSet <String> oColumnNamesToExclude = new HashSet <String>( new String[] { VertexTableColumnNames.VertexName }); foreach (ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows()) { // Get the name of the vertex. String sVertexName; if (!oRow.TryGetNonEmptyStringFromCell( VertexTableColumnNames.VertexName, out sVertexName)) { continue; } // If the vertex was added to the graph as part of an edge, // retrieve the vertex. IVertex oVertex; if (!oVertexNameDictionary.TryGetValue(sVertexName, out oVertex)) { oVertex = null; } // Assume a default visibility. Visibility eVisibility = Visibility.ShowIfInAnEdge; String sVisibility; if (oRow.TryGetNonEmptyStringFromCell( CommonTableColumnNames.Visibility, out sVisibility)) { if (!oVertexVisibilityConverter.TryWorkbookToGraph( sVisibility, out eVisibility)) { OnInvalidVisibility(oRow); } } switch (eVisibility) { case Visibility.ShowIfInAnEdge: // If the vertex is part of an edge, show it using the // specified vertex attributes. Otherwise, skip the vertex // row. if (oVertex == null) { continue; } break; case Visibility.Skip: // Skip the vertex row and any edge rows that include the // vertex. Do not read them into the graph. if (oVertex != null) { // Remove the vertex and its incident edges from the // graph and dictionaries. foreach (IEdge oIncidentEdge in oVertex.IncidentEdges) { if (oIncidentEdge.Tag is Int32) { oEdgeRowIDDictionary.Remove( (Int32)oIncidentEdge.Tag); } } oVertexNameDictionary.Remove(sVertexName); oVertices.Remove(oVertex); // (The vertex doesn't get added to // ReadWorkbookContext.VertexIDDictionary until after // this switch statement, so it doesn't need to be // removed from that dictionary.) } continue; case Visibility.Hide: // If the vertex is part of an edge, hide it and its // incident edges. Otherwise, skip the vertex row. if (oVertex == null) { continue; } // Hide the vertex and its incident edges. oVertex.SetValue(ReservedMetadataKeys.Visibility, VisibilityKeyValue.Hidden); foreach (IEdge oIncidentEdge in oVertex.IncidentEdges) { oIncidentEdge.SetValue(ReservedMetadataKeys.Visibility, VisibilityKeyValue.Hidden); } break; case Visibility.Show: // Show the vertex using the specified attributes // regardless of whether it is part of an edge. if (oVertex == null) { oVertex = CreateVertex(sVertexName, oVertices, oVertexNameDictionary); } break; default: Debug.Assert(false); break; } Debug.Assert(oVertex != null); // If there is an ID column, add the vertex to the vertex ID // dictionary and set the vertex's Tag to the ID. AddToRowIDDictionary(oRow, oVertex, oReadWorkbookContext.VertexRowIDDictionary); if (bReadAllEdgeAndVertexColumns) { // All columns except the vertex name should be read and stored // as metadata on the vertex. ReadAllColumns(oExcelTableReader, oRow, oVertex, oColumnNamesToExclude); continue; } // Layout order. if (ReadLayoutOrder(oRow, oVertex)) { bLayoutOrderSet = true; } // Location and Locked. if (!oReadWorkbookContext.IgnoreVertexLocations) { Boolean bLocationSpecified = false; bLocationSpecified = ReadLocation(oRow, oReadWorkbookContext.VertexLocationConverter, oVertex); ReadLocked(oRow, oBooleanConverter, bLocationSpecified, oVertex); } // Polar coordinates. ReadPolarCoordinates(oRow, oVertex); // Marked. ReadMarked(oRow, oBooleanConverter, oVertex); // Custom menu items. if (aoCustomMenuItemPairNames.Count > 0) { ReadCustomMenuItems(oRow, aoCustomMenuItemPairNames, oVertex); } // Alpha. ReadAlpha(oRow, oVertex); // Tooltip. if (ReadCellAndSetMetadata(oRow, VertexTableColumnNames.ToolTip, oVertex, ReservedMetadataKeys.VertexToolTip)) { bToolTipSet = true; } // Label. if (oReadWorkbookContext.ReadVertexLabels) { ReadCellAndSetMetadata(oRow, VertexTableColumnNames.Label, oVertex, ReservedMetadataKeys.PerVertexLabel); } // Label fill color. ReadColor(oRow, VertexTableColumnNames.LabelFillColor, oVertex, ReservedMetadataKeys.PerVertexLabelFillColor, oReadWorkbookContext.ColorConverter2); // Label position. ReadLabelPosition(oRow, oVertexLabelPositionConverter, oVertex); // Radius. Nullable <Single> oRadiusWorkbook = new Nullable <Single>(); oRadiusWorkbook = ReadRadius(oRow, oReadWorkbookContext.VertexRadiusConverter, oVertex); // Shape. VertexShape eVertexShape; if (!ReadShape(oRow, oVertex, out eVertexShape)) { eVertexShape = oReadWorkbookContext.DefaultVertexShape; } // Label font size. if (eVertexShape == VertexShape.Label && oRadiusWorkbook.HasValue) { // The vertex radius is used to specify font size when the // shape is Label. oVertex.SetValue(ReservedMetadataKeys.PerVertexLabelFontSize, oReadWorkbookContext.VertexRadiusConverter. WorkbookToLabelFontSize(oRadiusWorkbook.Value)); } // Image URI. if (eVertexShape == VertexShape.Image && oReadWorkbookContext.ReadVertexImages) { ReadImageUri(oRow, oVertex, oReadWorkbookContext.VertexRadiusConverter, oRadiusWorkbook.HasValue ? oRadiusWorkbook : oReadWorkbookContext.DefaultVertexImageSize ); } // Color ReadColor(oRow, VertexTableColumnNames.Color, oVertex, ReservedMetadataKeys.PerColor, oReadWorkbookContext.ColorConverter2); } if (bReadAllEdgeAndVertexColumns) { // Store the vertex column names on the graph. oGraph.SetValue(ReservedMetadataKeys.AllVertexMetadataKeys, FilterColumnNames(oExcelTableReader, oColumnNamesToExclude)); } }
//************************************************************************* // Method: ReadGroupTable() // /// <summary> /// Reads the group table. /// </summary> /// /// <param name="oGroupTable"> /// The group table. /// </param> /// /// <param name="oReadWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <returns> /// A dictionary. The key is the group name and the value is a /// GroupInformation object for the group. /// </returns> //************************************************************************* protected Dictionary<String, GroupInformation> ReadGroupTable( ListObject oGroupTable, ReadWorkbookContext oReadWorkbookContext ) { Debug.Assert(oGroupTable != null); Debug.Assert(oReadWorkbookContext != null); AssertValid(); if (oReadWorkbookContext.FillIDColumns) { FillIDColumn(oGroupTable); } Dictionary<String, GroupInformation> oGroupNameDictionary = new Dictionary<String, GroupInformation>(); ColorConverter2 oColorConverter2 = oReadWorkbookContext.ColorConverter2; BooleanConverter oBooleanConverter = oReadWorkbookContext.BooleanConverter; ExcelTableReader oExcelTableReader = new ExcelTableReader(oGroupTable); foreach ( ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows() ) { // Get the group information. String sGroupName; Color oVertexColor; VertexShape eVertexShape; if ( !oRow.TryGetNonEmptyStringFromCell(GroupTableColumnNames.Name, out sGroupName) || !TryGetColor(oRow, GroupTableColumnNames.VertexColor, oColorConverter2, out oVertexColor) || !TryGetVertexShape(oRow, GroupTableColumnNames.VertexShape, out eVertexShape) ) { continue; } Boolean bCollapsed = false; Boolean bCollapsedCellValue; if ( TryGetBoolean(oRow, GroupTableColumnNames.Collapsed, oBooleanConverter, out bCollapsedCellValue) && bCollapsedCellValue ) { bCollapsed = true; } Int32 iRowIDAsInt32; Nullable<Int32> iRowID = null; if ( oRow.TryGetInt32FromCell(CommonTableColumnNames.ID, out iRowIDAsInt32) ) { iRowID = iRowIDAsInt32; } GroupInformation oGroupInformation = new GroupInformation( sGroupName, iRowID, oVertexColor, eVertexShape, bCollapsed); if (oReadWorkbookContext.SaveGroupVertices) { // ReadGroupVertexTable() will save the group's vertices in // this LinkedList. oGroupInformation.Vertices = new LinkedList<IVertex>(); } try { oGroupNameDictionary.Add(sGroupName, oGroupInformation); } catch (ArgumentException) { Range oInvalidCell = oRow.GetRangeForCell( GroupTableColumnNames.Name); OnWorkbookFormatError( String.Format( "The cell {0} contains a duplicate group name. There" + " can't be two rows with the same group name." , ExcelUtil.GetRangeAddress(oInvalidCell) ), oInvalidCell ); } } return (oGroupNameDictionary); }
ReadEdgeTable ( ListObject oEdgeTable, ReadWorkbookContext oReadWorkbookContext, IGraph oGraph ) { Debug.Assert(oEdgeTable != null); Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oGraph != null); AssertValid(); Boolean bReadAllEdgeAndVertexColumns = oReadWorkbookContext.ReadAllEdgeAndVertexColumns; if (oReadWorkbookContext.FillIDColumns) { FillIDColumn(oEdgeTable); } Dictionary <String, IVertex> oVertexNameDictionary = oReadWorkbookContext.VertexNameDictionary; EdgeVisibilityConverter oEdgeVisibilityConverter = new EdgeVisibilityConverter(); Boolean bGraphIsDirected = (oGraph.Directedness == GraphDirectedness.Directed); ExcelTableReader oExcelTableReader = new ExcelTableReader(oEdgeTable); IVertexCollection oVertices = oGraph.Vertices; IEdgeCollection oEdges = oGraph.Edges; HashSet <String> oColumnNamesToExclude = new HashSet <String>( new String[] { EdgeTableColumnNames.Vertex1Name, EdgeTableColumnNames.Vertex2Name }); foreach (ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows()) { // Get the names of the edge's vertices. String sVertex1Name, sVertex2Name; Boolean bVertex1IsEmpty = !oRow.TryGetNonEmptyStringFromCell( EdgeTableColumnNames.Vertex1Name, out sVertex1Name); Boolean bVertex2IsEmpty = !oRow.TryGetNonEmptyStringFromCell( EdgeTableColumnNames.Vertex2Name, out sVertex2Name); if (bVertex1IsEmpty && bVertex2IsEmpty) { // Skip empty rows. continue; } if (bVertex1IsEmpty || bVertex2IsEmpty) { // A half-empty row is an error. OnHalfEmptyEdgeRow(oRow, bVertex1IsEmpty); } // Assume a default visibility. Visibility eVisibility = Visibility.Show; String sVisibility; if ( oRow.TryGetNonEmptyStringFromCell( CommonTableColumnNames.Visibility, out sVisibility) && !oEdgeVisibilityConverter.TryWorkbookToGraph( sVisibility, out eVisibility) ) { OnInvalidVisibility(oRow); } if (eVisibility == Visibility.Skip) { // Skip the edge an continue to the next edge. continue; } // Create the specified vertices or retrieve them from the // dictionary. IVertex oVertex1 = VertexNameToVertex( sVertex1Name, oVertices, oVertexNameDictionary); IVertex oVertex2 = VertexNameToVertex( sVertex2Name, oVertices, oVertexNameDictionary); // Add an edge connecting the vertices. IEdge oEdge = oEdges.Add(oVertex1, oVertex2, bGraphIsDirected); // If there is an ID column, add the edge to the edge row ID // dictionary and set the edge's Tag to the row ID. AddToRowIDDictionary(oRow, oEdge, oReadWorkbookContext.EdgeRowIDDictionary); if (bReadAllEdgeAndVertexColumns) { // All columns except the vertex names should be read and // stored as metadata on the edge. ReadAllColumns(oExcelTableReader, oRow, oEdge, oColumnNamesToExclude); continue; } if (eVisibility == Visibility.Hide) { // Hide the edge and continue to the next edge. oEdge.SetValue(ReservedMetadataKeys.Visibility, VisibilityKeyValue.Hidden); continue; } // Alpha. Boolean bAlphaIsZero = ReadAlpha(oRow, oEdge); if (bAlphaIsZero) { continue; } // Color. ReadColor(oRow, EdgeTableColumnNames.Color, oEdge, ReservedMetadataKeys.PerColor, oReadWorkbookContext.ColorConverter2); // Width. ReadWidth(oRow, oReadWorkbookContext.EdgeWidthConverter, oEdge); // Style. ReadStyle(oRow, oReadWorkbookContext.EdgeStyleConverter, oEdge); // Label. if (oReadWorkbookContext.ReadEdgeLabels) { ReadCellAndSetMetadata(oRow, EdgeTableColumnNames.Label, oEdge, ReservedMetadataKeys.PerEdgeLabel); } // Weight. if (oReadWorkbookContext.ReadEdgeWeights) { ReadEdgeWeight(oRow, oEdge); } } if (bReadAllEdgeAndVertexColumns) { // Store the edge column names on the graph. oGraph.SetValue(ReservedMetadataKeys.AllEdgeMetadataKeys, FilterColumnNames(oExcelTableReader, oColumnNamesToExclude)); } }
ReadWorkbookInternal ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext ) { Debug.Assert(readWorkbookContext != null); Debug.Assert(workbook != null); AssertValid(); if (readWorkbookContext.PopulateVertexWorksheet) { // Create and use the object that fills in the vertex worksheet. VertexWorksheetPopulator oVertexWorksheetPopulator = new VertexWorksheetPopulator(); try { oVertexWorksheetPopulator.PopulateVertexWorksheet( workbook, false); } catch (WorkbookFormatException) { // Ignore this type of error, which occurs when the vertex // worksheet is missing, for example. } } // Create a graph with the appropriate directedness. PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(workbook); IGraph oGraph = new Graph(oPerWorkbookSettings.GraphDirectedness); // Read the edge worksheet. This adds data to oGraph, // ReadWorkbookContext.VertexNameDictionary, and // ReadWorkbookContext.EdgeIDDictionary. EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader(); oEdgeWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oEdgeWorksheetReader = null; // Read the vertex worksheet. This adds metadata to the vertices in // oGraph; adds any isolated vertices to oGraph and // ReadWorkbookContext.VertexNameDictionary; and removes any skipped // vertices (and their incident edges) from // ReadWorkbookContext.VertexNameDictionary, // ReadWorkbookContext.EdgeIDDictionary, and oGraph. VertexWorksheetReader oVertexWorksheetReader = new VertexWorksheetReader(); oVertexWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oVertexWorksheetReader = null; if (readWorkbookContext.ReadAllEdgeAndVertexColumns) { // The other worksheets should be ignored. return(oGraph); } if (readWorkbookContext.ReadGroups) { // Read the group worksheets. This adds metadata to the vertices // in oGraph and to oGraph itself. GroupWorksheetReader oGroupWorksheetReader = new GroupWorksheetReader(); oGroupWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oGroupWorksheetReader = null; } // Read the per-workbook settings that are stored directly on the // graph. oPerWorkbookSettings.ReadWorksheet(workbook, readWorkbookContext, oGraph); return(oGraph); }
//************************************************************************* // Method: ReadWorksheet() // /// <summary> /// Reads per-workbook settings that are stored directly on an <see /// cref="IGraph" /> object. /// </summary> /// /// <param name="workbook"> /// Workbook containing the graph data. /// </param> /// /// <param name="readWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <param name="graph"> /// Graph to add data to. /// </param> //************************************************************************* public void ReadWorksheet( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext, IGraph graph ) { Debug.Assert(workbook != null); Debug.Assert(readWorkbookContext != null); Debug.Assert(graph != null); AssertValid(); Nullable<Color> oBackColor = this.BackColor; if (oBackColor.HasValue) { graph.SetValue(ReservedMetadataKeys.GraphBackColor, oBackColor.Value); // (Note that if there is no per-workbook background color, the // GraphDrawer.BackColor property will be used instead.) } String sBackgroundImageUri = this.BackgroundImageUri; if ( !String.IsNullOrEmpty(sBackgroundImageUri) ) { System.Windows.Media.ImageSource oImage = ( new WpfImageUtil() ). GetImageSynchronousIgnoreDpi(sBackgroundImageUri); graph.SetValue(ReservedMetadataKeys.GraphBackgroundImage, oImage); // (Note that if there is no per-workbook background image, no // background image will be drawn.) } }
//************************************************************************* // Method: ExportToNewMatrixWorkbook() // /// <summary> /// Exports the edge table to a new workbook as an adjacency matrix. /// </summary> /// /// <returns> /// The new workbook. /// </returns> /// /// <remarks> /// If there are no rows in the edge table, or if an error occurs while /// exporting the rows, an <see cref="ExportWorkbookException" /> exception /// is thrown. /// </remarks> //************************************************************************* public Microsoft.Office.Interop.Excel.Workbook ExportToNewMatrixWorkbook() { AssertValid(); // Merge duplicate edges and add an edge weight column. ( new DuplicateEdgeMerger() ).MergeDuplicateEdges(m_oWorkbookToExport); // Read the workbook, including the edge weight column. ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.ReadEdgeWeights = true; IGraph oGraph = ( new WorkbookReader() ).ReadWorkbook( m_oWorkbookToExport, oReadWorkbookContext); // Get an array of non-isolated vertices. Isolated vertices don't get // exported. List<IVertex> oNonIsolatedVertices = GraphUtil.GetNonIsolatedVertices(oGraph); Int32 iNonIsolatedVertices = oNonIsolatedVertices.Count; if (iNonIsolatedVertices == 0) { throw new ExportWorkbookException( "There are no edges to export." ); } Workbook oNewWorkbook = m_oWorkbookToExport.Application.Workbooks.Add(Missing.Value); Worksheet oNewWorksheet = (Worksheet)oNewWorkbook.ActiveSheet; // Fill in row 1 and column A with the vertex names, starting at B1 and // A2, respectively. String [,] asVertexNamesForRow1 = ExcelUtil.GetSingleRow2DStringArray( iNonIsolatedVertices); String [,] asVertexNamesForColumnA = ExcelUtil.GetSingleColumn2DStringArray(iNonIsolatedVertices); for (Int32 i = 0; i < iNonIsolatedVertices; i++) { asVertexNamesForRow1[1, i + 1] = asVertexNamesForColumnA[i + 1, 1] = oNonIsolatedVertices[i].Name; } ExcelUtil.SetRangeValues( (Range)oNewWorksheet.Cells[1, 2], asVertexNamesForRow1 ); ExcelUtil.SetRangeValues( (Range)oNewWorksheet.Cells[2, 1], asVertexNamesForColumnA ); asVertexNamesForRow1 = asVertexNamesForColumnA = null; // Now fill in the edge weights, row by row. Range oFirstColumnCell = (Range)oNewWorksheet.Cells[2, 2]; for (Int32 i = 0; i < iNonIsolatedVertices; i++) { Object [,] aoEdgeWeights = ExcelUtil.GetSingleRow2DArray( iNonIsolatedVertices); IVertex oVertexI = oNonIsolatedVertices[i]; for (Int32 j = 0; j < iNonIsolatedVertices; j++) { aoEdgeWeights[1, j + 1] = EdgeUtil.GetEdgeWeight( oVertexI, oNonIsolatedVertices[j] ); } ExcelUtil.SetRangeValues(oFirstColumnCell, aoEdgeWeights); oFirstColumnCell = oFirstColumnCell.get_Offset(1, 0); } return (oNewWorkbook); }
public void TearDown() { m_oReadWorkbookContext = null; }
//************************************************************************* // Method: ExportToGraphMLFile() // /// <summary> /// Exports the edge and vertex tables to a new GraphML file. /// </summary> //************************************************************************* public void ExportToGraphMLFile() { AssertValid(); if ( !this.ExcelApplicationIsReady(true) || !MergeIsApproved( "add an Edge Weight column, and export the edges and vertices" + " to a new GraphML file.") ) { return; } ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.ReadAllEdgeAndVertexColumns = true; SaveGraphMLFileDialog oSaveGraphMLFileDialog = new SaveGraphMLFileDialog(String.Empty, String.Empty); ExportToFile(oReadWorkbookContext, oSaveGraphMLFileDialog); }
//************************************************************************* // Method: ReadGroupVertexTable() // /// <summary> /// Reads the group vertex table. /// </summary> /// /// <param name="oGroupVertexTable"> /// The group vertex table. /// </param> /// /// <param name="oReadWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <param name="oGroupNameDictionary"> /// The key is the group name and the value is the GroupInformation object /// for the group. /// </param> /// /// <param name="oGraph"> /// Graph to add group data to. /// </param> //************************************************************************* protected void ReadGroupVertexTable( ListObject oGroupVertexTable, ReadWorkbookContext oReadWorkbookContext, Dictionary<String, GroupInformation> oGroupNameDictionary, IGraph oGraph ) { Debug.Assert(oGroupVertexTable != null); Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oGroupNameDictionary != null); Debug.Assert(oGraph != null); AssertValid(); Dictionary<String, IVertex> oVertexNameDictionary = oReadWorkbookContext.VertexNameDictionary; ExcelTableReader oExcelTableReader = new ExcelTableReader(oGroupVertexTable); foreach ( ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows() ) { // Get the group vertex information from the row. String sGroupName, sVertexName; if ( !oRow.TryGetNonEmptyStringFromCell( GroupVertexTableColumnNames.GroupName, out sGroupName) || !oRow.TryGetNonEmptyStringFromCell( GroupVertexTableColumnNames.VertexName, out sVertexName) ) { continue; } // Get the group information for the vertex and store the group // information in the vertex. GroupInformation oGroupInformation; IVertex oVertex; if ( !oGroupNameDictionary.TryGetValue(sGroupName, out oGroupInformation) || !oVertexNameDictionary.TryGetValue(sVertexName, out oVertex) ) { continue; } oVertex.SetValue(ReservedMetadataKeys.PerColor, oGroupInformation.VertexColor); oVertex.SetValue(ReservedMetadataKeys.PerVertexShape, oGroupInformation.VertexShape); if (oReadWorkbookContext.SaveGroupVertices) { Debug.Assert(oGroupInformation.Vertices != null); oGroupInformation.Vertices.Add(oVertex); } } }
ReadGroupTable ( ListObject oGroupTable, ReadWorkbookContext oReadWorkbookContext ) { Debug.Assert(oGroupTable != null); Debug.Assert(oReadWorkbookContext != null); AssertValid(); if (oReadWorkbookContext.FillIDColumns) { FillIDColumn(oGroupTable); } Dictionary <String, GroupInformation> oGroupNameDictionary = new Dictionary <String, GroupInformation>(); ColorConverter2 oColorConverter2 = oReadWorkbookContext.ColorConverter2; BooleanConverter oBooleanConverter = oReadWorkbookContext.BooleanConverter; ExcelTableReader oExcelTableReader = new ExcelTableReader(oGroupTable); foreach (ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows()) { // Get the group information. String sGroupName; Color oVertexColor; VertexShape eVertexShape; if ( !oRow.TryGetNonEmptyStringFromCell(GroupTableColumnNames.Name, out sGroupName) || !TryGetColor(oRow, GroupTableColumnNames.VertexColor, oColorConverter2, out oVertexColor) || !TryGetVertexShape(oRow, GroupTableColumnNames.VertexShape, out eVertexShape) ) { continue; } Boolean bCollapsed = false; Boolean bCollapsedCellValue; if ( TryGetBoolean(oRow, GroupTableColumnNames.Collapsed, oBooleanConverter, out bCollapsedCellValue) && bCollapsedCellValue ) { bCollapsed = true; } Int32 iRowIDAsInt32; Nullable <Int32> iRowID = null; if (oRow.TryGetInt32FromCell(CommonTableColumnNames.ID, out iRowIDAsInt32)) { iRowID = iRowIDAsInt32; } GroupInformation oGroupInformation = new GroupInformation( sGroupName, iRowID, oVertexColor, eVertexShape, bCollapsed); if (oReadWorkbookContext.SaveGroupVertices) { // ReadGroupVertexTable() will save the group's vertices in // this LinkedList. oGroupInformation.Vertices = new LinkedList <IVertex>(); } try { oGroupNameDictionary.Add(sGroupName, oGroupInformation); } catch (ArgumentException) { Range oInvalidCell = oRow.GetRangeForCell( GroupTableColumnNames.Name); OnWorkbookFormatError(String.Format( "The cell {0} contains a duplicate group name. There" + " can't be two rows with the same group name." , ExcelUtil.GetRangeAddress(oInvalidCell) ), oInvalidCell ); } } return(oGroupNameDictionary); }
//************************************************************************* // Method: ExportToUcinetFile() // /// <summary> /// Exports the edge and vertex tables to a new UCINET full matrix DL file. /// </summary> //************************************************************************* public void ExportToUcinetFile() { AssertValid(); if ( !this.ExcelApplicationIsReady(true) || !MergeIsApproved( "add an Edge Weight column, and export the edges to a new" + " UCINET full matrix DL file.") ) { return; } ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.ReadEdgeWeights = true; SaveUcinetFileDialog oSaveUcinetFileDialog = new SaveUcinetFileDialog(String.Empty, String.Empty); ExportToFile(oReadWorkbookContext, oSaveUcinetFileDialog); }
//************************************************************************* // Method: ExportToFile() // /// <summary> /// Merges duplicate edges and exports the edge and vertex tables to a file /// using a provided dialog. /// </summary> /// /// <param name="oReadWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <param name="oSaveGraphFileDialog"> /// The dialog to use to save the graph. /// </param> //************************************************************************* private void ExportToFile( ReadWorkbookContext oReadWorkbookContext, SaveGraphFileDialog oSaveGraphFileDialog ) { Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oSaveGraphFileDialog != null); AssertValid(); WorkbookReader oWorkbookReader = new WorkbookReader(); ShowWaitCursor = true; this.ScreenUpdating = false; try { ( new DuplicateEdgeMerger() ).MergeDuplicateEdges( this.InnerObject); this.ScreenUpdating = true; // Read the workbook and let the user save it. IGraph oGraph = oWorkbookReader.ReadWorkbook( this.InnerObject, oReadWorkbookContext); oSaveGraphFileDialog.ShowDialogAndSaveGraph(oGraph); } catch (Exception oException) { this.ScreenUpdating = true; ErrorUtil.OnException(oException); } finally { this.ScreenUpdating = true; ShowWaitCursor = false; } }
//************************************************************************* // Method: ReadWorkbookInternal() // /// <summary> /// Creates a NodeXL graph from the contents of an Excel workbook. /// </summary> /// /// <param name="workbook"> /// Workbook containing the graph data. /// </param> /// /// <param name="readWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <returns> /// A new graph. /// </returns> /// /// <remarks> /// If <paramref name="workbook" /> contains valid graph data, a new <see /// cref="IGraph" /> is created from the workbook contents and returned. /// Otherwise, a <see cref="WorkbookFormatException" /> is thrown. /// </remarks> //************************************************************************* protected IGraph ReadWorkbookInternal( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext ) { Debug.Assert(readWorkbookContext != null); Debug.Assert(workbook != null); AssertValid(); if (readWorkbookContext.PopulateVertexWorksheet) { // Create and use the object that fills in the vertex worksheet. VertexWorksheetPopulator oVertexWorksheetPopulator = new VertexWorksheetPopulator(); try { oVertexWorksheetPopulator.PopulateVertexWorksheet( workbook, false); } catch (WorkbookFormatException) { // Ignore this type of error, which occurs when the vertex // worksheet is missing, for example. } } // Create a graph with the appropriate directedness. PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(workbook); IGraph oGraph = new Graph(oPerWorkbookSettings.GraphDirectedness); // Read the edge worksheet. This adds data to oGraph, // ReadWorkbookContext.VertexNameDictionary, and // ReadWorkbookContext.EdgeIDDictionary. EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader(); oEdgeWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oEdgeWorksheetReader = null; // Read the vertex worksheet. This adds metadata to the vertices in // oGraph; adds any isolated vertices to oGraph and // ReadWorkbookContext.VertexNameDictionary; and removes any skipped // vertices (and their incident edges) from // ReadWorkbookContext.VertexNameDictionary, // ReadWorkbookContext.EdgeIDDictionary, and oGraph. VertexWorksheetReader oVertexWorksheetReader = new VertexWorksheetReader(); oVertexWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oVertexWorksheetReader = null; if (readWorkbookContext.ReadAllEdgeAndVertexColumns) { // The other worksheets should be ignored. return (oGraph); } if (readWorkbookContext.ReadGroups) { // Read the group worksheets. This adds metadata to the vertices // in oGraph and to oGraph itself. GroupWorksheetReader oGroupWorksheetReader = new GroupWorksheetReader(); oGroupWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oGroupWorksheetReader = null; } // Read the per-workbook settings that are stored directly on the // graph. oPerWorkbookSettings.ReadWorksheet(workbook, readWorkbookContext, oGraph); return (oGraph); }
//************************************************************************* // Method: ReadWorkbook() // /// <summary> /// Reads a workbook into a new graph. /// </summary> /// /// <param name="oWorkbook"> /// The workbook to read. /// </param> /// /// <returns> /// The new graph. /// </returns> //************************************************************************* protected IGraph ReadWorkbook( Microsoft.Office.Interop.Excel.Workbook oWorkbook ) { Debug.Assert(oWorkbook != null); AssertValid(); ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); WorkbookReader oWorkbookReader = new WorkbookReader(); // Convert the workbook contents to a Graph object. return ( oWorkbookReader.ReadWorkbook( oWorkbook, oReadWorkbookContext) ); }
//************************************************************************* // Constructor: ReadWorkbookContextTest() // /// <summary> /// Initializes a new instance of the <see /// cref="ReadWorkbookContextTest" /> class. /// </summary> //************************************************************************* public ReadWorkbookContextTest() { m_oReadWorkbookContext = null; }
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 (ExcelUtil.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 bLayoutOrderSet, bToolTipSet; try { ReadVertexTable(oVertexTable, readWorkbookContext, graph, out bLayoutOrderSet, out bToolTipSet); } finally { ExcelColumnHider.RestoreHiddenColumns(oVertexTable, oHiddenColumns); } if (bLayoutOrderSet) { // The layout order was specified for at least one vertex. // The ByMetadataVertexSorter used by SortableLayoutBase // requires that if layout 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 layout order for each // vertex that doesn't already specify one. SetUnsetVertexOrders(graph); // The layout order is ignored unless this key is added to the // graph. graph.SetValue( ReservedMetadataKeys.SortableLayoutOrderSet, null); } if (bToolTipSet) { graph.SetValue(ReservedMetadataKeys.ToolTipSet, null); } } }
ExportToNewMatrixWorkbook() { AssertValid(); // Merge duplicate edges and add an edge weight column. (new DuplicateEdgeMerger()).MergeDuplicateEdges(m_oWorkbookToExport); // Read the workbook, including the edge weight column. ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.ReadEdgeWeights = true; IGraph oGraph = (new WorkbookReader()).ReadWorkbook( m_oWorkbookToExport, oReadWorkbookContext); // Get an array of non-isolated vertices. Isolated vertices don't get // exported. List <IVertex> oNonIsolatedVertices = GraphUtil.GetNonIsolatedVertices(oGraph); Int32 iNonIsolatedVertices = oNonIsolatedVertices.Count; if (iNonIsolatedVertices == 0) { throw new ExportWorkbookException( "There are no edges to export." ); } Workbook oNewWorkbook = m_oWorkbookToExport.Application.Workbooks.Add(Missing.Value); Worksheet oNewWorksheet = (Worksheet)oNewWorkbook.ActiveSheet; // Fill in row 1 and column A with the vertex names, starting at B1 and // A2, respectively. String [,] asVertexNamesForRow1 = ExcelUtil.GetSingleRow2DStringArray( iNonIsolatedVertices); String [,] asVertexNamesForColumnA = ExcelUtil.GetSingleColumn2DStringArray(iNonIsolatedVertices); for (Int32 i = 0; i < iNonIsolatedVertices; i++) { asVertexNamesForRow1[1, i + 1] = asVertexNamesForColumnA[i + 1, 1] = oNonIsolatedVertices[i].Name; } ExcelUtil.SetRangeValues((Range)oNewWorksheet.Cells[1, 2], asVertexNamesForRow1); ExcelUtil.SetRangeValues((Range)oNewWorksheet.Cells[2, 1], asVertexNamesForColumnA); asVertexNamesForRow1 = asVertexNamesForColumnA = null; // Now fill in the edge weights, row by row. Range oFirstColumnCell = (Range)oNewWorksheet.Cells[2, 2]; for (Int32 i = 0; i < iNonIsolatedVertices; i++) { Object [,] aoEdgeWeights = ExcelUtil.GetSingleRow2DArray( iNonIsolatedVertices); IVertex oVertexI = oNonIsolatedVertices[i]; for (Int32 j = 0; j < iNonIsolatedVertices; j++) { aoEdgeWeights[1, j + 1] = EdgeUtil.GetEdgeWeight(oVertexI, oNonIsolatedVertices[j]); } ExcelUtil.SetRangeValues(oFirstColumnCell, aoEdgeWeights); oFirstColumnCell = oFirstColumnCell.get_Offset(1, 0); } return(oNewWorkbook); }
//************************************************************************* // Method: ReadWorksheet() // /// <summary> /// Reads the group worksheets and adds the contents to a graph. /// </summary> /// /// <param name="workbook"> /// Workbook containing the graph data. /// </param> /// /// <param name="readWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <param name="graph"> /// Graph to add group data to. /// </param> /// /// <remarks> /// If the group worksheets in <paramref name="workbook" /> contain valid /// group data, the data is added to <paramref name="graph" />. Otherwise, /// a <see cref="WorkbookFormatException" /> is thrown. /// </remarks> //************************************************************************* public void 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 ( ExcelUtil.TryGetTable(workbook, WorksheetNames.Groups, TableNames.Groups, out oGroupTable) && ExcelUtil.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); } } }
//************************************************************************* // Method: ReadVertexTable() // /// <summary> /// Reads the vertex table and adds the contents to a graph. /// </summary> /// /// <param name="oVertexTable"> /// Table that contains the vertex data. /// </param> /// /// <param name="oReadWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <param name="oGraph"> /// Graph to add vertices to. /// </param> /// /// <param name="bLayoutOrderSet"> /// Gets set to true if a vertex layout order was specified for at least /// one vertex, or false otherwise. /// </param> /// /// <param name="bToolTipSet"> /// Gets set to true if a tooltip was specified for at least one vertex, or /// false otherwise. /// </param> //************************************************************************* protected void ReadVertexTable( ListObject oVertexTable, ReadWorkbookContext oReadWorkbookContext, IGraph oGraph, out Boolean bLayoutOrderSet, out Boolean bToolTipSet ) { Debug.Assert(oVertexTable != null); Debug.Assert(oReadWorkbookContext != null); Debug.Assert(oGraph != null); AssertValid(); bLayoutOrderSet = bToolTipSet = false; if (GetTableColumnIndex(oVertexTable, VertexTableColumnNames.VertexName, false) == NoSuchColumn) { // Nothing can be done without vertex names. return; } Boolean bReadAllEdgeAndVertexColumns = oReadWorkbookContext.ReadAllEdgeAndVertexColumns; if (oReadWorkbookContext.FillIDColumns) { FillIDColumn(oVertexTable); } // Get the names of all the column pairs that are used to add custom // menu items to the vertex context menu in the graph. TableColumnAdder oTableColumnAdder = new TableColumnAdder(); ICollection< KeyValuePair<String, String> > aoCustomMenuItemPairNames = oTableColumnAdder.GetColumnPairNames(oVertexTable, VertexTableColumnNames.CustomMenuItemTextBase, VertexTableColumnNames.CustomMenuItemActionBase); IVertexCollection oVertices = oGraph.Vertices; Dictionary<String, IVertex> oVertexNameDictionary = oReadWorkbookContext.VertexNameDictionary; Dictionary<Int32, IIdentityProvider> oEdgeRowIDDictionary = oReadWorkbookContext.EdgeRowIDDictionary; BooleanConverter oBooleanConverter = oReadWorkbookContext.BooleanConverter; VertexVisibilityConverter oVertexVisibilityConverter = new VertexVisibilityConverter(); VertexLabelPositionConverter oVertexLabelPositionConverter = new VertexLabelPositionConverter(); ExcelTableReader oExcelTableReader = new ExcelTableReader(oVertexTable); HashSet<String> oColumnNamesToExclude = new HashSet<String>( new String[] { VertexTableColumnNames.VertexName } ); foreach ( ExcelTableReader.ExcelTableRow oRow in oExcelTableReader.GetRows() ) { // Get the name of the vertex. String sVertexName; if ( !oRow.TryGetNonEmptyStringFromCell( VertexTableColumnNames.VertexName, out sVertexName) ) { continue; } // If the vertex was added to the graph as part of an edge, // retrieve the vertex. IVertex oVertex; if ( !oVertexNameDictionary.TryGetValue(sVertexName, out oVertex) ) { oVertex = null; } // Assume a default visibility. Visibility eVisibility = Visibility.ShowIfInAnEdge; String sVisibility; if ( oRow.TryGetNonEmptyStringFromCell( CommonTableColumnNames.Visibility, out sVisibility) ) { if ( !oVertexVisibilityConverter.TryWorkbookToGraph( sVisibility, out eVisibility) ) { OnInvalidVisibility(oRow); } } switch (eVisibility) { case Visibility.ShowIfInAnEdge: // If the vertex is part of an edge, show it using the // specified vertex attributes. Otherwise, skip the vertex // row. if (oVertex == null) { continue; } break; case Visibility.Skip: // Skip the vertex row and any edge rows that include the // vertex. Do not read them into the graph. if (oVertex != null) { // Remove the vertex and its incident edges from the // graph and dictionaries. foreach (IEdge oIncidentEdge in oVertex.IncidentEdges) { if (oIncidentEdge.Tag is Int32) { oEdgeRowIDDictionary.Remove( (Int32)oIncidentEdge.Tag); } } oVertexNameDictionary.Remove(sVertexName); oVertices.Remove(oVertex); // (The vertex doesn't get added to // ReadWorkbookContext.VertexIDDictionary until after // this switch statement, so it doesn't need to be // removed from that dictionary.) } continue; case Visibility.Hide: // If the vertex is part of an edge, hide it and its // incident edges. Otherwise, skip the vertex row. if (oVertex == null) { continue; } // Hide the vertex and its incident edges. oVertex.SetValue(ReservedMetadataKeys.Visibility, VisibilityKeyValue.Hidden); foreach (IEdge oIncidentEdge in oVertex.IncidentEdges) { oIncidentEdge.SetValue(ReservedMetadataKeys.Visibility, VisibilityKeyValue.Hidden); } break; case Visibility.Show: // Show the vertex using the specified attributes // regardless of whether it is part of an edge. if (oVertex == null) { oVertex = CreateVertex(sVertexName, oVertices, oVertexNameDictionary); } break; default: Debug.Assert(false); break; } Debug.Assert(oVertex != null); // If there is an ID column, add the vertex to the vertex ID // dictionary and set the vertex's Tag to the ID. AddToRowIDDictionary(oRow, oVertex, oReadWorkbookContext.VertexRowIDDictionary); if (bReadAllEdgeAndVertexColumns) { // All columns except the vertex name should be read and stored // as metadata on the vertex. ReadAllColumns( oExcelTableReader, oRow, oVertex, oColumnNamesToExclude); continue; } // Layout order. if ( ReadLayoutOrder(oRow, oVertex) ) { bLayoutOrderSet = true; } // Location and Locked. if (!oReadWorkbookContext.IgnoreVertexLocations) { Boolean bLocationSpecified = false; bLocationSpecified = ReadLocation(oRow, oReadWorkbookContext.VertexLocationConverter, oVertex); ReadLocked(oRow, oBooleanConverter, bLocationSpecified, oVertex); } // Polar coordinates. ReadPolarCoordinates(oRow, oVertex); // Marked. ReadMarked(oRow, oBooleanConverter, oVertex); // Custom menu items. if (aoCustomMenuItemPairNames.Count > 0) { ReadCustomMenuItems(oRow, aoCustomMenuItemPairNames, oVertex); } // Alpha. ReadAlpha(oRow, oVertex); // Tooltip. if ( ReadCellAndSetMetadata(oRow, VertexTableColumnNames.ToolTip, oVertex, ReservedMetadataKeys.VertexToolTip) ) { bToolTipSet = true; } // Label. if (oReadWorkbookContext.ReadVertexLabels) { ReadCellAndSetMetadata(oRow, VertexTableColumnNames.Label, oVertex, ReservedMetadataKeys.PerVertexLabel); } // Label fill color. ReadColor(oRow, VertexTableColumnNames.LabelFillColor, oVertex, ReservedMetadataKeys.PerVertexLabelFillColor, oReadWorkbookContext.ColorConverter2); // Label position. ReadLabelPosition(oRow, oVertexLabelPositionConverter, oVertex); // Radius. Nullable<Single> oRadiusWorkbook = new Nullable<Single>(); oRadiusWorkbook = ReadRadius(oRow, oReadWorkbookContext.VertexRadiusConverter, oVertex); // Shape. VertexShape eVertexShape; if ( !ReadShape(oRow, oVertex, out eVertexShape) ) { eVertexShape = oReadWorkbookContext.DefaultVertexShape; } // Label font size. if (eVertexShape == VertexShape.Label && oRadiusWorkbook.HasValue) { // The vertex radius is used to specify font size when the // shape is Label. oVertex.SetValue( ReservedMetadataKeys.PerVertexLabelFontSize, oReadWorkbookContext.VertexRadiusConverter. WorkbookToLabelFontSize(oRadiusWorkbook.Value) ); } // Image URI. if (eVertexShape == VertexShape.Image && oReadWorkbookContext.ReadVertexImages) { ReadImageUri(oRow, oVertex, oReadWorkbookContext.VertexRadiusConverter, oRadiusWorkbook.HasValue ? oRadiusWorkbook : oReadWorkbookContext.DefaultVertexImageSize ); } // Color ReadColor(oRow, VertexTableColumnNames.Color, oVertex, ReservedMetadataKeys.PerColor, oReadWorkbookContext.ColorConverter2); } if (bReadAllEdgeAndVertexColumns) { // Store the vertex column names on the graph. oGraph.SetValue( ReservedMetadataKeys.AllVertexMetadataKeys, FilterColumnNames(oExcelTableReader, oColumnNamesToExclude) ); } }
//************************************************************************* // Method: ReadGroupTables() // /// <summary> /// Reads the group tables and add the contents to a graph. /// </summary> /// /// <param name="oGroupTable"> /// Table that contains the group data. /// </param> /// /// <param name="oGroupVertexTable"> /// Table that contains the group vertex data. /// </param> /// /// <param name="oReadWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <param name="oGraph"> /// Graph to add group data to. /// </param> //************************************************************************* protected void 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 ( !ExcelUtil.TryGetTableColumn(oGroupTable, GroupTableColumnNames.Name, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupTable, GroupTableColumnNames.VertexColor, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupTable, GroupTableColumnNames.VertexShape, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupVertexTable, GroupVertexTableColumnNames.GroupName, out oColumn) || !ExcelUtil.TryGetTableColumn(oGroupVertexTable, GroupVertexTableColumnNames.VertexName, out oColumn) ) { return; } // Create a dictionary from the group table. The key is the group name // and the value is a GroupInformation object for the group. Dictionary<String, GroupInformation> oGroupNameDictionary = ReadGroupTable(oGroupTable, oReadWorkbookContext); // Read the group vertex table and set the color and shape of each // group vertex in the graph. ReadGroupVertexTable(oGroupVertexTable, oReadWorkbookContext, oGroupNameDictionary, oGraph); // Save the group information on the graph. Debug.Assert( oGroupNameDictionary.Values is ICollection<GroupInformation> ); oGraph.SetValue(ReservedMetadataKeys.GroupInformation, oGroupNameDictionary.Values); }
//************************************************************************* // Method: ReadWorksheet() // /// <summary> /// Reads the vertex worksheet and adds the contents to a graph. /// </summary> /// /// <param name="workbook"> /// Workbook containing the graph data. /// </param> /// /// <param name="readWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// /// <param name="graph"> /// Graph to add vertex data to. /// </param> /// /// <remarks> /// If the vertex worksheet in <paramref name="workbook" /> contains valid /// vertex data, the vertices in <paramref name="graph" /> are marked with /// metadata; any isolated vertices are added to <paramref /// name="graph" /> and <paramref /// name="readWorkbookContext" />.VertexNameDictionary; and any /// skipped vertices (and their incident edges) are removed from <paramref /// name="graph" />, <paramref /// name="readWorkbookContext" />.VertexNameDictionary, and /// <paramref name="readWorkbookContext" />.EdgeRowIDDictionary. /// Otherwise, a <see cref="WorkbookFormatException" /> is thrown. /// </remarks> //************************************************************************* public void 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 ( ExcelUtil.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 bLayoutOrderSet, bToolTipSet; try { ReadVertexTable(oVertexTable, readWorkbookContext, graph, out bLayoutOrderSet, out bToolTipSet); } finally { ExcelColumnHider.RestoreHiddenColumns(oVertexTable, oHiddenColumns); } if (bLayoutOrderSet) { // The layout order was specified for at least one vertex. // The ByMetadataVertexSorter used by SortableLayoutBase // requires that if layout 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 layout order for each // vertex that doesn't already specify one. SetUnsetVertexOrders(graph); // The layout order is ignored unless this key is added to the // graph. graph.SetValue( ReservedMetadataKeys.SortableLayoutOrderSet, null); } if (bToolTipSet) { graph.SetValue(ReservedMetadataKeys.ToolTipSet, null); } } }
//************************************************************************* // Method: ReadWorkbook() // /// <summary> /// Reads the workbook contents into a NodeXL graph. /// </summary> /// /// <param name="oWorkbook"> /// Workbook containing the graph contents. /// </param> /// /// <param name="oGraphMetricUserSettings"> /// User settings for calculating graph metrics. /// </param> /// /// <returns> /// The <see cref="IGraph" /> read from the workbook. /// </returns> /// /// <remarks> /// If <paramref name="oWorkbook" /> contains valid graph data, a new <see /// cref="IGraph" /> is created from the workbook contents and returned. /// Otherwise, a <see cref="WorkbookFormatException" /> is thrown. /// </remarks> //************************************************************************* protected IGraph ReadWorkbook( Microsoft.Office.Interop.Excel.Workbook oWorkbook, GraphMetricUserSettings oGraphMetricUserSettings ) { Debug.Assert(oWorkbook != null); Debug.Assert(oGraphMetricUserSettings != null); AssertValid(); ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.FillIDColumns = true; oReadWorkbookContext.PopulateVertexWorksheet = true; if ( (oGraphMetricUserSettings.GraphMetricsToCalculate & GraphMetrics.GroupMetrics) != 0) { oReadWorkbookContext.ReadGroups = true; oReadWorkbookContext.SaveGroupVertices = true; } WorkbookReader oWorkbookReader = new WorkbookReader(); return ( oWorkbookReader.ReadWorkbook( oWorkbook, oReadWorkbookContext) ); }
//************************************************************************* // Method: ExportToPajekFile() // /// <summary> /// Exports the edge and vertex tables to a new Pajek text file. /// </summary> //************************************************************************* public void ExportToPajekFile() { AssertValid(); if ( !this.ExcelApplicationIsReady(true) || !MergeIsApproved( "add an Edge Weight column, and export the edges and vertices" + " to a new Pajek file.") ) { return; } ReadWorkbookContext oReadWorkbookContext = new ReadWorkbookContext(); oReadWorkbookContext.ReadEdgeWeights = true; // Map any vertex coordinates stored in the workbook to an arbitrary // rectangle. PajekGraphAdapter will in turn map these to Pajek // coordinates. oReadWorkbookContext.IgnoreVertexLocations = false; oReadWorkbookContext.GraphRectangle = new System.Drawing.Rectangle(0, 0, 10000, 10000); SavePajekFileDialog oSavePajekFileDialog = new SavePajekFileDialog(String.Empty, String.Empty); ExportToFile(oReadWorkbookContext, oSavePajekFileDialog); }