private void ExportOddsToExcel(DataTable dt_Odds, string League, string strFileName, bool flagOpen) { try { int i = 2; string strPathTemp = Application.StartupPath + @"\Charts"; string strPath = strPathTemp + @"\" + League; if (!System.IO.Directory.Exists(strPath)) { System.IO.Directory.CreateDirectory(strPath); } string strTemplate = strPathTemp + @"\" + "Tips.xlsx"; clsExcelHelper xlsHelper = new clsExcelHelper(); // Open template chart xlsHelper.OpenExcelAppWithFile(strTemplate, false); xlsHelper.WriteBlock("B2", 6, 19, String.Empty); // Write odds foreach (DataRow dr_odd in dt_Odds.Rows) { for (int j = 2; j < 21; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_odd[j - 2]); } i++; } //Save file xlsHelper.SaveToFile(strPath + @"\" + strFileName); xlsHelper.CloseExcel(); if (flagOpen) { // Open chart to preview Process.Start(strPath + @"\" + strFileName); } } catch (Exception ex) { logger.Debug(ex.ToString()); throw ex; } }
private static void ExportHDPsToExcel(DataTable dt_Handicaps1, DataTable dt_Handicaps, DataTable dt_Handicaps2, DataTable dt_Odds, DataTable dt_OUs1, DataTable dt_OUs, DataTable dt_OUs2, DataTable dt_Avg, string League, string strFileName, bool flagOpen) { try { int i = 2; string strPathTemp = Application.StartupPath + @"\Charts"; string strPath = strPathTemp + @"\" + League; if (!System.IO.Directory.Exists(strPath)) { System.IO.Directory.CreateDirectory(strPath); } string strTemplate = strPathTemp + @"\" + "OddsGraph.xlsx"; clsExcelHelper xlsHelper = new clsExcelHelper(); // Open template chart xlsHelper.OpenExcelAppWithFile(strTemplate, false); // Clear old data xlsHelper.WriteBlock("A2", 6, 30, String.Empty); xlsHelper.WriteBlock("A10", 6, 30, String.Empty); xlsHelper.WriteBlock("A18", 6, 30, String.Empty); xlsHelper.WriteBlock("B26", 6, 19, String.Empty); xlsHelper.WriteBlock("A34", 6, 30, String.Empty); xlsHelper.WriteBlock("A42", 6, 30, String.Empty); xlsHelper.WriteBlock("A50", 6, 30, String.Empty); xlsHelper.WriteBlock("C58", 6, 14, String.Empty); //DataRow[] dr_Handicaps = dt_Handicaps.Select("[Bookmaker] <> 'Convergence'"); // Write HDP foreach (DataRow dr_hdp1 in dt_Handicaps1.Rows) { for (int j = 1; j < 31; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_hdp1[j - 1]); } i++; } i = 10; foreach (DataRow dr_hdp in dt_Handicaps.Rows) { for (int j = 1; j < 31; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_hdp[j - 1]); } i++; } i = 18; foreach (DataRow dr_hdp2 in dt_Handicaps2.Rows) { for (int j = 1; j < 31; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_hdp2[j - 1]); } i++; } // Write odds i = 26; foreach (DataRow dr_odd in dt_Odds.Rows) { for (int j = 2; j < 21; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_odd[j - 2]); } i++; } // Write OUs i = 34; foreach (DataRow dr_ou1 in dt_OUs1.Rows) { for (int j = 1; j < 31; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_ou1[j - 1]); } i++; } i = 42; foreach (DataRow dr_ou in dt_OUs.Rows) { for (int j = 1; j < 31; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_ou[j - 1]); } i++; } i = 50; foreach (DataRow dr_ou2 in dt_OUs2.Rows) { for (int j = 1; j < 31; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j - 1) + i, dr_ou2[j - 1]); } i++; } i = 58; foreach (DataRow dr_Avg in dt_Avg.Rows) { for (int j = 0; j < 14; j++) { xlsHelper.WriteCell(CalculateExcelColumnIndex_Alphabet(j + 2) + i, dr_Avg[j]); } i++; } //Save file xlsHelper.SaveToFile(strPath + @"\" + strFileName); xlsHelper.CloseExcel(); if (flagOpen) { // Open chart to preview Process.Start(strPath + @"\" + strFileName); } } catch (Exception ex) { logger.Debug(ex.ToString()); throw ex; } }
private void correctScoreBettingToolStripMenuItem_Click(object sender, EventArgs e) { try { DataGridViewRow currRow = grdMatchs.CurrentRow; string HomeTeam, AwayTeam, Country, Season, dbName; string MatchName = currRow.Cells["MatchName"].Value.ToString().Trim(); MatchName = MatchName.Replace("Atl. Madrid", "Atl Madrid").Replace("Celta Vigo", "Celta").Replace("Granada CF", "Granada").Replace("Rayo Vallecano", "Vallecano").Replace("Real Sociedad", "Sociedad").Replace("Espanyol", "Espanol"); MatchName = MatchName.Replace("Stoke City", "Stoke").Replace("Manchester United", "Man United").Replace("Manchester City", "Man City").Replace("Newcastle Utd", "Newcastle"); MatchName = MatchName.Replace("Evian TG", "Evian Thonon Gaillard"); MatchName = MatchName.Replace("B. Moenchengladbach", "M'gladbach").Replace("Bayer Leverkusen", "Leverkusen").Replace("Eintracht Frankfurt", "Ein Frankfurt").Replace("Hertha Berlin", "Hertha").Replace("Schalke", "Schalke 04").Replace("VfB Stuttgart", "Stuttgart").Replace("Hamburger SV", "Hamburg").Replace("Köln", "FC Koln"); MatchName = MatchName.Replace("AC Milan", "Milan").Replace("AS Roma", "Roma"); Country = currRow.Cells["Country"].Value.ToString().Trim(); Season = currRow.Cells["Season"].Value.ToString().Trim(); dbName = "FootballData_" + Convert.ToString(Convert.ToInt32(Season.Split('-')[0].Trim()) - 1) + "_" + Convert.ToString(Convert.ToInt32(Season.Split('-')[1].Trim()) - 1) + ".dbo."; HomeTeam = MatchName.Split('-')[0].Trim(); AwayTeam = MatchName.Split('-')[1].Trim(); string strPathTemp = Application.StartupPath + @"\Charts"; string strPath = strPathTemp + @"\" + Country; if (!System.IO.Directory.Exists(strPath)) { System.IO.Directory.CreateDirectory(strPath); } string strTemplate = strPathTemp + @"\" + "Probabilities.xls"; clsExcelHelper xlsHelper = new clsExcelHelper(); // Open template chart xlsHelper.OpenExcelAppWithFile(strTemplate, false); xlsHelper.WriteCell("B2", MatchName); xlsHelper.WriteCell("E7", oddBO.AvgGoalsHomeOfSeason(dbName + Country)); try { xlsHelper.WriteCell("E8", oddBO.AvgGoalsHome(dbName + Country, HomeTeam)); } catch (Exception ex) { xlsHelper.CloseExcel(); MessageBox.Show(HomeTeam + " is new team in this season!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { xlsHelper.WriteCell("E9", oddBO.AvgGoalsAwayConceded(dbName + Country, AwayTeam)); } catch (Exception ex) { xlsHelper.CloseExcel(); MessageBox.Show(AwayTeam + " is new team in this season!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } xlsHelper.WriteCell("F7", oddBO.AvgGoalsAwayOfSeason(dbName + Country)); try { xlsHelper.WriteCell("F8", oddBO.AvgGoalsAway(dbName + Country, AwayTeam)); } catch (Exception ex) { xlsHelper.CloseExcel(); MessageBox.Show(AwayTeam + " is new team in this season!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { xlsHelper.WriteCell("F9", oddBO.AvgGoalsHomeConceded(dbName + Country, HomeTeam)); } catch (Exception ex) { xlsHelper.CloseExcel(); MessageBox.Show(HomeTeam + " is new team in this season!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } xlsHelper.RunMacro("Calculator.cmdProbabilities_Click"); //Save file xlsHelper.SaveToFile(strPath + @"\" + MatchName + "_Probabilities.xlsx"); xlsHelper.CloseExcel(); // Open chart to preview Process.Start(strPath + @"\" + MatchName + "_Probabilities.xlsx"); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }