This is a follow up to the simpler question here.
I have hundreds of files looking something like this:
These files have names such as:
2301 Item 1.xlsx 2014 Item 2.xlsx 5321 Item 3.xlsx 3212 Item 200.xlsx
I also have a template file (
Template.xlsx) looking like this:
I want to:
- Copy the template file, and for each of the other files
- Give it the filename in cell C6.
- Copy the values of B2,B3,B3 over to the appropriate spot in the template (B4,B5,B6).
- Copy the filename to B8
I've written the following script in Python 3:
import os import shutil from xlrd import open_workbook from xlutils.copy import copy def duplicate_and_transfer(path): for filename in os.listdir(path): if filename == 'Template.xlsx': continue else: filepath = os.path.join(path, filename) excel_file = open_workbook(filepath) worksheet = excel_file.sheet_by_index(0) new_filename = worksheet.cell_value(5,2) new_filepath = os.path.join(path, new_filename + '.xls') r_value = [0,0,0] for row in range(3): r_value[row] = worksheet.cell_value(row+1,1) shutil.copy2(os.path.join(path, 'Template.xlsx'),new_filepath) rb = open_workbook(new_filepath) wb = copy(rb) s = wb.get_sheet(0) s.write(7,2,new_filename) for r in range(3): s.write(r+3,1,r_value[r]) wb.save(new_filepath) if __name__ == '__main__': duplicate_and_transfer(r'C:\Users\Stewie\Documents\excel_folder')
A few things:
- I have to make the new files
xlsx. They get corrupted if not (someone else have had this problem). Can I avoid this?
- Should I split this up into more functions?
This is fast enough, so my main issue is not with performance.