/* Opens a workbook handle based on a template byte array */
public SpreadsheetGear.IWorkbook GetTemplate(EIA.Repository.CModelRequest Request)
{
/* Calculate Path */
MemoryStream mStream = new MemoryStream(Request.TemplateWithData);
/* Open Workbook */
return SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromStream(mStream);
}
/* Handles a model request */
public async Task HandleRequest(EIA.Repository.CModelRequest Request)
{
/* Create Return */
CModelResponse nResponse = new CModelResponse();
/* Set default */
nResponse.ResponseData = null;
/* Step 1. Get a handle of the requested model */
var wHandle = GetWorkBook(Request);
var wTemplate = GetTemplate(Request);
/* Step 2. Fill in details */
FillWorkSheetWithTemplate(wHandle.Worksheets[0], wTemplate.Worksheets[0]);
/* Step 3. Run requested macros/functions */
if (Request.Parameters.HasFlag(RequestParameters.CalculateCP))
Macros.CalculateCP(ref wHandle);
if (Request.Parameters.HasFlag(RequestParameters.CalculateCX))
Macros.CalculateCX(ref wHandle);
if (Request.Parameters.HasFlag(RequestParameters.CalculateDF))
Macros.CalculateDF(ref wHandle);
if (Request.Parameters.HasFlag(RequestParameters.CalculateDX))
Macros.CalculateDX(ref wHandle);
if (Request.Parameters.HasFlag(RequestParameters.CalculateGP))
Macros.CalculateGP(ref wHandle);
if (Request.Parameters.HasFlag(RequestParameters.CalculateOP))
Macros.CalculateOP(ref wHandle);
if (Request.Parameters.HasFlag(RequestParameters.CalculateOX))
Macros.CalculateOX(ref wHandle);
/* Step 4. Extract results */
SpreadsheetGear.IWorkbookSet _OutWbSet =
SpreadsheetGear.Factory.GetWorkbookSet();
SpreadsheetGear.IWorkbook _WriteWB =
_OutWbSet.Workbooks.Add();
/* Reverse Order */
if (Request.Parameters.HasFlag(RequestParameters.CalculateOX))
{
wHandle.Worksheets["NPV&IRR(OX)"].CopyAfter(_WriteWB.Worksheets[0]);
_WriteWB.Worksheets[1].Name = "NPV&IRR(OX)";
}
if (Request.Parameters.HasFlag(RequestParameters.CalculateOP))
{
wHandle.Worksheets["NPV&IRR(OP)"].CopyAfter(_WriteWB.Worksheets[0]);
_WriteWB.Worksheets[1].Name = "NPV&IRR(OP)";
}
if (Request.Parameters.HasFlag(RequestParameters.CalculateGP))
{
wHandle.Worksheets["NPV&IRR(GP)"].CopyAfter(_WriteWB.Worksheets[0]);
_WriteWB.Worksheets[1].Name = "NPV&IRR(GP)";
}
if (Request.Parameters.HasFlag(RequestParameters.CalculateDX))
{
wHandle.Worksheets["NPV&IRR(DX)"].CopyAfter(_WriteWB.Worksheets[0]);
_WriteWB.Worksheets[1].Name = "NPV&IRR(DX)";
}
if (Request.Parameters.HasFlag(RequestParameters.CalculateDF))
{
wHandle.Worksheets["NPV&IRR(DF)"].CopyAfter(_WriteWB.Worksheets[0]);
_WriteWB.Worksheets[1].Name = "NPV&IRR(DF)";
}
if (Request.Parameters.HasFlag(RequestParameters.CalculateCX))
{
wHandle.Worksheets["NPV&IRR(CX)"].CopyAfter(_WriteWB.Worksheets[0]);
_WriteWB.Worksheets[1].Name = "NPV&IRR(CX)";
}
if (Request.Parameters.HasFlag(RequestParameters.CalculateCP))
{
wHandle.Worksheets["NPV&IRR(CP)"].CopyAfter(_WriteWB.Worksheets[0]);
_WriteWB.Worksheets[1].Name = "NPV&IRR(CP)";
}
/* Copy Values */
if (Request.Parameters.HasFlag(RequestParameters.CalculateCP))
CopySheet(wHandle.Worksheets["NPV&IRR(CP)"], _WriteWB.Worksheets["NPV&IRR(CP)"]);
if (Request.Parameters.HasFlag(RequestParameters.CalculateCX))
CopySheet(wHandle.Worksheets["NPV&IRR(CX)"], _WriteWB.Worksheets["NPV&IRR(CX)"]);
if (Request.Parameters.HasFlag(RequestParameters.CalculateDF))
CopySheet(wHandle.Worksheets["NPV&IRR(DF)"], _WriteWB.Worksheets["NPV&IRR(DF)"]);
if (Request.Parameters.HasFlag(RequestParameters.CalculateDX))
CopySheet(wHandle.Worksheets["NPV&IRR(DX)"], _WriteWB.Worksheets["NPV&IRR(DX)"]);
if (Request.Parameters.HasFlag(RequestParameters.CalculateGP))
CopySheet(wHandle.Worksheets["NPV&IRR(GP)"], _WriteWB.Worksheets["NPV&IRR(GP)"]);
if (Request.Parameters.HasFlag(RequestParameters.CalculateOP))
CopySheet(wHandle.Worksheets["NPV&IRR(OP)"], _WriteWB.Worksheets["NPV&IRR(OP)"]);
if (Request.Parameters.HasFlag(RequestParameters.CalculateOX))
CopySheet(wHandle.Worksheets["NPV&IRR(OX)"], _WriteWB.Worksheets["NPV&IRR(OX)"]);
/* Remove left-owers */
_WriteWB.Worksheets[0].Delete();
/* Save */
nResponse.ResponseData = _WriteWB.SaveToMemory(SpreadsheetGear.FileFormat.OpenXMLWorkbook);
/* Step 5. Cleanup */
_WriteWB.Close();
_OutWbSet.Dispose();
wHandle.Close();
/* Step 6. Encode/proxy results */
nResponse.Model = Request.Model;
/* Step 7. Insert into database and return */
using (RequestRepository rRepo = new RequestRepository())
{
await rRepo.Response(Request.Guid, nResponse);
}
}
/* Opens a workbook handle based on a request */
public SpreadsheetGear.IWorkbook GetWorkBook(EIA.Repository.CModelRequest Request)
{
/* Shared */
Guid mGuid = Guid.Parse(Request.Model.Guid);
Byte[] WbData, WbDataCopy;
/* Sanitize the cache before we decrypt */
if (lDecryptedCache.ContainsKey(mGuid)) {
WbData = lDecryptedCache[mGuid];
}
else
{
/* Create a new instance of encryptor */
AesEncryption Decryptor = new AesEncryption(Key, Vector);
/* Calculate Path */
String ModelPath = Environment.GetFolderPath(
Environment.SpecialFolder.CommonDocuments) + "\\EIA\\Models\\"
+ Request.Model.Name + " - " + Request.Model.Country + " - " + Request.Model.Type + ".xlsm";
/* Read in model */
Byte[] EncryptedWbData = File.ReadAllBytes(ModelPath);
WbData = Decryptor.StrToByteArray(Decryptor.Decrypt(EncryptedWbData));
/* Store */
lDecryptedCache.Add(mGuid, WbData);
}
/* Create a copy of the data */
WbDataCopy = new Byte[WbData.Length];
WbData.CopyTo(WbDataCopy, 0);
/* Create the memory stream */
MemoryStream mStream = new MemoryStream(WbDataCopy);
/* Open Workbook */
return SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromStream(mStream);
}