import openpyxl
# Load the workbook
workbook = openpyxl.load_workbook('C:\\cricket_scores.xlsx')
# Get the active sheet
worksheet = workbook.active
# Check if the "Points and NRR" sheet exists
if "Points and NRR" in workbook.sheetnames:
points_worksheet = workbook["Points and NRR"]
else:
# If the "Points and NRR" sheet doesn't exist, create a new sheet
points_worksheet = workbook.create_sheet("Points and NRR")
# Define column headers for the second sheet
headers = ["Team Name", "Matches Played", "Win", "Loss", "Points", "NRR"]
# Write headers to the second sheet if it's a newly created sheet
if points_worksheet.max_row == 1:
points_worksheet.append(headers)
# Initialize variables for team statistics
teams = {
"HBK": {
"Total_runs_scored": 0,
"Total_runs_conceded": 0,
"Total_overs_played": 0,
"Total_Matches": 0,
"Total_win": 0,
"Total_loss": 0
},
"CH": {
"Total_runs_scored": 0,
"Total_runs_conceded": 0,
"Total_overs_played": 0,
"Total_Matches": 0,
"Total_win": 0,
"Total_loss": 0
},
"DD": {
"Total_runs_scored": 0,
"Total_runs_conceded": 0,
"Total_overs_played": 0,
"Total_Matches": 0,
"Total_win": 0,
"Total_loss": 0
},
"BB": {
"Total_runs_scored": 0,
"Total_runs_conceded": 0,
"Total_overs_played": 0,
"Total_Matches": 0,
"Total_win": 0,
"Total_loss": 0
}
}
# Iterate over the existing sheet and update the values in the second sheet
for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row, values_only=True):
batting_1st_team = row[1]
batting_2nd_team = row[2]
batting_1st_team_total_runs = row[3]
batting_2nd_team_total_runs = row[6]
batting_1st_team_overs_played = row[5]
batting_2nd_team_overs_played = row[8]
total_runs_conceded = row[9]
total_overs_conceded = row[10]
win = 0
if batting_1st_team_total_runs > batting_2nd_team_total_runs:
win = 1
batting_1st_team_overs_played = 15
batting_2nd_team_overs_played = 15
else:
win = 2
batting_1st_team_overs_played = 15
batting_2nd_team_overs_played = 14.3 # Convert 14.3 overs to 14 + 3/6 or 11 + 2/6
if batting_1st_team == "HBK":
teams["HBK"]["Total_runs_scored"] += batting_1st_team_total_runs
teams["HBK"]["Total_runs_conceded"] += batting_2nd_team_total_runs
teams["HBK"]["Total_overs_played"] += batting_1st_team_overs_played +
batting_2nd_team_overs_played
teams["HBK"]["Total_Matches"] += 1
if win == 1:
teams["HBK"]["Total_win"] += 1
elif win == 2:
teams["HBK"]["Total_loss"] += 1
elif batting_1st_team == "DD":
teams["DD"]["Total_runs_scored"] += batting_1st_team_total_runs
teams["DD"]["Total_overs_played"] += batting_1st_team_overs_played +
batting_2nd_team_overs_played
teams["DD"]["Total_runs_conceded"] += batting_2nd_team_total_runs
teams["DD"]["Total_Matches"] += 1
if win == 1:
teams["DD"]["Total_win"] += 1
elif win == 2:
teams["DD"]["Total_loss"] += 1
elif batting_1st_team == "CH":
teams["CH"]["Total_runs_scored"] += batting_1st_team_total_runs
teams["CH"]["Total_overs_played"] += batting_1st_team_overs_played +
batting_2nd_team_overs_played
teams["CH"]["Total_runs_conceded"] += batting_2nd_team_total_runs
teams["CH"]["Total_Matches"] += 1
if win == 1:
teams["CH"]["Total_win"] += 1
elif win == 2:
teams["CH"]["Total_loss"] += 1
elif batting_1st_team == "BB":
teams["BB"]["Total_runs_scored"] += batting_1st_team_total_runs
teams["BB"]["Total_overs_played"] += batting_1st_team_overs_played +
batting_2nd_team_overs_played
teams["BB"]["Total_runs_conceded"] += batting_2nd_team_total_runs
teams["BB"]["Total_Matches"] += 1
if win == 1:
teams["BB"]["Total_win"] += 1
elif win == 2:
teams["BB"]["Total_loss"] += 1
# Calculate Net Run Rate (NRR) for each team
for team, data in teams.items():
total_runs_scored = data["Total_runs_scored"]
total_runs_conceded = data["Total_runs_conceded"]
total_overs_played = data["Total_overs_played"]
net_run_rate = (total_runs_scored - total_runs_conceded) / total_overs_played
data["NRR"] = net_run_rate
# Find the row index of the team in the second sheet
team_row_index = None
for i, row in enumerate(points_worksheet.iter_rows(values_only=True)):
if row[0] == team:
team_row_index = i + 2 # Adding 2 to match the row index in the sheet (1 for header
row)
break
# Update the corresponding row in the second sheet or append a new row
if team_row_index is not None:
points_worksheet.cell(row=team_row_index, column=2).value = data["Total_Matches"]
points_worksheet.cell(row=team_row_index, column=3).value = data["Total_win"]
points_worksheet.cell(row=team_row_index, column=4).value = data["Total_loss"]
points_worksheet.cell(row=team_row_index, column=5).value = data["Total_win"] * 2
points_worksheet.cell(row=team_row_index, column=6).value = data["NRR"]
else:
new_row = [team, data["Total_Matches"], data["Total_win"], data["Total_loss"],
data["Total_win"] * 2, data["NRR"]]
points_worksheet.append(new_row)
# Save the changes to the workbook
workbook.save('C:\\cricket_scores.xlsx')
