Aug-30-2022, 11:25 AM
def myApp():
import openpyxl
import datetime
import os
# these 4 can help you format cells in openpyxl
# check the documentation online
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
path2file = r'C:\Users\Sam\pythonProject\Folder_operations_CSV\writers.xlsx'
savepath = 'failures.xlsx'
curr_time = datetime.datetime.now()
print(curr_time)
sourceFile = openpyxl.load_workbook(path2file)
sourceFilesheets = sourceFile.sheetnames
# make a new XL
targetFile = openpyxl.Workbook()
# a new wb only has 1 sheet called Sheet
sheet = targetFile.active
sheet.title = 'failures'
# copy rows 1 to 4 into the targetFile
# this will give you the limits, the headers and whatever row 1 is
for sheet in sourceFilesheets:
print('sheet is', sheet)
# copy the first 4 rows from sourceFile to targetFile
maxCol = sourceFile[sheet].max_column
for rowNum in range(1, 5):
for colNum in range(1, maxCol + 1):
# write the headers to the target file
data = sourceFile[sheet].cell(row=rowNum, column=colNum).value
targetFile['failures'].cell(row=rowNum, column=colNum).value = data
# save that, open it in Excel, check it and reopen in Python
targetFile.save(savepath)
targetFile = openpyxl.load_workbook(savepath)
targetFilesheets = targetFile.sheetnames
# get the rows with failed
# write to targetFile row 5 as first row
count = 5
for sheet in sourceFilesheets:
print('sheet is', sheet)
maxRow = sourceFile[sheet].max_row
maxCol = sourceFile[sheet].max_column
for rowNum in range(5, maxRow + 1):
if sourceFile[sheet].cell(row=rowNum, column=6).value == 'Failed':
print('Failed row is', rowNum)
for colNum in range(1, maxCol + 1):
cell_value = sourceFile[sheet].cell(row=rowNum, column=colNum).value
targetFile['failures'].cell(row=count, column=colNum).value = cell_value
count += 1
print('count is', count)
# got all the data for Failed rows and copied to targetFile
# now just check the limits and paint cells outside of the limits
# I got a strange TypeError for column 310 don't know why
# a function to find cells outside the limits and paint them
def checkCol(lim1, lim2, col):
maxRow = targetFile['failures'].max_row
try:
for rowNum in range(5, maxRow + 1):
# print('column is', col)
column_value = targetFile['failures'].cell(row=rowNum, column=col).value
print('column is:', {rowNum},{col},{column_value},{lim1},{lim2})
if not column_value==None:
if column_value < lim1 or column_value > lim2:
print('found a value outside the limits', lim1, column_value, lim2)
targetFile[sheet].cell(row=rowNum, column=col).fill = PatternFill(start_color='D0FBA1',
end_color='D0FBA1',
fill_type='solid')
else:
rowNum+=1
except:
TypeError
# look through row 3 for cells with values not None
# pass the column number to checkCol(lim1, lim2, col)
for sheet in targetFilesheets:
maxCol = targetFile[sheet].max_column
# find a column with a limit
for colNum in range(13, maxCol + 1):
value1 = targetFile['failures'].cell(row=3, column=colNum).value
if not value1 == None:
# get the limits
value2 = targetFile['failures'].cell(row=4, column=colNum).value
# print('value1 is', value1, 'value2 is', value2)
checkCol(value1, value2, colNum)
# save the result so far
targetFile.save(savepath)
print('Cells with value outside of range limits marked with colour')
print('File saved to', savepath)
# format the XL file
# resize row height and cell width
def formatCells():
for sheet in targetFilesheets:
targetFile.active = targetFile[sheet]
for rowNum in range(1, targetFile.active.max_row + 1):
targetFile.active.row_dimensions[rowNum].height = 36 # about 1/2"
for sheet in targetFilesheets:
targetFile.active = targetFile[sheet]
for colNum in range(1, targetFile.active.max_column + 1):
targetFile.active.column_dimensions[get_column_letter(colNum)].width = 22 # about 1"
# set the fonts
ft1 = Font(name='Arial', size=20)
alignment = Alignment(horizontal='center',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=True,
indent=0)
ft2 = Font(name='Arial', size=20, bold=True)
alignment2 = Alignment(horizontal='center',
vertical='bottom',
text_rotation=0,
wrap_text=True,
shrink_to_fit=True,
indent=0)
ft3 = Font(name='Arial', size=24, bold=True)
alignment3 = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=True,
indent=0)
# set the fonts for all cells
for sheet in targetFilesheets:
targetFileActiveSheet = targetFile[sheet]
maxRow = targetFileActiveSheet.max_row
maxCol = targetFileActiveSheet.max_column
for colNum in range(2, maxCol + 1, 1):
for rowNum in range(1, maxRow + 1):
targetFileActiveSheet.cell(row=rowNum, column=colNum).font = ft1
targetFileActiveSheet.cell(row=rowNum, column=colNum).alignment = alignment
# set the font for row 1 and row 3
for sheet in targetFilesheets:
targetFileActiveSheet = targetFile[sheet]
maxRow = targetFileActiveSheet.max_row
maxCol = targetFileActiveSheet.max_column
for colNum in range(1, maxCol + 1, 1):
targetFileActiveSheet.cell(row=1, column=colNum).font = ft2
targetFileActiveSheet.cell(row=1, column=colNum).alignment = alignment2
targetFileActiveSheet.cell(row=3, column=colNum).font = ft2
targetFileActiveSheet.cell(row=3, column=colNum).alignment = alignment2
# set the font bigger for column 7, the end of term score
for sheet in targetFilesheets:
targetFileActiveSheet = targetFile[sheet]
maxRow = targetFileActiveSheet.max_row
for rowNum in range(1, maxRow + 1, 1):
targetFileActiveSheet.cell(row=rowNum, column=6).font = ft3
targetFileActiveSheet.cell(row=rowNum, column=6).alignment = alignment3
print('formatting finished ... !')
formatCells()
targetFile.save(savepath)
print('All done!')
curr_time2 = datetime.datetime.now()
print(curr_time2-curr_time)
if __name__ == '__main__':
print("Starting Check....")
myApp()I added check if none of rows .It's ok right now.But 5 testers csv files of all test log need converted to xlsx format by manually that could do it,so complicated for that.thanks!
