Copying certain cells of an excel file to fix the report in Python

Question

In the below table how can we copy the column tempx cells for each test from partition column long cell to the corresponding test cell

For example when we filter Scenario Column cell A1.results.0.test1 , it has two rows corresponding to each partition type;
Here need to make sure both rows Temp1 to Temp5 columns have the same cell value as that present in the long cell (partition column)

**

• Input Table

• Expected Result Output Table Please suggest how can we copy the cells for each test

import openpyxl as op
import os
import datetime

input_file_path = r"C:\deleteme_flat"
result_filename = "Report_Summary_" + datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%S') + ".xlsx"
result_file_path = os.path.join(input_file_path, result_filename)
results = list()


def job_summary_gen():
    # this is the extension you want to detect
    extension = '.xlsx'
    file_list = []
    i = 0
    new_wb = op.Workbook()
    # sheet name update
    new_wb['Sheet'].title = "Summary"
    # wb2 = op.load_workbook(result_file_path)
    ws2 = new_wb['Summary']

    # If there are any rows already populated in the destination sheet start at next row otherwise start at row 1
    if ws2.max_row == 1:
        new_row = ws2.max_row
    else:
        new_row = ws2.max_row + 1

    for root, dirs_list, files_list in os.walk(input_file_path):
        for file_name in files_list:
            if os.path.splitext(file_name)[-1] == extension and file_name == 'testReport.xlsx':
                file_name_path = os.path.join(root, file_name)
                print(file_name_path)  # This is the full path of the filter file
                file_list.append(file_name_path)
                file_dir = os.path.dirname(file_name_path)
                folder_name = os.path.basename(file_dir)
                print(folder_name)
                wb1 = op.load_workbook(file_name_path)
                ws1 = wb1['Summary']
                # read flat Report
                for cell in ws1.iter_rows(min_col=3, max_col=3):
                    # print(cell[0].value)
                    # creating the header
                    if 'Partition' in cell[0].value.lower() and i < 1:
                        # print("header workbook"),
                        # Add 'File #' to first cell in destination row using row number as #
                        ws2.cell(row=new_row, column=1).value = "File Name"
                        for x in range(2, ws1.max_column + 1):
                            # Read each cell from col 1 to last used col
                            cell_value = ws1.cell(row=cell[0].row, column=x)
                            # Write last read cell to next empty row
                            ws2.cell(row=new_row, column=x).value = cell_value.value
                        # Increment to next unused row
                        new_row += 1
                        i += 1

                    # search for 'long' lower case against the cell value converted to lower case
                    if 'Partition' not in cell[0].value.lower() and 'long' in cell[0].value.lower():
                        # Add 'File name' to first cell in destination row using row number as #
                        ws2.cell(row=new_row, column=1).value = folder_name
                        # Copy cells Temp1 to Temp5
                        for x in range(2, ws1.max_column + 1):
                            # Logic to copy cells Temp1 to Temp5 from the long row to test row

                        # Increment to next unused row
                        new_row += 1
    new_wb.save(result_file_path)


# create_report()
job_summary_gen()

Answer 1

I was able to solve this issue by looking for zero in the specific columns as below:

import openpyxl as op
import os
import datetime

input_file_path = r"C:\Users"
result_filename = "Test_Summary_" + datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%S') + ".xlsx"
result_file_path = os.path.join(input_file_path, result_filename)
results = list()


def job_summary_gen():
    # this is the extension you want to detect
    extension = '.xlsx'
    file_list = []
    i = 0
    new_wb = op.Workbook()
    # sheet name update
    new_wb['Sheet'].title = "Summary"
    # wb2 = op.load_workbook(result_file_path)
    ws2 = new_wb['Summary']

    # If there are any rows already populated in the destination sheet start at next row otherwise start at row 1
    if ws2.max_row == 1:
        new_row = ws2.max_row
    else:
        new_row = ws2.max_row + 1

    for root, dirs_list, files_list in os.walk(input_file_path):
        for file_name in files_list:
            if os.path.splitext(file_name)[-1] == extension and file_name == 'testReport.xlsx':
                file_name_path = os.path.join(root, file_name)
                print(file_name_path)  # This is the full path of the filter file
                file_list.append(file_name_path)
                file_dir = os.path.dirname(file_name_path)
                folder_name = os.path.basename(file_dir)
                print(folder_name)
                wb1 = op.load_workbook(file_name_path)
                ws1 = wb1['Summary']

                for cell in ws1.iter_rows(min_col=3, max_col=3):
                    # print(cell[0].value)
                    # creating the header
                    if 'Partition' in cell[0].value.lower() and i < 1:
                        # print("header workbook"),
                        # Add 'File #' to first cell in destination row using row number as #
                        ws2.cell(row=new_row, column=1).value = "File Name"
                        for x in range(2, ws1.max_column + 1):
                            # Read each cell from col 1 to last used col
                            cell_value = ws1.cell(row=cell[0].row, column=x)
                            # Write last read cell to next empty row
                            ws2.cell(row=new_row, column=x).value = cell_value.value
                        # Increment to next unused row
                        new_row += 1
                        i += 1

                    if 'Partition' not in cell[0].value.lower():
                        # print("Match, copying cells to new workbook")
                        # Add 'File name' to first cell in destination row using row number as #
                        ws2.cell(row=new_row, column=1).value = folder_name
                        for x in range(2, ws1.max_column + 1):
                            # Read each cell from col 1 to last used col- column count starts from 1
                            # copying score here
                            if x <= 7:
                                cell_value = ws1.cell(row=cell[0].row, column=x).value
                            else:
                                if ws1.cell(row=cell[0].row, column=x).value == 0:
                                    prev_cell = ws1.cell(row=cell[0].row - 1, column=x).value
                                    if prev_cell != 0:
                                        cell_value = prev_cell
                                    else:
                                        cell_value = ws1.cell(row=cell[0].row - 2, column=x).value
                                else:
                                    cell_value = ws1.cell(row=cell[0].row, column=x).value

                            # Write last read cell to next empty row
                            ws2.cell(row=new_row, column=x).value = cell_value

                        # Increment to next unused row
                        new_row += 1
    new_wb.save(result_file_path)

job_summary_gen()