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);
}
}
