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