public void TestGraphToWorkbook4() { // Empty rectangle. m_oVertexLocationConverter = new VertexLocationConverter( Rectangle.FromLTRB(100, 200, 100, 200) ); Single fGraphX = 600; Single fGraphY = 1200; Single ExpectedWorkbookX = VertexLocationConverter.MinimumXYWorkbook; Single ExpectedWorkbookY = ExpectedWorkbookX; Single fWorkbookX, fWorkbookY; m_oVertexLocationConverter.GraphToWorkbook( new PointF(fGraphX, fGraphY), out fWorkbookX, out fWorkbookY); Assert.AreEqual(ExpectedWorkbookX, fWorkbookX); Assert.AreEqual(ExpectedWorkbookY, fWorkbookY); }
//************************************************************************* // Method: OnVerticesMoved() // /// <summary> /// Handles the VerticesMoved event on ThisWorkbook. /// </summary> /// /// <param name="e"> /// Standard event argument. /// </param> //************************************************************************* private void OnVerticesMoved( VerticesMovedEventArgs2 e ) { Debug.Assert(e != null); AssertValid(); Microsoft.Office.Interop.Excel.ListObject oVertexTable = Vertices.InnerObject; // Get a dictionary that maps IDs to row numbers, and get the location // columns from the vertex table. These are the entire table columns, // including the headers. Dictionary<Int32, Int32> oRowIDDictionary; Microsoft.Office.Interop.Excel.ListColumn oXColumn, oYColumn; if ( !m_oSheets1And2Helper.TryGetAllRowIDs(out oRowIDDictionary) || !ExcelUtil.TryGetTableColumn(oVertexTable, VertexTableColumnNames.X, out oXColumn) || !ExcelUtil.TryGetTableColumn(oVertexTable, VertexTableColumnNames.Y, out oYColumn) ) { return; } // Create an object that converts a vertex location between coordinates // used in the NodeXL graph and coordinates used in the worksheet. VertexLocationConverter oVertexLocationConverter = new VertexLocationConverter(e.GraphRectangle); foreach (VertexAndRowID oVertexAndRowID in e.VerticesAndRowIDs) { // Look for the cell in the ID column that contains the specified // ID. Int32 iRowOneBased; if ( !oRowIDDictionary.TryGetValue(oVertexAndRowID.VertexRowID, out iRowOneBased) ) { continue; } // Convert the vertex location to workbook coordinates. Single fWorkbookX, fWorkbookY; oVertexLocationConverter.GraphToWorkbook( oVertexAndRowID.Vertex.Location, out fWorkbookX, out fWorkbookY); // Update the X and Y cells. this.Cells[iRowOneBased, oXColumn.Range.Column] = fWorkbookX; this.Cells[iRowOneBased, oYColumn.Range.Column] = fWorkbookY; } }
OnVerticesMoved ( VerticesMovedEventArgs2 e ) { Debug.Assert(e != null); AssertValid(); Microsoft.Office.Interop.Excel.ListObject oVertexTable = Vertices.InnerObject; // Get a dictionary that maps IDs to row numbers, and get the location // columns from the vertex table. These are the entire table columns, // including the headers. Dictionary <Int32, Int32> oRowIDDictionary; Microsoft.Office.Interop.Excel.ListColumn oXColumn, oYColumn; if ( !m_oSheets1And2Helper.TryGetAllRowIDs(out oRowIDDictionary) || !ExcelUtil.TryGetTableColumn(oVertexTable, VertexTableColumnNames.X, out oXColumn) || !ExcelUtil.TryGetTableColumn(oVertexTable, VertexTableColumnNames.Y, out oYColumn) ) { return; } // Create an object that converts a vertex location between coordinates // used in the NodeXL graph and coordinates used in the worksheet. VertexLocationConverter oVertexLocationConverter = new VertexLocationConverter(e.GraphRectangle); foreach (VertexAndRowID oVertexAndRowID in e.VerticesAndRowIDs) { // Look for the cell in the ID column that contains the specified // ID. Int32 iRowOneBased; if (!oRowIDDictionary.TryGetValue(oVertexAndRowID.VertexRowID, out iRowOneBased)) { continue; } // Convert the vertex location to workbook coordinates. Single fWorkbookX, fWorkbookY; oVertexLocationConverter.GraphToWorkbook( oVertexAndRowID.Vertex.Location, out fWorkbookX, out fWorkbookY); // Update the X and Y cells. this.Cells[iRowOneBased, oXColumn.Range.Column] = fWorkbookX; this.Cells[iRowOneBased, oYColumn.Range.Column] = fWorkbookY; } }
//************************************************************************* // Method: OnGraphLaidOut() // /// <summary> /// Handles the GraphLaidOut event on ThisWorkbook. /// </summary> /// /// <param name="e"> /// Standard event argument. /// </param> /// /// <param name="oVertexTable"> /// Vertex table. /// </param> /// /// <param name="oDataBodyRange"> /// Data body range within the vertex table. /// </param> /// /// <param name="oVisibleDataBodyRange"> /// Visible data body range, which may contain multiple areas. /// </param> /// /// <param name="oIDColumnData"> /// ID column data from the vertex table. This excludes the header. /// </param> /// /// <param name="oXColumn"> /// X-coordinate column from the vertex table. This is the entire column, /// including the header. /// </param> /// /// <param name="oYColumn"> /// Y-coordinate column from the vertex table. This is the entire column, /// including the header. /// </param> //************************************************************************* private void OnGraphLaidOut( GraphLaidOutEventArgs e, Microsoft.Office.Interop.Excel.ListObject oVertexTable, Microsoft.Office.Interop.Excel.Range oDataBodyRange, Microsoft.Office.Interop.Excel.Range oVisibleDataBodyRange, Microsoft.Office.Interop.Excel.Range oIDColumnData, Microsoft.Office.Interop.Excel.ListColumn oXColumn, Microsoft.Office.Interop.Excel.ListColumn oYColumn ) { Debug.Assert(e != null); Debug.Assert(oVertexTable != null); Debug.Assert(oDataBodyRange != null); Debug.Assert(oVisibleDataBodyRange != null); Debug.Assert(oIDColumnData != null); Debug.Assert(oXColumn != null); Debug.Assert(oYColumn != null); AssertValid(); // Read the ID column. Object [,] aoIDValues = ExcelUtil.GetRangeValues(oIDColumnData); // Read the lock column if it's available. This excludes the header. // (Locked vertices should not have their locations changed.) Microsoft.Office.Interop.Excel.Range oLockedColumnData; Object [,] aoLockedValues = null; if ( ExcelUtil.TryGetTableColumnData(oVertexTable, VertexTableColumnNames.Locked, out oLockedColumnData) ) { aoLockedValues = ExcelUtil.GetRangeValues(oLockedColumnData); } Int32 iDataBodyRangeRow = oDataBodyRange.Row; Int32 iXColumnOneBased = oXColumn.Range.Column; Int32 iYColumnOneBased = oYColumn.Range.Column; // Create an object that converts a vertex location between coordinates // used in the NodeXL graph and coordinates used in the worksheet. VertexLocationConverter oVertexLocationConverter = new VertexLocationConverter(e.GraphRectangle); // Loop through the areas in the visible range. foreach (Microsoft.Office.Interop.Excel.Range oVisibleDataArea in oVisibleDataBodyRange.Areas) { // Get the row numbers for the ID and lock columns. (These are // with respect to the ID and lock values.) Int32 iFirstIDRowOneBased = oVisibleDataArea.Row - iDataBodyRangeRow + 1; Int32 iLastIDRowOneBased = iFirstIDRowOneBased + oVisibleDataArea.Rows.Count - 1; Int32 iRows = iLastIDRowOneBased - iFirstIDRowOneBased + 1; // Get the row numbers for the location values within the visible // area. (These are with respect to the worksheet.) Int32 iFirstXYRowOneBased = oVisibleDataArea.Row; Int32 iLastXYRowOneBased = iFirstXYRowOneBased + iRows - 1; // Read the old location formulas within the visible area. (To // avoid overwriting locked X,Y values that are formulas, read // formulas and not values.) Microsoft.Office.Interop.Excel.Range oXRange = this.get_Range( this.Cells[iFirstXYRowOneBased, iXColumnOneBased], this.Cells[iLastXYRowOneBased, iXColumnOneBased] ); Microsoft.Office.Interop.Excel.Range oYRange = this.get_Range( this.Cells[iFirstXYRowOneBased, iYColumnOneBased], this.Cells[iLastXYRowOneBased, iYColumnOneBased] ); Object [,] aoXFormulas = ExcelUtil.GetRangeFormulas(oXRange); Object [,] aoYFormulas = ExcelUtil.GetRangeFormulas(oYRange); for (Int32 iIDRowOneBased = iFirstIDRowOneBased; iIDRowOneBased <= iLastIDRowOneBased; iIDRowOneBased++) { if ( aoLockedValues != null && VertexIsLocked(aoLockedValues, iIDRowOneBased) ) { // The old location shouldn't be changed. continue; } // Get this row's ID. Int32 iID; if ( !m_oSheets1And2Helper.TryGetRowID( aoIDValues, iIDRowOneBased, out iID) ) { // The user may have added a row since the workbook was // read into the graph. Ignore this row. continue; } // Get the vertex object corresponding to the ID. IIdentityProvider oVertex; if ( !e.VertexIDDictionary.TryGetValue(iID, out oVertex) ) { continue; } // Convert the vertex location to workbook coordinates. Single fWorkbookX, fWorkbookY; Debug.Assert(oVertex is IVertex); oVertexLocationConverter.GraphToWorkbook( ( (IVertex)oVertex ).Location, out fWorkbookX, out fWorkbookY); // Store the vertex locations in the arrays. Int32 iXYFormulasRowOneBased = iIDRowOneBased - iFirstIDRowOneBased + 1; aoXFormulas[iXYFormulasRowOneBased, 1] = fWorkbookX; aoYFormulas[iXYFormulasRowOneBased, 1] = fWorkbookY; } // Set the new vertex locations in the worksheet. oXRange.Formula = aoXFormulas; oYRange.Formula = aoYFormulas; } }
OnGraphLaidOut ( GraphLaidOutEventArgs e, Microsoft.Office.Interop.Excel.ListObject oVertexTable, Microsoft.Office.Interop.Excel.Range oDataBodyRange, Microsoft.Office.Interop.Excel.Range oVisibleDataBodyRange, Microsoft.Office.Interop.Excel.Range oIDColumnData, Microsoft.Office.Interop.Excel.ListColumn oXColumn, Microsoft.Office.Interop.Excel.ListColumn oYColumn ) { Debug.Assert(e != null); Debug.Assert(oVertexTable != null); Debug.Assert(oDataBodyRange != null); Debug.Assert(oVisibleDataBodyRange != null); Debug.Assert(oIDColumnData != null); Debug.Assert(oXColumn != null); Debug.Assert(oYColumn != null); AssertValid(); // Read the ID column. Object [,] aoIDValues = ExcelUtil.GetRangeValues(oIDColumnData); // Read the lock column if it's available. This excludes the header. // (Locked vertices should not have their locations changed.) Microsoft.Office.Interop.Excel.Range oLockedColumnData; Object [,] aoLockedValues = null; if (ExcelUtil.TryGetTableColumnData(oVertexTable, VertexTableColumnNames.Locked, out oLockedColumnData)) { aoLockedValues = ExcelUtil.GetRangeValues(oLockedColumnData); } Int32 iDataBodyRangeRow = oDataBodyRange.Row; Int32 iXColumnOneBased = oXColumn.Range.Column; Int32 iYColumnOneBased = oYColumn.Range.Column; // Create an object that converts a vertex location between coordinates // used in the NodeXL graph and coordinates used in the worksheet. VertexLocationConverter oVertexLocationConverter = new VertexLocationConverter(e.GraphRectangle); // Loop through the areas in the visible range. foreach (Microsoft.Office.Interop.Excel.Range oVisibleDataArea in oVisibleDataBodyRange.Areas) { // Get the row numbers for the ID and lock columns. (These are // with respect to the ID and lock values.) Int32 iFirstIDRowOneBased = oVisibleDataArea.Row - iDataBodyRangeRow + 1; Int32 iLastIDRowOneBased = iFirstIDRowOneBased + oVisibleDataArea.Rows.Count - 1; Int32 iRows = iLastIDRowOneBased - iFirstIDRowOneBased + 1; // Get the row numbers for the location values within the visible // area. (These are with respect to the worksheet.) Int32 iFirstXYRowOneBased = oVisibleDataArea.Row; Int32 iLastXYRowOneBased = iFirstXYRowOneBased + iRows - 1; // Read the old location formulas within the visible area. (To // avoid overwriting locked X,Y values that are formulas, read // formulas and not values.) Microsoft.Office.Interop.Excel.Range oXRange = this.get_Range( this.Cells[iFirstXYRowOneBased, iXColumnOneBased], this.Cells[iLastXYRowOneBased, iXColumnOneBased] ); Microsoft.Office.Interop.Excel.Range oYRange = this.get_Range( this.Cells[iFirstXYRowOneBased, iYColumnOneBased], this.Cells[iLastXYRowOneBased, iYColumnOneBased] ); Object [,] aoXFormulas = ExcelUtil.GetRangeFormulas(oXRange); Object [,] aoYFormulas = ExcelUtil.GetRangeFormulas(oYRange); for (Int32 iIDRowOneBased = iFirstIDRowOneBased; iIDRowOneBased <= iLastIDRowOneBased; iIDRowOneBased++) { if (aoLockedValues != null && VertexIsLocked(aoLockedValues, iIDRowOneBased)) { // The old location shouldn't be changed. continue; } // Get this row's ID. Int32 iID; if (!m_oSheets1And2Helper.TryGetRowID( aoIDValues, iIDRowOneBased, out iID)) { // The user may have added a row since the workbook was // read into the graph. Ignore this row. continue; } // Get the vertex object corresponding to the ID. IIdentityProvider oVertex; if (!e.VertexIDDictionary.TryGetValue(iID, out oVertex)) { continue; } // Convert the vertex location to workbook coordinates. Single fWorkbookX, fWorkbookY; Debug.Assert(oVertex is IVertex); oVertexLocationConverter.GraphToWorkbook( ((IVertex)oVertex).Location, out fWorkbookX, out fWorkbookY); // Store the vertex locations in the arrays. Int32 iXYFormulasRowOneBased = iIDRowOneBased - iFirstIDRowOneBased + 1; aoXFormulas[iXYFormulasRowOneBased, 1] = fWorkbookX; aoYFormulas[iXYFormulasRowOneBased, 1] = fWorkbookY; } // Set the new vertex locations in the worksheet. oXRange.Formula = aoXFormulas; oYRange.Formula = aoYFormulas; } }