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

[SamLiu]

Open the csv file in XL, save as XL! 2 clicks!

XL is the required output format.

You reply is very useful. If it's not too much effort, lease help me add limit check when assign limit in sheet. I have tried didn't got it.

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'

    index_number = [13, 14, 15, 16,23, 27, 33, 42, 43, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 59, 61, 63,
                    65, 67, 75, 81, 86, 87, 92, 93, 98, 99, 103,
                    105, 109, 111, 115, 117, 121, 123, 127, 129, 133, 135, 139, 141, 145, 147, 153, 174, 178, 205, 206,
                    207, 208, 209, 211, 213, 215, 216, 217,
                    219, 240, 241, 242, 243, 244, 248, 249, 264, 265, 271, 272, 276, 278, 282, 284, 293, 300, 302, 305,
                    307, 310, 311, 312, 313, 314, 315, 316,
                    317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335,
                    336, 337, 338, 339, 340, 341, 342, 343,
                    344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363,
                    364, 365, 366, 367, 368, 369, 370, 371,
                    372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384,385]

    path2files = 'C:/Users/Sam/pythonProject/'
    savepath = '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 = 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):
                    # write the headers to the target file
                    header1 = sourceFile[sheet].cell(row=1, column=colNum).value
                    header2 = sourceFile[sheet].cell(row=2, column=colNum).value
                    header3 = sourceFile[sheet].cell(row=3, column=colNum).value
                    header4 = sourceFile[sheet].cell(row=4, column=colNum).value
                    targetFile['failures'].cell(row=1, column=colNum).value = header1
                    targetFile['failures'].cell(row=2, column=colNum).value = header2
                    targetFile['failures'].cell(row=3, column=colNum).value = header3
                    targetFile['failures'].cell(row=4, column=colNum).value = header4

                    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(5, 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)

myApp()