Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
16. Exercise 1


Here comes the last exercise in this part!


We've loaded the food_intake.xlsx file for you. Your job is to check all the spreadsheets available and print the sum of caloric intake for every single worksheet, together with the date (which is the name of the worksheet). Use the following format:

02.01 : 1956 kcal
03.01 : 2400 kcal

Assume that every single worksheet in this xlsx file has the same structure, similar to the sample below. The column with calories is the sixth column.

1 food_name weight (g) proteins fats carbohydrates kcal
2 chicken breast 100 12.2 5.9 0 102
3 white rice 50 3.4 6.3 38.3 170
4 broccoli 200 3.6 0.4 3.6 32.4

Stuck? Here's a hint!

First, get a list of the names of all available worksheets:

worksheet_list = wb.sheetnames

Then analyze each worksheet to identify its structure and learn where to find the column with the caloric intake. You will have to iterate over the list of sheetnames and create two variables – one to hold the selected worksheet, and the second for the total caloric input:

for worksheet in worksheet_list:
  selected_ws = wb[worksheet]
  caloric_sum = 0

Next, create an inner loop to iterate over the selected columns/rows from a given worksheet:

for row in selected_ws.iter_rows(min_row=2, ...):
  for cell in row:
    caloric_sum += cell.value

Remember that you should set the max_row of the iter_rows function to the max row of that sheet, as the number of rows differs in each sheet.

After the loops end, use the print() function:

print(selected_ws.title, ":", caloric_sum, "kcal")