//************************************************************************* // Constructor: BackgroundDialog() // /// <overloads> /// Initializes a new instance of the <see cref="BackgroundDialog" /> /// class. /// </overloads> /// /// <summary> /// Initializes a new instance of the <see /// cref="BackgroundDialog" /> class with a PerWorkbookSettings object. /// </summary> /// /// <param name="perWorkbookSettings"> /// The object being edited. /// </param> //************************************************************************* public BackgroundDialog ( PerWorkbookSettings perWorkbookSettings ) : this() { Debug.Assert(perWorkbookSettings != null); perWorkbookSettings.AssertValid(); m_oPerWorkbookSettings = perWorkbookSettings; m_oOpenFileDialog = new OpenFileDialog(); m_oOpenFileDialog.Filter = "All files (*.*)|*.*|" + SaveableImageFormats.Filter ; m_oOpenFileDialog.Title = "Browse for Background Image"; // Instantiate an object that saves and retrieves the user settings for // this dialog. Note that the object automatically saves the settings // when the form closes. m_oBackgroundDialogUserSettings = new BackgroundDialogUserSettings(this); DoDataExchange(false); AssertValid(); }
//************************************************************************* // Constructor: BackgroundDialog() // /// <overloads> /// Initializes a new instance of the <see cref="BackgroundDialog" /> /// class. /// </overloads> /// /// <summary> /// Initializes a new instance of the <see /// cref="BackgroundDialog" /> class with a PerWorkbookSettings object. /// </summary> /// /// <param name="perWorkbookSettings"> /// The object being edited. /// </param> //************************************************************************* public BackgroundDialog( PerWorkbookSettings perWorkbookSettings ) : this() { Debug.Assert(perWorkbookSettings != null); perWorkbookSettings.AssertValid(); m_oPerWorkbookSettings = perWorkbookSettings; m_oOpenFileDialog = new OpenFileDialog(); m_oOpenFileDialog.Filter = "All files (*.*)|*.*|" + SaveableImageFormats.Filter ; m_oOpenFileDialog.Title = "Browse for Background Image"; // Instantiate an object that saves and retrieves the user settings for // this dialog. Note that the object automatically saves the settings // when the form closes. m_oBackgroundDialogUserSettings = new BackgroundDialogUserSettings(this); DoDataExchange(false); AssertValid(); }
//************************************************************************* // Method: AutomateFolder() // /// <summary> /// Runs a specified set of tasks on every unopened NodeXL workbook in a /// folder. /// </summary> /// /// <param name="folderToAutomate"> /// Path to the folder to automate. /// </param> /// /// <param name="tasksToRun"> /// The tasks to run on each unopened NodeXL workbook in the folder, as an /// ORed combination of <see cref="AutomationTasks" /> flags. /// </param> /// /// <param name="application"> /// The Excel application for the workbook calling this method. /// </param> //************************************************************************* public static void AutomateFolder( String folderToAutomate, AutomationTasks tasksToRun, Microsoft.Office.Interop.Excel.Application application ) { Debug.Assert( !String.IsNullOrEmpty(folderToAutomate) ); Debug.Assert(application != null); foreach ( String sFileName in Directory.GetFiles(folderToAutomate, "*.xlsx") ) { String sFilePath = Path.Combine(folderToAutomate, sFileName); try { if (!NodeXLWorkbookUtil.FileIsNodeXLWorkbook(sFilePath)) { continue; } } catch (IOException) { // Skip any workbooks that are already open, or that have any // other problems that prevent them from being opened. continue; } // Ideally, the Excel API would be used here to open the workbook // and run the AutomateThisWorkbook() method on it. Two things // make that impossible: // // 1. When you open a workbook using // Application.Workbooks.Open(), you get only a native Excel // workbook, not an "extended" ThisWorkbook object or its // associated Ribbon object. AutomateThisWorkbook() requires // a Ribbon object. // // Although a GetVstoObject() extension method is available to // convert a native Excel workbook to an extended workbook, // that method doesn't work on a native workbook opened via // the Excel API -- it always returns null. // // It might be possible to refactor AutomateThisWorkbook() to // require only a native workbook. However, problem 2 would // still make things impossible... // // 2. If this method is being run from a modal dialog, which it // is (see AutomateTasksDialog), then code in the workbook // that needs to be automated doesn't run until the modal // dialog closes. // // The following code works around these problems. try { // Store an "automate tasks on open" flag in the workbook, // indicating that task automation should be run on it the next // time it's opened. This can be done via the Excel API. Microsoft.Office.Interop.Excel.Workbook oWorkbookToAutomate = ExcelUtil.OpenWorkbook(sFilePath, application); PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(oWorkbookToAutomate); oPerWorkbookSettings.AutomateTasksOnOpen = true; oWorkbookToAutomate.Save(); oWorkbookToAutomate.Close(false, Missing.Value, Missing.Value); // Now open the workbook in another instance of Excel, which // bypasses problem 2. Code in the workbook's Ribbon will // detect the flag's presence, run task automation on it, close // the workbook, and close the other instance of Excel. OpenWorkbookToAutomate(sFilePath); } catch (Exception oException) { ErrorUtil.OnException(oException); return; } } }
//************************************************************************* // Method: UpdateAxes() // /// <summary> /// Updates the graph's axes. /// </summary> /// /// <param name="oPerWorkbookSettings"> /// Provides access to settings that are stored on a per-workbook basis. /// </param> //************************************************************************* protected void UpdateAxes( PerWorkbookSettings oPerWorkbookSettings ) { Debug.Assert(oPerWorkbookSettings != null); AssertValid(); AutoFillWorkbookResults oAutoFillWorkbookResults = oPerWorkbookSettings.AutoFillWorkbookResults; String sXSourceColumnName, sYSourceColumnName; Double dXSourceCalculationNumber1, dXSourceCalculationNumber2, dXDestinationNumber1, dXDestinationNumber2, dYSourceCalculationNumber1, dYSourceCalculationNumber2, dYDestinationNumber1, dYDestinationNumber2; AutoFillNumericRangeColumnResults oVertexXResults = oAutoFillWorkbookResults.VertexXResults; AutoFillNumericRangeColumnResults oVertexYResults = oAutoFillWorkbookResults.VertexYResults; if (oVertexXResults.ColumnAutoFilled) { sXSourceColumnName = oVertexXResults.SourceColumnName; dXSourceCalculationNumber1 = oVertexXResults.SourceCalculationNumber1; dXSourceCalculationNumber2 = oVertexXResults.SourceCalculationNumber2; dXDestinationNumber1 = oVertexXResults.DestinationNumber1; dXDestinationNumber2 = oVertexXResults.DestinationNumber2; // The X and Y columns are always autofilled together. Debug.Assert(oVertexYResults.ColumnAutoFilled); sYSourceColumnName = oVertexYResults.SourceColumnName; dYSourceCalculationNumber1 = oVertexYResults.SourceCalculationNumber1; dYSourceCalculationNumber2 = oVertexYResults.SourceCalculationNumber2; dYDestinationNumber1 = oVertexYResults.DestinationNumber1; dYDestinationNumber2 = oVertexYResults.DestinationNumber2; } else { // The X and Y columns weren't autofilled. Use default axis // values. sXSourceColumnName = "X"; sYSourceColumnName = "Y"; dXSourceCalculationNumber1 = dXDestinationNumber1 = dYSourceCalculationNumber1 = dYDestinationNumber1 = VertexLocationConverter.MinimumXYWorkbook; dXSourceCalculationNumber2 = dXDestinationNumber2 = dYSourceCalculationNumber2 = dYDestinationNumber2 = VertexLocationConverter.MaximumXYWorkbook; } UpdateAxis(m_oNodeXLWithAxesControl.XAxis, sXSourceColumnName, dXSourceCalculationNumber1, dXSourceCalculationNumber2, dXDestinationNumber1, dXDestinationNumber2); UpdateAxis(m_oNodeXLWithAxesControl.YAxis, sYSourceColumnName, dYSourceCalculationNumber1, dYSourceCalculationNumber2, dYDestinationNumber1, dYDestinationNumber2); }
//************************************************************************* // Method: UpdateAutoFillResultsLegend() // /// <summary> /// Updates the autofill results legend control. /// </summary> /// /// <param name="oPerWorkbookSettings"> /// Provides access to settings that are stored on a per-workbook basis. /// </param> //************************************************************************* protected void UpdateAutoFillResultsLegend( PerWorkbookSettings oPerWorkbookSettings ) { Debug.Assert(oPerWorkbookSettings != null); AssertValid(); // The autofill and "autofill with scheme" results are stored in the // per-workbook settings. Transfer them to the visual attributes // legend control, which can display either set of results but not // both. // // (The PerWorkbookSettings property setter for the autofill results // clears the "autofill with scheme" results, and vice versa.) AutoFillWorkbookResults oAutoFillWorkbookResults = oPerWorkbookSettings.AutoFillWorkbookResults; AutoFillWorkbookWithSchemeResults oAutoFillWorkbookWithSchemeResults = oPerWorkbookSettings.AutoFillWorkbookWithSchemeResults; if (oAutoFillWorkbookResults.AutoFilledNonXYColumnCount > 0) { usrAutoFillResultsLegend.Update(oAutoFillWorkbookResults); } else if (oAutoFillWorkbookWithSchemeResults.SchemeType != AutoFillSchemeType.None) { usrAutoFillResultsLegend.Update( oAutoFillWorkbookWithSchemeResults); } else { usrAutoFillResultsLegend.Clear(); } }
MergeDuplicateEdges ( Microsoft.Office.Interop.Excel.Workbook workbook ) { Debug.Assert(workbook != null); AssertValid(); ListObject oEdgeTable; if ( !ExcelUtil.TryGetTable(workbook, WorksheetNames.Edges, TableNames.Edges, out oEdgeTable) || oEdgeTable.DataBodyRange == null ) { // (The DataBodyRange test catches the odd case where the user // deletes the first data row of the table. It looks like the row // is still there, but it's not. Continuing with a null // DataBodyRange can cause a variety of problems.) return; } ExcelUtil.ActivateWorksheet(oEdgeTable); // Clear AutoFiltering, which would make this code much more // complicated. ExcelUtil.ClearTableAutoFilters(oEdgeTable); // Get the vertex name data. Range oVertex1NameData, oVertex2NameData; Object [,] aoVertex1NameValues, aoVertex2NameValues; if ( !ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable, EdgeTableColumnNames.Vertex1Name, out oVertex1NameData, out aoVertex1NameValues) || !ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable, EdgeTableColumnNames.Vertex2Name, out oVertex2NameData, out aoVertex2NameValues) ) { return; } // Add an edge weight column if it doesn't already exist. ListColumn oEdgeWeightColumn; if ( !ExcelUtil.TryGetTableColumn(oEdgeTable, EdgeTableColumnNames.EdgeWeight, out oEdgeWeightColumn) && !ExcelUtil.TryAddTableColumn(oEdgeTable, EdgeTableColumnNames.EdgeWeight, ExcelUtil.AutoColumnWidth, null, out oEdgeWeightColumn) ) { return; } // Get the edge weight data. Range oEdgeWeightData; Object [,] aoEdgeWeightValues; if (!ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable, EdgeTableColumnNames.EdgeWeight, out oEdgeWeightData, out aoEdgeWeightValues)) { return; } // Determine whether the graph is directed. PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(workbook); Boolean bGraphIsDirected = (oPerWorkbookSettings.GraphDirectedness == GraphDirectedness.Directed); // Now that the required information has been gathered, do the actual // merge. MergeDuplicateEdges(oEdgeTable, oVertex1NameData, aoVertex1NameValues, oVertex2NameData, aoVertex2NameValues, oEdgeWeightColumn, oEdgeWeightData, aoEdgeWeightValues, bGraphIsDirected); oEdgeTable.HeaderRowRange.Select(); }
//************************************************************************* // Method: ReadWorkbookInternal() // /// <summary> /// Creates a NodeXL graph from the contents of an Excel workbook. /// </summary> /// /// <param name="workbook"> /// Workbook containing the graph data. /// </param> /// /// <param name="readWorkbookContext"> /// Provides access to objects needed for converting an Excel workbook to a /// NodeXL graph. /// </param> /// /// <returns> /// A new graph. /// </returns> /// /// <remarks> /// If <paramref name="workbook" /> contains valid graph data, a new <see /// cref="IGraph" /> is created from the workbook contents and returned. /// Otherwise, a <see cref="WorkbookFormatException" /> is thrown. /// </remarks> //************************************************************************* protected IGraph ReadWorkbookInternal( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext ) { Debug.Assert(readWorkbookContext != null); Debug.Assert(workbook != null); AssertValid(); if (readWorkbookContext.PopulateVertexWorksheet) { // Create and use the object that fills in the vertex worksheet. VertexWorksheetPopulator oVertexWorksheetPopulator = new VertexWorksheetPopulator(); try { oVertexWorksheetPopulator.PopulateVertexWorksheet( workbook, false); } catch (WorkbookFormatException) { // Ignore this type of error, which occurs when the vertex // worksheet is missing, for example. } } // Create a graph with the appropriate directedness. PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(workbook); IGraph oGraph = new Graph(oPerWorkbookSettings.GraphDirectedness); // Read the edge worksheet. This adds data to oGraph, // ReadWorkbookContext.VertexNameDictionary, and // ReadWorkbookContext.EdgeIDDictionary. EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader(); oEdgeWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oEdgeWorksheetReader = null; // Read the vertex worksheet. This adds metadata to the vertices in // oGraph; adds any isolated vertices to oGraph and // ReadWorkbookContext.VertexNameDictionary; and removes any skipped // vertices (and their incident edges) from // ReadWorkbookContext.VertexNameDictionary, // ReadWorkbookContext.EdgeIDDictionary, and oGraph. VertexWorksheetReader oVertexWorksheetReader = new VertexWorksheetReader(); oVertexWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oVertexWorksheetReader = null; if (readWorkbookContext.ReadAllEdgeAndVertexColumns) { // The other worksheets should be ignored. return (oGraph); } if (readWorkbookContext.ReadGroups) { // Read the group worksheets. This adds metadata to the vertices // in oGraph and to oGraph itself. GroupWorksheetReader oGroupWorksheetReader = new GroupWorksheetReader(); oGroupWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oGroupWorksheetReader = null; } // Read the per-workbook settings that are stored directly on the // graph. oPerWorkbookSettings.ReadWorksheet(workbook, readWorkbookContext, oGraph); return (oGraph); }
//************************************************************************* // Method: MergeDuplicateEdges() // /// <summary> /// Merges duplicate edges in the edge worksheet. /// </summary> /// /// <param name="workbook"> /// Workbook containing the edge worksheet. /// </param> /// /// <remarks> /// This method adds an edge weight column to the edge worksheet, then /// searches for rows that represent the same edge. For each set of /// duplicate rows, all but the first row in the set are deleted, and the /// edge weight cell for the first row is set to the number of duplicate /// edges. /// /// <para> /// Any AutoFiltering on the edge table is cleared. /// </para> /// /// </remarks> //************************************************************************* public void MergeDuplicateEdges( Microsoft.Office.Interop.Excel.Workbook workbook ) { Debug.Assert(workbook != null); AssertValid(); ListObject oEdgeTable; if ( !ExcelUtil.TryGetTable(workbook, WorksheetNames.Edges, TableNames.Edges, out oEdgeTable) || oEdgeTable.DataBodyRange == null ) { // (The DataBodyRange test catches the odd case where the user // deletes the first data row of the table. It looks like the row // is still there, but it's not. Continuing with a null // DataBodyRange can cause a variety of problems.) return; } ExcelUtil.ActivateWorksheet(oEdgeTable); // Clear AutoFiltering, which would make this code much more // complicated. ExcelUtil.ClearTableAutoFilters(oEdgeTable); // Get the vertex name data. Range oVertex1NameData, oVertex2NameData; Object [,] aoVertex1NameValues, aoVertex2NameValues; if ( !ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable, EdgeTableColumnNames.Vertex1Name, out oVertex1NameData, out aoVertex1NameValues) || !ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable, EdgeTableColumnNames.Vertex2Name, out oVertex2NameData, out aoVertex2NameValues) ) { return; } // Add an edge weight column if it doesn't already exist. ListColumn oEdgeWeightColumn; if ( !ExcelUtil.TryGetTableColumn(oEdgeTable, EdgeTableColumnNames.EdgeWeight, out oEdgeWeightColumn) && !ExcelUtil.TryAddTableColumn(oEdgeTable, EdgeTableColumnNames.EdgeWeight, ExcelUtil.AutoColumnWidth, null, out oEdgeWeightColumn) ) { return; } // Get the edge weight data. Range oEdgeWeightData; Object [,] aoEdgeWeightValues; if ( !ExcelUtil.TryGetTableColumnDataAndValues(oEdgeTable, EdgeTableColumnNames.EdgeWeight, out oEdgeWeightData, out aoEdgeWeightValues) ) { return; } // Determine whether the graph is directed. PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(workbook); Boolean bGraphIsDirected = (oPerWorkbookSettings.GraphDirectedness == GraphDirectedness.Directed); // Now that the required information has been gathered, do the actual // merge. MergeDuplicateEdges(oEdgeTable, oVertex1NameData, aoVertex1NameValues, oVertex2NameData, aoVertex2NameValues, oEdgeWeightColumn, oEdgeWeightData, aoEdgeWeightValues, bGraphIsDirected); oEdgeTable.HeaderRowRange.Select(); }
AutomateFolder ( String folderToAutomate, AutomationTasks tasksToRun, Microsoft.Office.Interop.Excel.Application application ) { Debug.Assert(!String.IsNullOrEmpty(folderToAutomate)); Debug.Assert(application != null); foreach (String sFileName in Directory.GetFiles(folderToAutomate, "*.xlsx")) { String sFilePath = Path.Combine(folderToAutomate, sFileName); try { if (!NodeXLWorkbookUtil.FileIsNodeXLWorkbook(sFilePath)) { continue; } } catch (IOException) { // Skip any workbooks that are already open, or that have any // other problems that prevent them from being opened. continue; } // Ideally, the Excel API would be used here to open the workbook // and run the AutomateThisWorkbook() method on it. Two things // make that impossible: // // 1. When you open a workbook using // Application.Workbooks.Open(), you get only a native Excel // workbook, not an "extended" ThisWorkbook object or its // associated Ribbon object. AutomateThisWorkbook() requires // a Ribbon object. // // Although a GetVstoObject() extension method is available to // convert a native Excel workbook to an extended workbook, // that method doesn't work on a native workbook opened via // the Excel API -- it always returns null. // // It might be possible to refactor AutomateThisWorkbook() to // require only a native workbook. However, problem 2 would // still make things impossible... // // 2. If this method is being run from a modal dialog, which it // is (see AutomateTasksDialog), then code in the workbook // that needs to be automated doesn't run until the modal // dialog closes. // // The following code works around these problems. try { // Store an "automate tasks on open" flag in the workbook, // indicating that task automation should be run on it the next // time it's opened. This can be done via the Excel API. Microsoft.Office.Interop.Excel.Workbook oWorkbookToAutomate = ExcelUtil.OpenWorkbook(sFilePath, application); PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(oWorkbookToAutomate); oPerWorkbookSettings.AutomateTasksOnOpen = true; oWorkbookToAutomate.Save(); oWorkbookToAutomate.Close(false, Missing.Value, Missing.Value); // Now open the workbook in another instance of Excel, which // bypasses problem 2. Code in the workbook's Ribbon will // detect the flag's presence, run task automation on it, close // the workbook, and close the other instance of Excel. OpenWorkbookToAutomate(sFilePath); } catch (Exception oException) { ErrorUtil.OnException(oException); return; } } }
ReadWorkbookInternal ( Microsoft.Office.Interop.Excel.Workbook workbook, ReadWorkbookContext readWorkbookContext ) { Debug.Assert(readWorkbookContext != null); Debug.Assert(workbook != null); AssertValid(); if (readWorkbookContext.PopulateVertexWorksheet) { // Create and use the object that fills in the vertex worksheet. VertexWorksheetPopulator oVertexWorksheetPopulator = new VertexWorksheetPopulator(); try { oVertexWorksheetPopulator.PopulateVertexWorksheet( workbook, false); } catch (WorkbookFormatException) { // Ignore this type of error, which occurs when the vertex // worksheet is missing, for example. } } // Create a graph with the appropriate directedness. PerWorkbookSettings oPerWorkbookSettings = new PerWorkbookSettings(workbook); IGraph oGraph = new Graph(oPerWorkbookSettings.GraphDirectedness); // Read the edge worksheet. This adds data to oGraph, // ReadWorkbookContext.VertexNameDictionary, and // ReadWorkbookContext.EdgeIDDictionary. EdgeWorksheetReader oEdgeWorksheetReader = new EdgeWorksheetReader(); oEdgeWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oEdgeWorksheetReader = null; // Read the vertex worksheet. This adds metadata to the vertices in // oGraph; adds any isolated vertices to oGraph and // ReadWorkbookContext.VertexNameDictionary; and removes any skipped // vertices (and their incident edges) from // ReadWorkbookContext.VertexNameDictionary, // ReadWorkbookContext.EdgeIDDictionary, and oGraph. VertexWorksheetReader oVertexWorksheetReader = new VertexWorksheetReader(); oVertexWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oVertexWorksheetReader = null; if (readWorkbookContext.ReadAllEdgeAndVertexColumns) { // The other worksheets should be ignored. return(oGraph); } if (readWorkbookContext.ReadGroups) { // Read the group worksheets. This adds metadata to the vertices // in oGraph and to oGraph itself. GroupWorksheetReader oGroupWorksheetReader = new GroupWorksheetReader(); oGroupWorksheetReader.ReadWorksheet(workbook, readWorkbookContext, oGraph); oGroupWorksheetReader = null; } // Read the per-workbook settings that are stored directly on the // graph. oPerWorkbookSettings.ReadWorksheet(workbook, readWorkbookContext, oGraph); return(oGraph); }