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

admin管理员组

文章数量:1279015

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

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

• python
• python-3.x
• pandas
• dataframe
• openpyxl

Share Improve this question Follow edited Feb 25 at 5:08 moken 6,6391212 gold badges2929 silver badges3838 bronze badges asked Feb 24 at 19:36 user2077648user2077648 96777 gold badges2828 silver badges4343 bronze badges 4

• 1 The question needs improvements. There's no code showing what was tried and code/input samples/logs should never be posted as images but as plain text with proper OS format. – LMC Commented Feb 24 at 19:44
• sure let me add it – user2077648 Commented Feb 24 at 19:53
• first idea: use for-loop with range(0, 100, 2) to get row numbers i = 0, 2, 4, 6, and later copy from i to i+1 – furas Commented Feb 24 at 21:51
• 1 The code looks like it was thrown together from various bits. If you're using ws.iter_rows() there should be no need for additional counters. – Charlie Clark Commented Feb 25 at 9:51

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Should be pretty simple to just iterate through the rows skipping every 1st row and copy the value of the same cell offset by 1 row (up).
Of course it could also be done by looping the value cells and copy each value to the same cell offset by 1 row (down). _{If there are more columns after K you can also set the max column for the iteration, but otherwise the max will default to the last used column.}

...

## Iterate the rows, starting from row 3 and only need columns starting at Column G
for row in ws.iter_rows(min_row=3, min_col=7):
    if row[0].row % 2 == 0:  # If its an even row skip  
        continue
    else:
        for cell in row:  # For the cells in colunn G to the last column (K) 
            # Copy the cell value of the cell 1 row above 
            cell.value = cell.offset(row=-1).value


...

Example Sheet updated
[

Filtered on A1.results.0.test1

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

本文标签： Copying certain cells of an excel file to fix the report in PythonStack Overflow