public Array Get_intex_selections(string type) { using (WebClient client = new ThisAddIn.MyWebClient()) { string responsebody; byte[] responsebytes; string selected_deal = ""; if (type != "deals") { selected_deal = type; type = "yymm"; } var reqparm = new System.Collections.Specialized.NameValueCollection { { "type", type } }; reqparm.Add("selected_deal", selected_deal); responsebytes = client.UploadValues(Globals.ThisAddIn.Get_base_url() + "get_intex_selections", "POST", reqparm); responsebody = Encoding.UTF8.GetString(responsebytes); responsebody = responsebody.Replace("[", "").Replace("]", "").Replace("\"", "").Replace(" ", ""); Array list_ = responsebody.Split(','); return(list_); } }
private void button1_Click(object sender, EventArgs e) { if (deals.SelectedItem != null) { string deal_ = deals.SelectedItem.ToString(); string yymm_ = yymm.SelectedItem.ToString(); if (deal_ != "transaction" & yymm_ != "yymm") { var reqparm = new System.Collections.Specialized.NameValueCollection(); Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application; string responsebody; byte[] responsebytes; string result_path = TaraApp.Application.UserLibraryPath + "\\tara\\"; reqparm.Add("deal_", deal_); reqparm.Add("yymm_", yymm_); using (WebClient client = new ThisAddIn.MyWebClient()) { responsebytes = client.UploadValues(Globals.ThisAddIn.Get_base_url() + "load_intex_data", "POST", reqparm); responsebody = Encoding.UTF8.GetString(responsebytes); responsebody = responsebody.Replace("|", "--").Replace("}[", "|"); String[] dropdown_body = responsebody.Split('|'); responsebody = dropdown_body[1]; String drop_downs = dropdown_body[0]; responsebody = responsebody.Replace("},{", "|").Replace("}", "").Replace("]", "").Replace("[", "").Replace("'", "").Replace("{", ""); drop_downs = drop_downs.Replace("[", "").Replace("]", ""); Array raw_loan_data = responsebody.Split('|'); Array buckets_ = drop_downs.Replace("'", "").Replace("\", \"", "|").Split('|'); string f = deal_ + "_" + yymm_ + ".csv"; string written_file = Globals.Ribbons.Ribbon1.Response_to_csv(raw_loan_data, result_path, f, false, yymm_, false); string result_file_path = Path.Combine(result_path, written_file); TaraApp.Workbooks.Open(@result_file_path); Excel.Worksheet TaraSheet = TaraApp.ActiveWorkbook.ActiveSheet; TaraSheet.Name = "tara_sheet"; TaraSheet.Activate();; int iTotalRows = TaraSheet.UsedRange.Rows.Count; int col_count = 0; foreach (string bucket_col_val in buckets_) { string bucket = bucket_col_val.Split(':')[1].Replace("\"", "").Trim(); col_count += 1; Excel.Range cRng = TaraSheet.Cells[1, col_count]; if (bucket != "None" & bucket != "") { //if (cRng.Comment != null) { cRng.ClearComments(); } //Comment comment = cRng.AddComment(bucket); //comment.Shape.Width = 200; //comment.Shape.Height = 100; var drop_list = new System.Collections.Generic.List <string>(); drop_list = bucket.Split(',').ToList(); int drop_length = bucket.Length; var list_count = drop_list.Count; int section_length; string flatList = ""; if (drop_length > 250) { section_length = 250 / list_count - 1; for (int spark_num = 0; spark_num < list_count; spark_num += 1) { flatList = flatList + drop_list[spark_num].Trim().Substring(0, Math.Min(drop_list[spark_num].Trim().Length, section_length)) + ","; } } else { flatList = string.Join(",", drop_list.ToArray()); } //Array flat_list = flatList.ToArray(); //var flatList = string.Join(("Spark "+Convert.ToString(Enumerable.Range(1, list_count)).ToArray(),","); for (int row_count = 1; row_count <= iTotalRows; row_count += 1) { if (row_count < 250 & list_count < 40) { var drop_cell = TaraSheet.Cells[row_count, col_count];//.Columns[col_count];//:,col_count]#.Cells[row_count, col_count]; drop_cell.Validation.Delete(); drop_cell.Validation.Add( XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, XlFormatConditionOperator.xlBetween, flatList, Type.Missing); drop_cell.Validation.IgnoreBlank = false; drop_cell.Validation.InCellDropdown = true; drop_cell.Validation.ShowError = false; } } } } //var in_ = System.IO.File.ReadAllText(file_path); // var inputFile = new FileInfo(export_path); // could be .xls or .xlsx too Excel.Workbook tara_book = (TaraApp.ActiveWorkbook); // tara_book.SaveAs(deal_ + "_" + yymm_); Excel.Worksheet tara_sheet = (TaraApp.ActiveSheet); tara_sheet.Name = "tara_sheet"; //deal_ + "_" + yymm_; Globals.Ribbons.Ribbon1.Apply_formats(); } } } }
private void Compare_button_Click(object sender, RibbonControlEventArgs e) { string tara_sheet_ = "yes"; var reqparm = new System.Collections.Specialized.NameValueCollection(); var _data = Globals.ThisAddIn.Get_Data(); string responsebody; byte[] responsebytes; Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application; Excel.Worksheet tara_sheet = (Excel.Worksheet)TaraApp.ActiveSheet; //Worksheets["TARA"]; object mis = Type.Missing; if (_data.Item1 == "no data") { tara_sheet_ = "skip"; } if (tara_sheet_ != "skip") { using (WebClient client = new ThisAddIn.MyWebClient()) { Excel.Worksheet work_sheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet); reqparm.Add("data", _data.Item1); reqparm.Add("type", compare_dropdown.SelectedItem.Label); responsebytes = client.UploadValues(Globals.ThisAddIn.Get_base_url() + "load_comparison", "POST", reqparm); responsebody = Encoding.UTF8.GetString(responsebytes); responsebody = responsebody.Replace("{", "").Replace("}", "").Replace("\"", ""); Array ranks = responsebody.Split(','); int eTotalColumns = tara_sheet.UsedRange.Columns.Count; int comparison_col = 1; string add_comparison = "yes"; for (comparison_col = 1; comparison_col <= eTotalColumns; comparison_col++) { string existing_header = tara_sheet.Cells[1, comparison_col].Value.ToString().Trim(' ').Trim('"'); if (Convert.ToString(existing_header).Contains("comparison")) { add_comparison = "no"; break; } } if (add_comparison == "yes") { comparison_col = 3; Excel.Range Prng = tara_sheet.UsedRange.Columns[comparison_col]; Prng.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow); Prng.NumberFormat = "0%"; tara_sheet.Cells[1, comparison_col].Value2 = "comparison"; } foreach (string rank in ranks) { int row = Convert.ToInt16(rank.Trim(' ').Split(':')[0]); String rank_value = rank.Trim(' ').Split(':')[1]; work_sheet.Cells[row + 2, comparison_col].Value = rank_value; if (rank_value == "") { rank_value = ".5"; } if (rank_value != "") { Double v = Convert.ToDouble(rank_value) - .5; int c_index = 3; if (v < 0) { c_index = 23; } Double sensitivity = Math.Max(0, Math.Min(1, 1 - Math.Abs(v))); Excel.Range iRng = work_sheet.Cells[row + 2, comparison_col]; if (!String.IsNullOrEmpty(Convert.ToString(iRng.Cells.Value2))) { iRng.Interior.ColorIndex = c_index; iRng.Interior.TintAndShade = sensitivity; } } } } } tara_sheet.Outline.ShowLevels(3, 3); }
private void Tara_ratings() { Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application; Excel.Worksheet tara_sheet; string tara_sheet_ = "tara_sheet"; try { tara_sheet = (Excel.Worksheet)TaraApp.Worksheets["tara_sheet"]; } catch (System.Runtime.InteropServices.COMException) { tara_sheet_ = "no"; } if (tara_sheet_ != "no") { tara_sheet = (Excel.Worksheet)TaraApp.Worksheets[tara_sheet_]; using (WebClient client = new ThisAddIn.MyWebClient()) //client = new WebClient()) { string server = Globals.ThisAddIn.Get_base_url(); string responsebody = ""; var reqparm = new System.Collections.Specialized.NameValueCollection(); var _data = Globals.ThisAddIn.Get_Data(); reqparm.Add("data", _data.Item1); reqparm.Add("d_date", null); reqparm.Add("get_sup", "no"); byte[] responsebytes = client.UploadValues(server + "ask_tara", "POST", reqparm); responsebody = Encoding.UTF8.GetString(responsebytes); //responsebody = responsebody.Replace("},{", "|").Replace("[", "").Replace("]", "").Replace("\"", "").Replace("}", "").Replace("{", ""); responsebody = responsebody.Replace("|", "--").Replace("____change___", "|"); String[] body_change = responsebody.Split('|'); responsebody = body_change[0]; String responsechange = body_change[1]; responsebody = responsebody.Replace("], \"", "|").Replace("[", "").Replace("]", "").Replace("{", "").Replace("}", ""); responsechange = responsechange.Replace("], \"", "|").Replace("[", "").Replace("]", ""); Excel.Range pRng = tara_sheet.Range["A1"]; Array responses = responsebody.Split('|'); String[] changes = responsechange.Split('|'); string title = ""; if (Convert.ToString(responses.GetValue(0)) != "\"could not find data\"") { int col_count = 0; foreach (string r in responses) { // if value FALSE is in the corresponding changes array then dont' run if (changes[col_count].Contains("false")) { //column has an update proceed var column = r.Split(':')[0].Replace("\"", ""); int place_col = 0; for (int place_col_ = 1; place_col_ <= responses.Length; place_col_ += 1) { title = tara_sheet.Cells[1, place_col_].Value2; if (column == title) { place_col = place_col_; break; } } if (place_col != 0 & title != "") { int row_count = r.Split(':')[1].Replace("\", ", "|").Split('|').Length + 1; for (int place_row = 2; place_row <= row_count; place_row += 1)// can change this to only loop those fields with a FALSE value in change { if (changes[col_count].Split(':')[1].Split(',')[place_row - 2].Contains("false")) { // proceed found a change var cell = r.Split(':')[1].Replace("\", ", "|").Split('|')[place_row - 2].Replace("\"", "").Trim(); tara_sheet.Cells[place_row, place_col].Value2 = cell; if (title == "fy_ncf" | title == "mr_occupancy" | title == "stress") { tara_sheet.Cells[place_row, place_col].Interior.ColorIndex = 35; } } } } } col_count += 1; } } } Apply_formats(); } }
private void Get_illumination() { Excel.Application TaraApp = (Excel.Application)Globals.ThisAddIn.Application; var reqparm = new System.Collections.Specialized.NameValueCollection(); Excel.Worksheet tara_sheet = (Excel.Worksheet)TaraApp.Worksheets["tara_sheet"]; tara_sheet.UsedRange.Columns[9, Type.Missing].Interior.ColorIndex = 0; string JSON_data = Globals.ThisAddIn.Get_Data().Item1; using (WebClient client = new ThisAddIn.MyWebClient()) { string server = Globals.ThisAddIn.Get_base_url(); string responsebody = ""; reqparm.Add("data", JSON_data); byte[] responsebytes = client.UploadValues(server + "illuminate", "POST", reqparm); responsebody = Encoding.UTF8.GetString(responsebytes); responsebody = responsebody.Replace("|", "--").Replace("][", "|").Replace("[", "").Replace("]", ""); Excel.Range pRng = tara_sheet.Range["A1"]; Array responses = responsebody.Split('|'); Double sensitivity; int eTotalColumns = tara_sheet.UsedRange.Columns.Count; int base_loss_col = 1; foreach (string c in responses) { int place_col = 1; string response_header = c.Split(',')[0].Trim(' ').Trim('"'); for (int e_col = place_col; e_col <= eTotalColumns; e_col++) { if (tara_sheet.Cells[1, e_col].Value2 != null) { string existing_header = tara_sheet.Cells[1, e_col].Value.ToString().Trim(' ').Trim('"'); if (existing_header == "loss_expectation") { base_loss_col = e_col; } else { string r_h = response_header.ToLower().Replace(" ", "").Replace(")", "").Replace("(", "").Replace(":", "").Replace(";", "").Replace("-", "").Replace("'", "").Replace("]", "").Replace("[", "").Trim('"').Replace("\"", "").Replace(",", ""); string e_h = existing_header.ToLower().Replace(" ", "").Replace(")", "").Replace("(", "").Replace(":", "").Replace(";", "").Replace("-", "").Replace("'", "").Replace("]", "").Replace("[", "").Trim('"').Replace("\"", "").Replace(",", ""); if (r_h == "keydrivers" & e_h == "key_drivers") { int place_row = 1; for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1) { string v = c.Split(',')[row_c].Replace("\\\\r\\\\n", "\r\n").Replace("\\", "").Replace("\"", "").Replace("--", "|").Replace("*", "-"); tara_sheet.Cells[row_c + place_row, e_col].Value2 = v; } tara_sheet.UsedRange.Columns[e_col].WrapText = false; } else if (r_h == "latent_potential") { if (e_h == r_h) { int place_row = 1; for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1) { Double v = Convert.ToDouble(c.Split(',')[row_c]); string v_desc = "none"; if (v > .04) { v_desc = "volatile"; } else if (v > .03) { v_desc = "high"; } else if (v > .015) { v_desc = "elevated"; } else if (v > 0.000) { v_desc = "moderate"; } else { v_desc = "low"; } tara_sheet.Cells[row_c + place_row, e_col].Value2 = v_desc; } } else if (e_h == "tara_rating") { int place_row = 1; for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1) { Double v = Convert.ToDouble(c.Split(',')[row_c]); int c_index = 1; sensitivity = 0; c_index = 1; if (v > .4) { c_index = 3; sensitivity = .9; } else if (v > .2) { c_index = 3; sensitivity = .25; } else if (v > -.1) { c_index = 3; sensitivity = 0; } else if (v > -.2) { c_index = 3; sensitivity = -0.25; } else { c_index = 3; sensitivity = -.9; } //sensitivity = Math.Max(0.2, Math.Min(1,Math.Abs(v))); Excel.Range iRng = tara_sheet.Cells[row_c + place_row, e_col]; if (!String.IsNullOrEmpty(Convert.ToString(iRng.Cells.Value2))) { iRng.Font.Color = c_index; iRng.Font.TintAndShade = sensitivity; } } } } else if (e_h == r_h) { int place_row = 1; for (int row_c = 1; row_c < c.Split(',').Length; row_c += 1) { Double v = Convert.ToDouble(c.Split(',')[row_c]); int c_index = 3; if (v > 0) { c_index = 23; } sensitivity = Math.Max(0, Math.Min(1, 1 - Math.Abs(v))); Excel.Range iRng = tara_sheet.Cells[row_c + place_row, e_col]; if (!String.IsNullOrEmpty(Convert.ToString(iRng.Cells.Value2))) { iRng.Interior.ColorIndex = c_index; iRng.Interior.TintAndShade = sensitivity; } } e_col = eTotalColumns; } } } } } } Apply_formats(); tara_sheet.Outline.ShowLevels(3, 3); }