I am trying to select specific cells of an excel file using python and openpyxl:
My goal: The selection should take place depending on the time data of the second column and select all rows corresponding to a 24 hour timeframe between 07:00:05 [hh:mm:ss] on the first day and 07:00:00 on the second day. I am then copying the selection to another openpyxl worksheet.
Example of 2 selections (picture) (note: there are cells with values above and below the screenshot)
What I tried: What I already was able to do is to iterate thru every cell containing a selection that is 17280 cells long and 7 cells wide. That corresponds to a 24 hour timeframe. I then could copy the values to the other worksheet.
Here is my code that is opening two excel sheets, one as source and a template. The cell selection from source is copied to template and saved as xlsx file:
import openpyxl
# opening the source excel file
filename ="C:/Users/xxxx/source.xlsx"
wb1 = openpyxl.load_workbook(filename)
ws1 = wb1.worksheets[0]
# opening the template excel
filename1 ="C:/Users/xxxx/template.xlsm"
wb2 = openpyxl.load_workbook(filename1)
ws2 = wb2.worksheets[0]
# get date from source
date = ws1.cell(row = 1, column = 1).value[0:8]
# copying the cell values from source excel file to template file
for i in range (6, 6 + 17280):
for j in range (1, mc + 8):
# reading cell value from source excel file
c = ws1.cell(row = i, column = j)
# writing the read value to template file
ws2.cell(row = i + 4, column = j + 1).value = c.value
#saving named as date
wb2.save(str(date)+".xlsx")
The problem: Sometimes there are missing values and I can not predict if it will be 1 or 100 for one of the 24 hour timeframes. Because of this I need a variable selection but this is to much for my python knowledge.