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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
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")
