Jul-09-2020, 01:06 AM
Hello,
This script as it sits has no errors, and works, but I want to change it up slightly. Right now it takes the files in my input directory, and pastes it into a template, and then saves the file with a new name, and repeats over and over again.
The change that I need to make is to the source data that is copied to the template. I want to filter for a certain word in column A, but column A is blank from row 1-7, row 8 has the header (Direction), row 9 is blank, and then Row 10 is where the data starts and goes on for a while. I just want to search column A for the word "down", and keep all the data for that whole row. Although, the first 7 rows and row 9 are blank for column A, column B contains information in those rows that I can't lose, that will need to get pasted into the template. An Example is shown below how the columns are.
Column A
Blank
Blank
Blank
Blank
Blank
Blank
Blank
Direction (Header)
Blank
Down (Start of data)
Down
Up
Up
Column B
Information
Information
Information
Information
Information
Information
Information
TimeStep (Header)
FMT
0.09 (Start of data)
0.10
0.11
0.12
This script as it sits has no errors, and works, but I want to change it up slightly. Right now it takes the files in my input directory, and pastes it into a template, and then saves the file with a new name, and repeats over and over again.
The change that I need to make is to the source data that is copied to the template. I want to filter for a certain word in column A, but column A is blank from row 1-7, row 8 has the header (Direction), row 9 is blank, and then Row 10 is where the data starts and goes on for a while. I just want to search column A for the word "down", and keep all the data for that whole row. Although, the first 7 rows and row 9 are blank for column A, column B contains information in those rows that I can't lose, that will need to get pasted into the template. An Example is shown below how the columns are.
Column A
Blank
Blank
Blank
Blank
Blank
Blank
Blank
Direction (Header)
Blank
Down (Start of data)
Down
Up
Up
Column B
Information
Information
Information
Information
Information
Information
Information
TimeStep (Header)
FMT
0.09 (Start of data)
0.10
0.11
0.12
import openpyxl as xl; import os input_dir = 'C:\\Python\\Report' output_dir = 'C:\\Python\\Report\\output' template = os.path.join(input_dir, 'Template.xlsx') summaryFile = 'FinalReport_' schedule_index = 0 schedules=['T1', 'S1', 'S2'] files = [file for file in os.listdir(input_dir) if os.path.isfile(file) and file.startswith('RRT')] for file in files: input_file = os.path.join(input_dir, file) wb=xl.load_workbook(input_file) ws=wb.worksheets[1] # Open template wb2 = xl.load_workbook(template) ws2 = wb2.worksheets[1] # calculate total number of rows and # columns in source excel file mr = ws.max_row mc = ws.max_column # copying the cell values from source # excel file to destination excel file for i in range (1, mr + 1): for j in range (1, mc + 1): # reading cell value from source excel file c = ws.cell(row = i, column = j) # Cells for source data to pasted inside Template ws2.cell(row = i+8, column = j+1).value = c.value # saving the destination excel file output_file =os.path.join (output_dir, f"{summaryFile}_{schedules[schedule_index]}.xlsx") schedule_index += 1 wb2.save(output_file)