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);
}
}
EndAutoFill
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
ListObject oEdgeTable,
ListObject oVertexTable,
ExcelHiddenColumns oHiddenEdgeColumns,
ExcelHiddenColumns oHiddenVertexColumns
)
{
Debug.Assert(oWorkbook != null);
if (oEdgeTable != null && oHiddenEdgeColumns != null)
{
ExcelColumnHider.RestoreHiddenColumns(oEdgeTable,
oHiddenEdgeColumns);
}
if (oVertexTable != null && oHiddenVertexColumns != null)
{
ExcelColumnHider.RestoreHiddenColumns(oVertexTable,
oHiddenVertexColumns);
}
SetScreenUpdating(oWorkbook, true);
}
CopyTableToNewNodeXLWorkbook
(
String sWorksheetName,
String sTableName,
String sTemplatePath,
ref Workbook oNewNodeXLWorkbook
)
{
Debug.Assert(!String.IsNullOrEmpty(sWorksheetName));
Debug.Assert(!String.IsNullOrEmpty(sTableName));
Debug.Assert(!String.IsNullOrEmpty(sTemplatePath));
AssertValid();
ListObject oTable;
Range oSelectedTableRange;
if (
!ExcelTableUtil.TryGetSelectedTableRange(m_oWorkbookToExport,
sWorksheetName, sTableName, out oTable,
out oSelectedTableRange)
||
ExcelTableUtil.VisibleTableRangeIsEmpty(oTable)
)
{
return;
}
Range oVisibleSelectedTableRange;
// CopyRowsToNewNodeXLWorkbook() can handle hidden rows, but not hidden
// columns. Temporarily show all hidden columns in the table.
ExcelHiddenColumns oHiddenColumns =
ExcelColumnHider.ShowHiddenColumns(oTable);
try
{
if (ExcelUtil.TryGetVisibleRange(oSelectedTableRange,
out oVisibleSelectedTableRange))
{
// Create the new workbook if necessary and copy the table's
// selected rows to it.
if (oNewNodeXLWorkbook == null)
{
oNewNodeXLWorkbook =
m_oWorkbookToExport.Application.Workbooks.Add(
sTemplatePath);
}
CopyRowsToNewNodeXLWorkbook(oTable, oVisibleSelectedTableRange,
oNewNodeXLWorkbook);
}
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oTable, oHiddenColumns);
}
}
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 (
ExcelTableUtil.TryGetTable(workbook, WorksheetNames.Groups,
TableNames.Groups, out oGroupTable)
&&
ExcelTableUtil.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);
}
}
}
TryStartAutoFill
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
Boolean bShowVertexLabels,
String sVertexLabelColumnName,
out ListObject oEdgeTable,
out ListObject oVertexTable,
out ExcelHiddenColumns oHiddenEdgeColumns,
out ExcelHiddenColumns oHiddenVertexColumns
)
{
Debug.Assert(oWorkbook != null);
SetScreenUpdating(oWorkbook, false);
(new VertexWorksheetPopulator()).PopulateVertexWorksheet(
oWorkbook, false);
oEdgeTable = oVertexTable = null;
oHiddenEdgeColumns = oHiddenVertexColumns = null;
if (
!ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Edges,
TableNames.Edges, out oEdgeTable)
||
!ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oVertexTable)
)
{
return(false);
}
// Only visible cells are read and written to. Temporarily show all
// hidden columns in the tables.
oHiddenEdgeColumns = ExcelColumnHider.ShowHiddenColumns(oEdgeTable);
oHiddenVertexColumns = ExcelColumnHider.ShowHiddenColumns(
oVertexTable);
if (bShowVertexLabels)
{
TableColumnMapper.MapViaCopy(oVertexTable, sVertexLabelColumnName,
VertexTableColumnNames.Label);
}
return(true);
}
GetRowIDDictionary
(
ListObject oTable,
String sColumnName
)
{
Debug.Assert(oTable != null);
Debug.Assert(!String.IsNullOrEmpty(sColumnName));
Dictionary <Int32, Object> oRowIDDictionary =
new Dictionary <Int32, Object>();
// 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(oTable);
try
{
ExcelTableReader oExcelTableReader = new ExcelTableReader(oTable);
foreach (ExcelTableReader.ExcelTableRow oRow in
oExcelTableReader.GetRows())
{
Int32 iRowID;
Double dCellValue;
if (
oRow.TryGetInt32FromCell(CommonTableColumnNames.ID,
out iRowID)
&&
oRow.TryGetDoubleFromCell(sColumnName, out dCellValue)
)
{
oRowIDDictionary[iRowID] = dCellValue;
}
}
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oTable, oHiddenColumns);
}
return(oRowIDDictionary);
}
AutoFillByVertexCategory
(
Microsoft.Office.Interop.Excel.Workbook workbook,
String vertexCategoryColumnName,
Boolean showVertexLabels,
String vertexLabelColumnName
)
{
Debug.Assert(workbook != null);
Debug.Assert(!String.IsNullOrEmpty(vertexCategoryColumnName));
Debug.Assert(!showVertexLabels ||
!String.IsNullOrEmpty(vertexLabelColumnName));
ListObject oVertexTable = null;
ListObject oEdgeTable = null;
ExcelHiddenColumns oHiddenVertexColumns = null;
ExcelHiddenColumns oHiddenEdgeColumns = null;
try
{
if (
!TryStartAutoFill(workbook, showVertexLabels,
vertexLabelColumnName, out oEdgeTable, out oVertexTable,
out oHiddenEdgeColumns, out oHiddenVertexColumns)
||
ExcelUtil.VisibleTableRangeIsEmpty(oVertexTable)
)
{
return;
}
AutoFillByVertexCategory(workbook, oEdgeTable, oVertexTable,
vertexCategoryColumnName, showVertexLabels,
vertexLabelColumnName);
}
finally
{
EndAutoFill(workbook, oEdgeTable, oVertexTable,
oHiddenEdgeColumns, oHiddenVertexColumns);
}
}
ShowHiddenColumns
(
ListObject table
)
{
Debug.Assert(table != null);
ExcelHiddenColumns oExcelHiddenColumns = new ExcelHiddenColumns();
foreach (ListColumn oColumn in table.ListColumns)
{
if ( (Boolean)oColumn.Range.EntireColumn.Hidden )
{
oExcelHiddenColumns.AddLast(oColumn.Name);
ShowOrHideColumn(oColumn, true);
}
}
return (oExcelHiddenColumns);
}
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 (ExcelTableUtil.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 bLayoutAndZOrderSet;
try
{
ReadVertexTable(oVertexTable, readWorkbookContext, graph,
out bLayoutAndZOrderSet);
RemoveUnwantedIsolates(readWorkbookContext, graph);
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oVertexTable,
oHiddenColumns);
}
if (bLayoutAndZOrderSet)
{
// The layout and z-orders were specified for at least one
// vertex. The ByMetadataVertexSorter used by
// SortableLayoutBase and GraphDrawer requires that if an 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 order for each vertex that doesn't already specify
// one.
SetUnspecifiedVertexLayoutAndZOrders(graph);
// The layout and z-orders are ignored unless this key is added
// to the graph.
graph.SetValue(
ReservedMetadataKeys.SortableLayoutAndZOrderSet, null);
}
}
}
WriteGraphMetricColumnsToWorkbook
(
GraphMetricColumn [] graphMetricColumns,
Microsoft.Office.Interop.Excel.Workbook workbook
)
{
Debug.Assert(graphMetricColumns != null);
Debug.Assert(workbook != null);
AssertValid();
// Clear any worksheets that must be cleared before anything else is
// done.
ClearWorksheets(graphMetricColumns, workbook);
// (Note: Don't sort grapMetricColumns by worksheet name/table name in
// an effort to minimize worksheet switches in the code below. That
// would interfere with the column order specified by the
// IGraphMetricCalculator2 implementations.
// Create a dictionary of tables that have been written to. The key is
// the worksheet name + table name, and the value is a WrittenTableInfo
// object that contains information about the table.
Dictionary <String, WrittenTableInfo> oWrittenTables =
new Dictionary <String, WrittenTableInfo>();
// Loop through the columns.
String sCurrentWorksheetPlusTable = String.Empty;
ListObject oTable = null;
foreach (GraphMetricColumn oGraphMetricColumn in graphMetricColumns)
{
String sThisWorksheetPlusTable = oGraphMetricColumn.WorksheetName
+ oGraphMetricColumn.TableName;
if (sThisWorksheetPlusTable != sCurrentWorksheetPlusTable)
{
// This is a different table. Get its ListObject.
if (!TryGetTable(workbook, oGraphMetricColumn, out oTable))
{
continue;
}
sCurrentWorksheetPlusTable = sThisWorksheetPlusTable;
}
WrittenTableInfo oWrittenTableInfo;
if (!oWrittenTables.TryGetValue(sThisWorksheetPlusTable,
out oWrittenTableInfo))
{
// Show all the table's columns. If a graph metric column
// isn't visible, it can't be written to.
ExcelHiddenColumns oExcelHiddenColumns =
ExcelColumnHider.ShowHiddenColumns(oTable);
oWrittenTableInfo = new WrittenTableInfo();
oWrittenTableInfo.Table = oTable;
oWrittenTableInfo.HiddenColumns = oExcelHiddenColumns;
oWrittenTableInfo.Cleared = false;
oWrittenTables.Add(sThisWorksheetPlusTable, oWrittenTableInfo);
}
// Apparent Excel bug: Adding a column when the header row is not
// the default row height increases the header row height. Work
// around this by saving the height and restoring it below.
Double dHeaderRowHeight = (Double)oTable.HeaderRowRange.RowHeight;
// Write the column.
Debug.Assert(oTable != null);
if (oGraphMetricColumn is GraphMetricColumnWithID)
{
WriteGraphMetricColumnWithIDToWorkbook(
(GraphMetricColumnWithID)oGraphMetricColumn, oTable);
}
else if (oGraphMetricColumn is GraphMetricColumnOrdered)
{
if (!oWrittenTableInfo.Cleared)
{
// GraphMetricColumnOrdered columns require that the table
// be cleared before any graph metric values are written to
// it.
ExcelTableUtil.ClearTable(oTable);
oWrittenTableInfo.Cleared = true;
// Clear AutoFiltering, which interferes with writing an
// ordered list to the column.
ExcelTableUtil.ClearTableAutoFilters(oTable);
}
WriteGraphMetricColumnOrderedToWorkbook(
(GraphMetricColumnOrdered)oGraphMetricColumn, oTable);
}
else
{
Debug.Assert(false);
}
oTable.HeaderRowRange.RowHeight = dHeaderRowHeight;
}
// Restore any hidden columns in the tables that were written to.
foreach (KeyValuePair <String, WrittenTableInfo> oKeyValuePair in
oWrittenTables)
{
WrittenTableInfo oWrittenTableInfo = oKeyValuePair.Value;
ExcelColumnHider.RestoreHiddenColumns(oWrittenTableInfo.Table,
oWrittenTableInfo.HiddenColumns);
}
}
//*************************************************************************
// Method: TryStartAutoFill()
//
/// <summary>
/// Performs preliminary tasks required by every scheme.
/// </summary>
///
/// <param name="oWorkbook">
/// The workbook to autofill.
/// </param>
///
/// <param name="bShowVertexLabels">
/// true if vertex labels should be shown.
/// </param>
///
/// <param name="sVertexLabelColumnName">
/// The name of the vertex table column containing vertex labels. Used
/// only if <paramref name="bShowVertexLabels" /> is true.
/// </param>
///
/// <param name="oEdgeTable">
/// Where the edge table gets stored if true is returned.
/// </param>
///
/// <param name="oVertexTable">
/// Where the vertex table gets stored if true is returned.
/// </param>
///
/// <param name="oHiddenEdgeColumns">
/// Where information about the previously hidden edge columns gets stored.
/// </param>
///
/// <param name="oHiddenVertexColumns">
/// Where information about the previously hidden vertex columns gets
/// stored.
/// </param>
///
/// <returns>
/// true if autofill should continue.
/// </returns>
//*************************************************************************
private static Boolean TryStartAutoFill(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
Boolean bShowVertexLabels,
String sVertexLabelColumnName,
out ListObject oEdgeTable,
out ListObject oVertexTable,
out ExcelHiddenColumns oHiddenEdgeColumns,
out ExcelHiddenColumns oHiddenVertexColumns
)
{
Debug.Assert(oWorkbook != null);
SetScreenUpdating(oWorkbook, false);
( new VertexWorksheetPopulator() ).PopulateVertexWorksheet(
oWorkbook, false);
oEdgeTable = oVertexTable = null;
oHiddenEdgeColumns = oHiddenVertexColumns = null;
if (
!ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Edges,
TableNames.Edges, out oEdgeTable)
||
!ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oVertexTable)
)
{
return (false);
}
// Only visible cells are read and written to. Temporarily show all
// hidden columns in the tables.
oHiddenEdgeColumns = ExcelColumnHider.ShowHiddenColumns(oEdgeTable);
oHiddenVertexColumns = ExcelColumnHider.ShowHiddenColumns(
oVertexTable);
if (bShowVertexLabels)
{
TableColumnMapper.MapViaCopy(oVertexTable, sVertexLabelColumnName,
VertexTableColumnNames.Label);
}
return (true);
}
//*************************************************************************
// Method: EndAutoFill()
//
/// <summary>
/// Performs end tasks required by every scheme.
/// </summary>
///
/// <param name="oWorkbook">
/// The workbook to autofill.
/// </param>
///
/// <param name="oEdgeTable">
/// The edge table. Can be null.
/// </param>
///
/// <param name="oVertexTable">
/// The vertex table. Can be null.
/// </param>
///
/// <param name="oHiddenEdgeColumns">
/// Information about the previously hidden edge columns. Can be null.
/// </param>
///
/// <param name="oHiddenVertexColumns">
/// Where information about the previously hidden vertex columns. Can be
/// null.
/// </param>
//*************************************************************************
private static void EndAutoFill(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
ListObject oEdgeTable,
ListObject oVertexTable,
ExcelHiddenColumns oHiddenEdgeColumns,
ExcelHiddenColumns oHiddenVertexColumns
)
{
Debug.Assert(oWorkbook != null);
if (oEdgeTable != null && oHiddenEdgeColumns != null)
{
ExcelColumnHider.RestoreHiddenColumns(oEdgeTable,
oHiddenEdgeColumns);
}
if (oVertexTable != null && oHiddenVertexColumns != null)
{
ExcelColumnHider.RestoreHiddenColumns(oVertexTable,
oHiddenVertexColumns);
}
SetScreenUpdating(oWorkbook, true);
}
AutoFillByEdgeTimestamp
(
Microsoft.Office.Interop.Excel.Workbook workbook,
String edgeTimestampColumnName,
Boolean showVertexLabels,
String vertexLabelColumnName
)
{
Debug.Assert(workbook != null);
Debug.Assert(!String.IsNullOrEmpty(edgeTimestampColumnName));
Debug.Assert(!showVertexLabels ||
!String.IsNullOrEmpty(vertexLabelColumnName));
ListObject oVertexTable = null;
ListObject oEdgeTable = null;
ExcelHiddenColumns oHiddenVertexColumns = null;
ExcelHiddenColumns oHiddenEdgeColumns = null;
AutoFillWorkbookWithSchemeResults oAutoFillWorkbookWithSchemeResults =
new AutoFillWorkbookWithSchemeResults();
try
{
if (
!TryStartAutoFill(workbook, showVertexLabels,
vertexLabelColumnName, out oEdgeTable, out oVertexTable,
out oHiddenEdgeColumns, out oHiddenVertexColumns)
||
ExcelUtil.VisibleTableRangeIsEmpty(oEdgeTable)
)
{
return;
}
// Map the edge timestamp column to the edge color column.
Double dSourceCalculationNumber1, dSourceCalculationNumber2;
Int32 iDecimalPlaces;
if (!TableColumnMapper.TryMapToColor(oEdgeTable,
edgeTimestampColumnName, EdgeTableColumnNames.Color, false,
false, 0, 0, MinimumEdgeTimestampColor,
MaximumEdgeTimestampColor, false, false,
out dSourceCalculationNumber1, out dSourceCalculationNumber2,
out iDecimalPlaces
))
{
return;
}
// Fill in other columns with constants.
FillColumnsWithConstants(
oEdgeTable, EdgeTableColumnNames.Width, 2.0F,
oEdgeTable, CommonTableColumnNames.Alpha,
AlphaConverter.MaximumAlphaWorkbook,
oVertexTable, VertexTableColumnNames.Shape,
(new VertexShapeConverter()).GraphToWorkbook(
VertexShape.Circle),
oVertexTable, VertexTableColumnNames.Color,
(new ColorConverter2()).GraphToWorkbook(
Color.FromArgb(0, 0, 0)),
oVertexTable, VertexTableColumnNames.Radius, 1.5F,
oVertexTable, CommonTableColumnNames.Alpha,
AlphaConverter.MaximumAlphaWorkbook
);
// Save the results.
ListColumn oEdgeTimestampColumn;
ExcelUtil.TryGetTableColumn(oEdgeTable, edgeTimestampColumnName,
out oEdgeTimestampColumn);
oAutoFillWorkbookWithSchemeResults.SetEdgeTimestampResults(
edgeTimestampColumnName,
ExcelUtil.GetColumnFormat(oEdgeTimestampColumn),
dSourceCalculationNumber1, dSourceCalculationNumber2);
}
finally
{
(new PerWorkbookSettings(workbook)).
AutoFillWorkbookWithSchemeResults =
oAutoFillWorkbookWithSchemeResults;
EndAutoFill(workbook, oEdgeTable, oVertexTable,
oHiddenEdgeColumns, oHiddenVertexColumns);
}
}
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);
}
}
}
RestoreHiddenColumns
(
ListObject table,
ExcelHiddenColumns excelHiddenColumns
)
{
Debug.Assert(table != null);
Debug.Assert(excelHiddenColumns != null);
foreach (String sColumnName in excelHiddenColumns)
{
ListColumn oColumn;
if (ExcelTableUtil.TryGetTableColumn(table, sColumnName,
out oColumn))
{
ShowOrHideColumn(oColumn, false);
}
}
}
