//*************************************************************************
// Constructor: ReadWorkbookContext()
//
/// <summary>
/// Initializes a new instance of the <see cref="ReadWorkbookContext" />
/// class.
/// </summary>
//*************************************************************************
public ReadWorkbookContext()
{
m_bIgnoreVertexLocations = true;
m_bFillIDColumns = false;
m_bPopulateVertexWorksheet = false;
m_bReadEdgeWeights = false;
m_bReadGroups = false;
m_bSaveGroupVertices = false;
m_bReadVertexLabels = false;
m_bReadEdgeLabels = false;
m_bReadVertexImages = false;
m_oDefaultVertexImageSize = new Nullable <Single>();
m_eDefaultVertexShape = VertexShape.Disk;
m_bReadAllEdgeAndVertexColumns = false;
m_oGraphRectangle = Rectangle.FromLTRB(0, 0, 100, 100);
m_oColorConverter2 = new ColorConverter2();
m_oBooleanConverter = new BooleanConverter();
m_oEdgeWidthConverter = new EdgeWidthConverter();
m_oEdgeStyleConverter = new EdgeStyleConverter();
m_oVertexRadiusConverter = new VertexRadiusConverter();
m_oVertexLocationConverter =
new VertexLocationConverter(m_oGraphRectangle);
m_oVertexNameDictionary = new Dictionary <String, IVertex>();
m_oEdgeRowIDDictionary = new Dictionary <Int32, IIdentityProvider>();
m_oVertexRowIDDictionary = new Dictionary <Int32, IIdentityProvider>();
AssertValid();
}
//*************************************************************************
// Constructor: ReadWorkbookContext()
//
/// <summary>
/// Initializes a new instance of the <see cref="ReadWorkbookContext" />
/// class.
/// </summary>
//*************************************************************************
public ReadWorkbookContext()
{
m_bIgnoreVertexLocations = true;
m_bFillIDColumns = false;
m_bPopulateVertexWorksheet = false;
m_bReadEdgeWeights = false;
m_bReadGroups = false;
m_bSaveGroupVertices = false;
m_bReadVertexLabels = false;
m_bReadEdgeLabels = false;
m_bReadVertexImages = false;
m_oDefaultVertexImageSize = new Nullable<Single>();
m_eDefaultVertexShape = VertexShape.Disk;
m_bReadAllEdgeAndVertexColumns = false;
m_oGraphRectangle = Rectangle.FromLTRB(0, 0, 100, 100);
m_oColorConverter2 = new ColorConverter2();
m_oBooleanConverter = new BooleanConverter();
m_oEdgeWidthConverter = new EdgeWidthConverter();
m_oEdgeStyleConverter = new EdgeStyleConverter();
m_oVertexRadiusConverter = new VertexRadiusConverter();
m_oVertexLocationConverter =
new VertexLocationConverter(m_oGraphRectangle);
m_oVertexNameDictionary = new Dictionary<String, IVertex>();
m_oEdgeRowIDDictionary = new Dictionary<Int32, IIdentityProvider>();
m_oVertexRowIDDictionary = new Dictionary<Int32, IIdentityProvider>();
AssertValid();
}
public void TearDown()
{
m_oVertexLocationConverter = null;
}
public void SetUp()
{
m_oVertexLocationConverter =
new VertexLocationConverter(GraphRectangle);
}
//*************************************************************************
// Constructor: VertexLocationConverterTest()
//
/// <summary>
/// Initializes a new instance of the <see
/// cref="VertexLocationConverterTest" /> class.
/// </summary>
//*************************************************************************
public VertexLocationConverterTest()
{
m_oVertexLocationConverter = null;
}
public void TestWorkbookToGraph4()
{
// Empty rectangle.
m_oVertexLocationConverter = new VertexLocationConverter(
Rectangle.FromLTRB(100, 200, 100, 200) );
Single WorkbookX = VertexLocationConverter.MaximumXYWorkbook;
Single WorkbookY = VertexLocationConverter.MaximumXYWorkbook;
Single ExpectedGraphX = 100;
Single ExpectedGraphY = 200;
PointF oGraphPointF = m_oVertexLocationConverter.WorkbookToGraph(
WorkbookX, WorkbookY);
Assert.AreEqual(ExpectedGraphX, oGraphPointF.X);
Assert.AreEqual(ExpectedGraphY, oGraphPointF.Y);
}
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);
}
ReadLocation
(
ExcelTableReader.ExcelTableRow oRow,
VertexLocationConverter oVertexLocationConverter,
IVertex oVertex
)
{
Debug.Assert(oRow != null);
Debug.Assert(oVertexLocationConverter != null);
Debug.Assert(oVertex != null);
AssertValid();
String sX;
Boolean bHasX = oRow.TryGetNonEmptyStringFromCell(
VertexTableColumnNames.X, out sX);
String sY;
Boolean bHasY = oRow.TryGetNonEmptyStringFromCell(
VertexTableColumnNames.Y, out sY);
if (bHasX != bHasY)
{
// X or Y alone won't do.
goto Error;
}
if (!bHasX && !bHasY)
{
return(false);
}
Single fX, fY;
if (!Single.TryParse(sX, out fX) || !Single.TryParse(sY, out fY))
{
goto Error;
}
// Transform the location from workbook coordinates to graph
// coordinates.
oVertex.Location = oVertexLocationConverter.WorkbookToGraph(fX, fY);
return(true);
Error:
Range oInvalidCell = oRow.GetRangeForCell(
VertexTableColumnNames.X);
OnWorkbookFormatError(String.Format(
"There is a problem with the vertex location at {0}. If you"
+ " enter a vertex location, it must include both X and Y"
+ " numbers. Any numbers are acceptable, although {1} is used"
+ " for any number less than {1} and and {2} is used for any"
+ " number greater than {2}."
,
ExcelUtil.GetRangeAddress(oInvalidCell),
VertexLocationConverter.MinimumXYWorkbook.ToString(
ExcelTemplateForm.Int32Format),
VertexLocationConverter.MaximumXYWorkbook.ToString(
ExcelTemplateForm.Int32Format)
),
oInvalidCell
);
// Make the compiler happy.
return(false);
}
//*************************************************************************
// 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;
}
}
//*************************************************************************
// 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;
}
}
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;
}
}
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;
}
}
//*************************************************************************
// Method: ReadLocation()
//
/// <summary>
/// If a location has been specified for a vertex, sets the vertex's
/// location.
/// </summary>
///
/// <param name="oRow">
/// Row containing the vertex data.
/// </param>
///
/// <param name="oVertexLocationConverter">
/// Object that converts a vertex location between coordinates used in the
/// Excel workbook and coordinates used in the NodeXL graph.
/// </param>
///
/// <param name="oVertex">
/// Vertex to set the location on.
/// </param>
///
/// <returns>
/// true if a location was specified.
/// </returns>
//*************************************************************************
protected Boolean ReadLocation(
ExcelTableReader.ExcelTableRow oRow,
VertexLocationConverter oVertexLocationConverter,
IVertex oVertex
)
{
Debug.Assert(oRow != null);
Debug.Assert(oVertexLocationConverter != null);
Debug.Assert(oVertex != null);
AssertValid();
String sX;
Boolean bHasX = oRow.TryGetNonEmptyStringFromCell(
VertexTableColumnNames.X, out sX);
String sY;
Boolean bHasY = oRow.TryGetNonEmptyStringFromCell(
VertexTableColumnNames.Y, out sY);
if (bHasX != bHasY)
{
// X or Y alone won't do.
goto Error;
}
if (!bHasX && !bHasY)
{
return (false);
}
Single fX, fY;
if ( !Single.TryParse(sX, out fX) || !Single.TryParse(sY, out fY) )
{
goto Error;
}
// Transform the location from workbook coordinates to graph
// coordinates.
oVertex.Location = oVertexLocationConverter.WorkbookToGraph(fX, fY);
return (true);
Error:
Range oInvalidCell = oRow.GetRangeForCell(
VertexTableColumnNames.X);
OnWorkbookFormatError( String.Format(
"There is a problem with the vertex location at {0}. If you"
+ " enter a vertex location, it must include both X and Y"
+ " numbers. Any numbers are acceptable, although {1} is used"
+ " for any number less than {1} and and {2} is used for any"
+ " number greater than {2}."
,
ExcelUtil.GetRangeAddress(oInvalidCell),
VertexLocationConverter.MinimumXYWorkbook.ToString(
ExcelTemplateForm.Int32Format),
VertexLocationConverter.MaximumXYWorkbook.ToString(
ExcelTemplateForm.Int32Format)
),
oInvalidCell
);
// Make the compiler happy.
return (false);
}
