How to use Workbook.Add in the xlwings API way

The Add member of the Workbook object in the Excel object model is a method used to create a new workbook. In xlwings, which provides a powerful API to interact with Excel from Python, this functionality is accessed through the App class rather than directly from a Workbook instance. The App represents the Excel application itself, and its add() method creates a new workbook, returning a Book object (xlwings’ equivalent to a Workbook). This is essential for automating the generation of reports, dashboards, or any task requiring dynamic workbook creation.

Functionality:
The primary function is to launch a new, blank workbook in Excel. This new workbook becomes the active workbook and is added to the App.books collection. It provides a foundation for subsequent operations like adding data, creating charts, or applying formatting without needing a pre-existing file.

Syntax and Parameters:
In xlwings, the method is called on an App instance. The basic syntax is:

new_workbook = xw.App().add()

However, it is more common to use an existing application context. When you have an App object (e.g., app = xw.App() or when using xw.Book which creates an app implicitly), you call:

new_workbook = app.add()

The add() method does not take any parameters in xlwings. Its behavior is straightforward: it creates one new, empty workbook. This differs slightly from the native Excel VBA object model, where the Add method can accept a template parameter. In xlwings, to create a workbook from a template, you would typically use the Book constructor with a file path.

Code Examples:
Here are practical examples demonstrating the add() method.

1. Creating a new workbook in a new Excel instance:

import xlwings as xw

# Start a new Excel application
app = xw.App()
# Add a new, blank workbook
new_book = app.add()
# Write data to the first cell of the active sheet
new_book.sheets[0].range('A1').value = "New Workbook Data"
# Save the workbook
new_book.save(r'C:\Reports\Report1.xlsx')
# Close the workbook and quit Excel
new_book.close()
app.quit()

2. Adding multiple workbooks to an existing application instance:

import xlwings as xw

# Connect to a running instance or start a new one
app = xw.App(visible=True)
# Create the first new workbook
book1 = app.add()
book1.sheets[0].range('A1').value = "Workbook 1"
# Create a second new workbook
book2 = app.add()
book2.sheets[0].range('A1').value = "Workbook 2"
# At this point, two new workbooks are open in the same Excel application.
# ... perform other tasks ...
for book in app.books:
book.close()
app.quit()

3. Using within a context manager (recommended for resource management):

import xlwings as xw

with xw.App() as app:
# The `add()` method works the same within the context
new_book = app.add()
new_book.sheets[0].range('A1').value = "Created in Context"
new_book.save('context_workbook.xlsx')
# The context manager automatically closes the book and quits the app on exit.