//*************************************************************************
// 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);
}
