0 Replies - 759 Views - Last Post: 18 June 2020 - 08:39 AM Rate Topic: -----

#1 KLL2784   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 18-June 20

Excel file range and save as

Posted 18 June 2020 - 08:39 AM

Hello,

I'm trying to make my life a little easier when working with some excel files

I would like to get this script to do a couple things:

How could I make it so I can look at a certain range of excel files. Maybe I don't want to loop through all 27 files for example, maybe I want to only loop through files 10-20, or 5-10 for instance. Right now the script loops through all the excel files I have in my input folder. Sometimes I do want to loop through all of them, but there are times when I just want to loop through a certain range of them.

I want to save the files as a different name. Right now the excel files are called: NNB-2a_v03_Nm-Report, NNB-2a_v03_T01-Report, NNB-2a_v03_T02-Report

I want to save them as: Report-Nm, Report-T01, Report-T02


    import openpyxl as xl; 
    import os
    

    input_dir = 'C:/data'
    output_dir = os.path.join(input_dir, 'output')
    template = 'C:/data/Template.xlsx'


    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[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 = os.path.join(output_dir, file)
        wb2.save(output_file)


Is This A Good Question/Topic? 0
  • +

Page 1 of 1