public object[] CalculateParameters(string spreadsheetPath, string[] inputRanges, object[] inputValues, string[] outputRanges) { if (null == _driver) { _driver = new HPCExcel.ExcelDriver(); } spreadsheetPath = Environment.ExpandEnvironmentVariables(spreadsheetPath); if (null == _spreadsheet || !_spreadsheet.Equals(spreadsheetPath)) { _driver.OpenWorkbook(spreadsheetPath); _spreadsheet = spreadsheetPath; _driver.App.Calculation = Excel.XlCalculation.xlCalculationManual; } // insert inputs if (inputRanges.Length != inputValues.Length) { throw new Exception("Invalid parameters: input ranges and values don't match"); } for (int i = 0; i < inputRanges.Length; i++) { _driver.SetCellValue(inputRanges[i], inputValues[i].ToString()); } // force recalculation _driver.App.CalculateFull(); // collect outputs if (outputRanges.Length == 0) { return new object[] { } } ; object[] rslt = new object[outputRanges.Length]; for (int i = 0; i < outputRanges.Length; i++) { rslt[i] = _driver.GetCellValue(outputRanges[i]); } return(rslt); } }
/// <summary> /// Newest experimental calculate method /// </summary> /// <param name="macroName">Macro Name</param> /// <param name="inputs">Serialized Inputs</param> /// <param name="lastSaveDate">Last save date of workbook (or null)</param> /// <returns>Serialized result from macro </returns> public byte[] Calculate(string macroName, byte[] inputs, DateTime?lastSaveDate) { try { byte[] retVal = null; // Check if driver and excel process are available and if they are, find out if the excel process has gone down. if (this.xl != null ? (this.xl.ExcelProcess != null ? this.xl.ExcelProcess.HasExited : false) : false) { // If the process has gone down, clean up the previously created ExcelDriver // and create a new one before trying to open the workbook again this.xl.Dispose(); this.xl = new ExcelDriver(); this.workbookLoaded = false; } // Check if workbook already loaded if (this.workbookLoaded == false) { // Note whether workbook was loaded Tracing.WriteDebugTextVerbose(Tracing.ComponentId.ExcelService, Resources.ExcelService_FirstRequest); // Try to get Microsoft.Hpc.Excel.WorkbookPath environment variable string FindWorkBookPath() { var path = Environment.ExpandEnvironmentVariables(Environment.GetEnvironmentVariable(this.WORKBOOKENVVAR)); if (string.IsNullOrEmpty(path)) { throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, Resources.HPCExcelServiceWorkbookNotSet, this.WORKBOOKENVVAR)); } if (File.Exists(path)) { return(path); } else { string dataServiceShared = Environment.GetEnvironmentVariable(this.DATASERVICESHAREDENVVAR); if (string.IsNullOrEmpty(dataServiceShared)) { throw new InvalidOperationException(string.Format($"Can't find {path}, no files are shared through data service.")); } else { var workbookName = Path.GetFileName(path); foreach (var filePath in dataServiceShared.Split(';')) { if (Path.GetFileName(filePath) == workbookName) { return(filePath); } } throw new InvalidOperationException(string.Format($"Can't find {path} in files shared through data service: {dataServiceShared}.")); } } } string workbookPath = FindWorkBookPath(); Tracing.SoaTrace(XlTraceLevel.Information, $"Find workbook in {workbookPath}"); // Perform different workbook open logic on Azure than on premise if (AzureUtil.IsOnAzure()) { this.OpenWorkbookOnAzure(workbookPath, lastSaveDate); } else { this.xl.OpenReadOnly = true; this.OpenWorkbook(workbookPath, lastSaveDate); } // Register OnExiting callback this.SetOnExitingHook(); } else { Tracing.WriteDebugTextVerbose(Tracing.ComponentId.ExcelService, Resources.ExcelService_OtherRequest); } object returnedValue = null; WorkItem workItem = null; // Deserialize inputs try { workItem = WorkItem.Deserialize(inputs); } catch (Exception ex) { throw new ArgumentException(string.Format(CultureInfo.CurrentCulture, Resources.HPCExcelServiceDeserializeFailed, this.SERVICENAME), ex); } // Run the macro of provided name with inputs converted into an object returnedValue = this.xl.RunMacro(macroName, workItem.GetAll()); // Convert the returned object into work item WorkItem outputs = null; outputs = new WorkItem(); outputs.Insert(0, returnedValue); // Serialize the work item for WCF try { retVal = WorkItem.Serialize(outputs); } catch (Exception ex) { throw new SerializationException(string.Format(CultureInfo.CurrentCulture, Resources.HPCExcelServiceSerializeFailed, macroName), ex); } return(retVal); } catch (Exception ex) { // Handle corrupt workbooks as hard faults rather than retry operation exceptions. Bug 10285. bool corruptWorkbook = false; // Check for IO Exception containing COM Exception specific to corrupt workbook. if (ex.GetType().Equals(typeof(System.IO.IOException)) && ex.InnerException != null && ex.InnerException.GetType().Equals(typeof(COMException))) { COMException cex = (COMException)ex.InnerException; if ((uint)cex.ErrorCode == 0x800A03EC) { corruptWorkbook = true; } } // Failure logged and returned to client via new Exception if (this.workbookLoaded || corruptWorkbook) { // If the workbook has been loaded successfully, then the compute node is set up correctly and it is an // application error. Also report corrupt workbooks in this way to avoid retrying. Tracing.TraceEvent(XlTraceLevel.Error, Tracing.ComponentId.ExcelService, ex.ToString(), delegate { Tracing.EventProvider.LogExcelService_ApplicationError(Environment.GetEnvironmentVariable(this.SESSIONEV), Environment.GetEnvironmentVariable(this.TASKEV), Environment.GetEnvironmentVariable(this.NODENAMEEV), ex.ToString()); }); throw new FaultException <ExcelServiceError>(new ExcelServiceError(ex), Environment.GetEnvironmentVariable(this.NODENAMEEV) + ": " + ex.ToString()); } else { // If the workbook has not been loaded successfully, then the compute node is not set up correctly. // Either it cannot access the workbook, it cannot open excel, or it cannot load other installed HPC components. Tracing.TraceEvent(XlTraceLevel.Error, Tracing.ComponentId.ExcelService, ex.ToString(), delegate { Tracing.EventProvider.LogExcelService_SystemicFailure(Environment.GetEnvironmentVariable(this.SESSIONEV), Environment.GetEnvironmentVariable(this.TASKEV), Environment.GetEnvironmentVariable(this.NODENAMEEV), ex.ToString()); }); throw new FaultException <RetryOperationError>(new RetryOperationError(Environment.GetEnvironmentVariable(this.NODENAMEEV) + ": " + ex.ToString()), Environment.GetEnvironmentVariable(this.NODENAMEEV) + ": " + ex.ToString()); } } } // Calculate