//<Snippet2> private void Button_Click(object sender, RibbonControlEventArgs e) { Worksheet worksheet = Globals.Factory.GetVstoObject( Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]); string buttonName = "MyButton"; if (((RibbonCheckBox)sender).Checked) { Excel.Range selection = Globals.ThisAddIn.Application.Selection as Excel.Range; if (selection != null) { Microsoft.Office.Tools.Excel.Controls.Button button = new Microsoft.Office.Tools.Excel.Controls.Button(); worksheet.Controls.AddControl(button, selection, buttonName); } } else { worksheet.Controls.Remove(buttonName); } }
private void ThisWorkbook_Startup(object sender, System.EventArgs e) { var serviceBusConnectionString = ConfigurationManager.AppSettings["serviceBus"]; var queueClient = new QueueClient(serviceBusConnectionString, topic); //, ReceiveMode.PeekLock); Debug.WriteLine(topic); var player = new System.Media.SoundPlayer(); try { player.SoundLocation = System.IO.Path.GetDirectoryName(Application.ActiveWorkbook.FullName) + "\\Resources\\AllABoard2.wav"; player.Play(); //BrowserStuff.RunBrowser(); //BrowserStuff.RefreshBrowswer("http://www.convertit.com/Go/ConvertIt/Measurement/"); //BrowserStuff.RefreshBrowswer("http://www.convertit.com/Go/ConvertIt/Measurement/Converter.ASP"); } catch (Exception ex) { Debug.WriteLine("No Sounds"); Debug.WriteLine(System.IO.Path.GetDirectoryName(Application.ActiveWorkbook.FullName)); Debug.WriteLine(ex.Message); Console.Beep(); } Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet); Excel.Range firstRow = activeWorksheet.get_Range("A1"); firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown); Excel.Range newFirstRow = activeWorksheet.get_Range("A1"); //'newFirstRow.Value2 = "This text was added by using code"; var TradeAlertsConnectionString = ConfigurationManager.AppSettings["TradeAlertsStorage"]; CloudStorageAccount storageAccount = CloudStorageAccount.Parse(TradeAlertsConnectionString); CloudTableClient tableClient = storageAccount.CreateCloudTableClient(); CloudTable table = tableClient.GetTableReference("OpeningRanges"); TableQuery <OpeningRangesTable> query = new TableQuery <OpeningRangesTable>() .Where( TableQuery.GenerateFilterConditionForDate("Date", QueryComparisons.GreaterThanOrEqual, DateTime.Today) ) .Select( new string[] { "Symbol", "Quantity", "Date", "Low", "High" } ); //await table.ExecuteQuerySegmentedAsync<OpenRange>(query, null); //var list = table.ExecuteQuery(query); List <OpeningRangesTable> list = table.CreateQuery <OpeningRangesTable>() .Where(x => x.Date > DateTime.Today).ToList(); //.Where(x => x.Date > DateTime.Parse("04/03/2019")).ToList(); int pos = 0; list = list.OrderBy(x => (x.Symbol, x.Date)).ToList(); foreach (OpeningRangesTable O in list) { //Positions[O.Symbol] if (O.High > 0) { Positions.Add(new Position { Symbol = O.Symbol, Quantity = O.Quantity, Open = new OpenRange { Low = O.Low, High = O.High, Quantity = O.Quantity } }); } else { pos = Positions.FindIndex(x => x.Symbol == O.Symbol); try { Positions[pos].Alerts.Add(new Alert { Status = O.Status, Trade = O.Trade, Limit = O.Limit, Stop = O.Stop, flag = false }); } catch { } } } activeWorksheet.Cells[1, 1] = "Ticker Symbol"; activeWorksheet.Cells[1, 2] = "Opening Low"; activeWorksheet.Cells[1, 3] = "Opening High"; activeWorksheet.Cells[1, 4] = "Opening Spread $"; activeWorksheet.Cells[1, 5] = "Opening Spread %"; activeWorksheet.Cells[1, 6] = "Opening Mean"; activeWorksheet.Cells[1, 7] = "Trade Quantity"; //activeWorksheet.Cells[2, 7].EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignCenter; activeWorksheet.Cells[1, 1].EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter; activeWorksheet.Range[activeWorksheet.Cells[1, 7], activeWorksheet.Cells[99, 7]].HorizontalAlignment = XlHAlign.xlHAlignCenter; activeWorksheet.Cells[1, 1].EntireRow.WrapText = true; activeWorksheet.Cells[1, 1].EntireRow.Style.Font.Bold = true; activeWorksheet.Cells[1, 1].EntireRow.Interior.Color = System.Drawing.Color.GhostWhite; int row = 2; foreach (Position P in Positions) { //Console.Write($"{P.Symbol.PadRight(5)} {P.Open.Low.ToString("0.00").PadRight(6)} - {P.Open.High.ToString("0.00").PadRight(6)} "); //Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Application.ActiveSheet); activeWorksheet.Cells[row, 1].Value = P.Symbol; activeWorksheet.Cells[row, 2].Value = Math.Round(P.Open.Low, 2); activeWorksheet.Cells[row, 3].Value = Math.Round(P.Open.High, 2); activeWorksheet.Cells[row, 4].Value = Math.Round(P.Open.High - P.Open.Low, 2); activeWorksheet.Cells[row, 5].Value = Math.Round(((P.Open.High - P.Open.Low) / P.Open.High) * 100, 2); activeWorksheet.Cells[row, 6].Value = Math.Round((P.Open.High + P.Open.Low) / 2, 2); activeWorksheet.Cells[row, 7].Value = P.Open.Quantity; P.row = row++; foreach (Alert A in P.Alerts) { PushAlert(P.Symbol, A); } } queueClient.RegisterMessageHandler(async(msg, exception) => { var body = Encoding.UTF8.GetString(msg.Body); Debug.WriteLine(body); try { if (body.IndexOf("A-Up") > 0) { player.SoundLocation = System.IO.Path.GetDirectoryName(Application.ActiveWorkbook.FullName) + "\\Resources\\BuyBuyBuy.wav"; } else if (body.IndexOf("A-D") > 0) { player.SoundLocation = System.IO.Path.GetDirectoryName(Application.ActiveWorkbook.FullName) + "\\Resources\\SellSellSell.wav"; } else { player.SoundLocation = System.IO.Path.GetDirectoryName(Application.ActiveWorkbook.FullName) + "\\Resources\\BabyCry.wav"; } player.Play(); } catch { Debug.WriteLine("No Sounds"); Console.Beep(); } Alert A = JsonConvert.DeserializeObject <Alert>(body); PushAlert(A.Symbol, A); await Task.CompletedTask; }, async exception => { await Task.CompletedTask; Debug.WriteLine(exception); // log exception } ); Debug.WriteLine("Finished Queue."); void PushAlert(string symbol, Alert a) { int Apos = Positions.FindIndex(x => x.Symbol == symbol); int Arow = Positions[Apos].row; //activeWorksheet.Cells.Font.Color = System.Drawing.Color.Black; activeWorksheet.Range[activeWorksheet.Cells[2, 1], activeWorksheet.Cells[50, 20]].Interior.ColorIndex = 0; // System.Drawing.Color.Transparent; for (int Acol = 4; Acol < 99; Acol += 4) { if (activeWorksheet.Cells[Arow, Acol].Value == null) { activeWorksheet.Cells[Arow, Acol + 0].value = a.Status; activeWorksheet.Cells[Arow, Acol + 1].value = a.Trade; activeWorksheet.Cells[Arow, Acol + 2].value = Math.Round(a.Limit, 2); activeWorksheet.Cells[Arow, Acol + 3].value = Math.Round(a.Stop, 2); activeWorksheet.Range[activeWorksheet.Cells[Arow, Acol], activeWorksheet.Cells[Arow, Acol + 3]].Interior.Color = System.Drawing.Color.YellowGreen; Microsoft.Office.Tools.Excel.Controls.Button button = new Microsoft.Office.Tools.Excel.Controls.Button(); activeWorksheet.Cells[1, Acol + 0].value = "Alert Status"; activeWorksheet.Cells[1, Acol + 1].value = "Trade Type"; activeWorksheet.Cells[1, Acol + 2].value = "Limit Order"; activeWorksheet.Cells[1, Acol + 3].value = "Stop Loss"; Acol = 100; } } } }