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

[Pedroski55]

Why bother with csv?

Just do this directly from Excel to Excel, takes 1 second!

试一试刘先生！

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  

    path2files = '/home/pedro/myPython/openpyxl/'
    savepath = '/home/pedro/myPython/openpyxl/failures.xlsx'
    # open the file
    XLfiles = os.listdir(path2files)
    for file in XLfiles:
        if file.endswith('.xlsx'):
            print('The files are:', file)

    my_file = input('Copy and paste the SOURCE file you want here ... ')

    sourceFile = openpyxl.load_workbook(path2files + my_file)
    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'
    targetFile.save(savepath)
    targetFile = openpyxl.load_workbook(savepath)
    targetFilesheets = targetFile.sheetnames

    # get the rows with failed
    count = 2
    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):
                    # write the headers to the target file
                    header = sourceFile[sheet].cell(row=2, column=colNum).value
                    targetFile['failures'].cell(row=1, column=colNum).value=header
                    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)

    # paint the columns different colours
    # put the row and column numbers you want
    for sheet in targetFilesheets:    
        maxRow = targetFile[sheet].max_row
        for rowNum in range(2, maxRow + 1):
            targetFile[sheet].cell(row=rowNum, column=5).fill = PatternFill(start_color='D0FBA1', end_color='D0FBA1', fill_type = 'solid')
            targetFile[sheet].cell(row=rowNum, column=6).fill = PatternFill(start_color='44FFFF', end_color='44FFFF', fill_type = 'solid')
            targetFile[sheet].cell(row=rowNum, column=7).fill = PatternFill(start_color='ECA4FB', end_color='ECA4FB', fill_type = 'solid')
            targetFile[sheet].cell(row=rowNum, column=8).fill = PatternFill(start_color='FBC7A4', end_color='FBC7A4', fill_type = 'solid')
            targetFile[sheet].cell(row=rowNum, column=9).fill = PatternFill(start_color='D0F111', end_color='D0F111', fill_type = 'solid')

    targetFile.save(savepath)