Saving Report Names

[Kristenl2784]

Hello,

I'm trying to get the below code to save my files a certain way, and I can't seem to get it to work. What I want to happen is once the data is pasted into the template, it is then saved as 'NewReport_1a_Nm', and then the next file be saved as 'NewReport_1a_S01' I can't seem to figure out how to make this happen, I know I'm not typing the code correctly for out_file near the bottom. Basically what I want this code to do is loop through excel files I have saved inside a folder, take one file paste it to the template then it needs to save the file with a certain name as mentioned above, and then loop to the next file paste it to the template, and save the file with a certain name as mentioned above. I thought by making Schedules 'Nm, S01, S02 I would be able to concatenate this name to each new report.

import openpyxl as xl; 
import os
 
input_dir = 'C:\\Python\\Report Detail'
output_dir = 'C:\\Python\\Report Detail\\output'
template = 'C:\\Python\\Report_Template.xlsx'
summaryFile = 'NewReport_1a'

schedules=[
		"Nm",
		"S01",
		"S02"]
  
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) # make the full path, so that it does not depend on input_dir and CWD being the same
    wb=xl.load_workbook(input_file)
    ws=wb.worksheets[1]
      
    # Open template
    wb2 = xl.load_workbook(template) 
    ws2 = wb2.worksheets[2] 
     
    # 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+12, column = j+1).value = c.value 
     
    # saving the destination excel file 
    output_file = (output_dir, summaryFile, schedules(files))
    wb2.save(output_file)
	

[ibreeden]

You need an index for the schedules. So near the definition of "schedules" you should add a line:

schedule_index = 0

Then when creating the output_file you must use the index. And also increment it for the next file.

    output_file = (output_dir, f"{summaryFile}_{schedules[schedule_index]}")
    schedule_index += 1

(Untested)

It is your responsibility to make the schedules list large enough.

[Kristenl2784]

Like this? I end up with AttributeError: 'tuple' object has no attribute 'write'

import openpyxl as xl; 
import os
  
input_dir = 'C:\\Python\\Report Detail'
output_dir = 'C:\\Python\\Report Detail\\output'
template = 'C:\\Python\\Report_Template.xlsx'
summaryFile = 'NewReport_1a'

schedule_index = 0
schedules=[
        "Nm",
        "S01",
        "S02"]
   
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) # make the full path, so that it does not depend on input_dir and CWD being the same
    wb=xl.load_workbook(input_file)
    ws=wb.worksheets[1]
       
    # Open template
    wb2 = xl.load_workbook(template) 
    ws2 = wb2.worksheets[2] 
      
    # 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+12, column = j+1).value = c.value 
      
    # saving the destination excel file 
    output_file = (output_dir, f"{summaryFile}_{schedules[schedule_index]}")
    schedule_index += 1
    wb2.save(output_file)

[ibreeden]

On which line? Please give the exact error message. In Error tags.

[Kristenl2784]

On which line? Please give the exact error message. In Error tags.

The variable explorer is saying that the output_file is creating a tuple.
And the error is at the line in the code for output_file

[Yoriz]

output_file = (output_dir, f"{summaryFile}_{schedules[schedule_index]}")

output_file is a tuple of two strings not a single string that represents a valid file path.

[Kristenl2784]

On which line? Please give the exact error message. In Error tags.

It's actually the very last line that is causing that message.

[Kristenl2784]

I figured it out.

output_file =os.path.join (output_dir, f"{summaryFile}_{schedules[schedule_index]}.xlsx")