Xlwings, how do I open a single template workbook, create 2 copies, edit each copy, and save 2 separate workbooks

Question

I want to open a single excel file as a template, then create two copies of this template, then edited each book with related calculations, and then save as two separate and unrelated outputs.

After opening the template file, it does not seem possible to then work simultaneously with two workbooks using xlwings. While it appears there are two instances of the file, changes to one file over-write the values in the other file.

Here is some dummy code to show what I am tryign to do:

import xlwings as xw
import random

oneBook = xw.Book(r'TemplateExcelFile.xlsx')
# oneBook.save(r'TemplateExcelFile_temp')                   # first step in work around
twoBook = xw.Book(r'TemplateExcelFile.xlsx')                # this creates two instances/links to the same open file (not wanted)
# twoBook = xw.books.add(r'TemplateExcelFile_temp.xlsx')    # this approach does not open two separate workbooks
# twoBook = xw.Book(r'TemplateExcelFile_temp.xlsx')         # this approach (with step 1) opens the same workbook twice, but feels like a workaround

result1a = random.randint(1,10)
oneBookSheet = oneBook.sheets["Sheet 1"]
oneBookSheet.range('a1').value = result1a

result2a = result1a * random.randint(1,10)
twoBookSheet = twoBook.sheets["Sheet 2"]
twoBookSheet.range('a1').value = result2a

result1b = result2a * random.randint(1,2)
oneBookSheet = oneBook.sheets["Sheet 1"]
oneBookSheet.range('a2').value = result1b


oneBook.save()
twoBook.save()

Is there a (better) way to open the same workbook as a template for two outputs with xlwings?

Answer 1

You could take advantage of the use of Excel and the ease of move/copy Sheets and just create copies of the template, modify and move to new workbooks;

1. Open the template xlsx file
2. Create 2 (or whatever number of new Sheets you need) copies of the template Sheet in the same workbook
3. Edit the Sheets as desired as worksheets in the template workbook ('ws1' and 'ws2').
4. When editing is completed create 2 new workbooks ('wb1' and 'wb2')
5. Move the new modified Sheets one to each new workbook (and delete the default 'Sheet1' created when the workbook is created)
6. Save the new workbooks and End

This results in your two new workbooks with Sheets based off the Template sheet and the original template workbook is not changed.
You can change the order of updating sheets, creating workbooks and saving and can update the Sheets while in the original workbook or after they are migrated to their new workbooks but remember after the new workbooks are created there may be 4 Excel instances running.

import xlwings as xw

excel_template = 'template_file.xlsx'
with xw.App(visible=True) as app:
    wb = xw.Book(excel_template)
    ws = wb.sheets['Template']

    ###  Create template 2 Sheet
    ws.copy(after=ws, name="Template_New2")
    ws2 = wb.sheets['Template_New2']

    ###  Create template 1 Sheet
    ws.copy(after=ws, name="Template_new1")
    ws1 = wb.sheets['Template_New1']

    ### Add data to Sheets 1 and 2 as needed
    ws1["A2"].value = "Hello"
    ws1["B2"].value = "Workbook1"

    ws2["A10"].value = "Hello"
    ws2["B10"].value = "WorkBook2"

    ### Create new workbooks
    wb1 = xw.Book()
    wb1_sheet = wb1.sheets.active
    wb2 = xw.Book()
    wb2_sheet = wb2.sheets.active

    ### Move Sheets to new work book and Delete the unwanted Sheets
    ws1.copy(before=wb1_sheet)
    wb1_sheet.delete()

    ws2.copy(before=wb2_sheet)
    wb2_sheet.delete()

    ### Save the new workbooks
    wb1.save('Template_new1.xlsx')
    wb2.save('Template_new2.xlsx')