Jul-13-2020, 03:19 PM
Hello,
Right now when this script saves a file it uses the summaryFile variable 'Report_v1_' and then adds the first schedule name 'Nm' to the end so it saves as Report_v1_Nm, and then it loops around and saves the next as Report_v1_S01.
How would I make it so that if I wanted to start saving at 'S01' I can, instead of 'Nm'? If I change the schedule_index from 0 to 1, it does what I want but, it takes the first report in the input dir which is finalstats_nm, and saves it as Report_v1_S01 so it has "Nm" data saved under S01 which is not right. If I want to start at S01, I want it to grab the finalstats_S01 file from the input_dir, and save as Report_v1_S01.
Right now when this script saves a file it uses the summaryFile variable 'Report_v1_' and then adds the first schedule name 'Nm' to the end so it saves as Report_v1_Nm, and then it loops around and saves the next as Report_v1_S01.
How would I make it so that if I wanted to start saving at 'S01' I can, instead of 'Nm'? If I change the schedule_index from 0 to 1, it does what I want but, it takes the first report in the input dir which is finalstats_nm, and saves it as Report_v1_S01 so it has "Nm" data saved under S01 which is not right. If I want to start at S01, I want it to grab the finalstats_S01 file from the input_dir, and save as Report_v1_S01.
import openpyxl as xl; import os input_dir = 'C:\\Python\\Reports Combined' output_dir = 'C:\\Python\\Reports Combined\\output' template = 'C:\\Python\\Template.xlsx' summaryFile = 'Report_v1_' schedule_index = 0 schedules=['Nm', 'S01', 'S02','S03','S04','S05',S06'] files = [file for file in os.listdir(input_dir) if os.path.isfile(file) and file.endswith(".xlsx")] 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)