ShowOrHideColumnGroup
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
ColumnGroups eColumnGroup,
Boolean bShow
)
{
Debug.Assert(oWorkbook != null);
#if DEBUG
// Verify that a single value in the ColumnGroup enumeration has been
// specified.
Double dLogColumnGroup = Math.Log((Double)eColumnGroup, 2);
Debug.Assert((Int32)dLogColumnGroup == dLogColumnGroup);
#endif
ListObject oTable;
if (TryGetColumnGroupTable(oWorkbook, eColumnGroup, out oTable))
{
ExcelColumnHider.ShowOrHideColumns(oTable,
GetColumnNames(oWorkbook, eColumnGroup), bShow);
if (eColumnGroup == ColumnGroups.VertexOtherColumns)
{
// Hiding the subgraph image column doesn't hide the images in
// the column.
SubgraphImageColumnPopulator.ShowOrHideSubgraphImages(
oWorkbook, bShow);
}
}
}
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);
}
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);
}
}
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);
}
AutoFillTable
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
AutoFillUserSettings oAutoFillUserSettings,
AutoFillWorkbookResults oAutoFillWorkbookResults,
String sWorksheetName,
String sTableName,
AutoFillTableMethod oAutoFillTableMethod
)
{
Debug.Assert(oWorkbook != null);
Debug.Assert(oAutoFillUserSettings != null);
Debug.Assert(oAutoFillWorkbookResults != null);
Debug.Assert(!String.IsNullOrEmpty(sWorksheetName));
Debug.Assert(!String.IsNullOrEmpty(sTableName));
Debug.Assert(oAutoFillTableMethod != null);
ListObject oTable;
ExcelHiddenColumns oHiddenColumns;
if (ExcelTableUtil.TryGetTable(oWorkbook, sWorksheetName, sTableName,
out oTable))
{
// The TableColumnMapper class that does the actual autofilling
// fills only visible cells. Temporarily show all hidden columns
// in the table.
oHiddenColumns = ExcelColumnHider.ShowHiddenColumns(oTable);
try
{
oAutoFillTableMethod(oTable, oAutoFillUserSettings,
oAutoFillWorkbookResults);
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oTable, oHiddenColumns);
}
}
}
ShowOrHideColumnGroup
(
Microsoft.Office.Interop.Excel.Workbook workbook,
ColumnGroup columnGroup,
Boolean show,
Boolean activateWorksheet
)
{
Debug.Assert(workbook != null);
ListObject oTable;
if (TryGetColumnGroupTable(workbook, columnGroup, out oTable))
{
ExcelColumnHider.ShowOrHideColumns(oTable,
GetColumnNames(workbook, columnGroup), show);
if (columnGroup == ColumnGroup.VertexOtherColumns)
{
// Hiding the subgraph image column doesn't hide the images in
// the column.
TableImagePopulator.ShowOrHideImagesInColumn(workbook,
WorksheetNames.Vertices,
VertexTableColumnNames.SubgraphImage, show);
}
if (activateWorksheet)
{
ExcelUtil.ActivateWorksheet(oTable);
}
(new PerWorkbookSettings(workbook)).SetColumnGroupVisibility(
columnGroup, show);
}
}
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);
}
}
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);
}
}
}
AutoFillWorkbookInternal
(
Microsoft.Office.Interop.Excel.Workbook oWorkbook,
AutoFillUserSettings oAutoFillUserSettings
)
{
Debug.Assert(oWorkbook != null);
Debug.Assert(oAutoFillUserSettings != null);
// Populate the vertex worksheet with the name of each unique vertex in
// the edge worksheet.
(new VertexWorksheetPopulator()).PopulateVertexWorksheet(
oWorkbook, false);
ListObject oTable;
ExcelHiddenColumns oHiddenColumns;
AutoFillWorkbookResults oAutoFillWorkbookResults =
new AutoFillWorkbookResults();
if (ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Edges,
TableNames.Edges, out oTable))
{
// The TableColumnMapper class that does the actual autofilling
// fills only visible cells. Temporarily show all hidden columns
// in the table.
oHiddenColumns = ExcelColumnHider.ShowHiddenColumns(oTable);
try
{
AutoFillEdgeTable(oTable, oAutoFillUserSettings,
oAutoFillWorkbookResults);
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oTable, oHiddenColumns);
}
}
if (ExcelUtil.TryGetTable(oWorkbook, WorksheetNames.Vertices,
TableNames.Vertices, out oTable))
{
oHiddenColumns = ExcelColumnHider.ShowHiddenColumns(oTable);
try
{
AutoFillVertexTable(oTable, oAutoFillUserSettings,
oAutoFillWorkbookResults);
}
finally
{
ExcelColumnHider.RestoreHiddenColumns(oTable, oHiddenColumns);
}
}
// Save the results.
(new PerWorkbookSettings(oWorkbook)).AutoFillWorkbookResults =
oAutoFillWorkbookResults;
}
