Duplicating Excel-workbook, and transferring data

Question

_{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 xls instead of 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.

Answer 1

• Since you use continue in the if clause, you can get rid of the else part and save a level of indentation.
• You can use a list-comprehension to build the list of r_value: [worksheet.cell_value(row + 1, 1) for row in range(3)].

• You can iterate directly over the content of r_value to set it back:

  for row, value in enumerate(r_value, 3):
      s.write(row, 1, value)
  

• I don't think you need to copy the template first and modify it after. You may be able to open the template and then save it using the new filename.

• You can use a second parameter with a default value to hold the name of the template.

Proposed improvements:

import os

from xlrd import open_workbook
from xlutils.copy import copy


def duplicate_and_transfer(path, template_name='Template.xlsx'):
    template = os.path.join(path, template_name)

    for filename in os.listdir(path):
        if filename == template_name:
            continue

        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')
        values = [worksheet.cell_value(row + 1, 1) for row in range(3)]

        rb = open_workbook(template)
        workbook = copy(rb)
        worksheet = workbook.get_sheet(0)
        worksheet.write(7, 2, new_filename)
        for row, value in enumerate(values, 3):
            worksheet.write(row, 1, value)

        workbook.save(new_filepath)


if __name__ == '__main__':
    duplicate_and_transfer(r'C:\Users\Stewie\Documents\excel_folder')

You may as well be able to open the template once and save it several times.

Lastly, if you have troubles working with XLSX files, you may want to try openpyxl instead:

import os

from openpyxl import load_workbook


def duplicate_and_transfer(path, template_name='Template.xlsx'):
    template = load_workbook(os.path.join(path, template_name))

    for filename in os.listdir(path):
        if filename == template_name:
            continue

        workbook = load_workbook(os.path.join(path, filename))
        worksheet = workbook.worksheets[0]

        new_filename = worksheet['C6'].value
        template['B8'] = new_filename
        new_filepath = os.path.join(path, new_filename + '.xlsx')

        # Using double unpacking as the ranges will return a 3-tuple of 1-tuple
        for (src,), (dest,) in zip(worksheet['B2:B4'], template['B4:B6']):
            dest.value = src.value

        template.save(new_filepath)


if __name__ == '__main__':
    duplicate_and_transfer(r'C:\Users\Stewie\Documents\excel_folder')