0
# Utility Functions
1

2
Helper functions for data type conversion, configuration management, and Excel-specific operations like date/time handling. These utilities support the core xlwings functionality and provide convenient helpers for common operations.
3

4
## Capabilities
5

6
### Date and Time Utilities
7

8
Functions for converting between Excel's date serial format and Python datetime objects.
9

10
```python { .api }
11
def to_datetime(xldate: float, datemode: int = 0):
12
    """
13
    Convert Excel serial date to Python datetime.
14
    
15
    Args:
16
        xldate (float): Excel serial date number.
17
        datemode (int): Date mode (0 for 1900 system, 1 for 1904 system).
18
    
19
    Returns:
20
        datetime: Python datetime object.
21
        
22
    Examples:
23
        # Convert Excel date serial to datetime
24
        dt = xw.to_datetime(44197)  # 2021-01-01
25
        
26
        # Handle 1904 date system (Mac)
27
        dt_mac = xw.to_datetime(43831, datemode=1)
28
    """
29

30
# Additional date utilities in xlwings.utils module
31
def np_datetime_to_xl(np_date):
32
    """
33
    Convert NumPy datetime to Excel serial format.
34
    
35
    Args:
36
        np_date: NumPy datetime64 object.
37
        
38
    Returns:
39
        float: Excel serial date.
40
    """
41

42
def pd_datetime_to_xl(pd_date):
43
    """
44
    Convert pandas datetime to Excel serial format.
45
    
46
    Args:  
47
        pd_date: pandas Timestamp or datetime.
48
        
49
    Returns:
50
        float: Excel serial date.
51
    """
52

53
def datetime_to_xl(dt):
54
    """
55
    Convert Python datetime to Excel serial format.
56
    
57
    Args:
58
        dt (datetime): Python datetime object.
59
        
60
    Returns:
61
        float: Excel serial date.
62
    """
63
```
64

65
### Data Preparation Utilities
66

67
Functions for preparing and validating data before Excel operations.
68

69
```python { .api }
70
def prepare_xl_data_value(value):
71
    """
72
    Prepare Python data for Excel consumption.
73
    
74
    Args:
75
        value: Python data structure to prepare.
76
        
77
    Returns:
78
        Prepared data suitable for Excel ranges.
79
        
80
    Description:
81
        Handles type conversion, None values, and data structure
82
        normalization for optimal Excel compatibility.
83
    """
84

85
def get_duplicates(seq):
86
    """
87
    Find duplicate items in a sequence.
88
    
89
    Args:
90
        seq: Sequence to check for duplicates.
91
        
92
    Returns:
93
        list: List of duplicate items.
94
        
95
    Examples:
96
        dupes = get_duplicates(['a', 'b', 'a', 'c', 'b'])
97
        # Returns: ['a', 'b']
98
    """
99
```
100

101
### Performance and Caching Utilities
102

103
Utilities for optimizing xlwings performance through caching and call optimization.
104

105
```python { .api }
106
def get_cache():
107
    """
108
    Get xlwings internal cache for performance optimization.
109
    
110
    Returns:
111
        dict: Cache dictionary for storing computed values.
112
        
113
    Description:
114
        Used internally by xlwings for caching expensive operations
115
        like engine initialization and object references.
116
    """
117

118
def log_call(func):
119
    """
120
    Decorator for logging function calls (debugging utility).
121
    
122
    Args:
123
        func (callable): Function to wrap with logging.
124
        
125
    Returns:
126
        callable: Wrapped function with call logging.
127
        
128
    Examples:
129
        @log_call
130
        def my_function(x, y):
131
            return x + y
132
    """
133
```
134

135
### Configuration and Environment Utilities
136

137
Functions for managing xlwings configuration and environment settings.
138

139
```python
140
import xlwings as xw
141

142
# Configuration file locations (from xlwings.__init__)
143
# USER_CONFIG_FILE: Platform-specific user configuration path
144
# - macOS: ~/Library/Containers/com.microsoft.Excel/Data/xlwings.conf  
145
# - Other: ~/.xlwings/xlwings.conf
146

147
# Usage examples
148
def setup_xlwings_config():
149
    """Setup xlwings configuration programmatically."""
150
    import configparser
151
    import os
152
    
153
    config = configparser.ConfigParser()
154
    
155
    # Set default configuration
156
    config['xlwings'] = {
157
        'INTERPRETER': 'python',
158
        'INTERPRETER_MAC': 'python',
159
        'PYTHONPATH': '',
160
        'LOG_FILE': '',
161
        'SHOW_CONSOLE': 'False'
162
    }
163
    
164
    # Write configuration
165
    config_path = xw.USER_CONFIG_FILE
166
    os.makedirs(os.path.dirname(config_path), exist_ok=True)
167
    
168
    with open(config_path, 'w') as f:
169
        config.write(f)
170

171
def read_xlwings_config():
172
    """Read current xlwings configuration."""
173
    import configparser
174
    
175
    config = configparser.ConfigParser()
176
    try:
177
        config.read(xw.USER_CONFIG_FILE)
178
        return dict(config['xlwings'])
179
    except:
180
        return {}
181
```
182

183
### Data Validation and Type Checking
184

185
Utilities for validating data types and structures before Excel operations.
186

187
```python
188
# Custom validation utilities (usage patterns)
189
def validate_range_data(data):
190
    """Validate data is suitable for Excel range assignment."""
191
    if data is None:
192
        return True
193
    
194
    # Check for supported types
195
    if isinstance(data, (int, float, str, bool)):
196
        return True
197
    
198
    # Check for sequences
199
    if hasattr(data, '__iter__') and not isinstance(data, str):
200
        # Validate nested structure
201
        try:
202
            # Ensure rectangular structure for 2D data
203
            if isinstance(data, list) and len(data) > 0:
204
                if isinstance(data[0], list):
205
                    first_len = len(data[0])
206
                    return all(len(row) == first_len for row in data)
207
            return True
208
        except:
209
            return False
210
    
211
    # Check pandas/numpy objects
212
    try:
213
        import pandas as pd
214
        import numpy as np
215
        if isinstance(data, (pd.DataFrame, pd.Series, np.ndarray)):
216
            return True
217
    except ImportError:
218
        pass
219
    
220
    return False
221

222
def normalize_range_address(address):
223
    """Normalize range address to standard format."""
224
    import re
225
    
226
    # Handle various address formats
227
    if isinstance(address, tuple):
228
        # Convert (row, col) to A1 notation
229
        row, col = address
230
        col_letter = ''
231
        while col > 0:
232
            col -= 1
233
            col_letter = chr(65 + col % 26) + col_letter
234
            col //= 26
235
        return f"{col_letter}{row}"
236
    
237
    # Clean up string addresses
238
    if isinstance(address, str):
239
        # Remove spaces and normalize
240
        return re.sub(r'\s+', '', address.upper())
241
    
242
    return str(address)
243

244
def get_range_dimensions(data):
245
    """Get dimensions of data for range sizing."""
246
    if data is None:
247
        return (1, 1)
248
    
249
    # Scalar values
250
    if isinstance(data, (int, float, str, bool)):
251
        return (1, 1)
252
    
253
    # 2D data (list of lists)
254
    if isinstance(data, list) and len(data) > 0:
255
        if isinstance(data[0], list):
256
            return (len(data), len(data[0]) if data[0] else 0)
257
        else:
258
            return (len(data), 1)
259
    
260
    # pandas DataFrame
261
    try:
262
        import pandas as pd
263
        if isinstance(data, pd.DataFrame):
264
            return data.shape
265
        elif isinstance(data, pd.Series):
266
            return (len(data), 1)
267
    except ImportError:
268
        pass
269
    
270
    # NumPy array
271
    try:
272
        import numpy as np
273
        if isinstance(data, np.ndarray):
274
            if data.ndim == 1:
275
                return (len(data), 1)
276
            elif data.ndim == 2:
277
                return data.shape
278
    except ImportError:
279
        pass
280
    
281
    # Default for unknown types
282
    return (1, 1)
283
```
284

285
### Error Handling Utilities
286

287
Helper functions for robust error handling in xlwings operations.
288

289
```python
290
def safe_excel_operation(operation, *args, **kwargs):
291
    """Safely execute Excel operation with error handling."""
292
    try:
293
        return operation(*args, **kwargs)
294
    except Exception as e:
295
        # Log error and provide meaningful feedback
296
        error_msg = f"Excel operation failed: {str(e)}"
297
        print(f"WARNING: {error_msg}")
298
        return None
299

300
def retry_excel_operation(operation, max_retries=3, delay=0.1):
301
    """Retry Excel operation with exponential backoff."""
302
    import time
303
    
304
    for attempt in range(max_retries):
305
        try:
306
            return operation()
307
        except Exception as e:
308
            if attempt == max_retries - 1:
309
                raise e
310
            time.sleep(delay * (2 ** attempt))
311
    
312
def with_excel_error_handling(func):
313
    """Decorator for comprehensive Excel error handling."""
314
    from functools import wraps
315
    
316
    @wraps(func)
317
    def wrapper(*args, **kwargs):
318
        try:
319
            return func(*args, **kwargs)
320
        except Exception as e:
321
            # Handle common Excel errors
322
            error_type = type(e).__name__
323
            if 'COM' in error_type:
324
                raise XlwingsError(f"Excel COM error: {str(e)}")
325
            elif 'AppleScript' in error_type:
326
                raise XlwingsError(f"Excel AppleScript error: {str(e)}")
327
            else:
328
                raise XlwingsError(f"Excel operation error: {str(e)}")
329
    
330
    return wrapper
331
```
332

333
### Performance Monitoring Utilities
334

335
Tools for monitoring and optimizing xlwings performance.
336

337
```python
338
def measure_excel_performance(func):
339
    """Decorator to measure Excel operation performance."""
340
    import time
341
    from functools import wraps
342
    
343
    @wraps(func)
344
    def wrapper(*args, **kwargs):
345
        start_time = time.time()
346
        result = func(*args, **kwargs)
347
        end_time = time.time()
348
        
349
        duration = end_time - start_time
350
        print(f"{func.__name__} took {duration:.3f} seconds")
351
        return result
352
    
353
    return wrapper
354

355
def profile_excel_operations(operations):
356
    """Profile multiple Excel operations and report performance."""
357
    import time
358
    
359
    results = {}
360
    
361
    for name, operation in operations.items():
362
        start_time = time.time()
363
        try:
364
            operation()
365
            duration = time.time() - start_time
366
            results[name] = {'success': True, 'duration': duration}
367
        except Exception as e:
368
            duration = time.time() - start_time
369
            results[name] = {'success': False, 'duration': duration, 'error': str(e)}
370
    
371
    return results
372
```
373

374
## Usage Patterns
375

376
Common utility usage patterns for xlwings applications:
377

378
```python
379
import xlwings as xw
380
from datetime import datetime
381

382
# Pattern 1: Safe data operations
383
@with_excel_error_handling
384
def safe_data_write(sheet, address, data):
385
    """Safely write data with validation."""
386
    if validate_range_data(data):
387
        sheet.range(address).value = data
388
    else:
389
        raise ValueError("Invalid data format for Excel range")
390

391
# Pattern 2: Performance monitoring
392
@measure_excel_performance
393
def bulk_data_operation(workbook):
394
    """Monitor performance of bulk operations."""
395
    for i, sheet in enumerate(workbook.sheets):
396
        # Simulated bulk operation
397
        data = [[j + i for j in range(100)] for _ in range(100)]
398
        sheet.range('A1').value = data
399

400
# Pattern 3: Configuration management
401
def setup_optimal_excel_config():
402
    """Configure Excel for optimal performance."""
403
    for app in xw.apps:
404
        app.calculation = 'manual'
405
        app.screen_updating = False
406
        app.display_alerts = False
407
    
408
    # Restore at end of operations
409
    def restore_config():
410
        for app in xw.apps:
411
            app.calculation = 'automatic'
412
            app.screen_updating = True
413
            app.display_alerts = True
414
    
415
    return restore_config
416

417
# Usage
418
restore = setup_optimal_excel_config()
419
try:
420
    # Perform bulk operations
421
    pass
422
finally:
423
    restore()
424
```
425

426
## Types
427

428
```python { .api }
429
# Utility function types
430
DateConversionFunction = Callable[[float, int], datetime]
431
DataPreparationFunction = Callable[[Any], Any]
432
ValidationFunction = Callable[[Any], bool]
433
CacheFunction = Callable[[], dict]
434
LoggingDecorator = Callable[[Callable], Callable]
435

436
# Configuration types
437
ConfigDict = dict[str, str]
438
ConfigPath = str
439

440
# Performance monitoring types
441
PerformanceResult = dict[str, Union[bool, float, str]]
442
OperationDict = dict[str, Callable[[], Any]]
443
```