I had to do something like this once.
I just adapted an old file I had to get your data, using openpyxl.
Just change the paths and paste myApp() in your shell to try it.
I deleted the existing data in your sheet 2 first, and put the same column headers as sheet 1.
If you really don't want the column DY, you can delete it with: targetFile[target].delete_cols(3)
def myApp():
import openpyxl
from openpyxl.styles import PatternFill
# set the fill colour to highlight the maximum and minimum
yellow = "00FFFF00"
print('Opening the file /home/pedro/Downloads/sample.xlsx ....')
pathToExcel = '/home/pedro/Downloads/sample.xlsx'
print('Opening the target file ....')
targetFile = openpyxl.load_workbook(pathToExcel)
print('Target opened ...')
# print the target file sheet names
targetFileSheetNames = targetFile.sheetnames
for sheet in targetFileSheetNames:
print('The target file has the following worksheets', sheet)
source = targetFileSheetNames[0]
target = targetFileSheetNames[1]
max_row_source = targetFile[source].max_row + 1
# reset these values if you repeat the following loop
T2M_MAX = targetFile[source].cell(row=2, column=4).value
T2M_MIN = targetFile[source].cell(row=2, column=5).value
# find the maximum and minimum values
# also save the corresponding row numbers of the max and min values
# if there can be 2 rows with the maximum value, you can get that later
# search the rows by value
for rowNum in range(2, max_row_source):
if targetFile[source].cell(row=rowNum, column=1).value == 2015:
print('found the correct year ... ')
maxValue = targetFile[source].cell(row=rowNum, column=4).value
if maxValue > T2M_MAX:
T2M_MAX = maxValue
maxRownr = rowNum
print('maxRownr is:', maxRownr)
minValue = targetFile[source].cell(row=rowNum, column=5).value
if minValue < T2M_MIN:
T2M_MIN = minValue
minRownr = rowNum
print('minRownr is:', minRownr)
print('The maximum value in column T2M_MAX is:', T2M_MAX)
print('The minimum value in column T2M_MIN is:', T2M_MIN)
# now write these values in the target sheet
# first get the maximum column number
# add 1 or you won't get the last column
max_col_source = targetFile[source].max_column + 1
# get the maximum row in the target sheet
# I deleted all rows in the target sheet, except the headers
# so the row to write to is row 2
max_row_target = targetFile[target].max_row + 1
# get the whole row with the maximum value
# write it to the target sheet
print('Writing the maximum values to the target sheet ... ')
for col in range(1, max_col_source):
getValue = targetFile[source].cell(row=maxRownr, column=col).value
targetFile[target].cell(row=max_row_target, column=col, value=getValue)
if col == 4:
targetFile[target].cell(row= max_row_target, column=col).fill = PatternFill(start_color=yellow, end_color=yellow, fill_type = "solid")
targetFile[target].cell(row=max_row_target, column=col+1, value='This is the maximum value row')
# get the whole row with the minimum value
# add 1 or you won't get the next empty row
max_row_target = targetFile[target].max_row + 1
# get the whole row with the minimum value
print('Writing the minimum values to the target sheet ... ')
for col in range(1, max_col_source):
getValue = targetFile[source].cell(row=minRownr, column=col).value
targetFile[target].cell(row=max_row_target, column=col, value=getValue)
if col == 5:
targetFile[target].cell(row=max_row_target, column=col).fill = PatternFill(start_color=yellow, end_color=yellow, fill_type = "solid")
targetFile[target].cell(row=max_row_target, column=col+1, value='This is the minimum value row')
# if for some reason you don't want the column DY in the target sheet
# you can delete it with this
# targetFile[target].delete_cols(3)
print('Saving the results to:', pathToExcel)
targetFile.save(pathToExcel)
print('All done!')
sample.xlsx (Size: 11.79 KB / Downloads: 212)
