TryGetMarkedColumnData
(
out Microsoft.Office.Interop.Excel.Range oMarkedColumnData
)
{
AssertValid();
oMarkedColumnData = null;
Microsoft.Office.Interop.Excel.ListColumn oMarkedColumn;
return(
ExcelTableUtil.TryGetOrAddTableColumn(Vertices.InnerObject,
VertexTableColumnNames.IsMarked,
ExcelTableUtil.AutoColumnWidth, null, out oMarkedColumn)
&&
ExcelTableUtil.TryGetTableColumnData(oMarkedColumn,
out oMarkedColumnData)
);
}
GetAttributeColumn
(
ListObject oTable,
String sAttribute,
out ListColumn oAttributeColumn,
out Range oAttributeColumnData
)
{
Debug.Assert(oTable != null);
Debug.Assert(!String.IsNullOrEmpty(sAttribute));
if (!ExcelTableUtil.TryGetTableColumn(oTable, sAttribute,
out oAttributeColumn))
{
if (!ExcelTableUtil.TryAddTableColumn(oTable, sAttribute,
ExcelTableUtil.AutoColumnWidth, null, out oAttributeColumn))
{
goto CannotGetColumn;
}
// Wrap the text in the new column's header.
ExcelTableUtil.WrapTableColumnHeader(oAttributeColumn);
// This sometimes wraps a single-word header. Fix it.
oAttributeColumn.Range.EntireColumn.AutoFit();
}
if (ExcelTableUtil.TryGetTableColumnData(oAttributeColumn,
out oAttributeColumnData))
{
// Success.
return;
}
CannotGetColumn:
throw new WorkbookFormatException(
"The " + sAttribute + " column couldn't be added."
);
}
TryGetVertexTableAndVisibleColumnData
(
Workbook oWorkbook,
out ListObject oVertexTable,
out Range oVisibleNameColumnData,
out Range oVisibleSubgraphImageColumnData
)
{
Debug.Assert(oWorkbook != null);
oVertexTable = null;
oVisibleNameColumnData = null;
oVisibleSubgraphImageColumnData = null;
Range oNameColumnData, oSubgraphImageColumnData;
return(
// If the vertex table, the name column data, or the image column
// data aren't available, nothing can be done.
ExcelTableUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oVertexTable)
&&
ExcelTableUtil.TryGetTableColumnData(oVertexTable,
VertexTableColumnNames.VertexName, out oNameColumnData)
&&
TryGetSubgraphImageColumnData(oVertexTable,
out oSubgraphImageColumnData)
// Reduce the name and image column data to visible areas only.
&&
ExcelUtil.TryGetVisibleRange(oNameColumnData,
out oVisibleNameColumnData)
&&
ExcelUtil.TryGetVisibleRange(oSubgraphImageColumnData,
out oVisibleSubgraphImageColumnData)
);
}
TryGetRequiredColumnInformation
(
GraphMetricColumn oGraphMetricColumn,
ListObject oTable,
out Range oVisibleColumnData
)
{
Debug.Assert(oGraphMetricColumn != null);
Debug.Assert(oTable != null);
AssertValid();
oVisibleColumnData = null;
// Add the specified column if it's not already present.
String sColumnName = oGraphMetricColumn.ColumnName;
String sColumnStyle = oGraphMetricColumn.Style;
Microsoft.Office.Interop.Excel.ListColumn oColumn;
if (
!ExcelTableUtil.TryGetTableColumn(oTable, sColumnName, out oColumn)
&&
!ExcelTableUtil.TryAddTableColumn(oTable, sColumnName,
oGraphMetricColumn.ColumnWidthChars, sColumnStyle, out oColumn)
)
{
// Give up.
return(false);
}
Range oColumnData;
if (!ExcelTableUtil.TryGetTableColumnData(oTable, sColumnName,
out oColumnData))
{
return(false);
}
ExcelUtil.SetRangeStyle(oColumnData, sColumnStyle);
String sNumberFormat = oGraphMetricColumn.NumberFormat;
if (sNumberFormat != null)
{
oColumnData.NumberFormat = sNumberFormat;
}
// Wrapping text makes Range.set_Value() very slow, so turn it off.
oColumn.Range.WrapText = false;
// But wrap the text in the column's header.
ExcelTableUtil.WrapTableColumnHeader(oColumn);
// Get the visible range.
if (!ExcelUtil.TryGetVisibleRange(oColumnData,
out oVisibleColumnData))
{
return(false);
}
return(true);
}
FillVertexTable
(
ListObject oVertexTable,
HashSet <String> oUniqueVertexNames
)
{
Debug.Assert(oVertexTable != null);
Debug.Assert(oUniqueVertexNames != null);
AssertValid();
// There may already be some vertex names in the table. For each
// existing name, remove the name from the HashSet.
Int32 iExistingRows = 0;
Range oVertexNameRange;
if (ExcelTableUtil.TryGetTableColumnData(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameRange))
{
iExistingRows = oVertexNameRange.Rows.Count;
// Read the vertex names all at once.
Object [,] aoVertexNameValues =
ExcelUtil.GetRangeValues(oVertexNameRange);
// Loop through the vertices.
for (Int32 iRowOneBased = 1; iRowOneBased <= iExistingRows;
iRowOneBased++)
{
// Get the vertex name and remove it from the HashSet.
String sVertexName;
if (ExcelUtil.TryGetNonEmptyStringFromCell(
aoVertexNameValues, iRowOneBased, 1, out sVertexName))
{
oUniqueVertexNames.Remove(sVertexName);
}
}
}
// Now create an array for the vertices that remain in the HashSet.
// These are vertices that were in the edge table but not the vertex
// table.
Int32 iRowsToAdd = oUniqueVertexNames.Count;
if (iRowsToAdd == 0)
{
return;
}
String [,] asAddedVertexNameValues = new String [iRowsToAdd, 1];
Int32 iIndex = 0;
foreach (String sUniqueVertexName in oUniqueVertexNames)
{
asAddedVertexNameValues[iIndex, 0] = sUniqueVertexName;
iIndex++;
}
// The table may be empty or contain empty rows. If so, the remaining
// vertices should be appended after the last non-empty row.
Int32 iLastNonEmptyRowOneBased;
Range oDataBodyRange = oVertexTable.DataBodyRange;
if (
oDataBodyRange == null
||
!ExcelUtil.TryGetLastNonEmptyRow(oDataBodyRange,
out iLastNonEmptyRowOneBased)
)
{
// There were no non-empty data rows in the table. Use an offset
// of 1 from the header row instead.
oDataBodyRange = oVertexTable.HeaderRowRange;
iExistingRows = 1;
}
else
{
iExistingRows = iLastNonEmptyRowOneBased - oDataBodyRange.Row + 1;
}
// Get the index of the vertex name column.
ListColumn oVertexNameColumn;
if (!ExcelTableUtil.TryGetTableColumn(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameColumn))
{
// This can't happen, because GetRequiredTables() has
// verified that the column exists.
Debug.Assert(false);
}
Int32 iVertexNameColumnIndexOneBased = oVertexNameColumn.Index;
Debug.Assert(oVertexTable.Parent is Worksheet);
Worksheet oVertexWorksheet = (Worksheet)oVertexTable.Parent;
Range oAddedVertexNameRange = oVertexWorksheet.get_Range(
(Range)oDataBodyRange.Cells[iExistingRows + 1,
iVertexNameColumnIndexOneBased],
(Range)oDataBodyRange.Cells[iExistingRows + iRowsToAdd,
iVertexNameColumnIndexOneBased]
);
oAddedVertexNameRange.set_Value(
Missing.Value, asAddedVertexNameValues);
}
ImportGraph
(
IGraph sourceGraph,
String [] edgeAttributes,
String [] vertexAttributes,
Boolean clearTablesFirst,
Microsoft.Office.Interop.Excel.Workbook destinationNodeXLWorkbook
)
{
Debug.Assert(sourceGraph != null);
Debug.Assert(destinationNodeXLWorkbook != null);
if (clearTablesFirst)
{
NodeXLWorkbookUtil.ClearAllNodeXLTables(destinationNodeXLWorkbook);
}
// Get the required table that contains edge data. GetEdgeTable()
// throws an exception if the table is missing.
EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader();
ListObject oEdgeTable =
oEdgeWorksheetReader.GetEdgeTable(destinationNodeXLWorkbook);
// Get the required columns.
Range oVertex1NameColumnData = null;
Range oVertex2NameColumnData = null;
if (
!ExcelTableUtil.TryGetTableColumnData(oEdgeTable,
EdgeTableColumnNames.Vertex1Name, out oVertex1NameColumnData)
||
!ExcelTableUtil.TryGetTableColumnData(oEdgeTable,
EdgeTableColumnNames.Vertex2Name, out oVertex2NameColumnData)
)
{
ErrorUtil.OnMissingColumn();
}
// Import the edges and their attributes into the workbook.
ImportEdges(sourceGraph, edgeAttributes, oEdgeTable,
oVertex1NameColumnData, oVertex2NameColumnData, !clearTablesFirst);
// Populate the vertex worksheet with the name of each unique vertex in
// the edge worksheet.
(new VertexWorksheetPopulator()).PopulateVertexWorksheet(
destinationNodeXLWorkbook, false);
// Get the table that contains vertex data.
ListObject oVertexTable;
Range oVertexNameColumnData = null;
Range oVisibilityColumnData = null;
if (
!ExcelTableUtil.TryGetTable(destinationNodeXLWorkbook,
WorksheetNames.Vertices, TableNames.Vertices, out oVertexTable)
||
!ExcelTableUtil.TryGetTableColumnData(oVertexTable,
VertexTableColumnNames.VertexName, out oVertexNameColumnData)
||
!ExcelTableUtil.TryGetTableColumnData(oVertexTable,
CommonTableColumnNames.Visibility, out oVisibilityColumnData)
)
{
ErrorUtil.OnMissingColumn();
}
// Import isolated vertices and the attributes for all the graph's
// vertices.
ImportVertices(sourceGraph, vertexAttributes, oVertexTable,
oVertexNameColumnData, oVisibilityColumnData);
}
WriteAllSettings()
{
AssertValid();
Debug.Assert(m_oSettings != null);
ListObject oPerWorkbookSettingsTable;
if (!TryGetPerWorkbookSettingsTable(out oPerWorkbookSettingsTable))
{
return;
}
// Don't do this. For some reason, it causes the table to misbehave
// in the ExcelUtil.SetRangeValues() calls below when the workbook is
// saved via TaskAutomator.
//
// The consequence of not clearing the table is that names and values
// that are no longer used will remain in the table.
//
// ExcelTableUtil.ClearTable(oPerWorkbookSettingsTable);
// Attempt to get the optional table columns that contain the settings.
Range oNameColumnData, oValueColumnData;
if (
!ExcelTableUtil.TryGetTableColumnData(oPerWorkbookSettingsTable,
PerWorkbookSettingsTableColumnNames.Name, out oNameColumnData)
||
!ExcelTableUtil.TryGetTableColumnData(oPerWorkbookSettingsTable,
PerWorkbookSettingsTableColumnNames.Value,
out oValueColumnData)
)
{
return;
}
// Copy the settings to arrays.
Int32 iSettings = m_oSettings.Count;
Object [,] aoNameColumnValues =
ExcelUtil.GetSingleColumn2DArray(iSettings);
Object [,] aoValueColumnValues =
ExcelUtil.GetSingleColumn2DArray(iSettings);
Int32 i = 1;
foreach (KeyValuePair <String, Object> oKeyValuePair in m_oSettings)
{
aoNameColumnValues[i, 1] = oKeyValuePair.Key;
aoValueColumnValues[i, 1] =
ExcelUtil.RemoveFormulaFromValue(oKeyValuePair.Value);
i++;
}
// Write the arrays to the columns.
ExcelUtil.SetRangeValues(oNameColumnData, aoNameColumnValues);
ExcelUtil.SetRangeValues(oValueColumnData, aoValueColumnValues);
}
SetLocations <TLocationInfo>
(
ICollection <TLocationInfo> locationInfo,
System.Drawing.Rectangle graphRectangle,
String xColumnName,
String yColumnName,
String lockedColumnName,
TryGetRowIDAndLocation <TLocationInfo> tryGetRowIDAndLocation
)
{
Debug.Assert(locationInfo != null);
Debug.Assert(!String.IsNullOrEmpty(xColumnName));
Debug.Assert(!String.IsNullOrEmpty(yColumnName));
Debug.Assert(tryGetRowIDAndLocation != null);
AssertValid();
if (locationInfo.Count == 0)
{
return;
}
// Gather some required information.
ListObject oTable = m_oTable.InnerObject;
Range oXColumnData, oYColumnData, oLockedColumnData;
Object [,] aoLockedColumnValues = null;
// The key is a row ID and the value is the row's one-based row number
// relative to the worksheet.
Dictionary <Int32, Int32> oRowIDDictionary;
if (
TryGetAllRowIDs(out oRowIDDictionary)
&&
ExcelTableUtil.TryGetTableColumnData(oTable, xColumnName,
out oXColumnData)
&&
ExcelTableUtil.TryGetTableColumnData(oTable, yColumnName,
out oYColumnData)
&&
(
lockedColumnName == null
||
ExcelTableUtil.TryGetTableColumnDataAndValues(oTable,
lockedColumnName, out oLockedColumnData,
out aoLockedColumnValues)
)
)
{
// Activate this worksheet, because writing to an inactive
// worksheet causes problems with the selection in Excel.
ExcelActiveWorksheetRestorer oExcelActiveWorksheetRestorer =
GetExcelActiveWorksheetRestorer();
ExcelActiveWorksheetState oExcelActiveWorksheetState =
oExcelActiveWorksheetRestorer.ActivateWorksheet(
m_oWorksheet.InnerObject);
try
{
SetLocations <TLocationInfo>(locationInfo, graphRectangle,
tryGetRowIDAndLocation, oRowIDDictionary, oXColumnData,
oYColumnData, aoLockedColumnValues);
}
finally
{
oExcelActiveWorksheetRestorer.Restore(
oExcelActiveWorksheetState);
}
}
}