Пример #1
0
        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));
        }
Пример #2
0
        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);
        }