Jul-09-2020, 03:57 PM
(This post was last modified: Jul-09-2020, 04:30 PM by Kristenl2784.)
Hello,
I can't seem to figure out why this script will only look at the 1st source file, perform the actions, paste to template and save then stop. It's supposed to loop through the 3 source files I have in my input dir perform some actions paste into my template then save I should have 3 different files saved. With the schedule index=0 the first source file will get pasted into my template and then save and the script will stop, if I change it to schedule index=1 the second source file will get pasted into the template and save and the script will stop. But I want it to just go through all 3. The second to last line in my script I thought was supposed to do this schedule_index += 1. I thought if schedule index=0 means start at the first file, and loop through the rest.
I fixed the issue I needed to indent lines 45-47
I can't seem to figure out why this script will only look at the 1st source file, perform the actions, paste to template and save then stop. It's supposed to loop through the 3 source files I have in my input dir perform some actions paste into my template then save I should have 3 different files saved. With the schedule index=0 the first source file will get pasted into my template and then save and the script will stop, if I change it to schedule index=1 the second source file will get pasted into the template and save and the script will stop. But I want it to just go through all 3. The second to last line in my script I thought was supposed to do this schedule_index += 1. I thought if schedule index=0 means start at the first file, and loop through the rest.
import openpyxl as xl; import os import pandas as pd from openpyxl.utils.dataframe import dataframe_to_rows input_dir = 'C:\\Python\\Reports' output_dir = 'C:\\Python\\Reports\\output' template = os.path.join(input_dir, 'Template.xlsx') summaryFile = 'FinalReport_' searchName = 'Down*' TimeStep1 = '0.09:00:00' TimeStep2 = '0.09:01:39' schedule_index = 0 schedules=['Nm', 'S01', 'S02'] files = [file for file in os.listdir(input_dir) if os.path.isfile(file) and file.startswith('MGR')] for file in files: df1 = pd.read_excel(file,sheet_name = 'Reports', nrows=9, header=None) df2 = pd.read_excel(file, sheet_name = 'Reports', skiprows=9, header=None) df1.columns = ["Direction", "Time Step", "Road1", "Distance","Distance","Distance","Road2","Road3","Speed"] df2.columns = ["Direction", "Time Step", "Road1", "Distance","Distance","Distance","Road2","Road3","Speed"] df2 = df2[df2["Direction"].str.contains(searchName na=False)] df2 = df2[(df2['Time Step'] >= TimeStep1) & (df2['Time Step'] <= TimeStep2)] all_dfs = [df1, df2] df3=pd.concat(all_dfs).reset_index(drop=True) rows = dataframe_to_rows(df3, header=None) # Open template wb2 = xl.load_workbook(template) ws2 = wb2.worksheets[1] for r_idx, row in enumerate(rows, 8): for c_idx, value in enumerate(row, 1): ws2.cell(row=r_idx, column=c_idx, value=value) output_file =os.path.join (output_dir, f"{summaryFile}_{schedules[schedule_index]}.xlsx") schedule_index += 1 wb2.save(output_file)
I fixed the issue I needed to indent lines 45-47