Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Saving Report Names
#1
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)
	
Reply
#2
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.
Reply
#3
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)
Reply
#4
On which line? Please give the exact error message. In Error tags.
Reply
#5
(Jul-08-2020, 06:36 PM)ibreeden Wrote: 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
Reply
#6
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.
Reply
#7
(Jul-08-2020, 06:36 PM)ibreeden Wrote: On which line? Please give the exact error message. In Error tags.
It's actually the very last line that is causing that message.
Reply
#8
I figured it out.

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


Possibly Related Threads…
Thread Author Replies Views Last Post
  Print Report Invoice nio74maz 0 151 Jun-17-2021, 08:25 AM
Last Post: nio74maz
  fpdf adding a new font to my report KatMac 0 296 Apr-23-2021, 02:19 PM
Last Post: KatMac
  how to generate html report for each folder using pytest ktrsarath 0 718 Jun-30-2020, 05:14 AM
Last Post: ktrsarath
  Saving Excel workbook file with dataframe names Biplab1985 0 694 Jun-07-2020, 12:25 PM
Last Post: Biplab1985
  I need to get only string datatype and report in excel file. akshay3210 3 895 Dec-12-2019, 09:53 AM
Last Post: akshay3210
  python and py report different versions lordvold 3 1,012 Nov-27-2019, 11:01 PM
Last Post: lordvold
  pytest-html report customize manoj 4 10,247 Nov-26-2019, 09:10 AM
Last Post: manojshetty
  Generate a report in Python qureshi 2 1,794 Aug-24-2019, 04:50 AM
Last Post: ndc85430
  Crystal Report rpc86 5 8,511 Apr-10-2018, 02:17 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020