I'm new in python and openpyxl. I started to learn in order to make my every day tasks easier and faster at my workplace.
Task: There is an excel file with a lots of rows, looks like this excel file
I want to create a daily report based on this excel file. In my example Today is 2019/05/08.
Expected result: Only show the info where the date is match with Today date. Expected structure:
My solution In my solution I create a list of the rows where I can find only the Today values. After that I read only that rows and create dictionaries. But the result is nothing. I also in a trouble about how to work with multiple keys. Because there are multiple issue numbers are in the list.
from datetime import datetime import openpyxl from openpyxl import load_workbook from openpyxl.utils import get_column_letter from openpyxl.utils import column_index_from_string #Open excel file excel_path = "\\REE.xlsx" wb = openpyxl.load_workbook(excel_path, data_only=True) ws_1 = wb.worksheets #The Today date. need some format due to excel date handling today = datetime.today() today = today.replace(hour=00, minute=00, second=00, microsecond=00) #Crate a list of the lines where only Today values are present issue_line_list =  for cell in ws_1["B"]: if cell.value == today: issue_line = cell.row issue_line_list.append(issue_line) #Creare a txt file for output file = open("daily_report.txt", "w") #The dict what I want to use dict =  issue_numbers_list =  issue =  #Create a dict for the issues for line in issue_line_list: issue_number_value = ws_1.cell(row = line, column = 3).value issue_numbers_list.append(issue_number_value) #Create a dict for other information for line in issue_line_list: issue_number_value = ws_1.cell(row = line, column = 3).value by_value = ws_1.cell(row = line, column = 2 ).value group_value = ws_1.cell(row = line, column = 4).value events_value = ws_1.cell(row = line, column = 5).value deadline_value = ws_1.cell(row = line, column = 6).value try: deadline_value = deadline_value.strftime('%Y.%m.%d') except: deadline_value = "" issue.append(issue_number_value) issue.append(by_value) issue.append(group_value) issue.append(events_value) issue.append(deadline_value) issue.append(deadline_value) #Append the two dict dict.append(issue_numbers_list) dict.append(issue) #Save it to the txt file. file.write(dict) file.close()
Questions - How to solve the multiple same key issue? - How to create nested groups? - What should add or delete to my code in order to get the expected result?
Remark Openpyxl is not only option. If you have a bettwer/easier/faster way I open for every idea.
Thank you in advance for you support!