AutomateOneWorkbookIndirect
(
String nodeXLWorkbookFilePath,
String workbookSettings
)
{
Debug.Assert( !String.IsNullOrEmpty(nodeXLWorkbookFilePath) );
Debug.Assert( !String.IsNullOrEmpty(workbookSettings) );
// Ideally, the Excel API would be used here to open the workbook
// and run the AutomateOneWorkbook() 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.
//
// 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 AutomateOneWorkbook() 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.
Microsoft.Office.Interop.Excel.Application oExcelApplication =
null;
ExcelApplicationKiller oExcelApplicationKiller = null;
try
{
// Use a new Application object for each workbook. If the same
// Application object is reused, the memory used by each
// workbook is never released and the machine will eventually
// run out of memory.
oExcelApplication =
new Microsoft.Office.Interop.Excel.Application();
if (oExcelApplication == null)
{
throw new Exception("Excel couldn't be opened.");
}
// ExcelApplicationKiller requires that the application be
// visible.
oExcelApplication.Visible = true;
oExcelApplicationKiller = new ExcelApplicationKiller(
oExcelApplication);
// 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(nodeXLWorkbookFilePath,
oExcelApplication);
PerWorkbookSettings oPerWorkbookSettings =
new PerWorkbookSettings(oWorkbookToAutomate);
oPerWorkbookSettings.WorkbookSettings = workbookSettings;
oPerWorkbookSettings.AutomateTasksOnOpen = true;
oWorkbookToAutomate.Save();
oWorkbookToAutomate.Close(false, Missing.Value, Missing.Value);
oExcelApplication.Quit();
}
catch (Exception oException)
{
ErrorUtil.OnException(oException);
return;
}
finally
{
// Quitting the Excel application does not remove it from
// memory. Kill its process.
oExcelApplicationKiller.KillExcelApplication();
oExcelApplication = null;
oExcelApplicationKiller = null;
}
try
{
// 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(nodeXLWorkbookFilePath, 60 * 60);
}
catch (Exception oException)
{
ErrorUtil.OnException(oException);
return;
}
}
TryGetGraphMetricsForOneNodeXLWorkbook
(
String sNodeXLWorkbookFilePath,
out OverallMetricsInfo oOverallMetricsInfo
)
{
Debug.Assert( !String.IsNullOrEmpty(sNodeXLWorkbookFilePath) );
AssertValid();
oOverallMetricsInfo = null;
// Create a new instance of Excel. Do not use the instance that was
// passed to AggregateGraphMetricsAsync(), because when a NodeXL
// workbook is opened and closed in Excel, its memory is not released
// and the machine will eventually run out of memory.
Application oExcelApplication = new Application();
if (oExcelApplication == null)
{
throw new Exception("Excel couldn't be opened.");
}
// ExcelApplicationKiller requires that the application be visible.
oExcelApplication.Visible = true;
ExcelApplicationKiller oExcelApplicationKiller =
new ExcelApplicationKiller(oExcelApplication);
Workbook oNodeXLWorkbook = null;
try
{
oNodeXLWorkbook = ExcelUtil.OpenWorkbook(sNodeXLWorkbookFilePath,
oExcelApplication);
OverallMetrics oOverallMetrics;
if ( ( new OverallMetricsReader() ).TryReadMetrics(
oNodeXLWorkbook, out oOverallMetrics ) )
{
oOverallMetricsInfo = new OverallMetricsInfo();
oOverallMetricsInfo.NodeXLWorkbookFileName =
Path.GetFileName(sNodeXLWorkbookFilePath);
oOverallMetricsInfo.OverallMetrics = oOverallMetrics;
return (true);
}
}
finally
{
if (oNodeXLWorkbook != null)
{
oNodeXLWorkbook.Close(false, Missing.Value, Missing.Value);
}
oExcelApplication.Quit();
// Quitting the Excel application does not remove it from
// memory. Kill its process.
oExcelApplicationKiller.KillExcelApplication();
}
return (false);
}
SaveGraphToNodeXLWorkbook
(
XmlDocument graphMLDocument,
String graphMLFilePath,
String nodeXLWorkbookPath,
String nodeXLWorkbookSettingsFilePath,
Boolean setAutomateTasksOnOpen
)
{
Debug.Assert(graphMLDocument != null);
Debug.Assert(nodeXLWorkbookPath == null ||
nodeXLWorkbookPath.Length > 0);
// Open Excel.
Application oExcelApplication = new Application();
if (oExcelApplication == null)
{
throw new ConvertGraphMLToNodeXLWorkbookException(
ErrorCode.CouldNotOpenExcel,
"Excel couldn't be opened. Is it installed on this computer?"
);
}
// ExcelApplicationKiller requires that the application be visible.
oExcelApplication.Visible = true;
// Suppress alerts about overwriting an existing file.
oExcelApplication.DisplayAlerts = false;
ExcelApplicationKiller oExcelApplicationKiller =
new ExcelApplicationKiller(oExcelApplication);
try
{
SaveGraphToNodeXLWorkbook(graphMLDocument, graphMLFilePath,
nodeXLWorkbookPath, nodeXLWorkbookSettingsFilePath,
setAutomateTasksOnOpen, oExcelApplication);
}
finally
{
if (nodeXLWorkbookPath != null)
{
oExcelApplication.Quit();
// Quitting the Excel application does not remove it from
// memory. Kill its process.
oExcelApplicationKiller.KillExcelApplication();
}
}
}
