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