0
# Collections and Module Functions
1

2
Collections for managing multiple Excel objects and module-level functions for high-level operations like loading data and viewing in Excel. These provide convenient access patterns and batch operations across Excel objects.
3

4
## Capabilities
5

6
### Collections
7

8
xlwings provides several collections that manage multiple Excel objects and provide convenient access patterns. All collections support indexing, iteration, and common collection operations.
9

10
```python { .api }
11
# Global collections (imported from xlwings module)
12
apps: Apps          # Collection of all App instances
13
books: Books        # Collection of all Book instances  
14
sheets: Sheets      # Collection of all Sheet instances
15
engines: Engines    # Collection of available Excel engines
16

17
class Collection:
18
    """Base collection class providing common collection functionality."""
19
    
20
    def __call__(self, name_or_index):
21
        """Access item by name or index (1-based for Excel compatibility)."""
22
    
23
    def __len__(self) -> int:
24
        """Number of items in collection."""
25
    
26
    def __iter__(self):
27
        """Iterate over collection items."""
28
    
29
    def __getitem__(self, key):
30
        """Access item by key (supports both string names and integer indices)."""
31
    
32
    def __contains__(self, key) -> bool:
33
        """Check if item exists in collection."""
34
    
35
    @property
36
    def count(self) -> int:
37
        """Number of items in collection."""
38
    
39
    @property
40
    def api(self):
41
        """Access to native Excel collection object."""
42

43
class Apps(Collection):
44
    """Collection of Excel application instances."""
45
    
46
    def add(self, **kwargs) -> App:
47
        """
48
        Create a new Excel application.
49
        
50
        Args:
51
            **kwargs: Arguments passed to App constructor.
52
            
53
        Returns:
54
            App: New Excel application instance.
55
        """
56
    
57
    @property
58
    def active(self) -> App:
59
        """Currently active Excel application."""
60

61
class Books(Collection):
62
    """Collection of Excel workbooks across all applications."""
63
    
64
    def add(self, **kwargs) -> Book:
65
        """
66
        Create a new workbook.
67
        
68
        Args:
69
            **kwargs: Arguments for workbook creation.
70
            
71
        Returns:
72
            Book: New workbook instance.
73
        """
74
    
75
    def open(self, fullname: str, **kwargs) -> Book:
76
        """
77
        Open an existing workbook.
78
        
79
        Args:
80
            fullname (str): Full path to workbook file.
81
            **kwargs: Additional arguments for opening workbook.
82
            
83
        Returns:
84
            Book: Opened workbook instance.
85
        """
86
    
87
    @property
88
    def active(self) -> Book:
89
        """Currently active workbook."""
90

91
class Sheets(Collection):
92
    """Collection of worksheets across all workbooks."""
93
    
94
    def add(self, name: str = None, before=None, after=None, **kwargs) -> Sheet:
95
        """
96
        Add a new worksheet.
97
        
98
        Args:
99
            name (str, optional): Worksheet name.
100
            before (Sheet, optional): Sheet to insert before.
101
            after (Sheet, optional): Sheet to insert after.
102
            **kwargs: Additional arguments.
103
            
104
        Returns:
105
            Sheet: New worksheet instance.
106
        """
107
    
108
    @property  
109
    def active(self) -> Sheet:
110
        """Currently active worksheet."""
111

112
class Engines(Collection):
113
    """Collection of available Excel engines."""
114
    
115
    def add(self, engine: Engine):
116
        """
117
        Add an engine to the collection.
118
        
119
        Args:
120
            engine (Engine): Engine instance to add.
121
        """
122
    
123
    @property
124
    def active(self) -> Engine:
125
        """Currently active engine for new Excel operations."""
126
    
127
    @active.setter
128
    def active(self, engine: Engine): ...
129
```
130

131
Usage examples:
132

133
```python
134
import xlwings as xw
135

136
# Working with apps collection
137
app1 = xw.apps.add()  # Create new app
138
app2 = xw.apps.add(visible=True)
139

140
print(len(xw.apps))  # Number of app instances
141
for app in xw.apps:  # Iterate over apps
142
    print(app.version)
143

144
# Working with books collection  
145
wb1 = xw.books.add()  # Create new workbook
146
wb2 = xw.books.open('/path/to/existing.xlsx')  # Open existing
147

148
active_book = xw.books.active  # Get active workbook
149
book_by_name = xw.books['MyWorkbook.xlsx']  # Access by name
150

151
# Working with sheets collection
152
ws1 = xw.sheets.add('NewSheet')  # Add new sheet
153
ws2 = xw.sheets.add('DataSheet', after=ws1)  # Add after another sheet
154

155
active_sheet = xw.sheets.active  # Get active sheet
156
sheet_by_name = xw.sheets['Sheet1']  # Access by name
157

158
# Working with engines
159
print([engine.name for engine in xw.engines])  # List available engines
160
xw.engines.active = xw.engines['excel']  # Set active engine
161
```
162

163
### Module-Level Functions
164

165
High-level functions for common Excel operations that work across different Excel objects and provide simplified interfaces for complex operations.
166

167
```python { .api }
168
def load(json: dict, sheet: Sheet = None, header: bool = True, index: bool = True):
169
    """
170
    Load JSON data into Excel worksheet.
171
    
172
    Args:
173
        json (dict): Data to load into Excel. Can contain nested structures.
174
        sheet (Sheet, optional): Target worksheet. Uses active sheet if None.
175
        header (bool): Whether to include header row. Defaults to True.
176
        index (bool): Whether to include index column. Defaults to True.
177
    
178
    Examples:
179
        # Load simple data
180
        data = {'A': [1, 2, 3], 'B': [4, 5, 6]}
181
        xw.load(data)
182
        
183
        # Load to specific sheet
184
        ws = xw.sheets['MySheet']
185
        xw.load(data, sheet=ws, header=False)
186
    """
187

188
def view(data, sheet_name: str = None):
189
    """
190
    Display data in Excel viewer for inspection and analysis.
191
    
192
    Args:
193
        data: Data to display. Supports pandas DataFrames, NumPy arrays, 
194
              lists, dictionaries, and other common Python data structures.
195
        sheet_name (str, optional): Name for the viewer sheet.
196
    
197
    Examples:
198
        import pandas as pd
199
        import numpy as np
200
        
201
        # View DataFrame
202
        df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
203
        xw.view(df)
204
        
205
        # View NumPy array
206
        arr = np.random.rand(10, 5)
207
        xw.view(arr, sheet_name='RandomData')
208
        
209
        # View nested list
210
        matrix = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
211
        xw.view(matrix)
212
    """
213
```
214

215
Usage examples:
216

217
```python
218
import xlwings as xw
219
import pandas as pd
220
import numpy as np
221

222
# Using load() function
223
sales_data = {
224
    'Product': ['A', 'B', 'C'],
225
    'Q1': [100, 150, 200],
226
    'Q2': [120, 160, 180],
227
    'Q3': [110, 170, 220],
228
    'Q4': [130, 155, 210]
229
}
230

231
# Load into active sheet
232
wb = xw.books.add()
233
xw.load(sales_data)
234

235
# Load into specific sheet without headers
236
ws = wb.sheets.add('RawData')
237
xw.load(sales_data, sheet=ws, header=False)
238

239
# Using view() function for data exploration
240
df = pd.DataFrame({
241
    'Date': pd.date_range('2024-01-01', periods=100),
242
    'Value': np.random.randn(100).cumsum(),
243
    'Category': np.random.choice(['A', 'B', 'C'], 100)
244
})
245

246
# Quick view of DataFrame
247
xw.view(df, sheet_name='TimeSeries')
248

249
# View correlation matrix
250
correlation = df[['Value']].corr()
251
xw.view(correlation, sheet_name='Correlation')
252

253
# View summary statistics
254
summary = df.describe()
255
xw.view(summary, sheet_name='Statistics')
256
```
257

258
### Collection Patterns and Best Practices
259

260
Common patterns for working with xlwings collections effectively:
261

262
```python
263
# Pattern 1: Safe collection access
264
def get_or_create_book(name):
265
    """Get existing book or create new one."""
266
    try:
267
        return xw.books[name]
268
    except KeyError:
269
        return xw.books.add().save(name)
270

271
# Pattern 2: Batch operations on collections
272
def close_all_books():
273
    """Close all open workbooks."""
274
    for book in xw.books:
275
        book.close()
276

277
# Pattern 3: Finding objects by criteria
278
def find_sheets_by_pattern(pattern):
279
    """Find sheets matching name pattern."""
280
    import re
281
    matching_sheets = []
282
    for sheet in xw.sheets:
283
        if re.match(pattern, sheet.name):
284
            matching_sheets.append(sheet)
285
    return matching_sheets
286

287
# Pattern 4: Engine management
288
def with_engine(engine_name):
289
    """Context manager for temporary engine switching."""
290
    from contextlib import contextmanager
291
    
292
    @contextmanager
293
    def engine_context():
294
        original = xw.engines.active
295
        try:
296
            xw.engines.active = xw.engines[engine_name]
297
            yield
298
        finally:
299
            xw.engines.active = original
300
    
301
    return engine_context()
302

303
# Usage
304
with with_engine('calamine'):
305
    # Operations using Calamine engine
306
    wb = xw.books.open('large_file.xlsx')
307
    data = wb.sheets[0].used_range.value
308
```
309

310
## Advanced Collection Operations
311

312
```python { .api }
313
# Advanced collection methods and properties
314
class Books(Collection):
315
    @property
316
    def active(self) -> Book:
317
        """Currently active workbook."""
318
    
319
    def add(self, template: str = None) -> Book:
320
        """
321
        Add new workbook, optionally from template.
322
        
323
        Args:
324
            template (str, optional): Template file path.
325
        """
326

327
class Sheets(Collection):  
328
    def add(self, name: str = None, before=None, after=None) -> Sheet:
329
        """Add worksheet with positioning control."""
330
    
331
    def copy(self, before=None, after=None) -> Sheet:
332
        """Copy active sheet to new position."""
333

334
# Engine-specific collections
335
class Engines(Collection):
336
    def __getitem__(self, key: str) -> Engine:
337
        """Get engine by name ('excel', 'calamine', 'remote', etc.)."""
338
    
339
    @property  
340
    def available(self) -> list:
341
        """List of available engine names."""
342
```
343

344
Usage examples:
345

346
```python
347
# Advanced workbook operations
348
template_wb = xw.books.add(template='/path/to/template.xlsx')
349

350
# Advanced sheet operations  
351
data_sheet = xw.sheets.add('Data', before=xw.sheets[0])
352
copy_sheet = data_sheet.copy(after=data_sheet)
353

354
# Engine inspection and management
355
print(f"Available engines: {xw.engines.available}")
356
for engine in xw.engines:
357
    print(f"Engine: {engine.name}, Type: {engine.type}")
358

359
# Conditional engine usage
360
if 'calamine' in xw.engines.available:
361
    fast_engine = xw.engines['calamine']
362
    fast_engine.activate()
363
```
364

365
## Types
366

367
```python { .api }
368
# Collection type aliases
369
Apps = Collection[App]
370
Books = Collection[Book]
371
Sheets = Collection[Sheet]  
372
Engines = Collection[Engine]
373

374
# Function signatures for type checking
375
LoadFunction = Callable[[dict, Optional[Sheet], bool, bool], None]
376
ViewFunction = Callable[[Any, Optional[str]], None]
377
```