public static string teGetTS(string cntry, string indctr, string startDate, string endDate, [ExcelArgument(AllowReference = true)] object myArgument)
{
SearchEngine.fromSearch = false;
bool fromTS = true;
udfClassHelper("TESeries");
if (myArgument is ExcelMissing)
{
dataStartCell = formulaCell;
newFormula = string.Format($"=TESeries( \"{cntry}\", \"{indctr}\", \"{startDate}\", \"{endDate}\")");
//Debug.WriteLine(newFormula);
}
else
{
try
{
dataStartCell = helperClass.ReferenceToRange((ExcelReference)myArgument);
newFormula = string.Format(
$"=TESeries( \"{cntry}\", \"{indctr}\", \"{startDate}\", \"{endDate}\", {dataStartCell.Address[false, false]})");
//Debug.WriteLine(newFormula);
}
catch (Exception)
{
refError = true;
helperClass.getNewDict(fromTS);
return("#REF!");
throw;
}
}
formulaColumns frmlaColumnsPair = new formulaColumns(newFormula, cntry, null, formulaCell);
MyRibbon.myNewDict = new Dictionary <string, formulaColumns>();
try
{
XlCall.Excel(XlCall.xlfVolatile, false);
}
catch (Exception e)
{
helperClass.log.Error(e.Message);
helperClass.log.Trace(e.StackTrace);
throw;
}
if (helperClass.runFormula == "RunAutomatically = 1")
{
if (MyRibbon.refresh != true)
{
helperClass.setGlobalDict(formulaCell.Address[false, false], frmlaColumnsPair);
}
try
{
var dict = new getDictionary(cntry, indctr, startDate, endDate);
Dictionary <DateTime, Dictionary <string, string> > dicts = dict.getDic();
//helperClass.log.Info("Starting function data_to_excel");
var retriever = new RetrieveAndWriteTSData(dict.getColumns().ToArray(), dicts, dataStartCell, newFormula, formulaCell);
var thready = new Thread(retriever.fetchData);
thready.Priority = ThreadPriority.Normal;
thready.IsBackground = true;
thready.Start();
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
else
{
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
MyRibbon.myFormulasDict = (MyRibbon.myMainDict.ContainsKey(MyRibbon.sheet.Index.ToString())) ?
MyRibbon.myMainDict[MyRibbon.sheet.Index.ToString()] : new Dictionary <string, formulaColumns>();
foreach (var item in MyRibbon.myFormulasDict.Keys)
{
if (MyRibbon.myFormulasDict[item]._formula == newFormula && item == MyRibbon.myFormulasDict[item]._caller.Address[false, false])
{
return(formulaCell.Text);
}
}
Dictionary <string, formulaColumns> myNewDict = helperClass.getNewDict(fromTS);
try
{
var dict = new getDictionary(cntry, indctr, startDate, endDate);
Dictionary <DateTime, Dictionary <string, string> > dicts = dict.getDic();
var columns = dict.getColumns();
string[] clms = columns.ToArray();
//helperClass.log.Info("Starting function data_to_excel");
var retriever = new RetrieveAndWriteTSData(clms, dicts, dataStartCell, newFormula, formulaCell);
var thready = new Thread(retriever.fetchData);
thready.Priority = ThreadPriority.Normal;
thready.IsBackground = true;
thready.Start();
helperClass.RemoveOldKey(myNewDict);
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
customFunctionEnd("TS");
return((formulaCell.Address == dataStartCell.Address) ? "DateTime" : sharedFunctions.getAnswer(indctr));
}
public static string teGetTS(string cntry, string indctr, string startDate, string endDate, [ExcelArgument(AllowReference = true)] object myArgument)
{
helperClass.log.Info("=======================");
helperClass.log.Info("Starting TESeries udf");
string key = Properties.Settings.Default.userApiKey;
string answer = "Updated at " + DateTime.Now.TimeOfDay.ToString("hh\\:mm\\:ss");
bool fromTS = true;
try
{
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
}
catch (Exception)
{
MyRibbon.app = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
}
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
Range caller_range = helperClass.ReferenceToRange(caller);
Range formulaCell = caller_range; //Last cell used in userform
Range dataStartCell;
string newFormula;
if (myArgument is ExcelMissing)
{
dataStartCell = formulaCell;
newFormula = string.Format($"=TESeries( \"{cntry}\", \"{indctr}\", \"{startDate}\", \"{endDate}\")");
//Debug.WriteLine(newFormula);
}
else
{
try
{
dataStartCell = helperClass.ReferenceToRange((ExcelReference)myArgument);
newFormula = string.Format($"=TESeries( \"{cntry}\", \"{indctr}\", \"{startDate}\", \"{endDate}\", {dataStartCell.Address[false, false]})");
//Debug.WriteLine(newFormula);
}
catch (Exception)
{
refError = true;
helperClass.getNewDict(fromTS);
return("#REF!");
throw;
}
}
Range pass = null;
string columns2 = "";
//formulaColumns frmlaColumnsPair = new formulaColumns(newFormula, columns2, pass, formulaCell);
formulaColumns frmlaColumnsPair = new formulaColumns(newFormula, cntry, pass, formulaCell);
MyRibbon.myNewDict = new Dictionary <string, formulaColumns>();
if (formulaCell.Address == dataStartCell.Address)
{
answer = "DateTime";
}
try
{
XlCall.Excel(XlCall.xlfVolatile, false);
}
catch (Exception e)
{
helperClass.log.Error(e.Message);
helperClass.log.Trace(e.StackTrace);
throw;
}
if (helperClass.runFormula == "RunAutomatically = 1")
{
helperClass.log.Info("TESeries udf -> RunAutomatically = 1");
if (MyRibbon.refresh != true)
{
helperClass.setGlobalDict(formulaCell.Address[false, false], frmlaColumnsPair);
}
try
{
var dict = new getDictionary(cntry, indctr, key, startDate, endDate);
//Dictionary<string, Dictionary<string, string>> dicts = dict.getDic();
Dictionary <DateTime, Dictionary <string, string> > dicts = dict.getDic();
var columns = dict.getColumns();
string[] clms = columns.ToArray();
helperClass.log.Info("Starting function data_to_excel");
var retriever = new RetrieveAndWriteTSData(clms, dicts, key, dataStartCell, newFormula, formulaCell);
var thready = new Thread(retriever.fetchData);
thready.Priority = ThreadPriority.Normal;
thready.IsBackground = true;
thready.Start();
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
else
{
helperClass.log.Info("TESeries udf -> RunAutomatically != 1");
MyRibbon.sheet = MyRibbon.app.ActiveSheet;
if (MyRibbon.myMainDict.ContainsKey(MyRibbon.sheet.Index.ToString()))
{
MyRibbon.myFormulasDict = MyRibbon.myMainDict[MyRibbon.sheet.Index.ToString()];
}
else
{
MyRibbon.myFormulasDict = new Dictionary <string, formulaColumns>();
}
foreach (var item in MyRibbon.myFormulasDict.Keys)
{
if (MyRibbon.myFormulasDict[item]._formula == newFormula && item == MyRibbon.myFormulasDict[item]._caller.Address[false, false])
{
return(answer);
}
}
Dictionary <string, formulaColumns> myNewDict = helperClass.getNewDict(fromTS);
try
{
var dict = new getDictionary(cntry, indctr, key, startDate, endDate);
//Dictionary<string, Dictionary<string, string>> dicts = dict.getDic();
Dictionary <DateTime, Dictionary <string, string> > dicts = dict.getDic();
var columns = dict.getColumns();
string[] clms = columns.ToArray();
helperClass.log.Info("Starting function data_to_excel");
var retriever = new RetrieveAndWriteTSData(clms, dicts, key, dataStartCell, newFormula, formulaCell);
var thready = new Thread(retriever.fetchData);
thready.Priority = ThreadPriority.Normal;
thready.IsBackground = true;
thready.Start();
helperClass.RemoveOldKey(myNewDict);
}
catch (Exception ex)
{
helperClass.log.Info(ex.Message);
helperClass.log.Trace(ex.StackTrace);
throw;
}
}
helperClass.log.Info("TS finish");
helperClass.runFormula = "RunAutomatically = 0";
helperClass.origin = true;
MyRibbon.refresh = false;
helperClass.log.Info("Printing current cell value and finishing TS process");
return(answer);
}
