How to sort .csv file test log which item first fail and paint color

[Pedroski55]

Aha, now I understand!

There was problem with column 310. Rows 3 and 4 both contain values, but I got a TypeError

column is 310
Traceback (most recent call last):
File "/usr/lib/python3.8/idlelib/run.py", line 559, in runcode
exec(code, self.locals)
File "<pyshell#48>", line 10, in <module>
File "<pyshell#42>", line 6, in checkCol
TypeError: '<' not supported between instances of 'NoneType' and 'float'

>>> value1 = targetFile['failures'].cell(row=3, column=310).value
>>> value2 = targetFile['failures'].cell(row=4, column=310).value
>>> value1
-39.19524256
>>> value2
-37.39370508
>>>

Can't explain that! So I put in a try: ..... except: TypeError

def myApp():
    import openpyxl
    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 = '/home/pedro/myPython/openpyxl/log_data.xlsx'
    savepath = '/home/pedro/myPython/openpyxl/failures.xlsx'

    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
                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')
        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(3, 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!')