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