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