How to use Application.NewWorkbook in the xlwings API way

The Application.NewWorkbook property in xlwings provides a powerful way to create a new Excel workbook programmatically. It returns a Workbook object representing the newly created workbook, allowing for immediate manipulation of its contents, sheets, and properties. This functionality is essential for automating report generation, data processing workflows, or any task requiring the dynamic creation of Excel files without manual intervention.

Functionality
The primary function of Application.NewWorkbook is to generate a fresh, blank workbook in Excel. This new workbook becomes the active workbook, and you can start adding data, formatting, or charts right away. It is particularly useful in scenarios where you need to produce multiple output files from a single data source or when building templates on the fly. Unlike simply opening an existing file, this method ensures you begin with a clean slate, adhering to default Excel settings unless otherwise modified.

Syntax and Parameters
In xlwings, you access this property through the app object, which represents the Excel Application. The basic syntax is:

new_wb = app.books.add()

While the VBA object model uses Application.NewWorkbook, xlwings employs the books.add() method as the direct equivalent. The add() method can accept several optional parameters to customize the new workbook:

• Template: A string specifying the path to an Excel template file (.xltx, .xltm). If provided, the new workbook is based on this template instead of the default blank workbook.
• Before: A Sheet object. The new workbook is placed before this specified sheet (relevant when adding within a specific workbook context, though typically used with app.books.add() for a new file).
• After: A Sheet object. The new workbook is placed after this specified sheet.

For most common use cases, calling app.books.add() without arguments is sufficient. The method returns a Book object (xlwings’ term for a Workbook), which you can assign to a variable for further operations.

Code Examples

1. Creating a Simple New Workbook and Adding Data:

import xlwings as xw

# Start a new Excel instance (visible=False for background operation)
app = xw.App(visible=True)

# Create a new workbook
new_workbook = app.books.add()

# Access the first sheet and write data
sheet = new_workbook.sheets[0]
sheet.range('A1').value = 'Product'
sheet.range('B1').value = 'Sales'
sheet.range('A2').value = ['Widget A', 'Widget B', 'Widget C']
sheet.range('B2').value = [1500, 2100, 1850]

# Save the workbook
new_workbook.save(r'C:\Reports\NewReport.xlsx')
# new_workbook.close()
# app.quit()

2. Creating a Workbook from a Template:

import xlwings as xw

app = xw.App(visible=False)

# Specify the path to your template
template_path = r'C:\Templates\CompanyReport.xltx'

# Create a new workbook based on the template
new_wb = app.books.add(template_path)

# The new workbook already contains the template's formatting and sheets.
# You can populate predefined cells.
new_wb.sheets['Data'].range('C5').value = 'Q4-2023'
new_wb.sheets['Summary'].range('B10').value = 95000

# Save it with a new name
new_wb.save(r'C:\Reports\Q4_Report_Final.xlsx')
app.quit()

3. Creating Multiple Workbooks in a Loop:

import xlwings as xw

app = xw.App(visible=False)

departments = ['Sales', 'Marketing', 'Engineering', 'HR']

for dept in departments:
    # Create a new workbook for each department
    wb = app.books.add()
    wb.sheets[0].name = dept
    wb.sheets[0].range('A1').value = f'Department: {dept}'
    # ... add more department-specific data ...
    wb.save(fr'C:\DepartmentReports\{dept}_Data.xlsx')
    wb.close() # Close the workbook after saving to free memory

app.quit()