0
# Utility Functions and Helpers
1

2
Essential utility functions for Excel coordinate conversions, cell reference handling, range creation, and worksheet name management. These functions help convert between different Excel addressing systems and handle Excel-specific formatting requirements.
3

4
## Capabilities
5

6
### Cell Reference Conversion
7

8
Convert between row/column numbers and Excel cell references.
9

10
```python { .api }
11
def xl_rowcol_to_cell(row, col, row_abs=False, col_abs=False):
12
    """
13
    Convert row and column numbers to Excel cell reference.
14
    
15
    Args:
16
        row (int): Cell row (0-indexed)
17
        col (int): Cell column (0-indexed)  
18
        row_abs (bool): Make row reference absolute (add $)
19
        col_abs (bool): Make column reference absolute (add $)
20
        
21
    Returns:
22
        str: Excel cell reference (e.g., 'A1', '$A$1', 'B5')
23
        
24
    Examples:
25
        xl_rowcol_to_cell(0, 0)        # 'A1'
26
        xl_rowcol_to_cell(0, 0, True, True)  # '$A$1' 
27
        xl_rowcol_to_cell(10, 5)       # 'F11'
28
    """
29

30
def xl_rowcol_to_cell_fast(row, col):
31
    """
32
    Fast conversion of row/column to cell reference (relative only).
33
    
34
    Args:
35
        row (int): Cell row (0-indexed)
36
        col (int): Cell column (0-indexed)
37
        
38
    Returns:
39
        str: Excel cell reference (e.g., 'A1', 'B5')
40
        
41
    Note: Optimized version without absolute reference support
42
    """
43

44
def xl_cell_to_rowcol(cell_str):
45
    """
46
    Convert Excel cell reference to row and column numbers.
47
    
48
    Args:
49
        cell_str (str): Excel cell reference (e.g., 'A1', 'B5', '$A$1')
50
        
51
    Returns:
52
        tuple: (row, col) as 0-indexed integers
53
        
54
    Examples:
55
        xl_cell_to_rowcol('A1')        # (0, 0)
56
        xl_cell_to_rowcol('F11')       # (10, 5)
57
        xl_cell_to_rowcol('$A$1')      # (0, 0)
58
    """
59

60
def xl_cell_to_rowcol_abs(cell_str):
61
    """
62
    Convert Excel cell reference to row/column with absolute flags.
63
    
64
    Args:
65
        cell_str (str): Excel cell reference (e.g., 'A1', '$A$1', 'A$1')
66
        
67
    Returns:
68
        tuple: (row, col, row_abs, col_abs)
69
            - row (int): Row number (0-indexed)
70
            - col (int): Column number (0-indexed)
71
            - row_abs (bool): True if row is absolute
72
            - col_abs (bool): True if column is absolute
73
            
74
    Examples:
75
        xl_cell_to_rowcol_abs('A1')    # (0, 0, False, False)
76
        xl_cell_to_rowcol_abs('$A$1')  # (0, 0, True, True)
77
        xl_cell_to_rowcol_abs('A$1')   # (0, 0, True, False)
78
    """
79
```
80

81
### Column Name Conversion
82

83
Convert between column numbers and Excel column names.
84

85
```python { .api }
86
def xl_col_to_name(col, col_abs=False):
87
    """
88
    Convert column number to Excel column name.
89
    
90
    Args:
91
        col (int): Column number (0-indexed)
92
        col_abs (bool): Make column reference absolute (add $)
93
        
94
    Returns:
95
        str: Excel column name (e.g., 'A', 'B', 'AA', '$A')
96
        
97
    Examples:
98
        xl_col_to_name(0)              # 'A'
99
        xl_col_to_name(25)             # 'Z'
100
        xl_col_to_name(26)             # 'AA'
101
        xl_col_to_name(0, True)        # '$A'
102
    """
103
```
104

105
### Range Creation
106

107
Create Excel range strings from coordinates.
108

109
```python { .api }
110
def xl_range(first_row, first_col, last_row, last_col):
111
    """
112
    Create Excel range string from cell coordinates.
113
    
114
    Args:
115
        first_row (int): First row of range (0-indexed)
116
        first_col (int): First column of range (0-indexed)
117
        last_row (int): Last row of range (0-indexed)
118
        last_col (int): Last column of range (0-indexed)
119
        
120
    Returns:
121
        str: Excel range string (e.g., 'A1:B5')
122
        
123
    Examples:
124
        xl_range(0, 0, 4, 1)           # 'A1:B5'
125
        xl_range(2, 2, 2, 2)           # 'C3'
126
    """
127

128
def xl_range_abs(first_row, first_col, last_row, last_col):
129
    """
130
    Create absolute Excel range string from cell coordinates.
131
    
132
    Args:
133
        first_row (int): First row of range (0-indexed)
134
        first_col (int): First column of range (0-indexed)
135
        last_row (int): Last row of range (0-indexed)
136
        last_col (int): Last column of range (0-indexed)
137
        
138
    Returns:
139
        str: Absolute Excel range string (e.g., '$A$1:$B$5')
140
    """
141

142
def xl_range_formula(sheetname, first_row, first_col, last_row, last_col):
143
    """
144
    Create Excel range formula with sheet reference.
145
    
146
    Args:
147
        sheetname (str): Worksheet name
148
        first_row (int): First row of range (0-indexed)
149
        first_col (int): First column of range (0-indexed)
150
        last_row (int): Last row of range (0-indexed)
151
        last_col (int): Last column of range (0-indexed)
152
        
153
    Returns:
154
        str: Excel range formula (e.g., 'Sheet1!A1:B5', "'Sheet Name'!A1:B5")
155
    """
156
```
157

158
### Worksheet Name Handling
159

160
Handle worksheet names with special characters.
161

162
```python { .api }
163
def quote_sheetname(sheetname):
164
    """
165
    Quote worksheet name if it contains special characters.
166
    
167
    Args:
168
        sheetname (str): Worksheet name
169
        
170
    Returns:
171
        str: Quoted worksheet name if needed (e.g., "'Sheet Name'")
172
        
173
    Quotes are added if the name contains:
174
    - Spaces
175
    - Special characters: []:'*?/\
176
    - Starts with a digit
177
    """
178
```
179

180
### Text Width Calculations
181

182
Calculate text width for column sizing and autofit functionality.
183

184
```python { .api }
185
def xl_pixel_width(string):
186
    """
187
    Calculate the pixel width of a string in Excel's default font.
188
    
189
    Args:
190
        string (str): Text string to measure
191
        
192
    Returns:
193
        int: Approximate pixel width of the string
194
        
195
    Used internally for autofit calculations and column width estimation.
196
    """
197

198
def cell_autofit_width(string):
199
    """
200
    Calculate the autofit width for a cell containing the given string.
201
    
202
    Args:
203
        string (str): Text string in the cell
204
        
205
    Returns:
206
        int: Recommended column width for autofit
207
    """
208
```
209

210
## Exception Classes
211

212
Error handling classes for XlsxWriter operations.
213

214
```python { .api }
215
class XlsxWriterException(Exception):
216
    """Base exception class for all XlsxWriter errors."""
217

218
class XlsxInputError(XlsxWriterException):
219
    """Exception for input data related errors."""
220

221
class XlsxFileError(XlsxWriterException):
222
    """Exception for file operation related errors."""
223

224
class EmptyChartSeries(XlsxInputError):
225
    """Chart must contain at least one data series."""
226

227
class DuplicateTableName(XlsxInputError):
228
    """Worksheet table name already exists."""
229

230
class InvalidWorksheetName(XlsxInputError):
231
    """Worksheet name is too long or contains restricted characters."""
232

233
class DuplicateWorksheetName(XlsxInputError):
234
    """Worksheet name already exists."""
235

236
class OverlappingRange(XlsxInputError):
237
    """Worksheet merge range or table overlaps previous range."""
238

239
class UndefinedImageSize(XlsxFileError):
240
    """No size data found in image file."""
241

242
class UnsupportedImageFormat(XlsxFileError):
243
    """Unsupported image file format."""
244

245
class FileCreateError(XlsxFileError):
246
    """IO error when creating xlsx file."""
247

248
class FileSizeError(XlsxFileError):
249
    """Filesize would require ZIP64 extensions."""
250
```
251

252
## Usage Examples
253

254
### Cell Reference Conversions
255

256
```python
257
import xlsxwriter
258
from xlsxwriter.utility import *
259

260
# Convert row/col to cell reference
261
cell_ref = xl_rowcol_to_cell(0, 0)        # 'A1'
262
abs_ref = xl_rowcol_to_cell(0, 0, True, True)  # '$A$1'
263

264
# Convert cell reference to row/col
265
row, col = xl_cell_to_rowcol('F11')       # (10, 5)
266
row, col, row_abs, col_abs = xl_cell_to_rowcol_abs('$A$1')  # (0, 0, True, True)
267

268
# Column name conversions
269
col_name = xl_col_to_name(26)             # 'AA'
270
abs_col = xl_col_to_name(0, True)         # '$A'
271
```
272

273
### Range Creation
274

275
```python
276
# Create range strings
277
range_str = xl_range(0, 0, 4, 1)          # 'A1:B5'
278
abs_range = xl_range_abs(0, 0, 4, 1)      # '$A$1:$B$5'
279

280
# Create range formulas with sheet names
281
formula = xl_range_formula('Data', 0, 0, 4, 1)     # 'Data!A1:B5'
282
formula = xl_range_formula('My Sheet', 0, 0, 4, 1) # "'My Sheet'!A1:B5"
283
```
284

285
### Dynamic Range Building
286

287
```python
288
workbook = xlsxwriter.Workbook('utility_demo.xlsx')
289
worksheet = workbook.add_worksheet()
290

291
# Write data using utility functions
292
data = [
293
    ['Name', 'Score', 'Grade'],
294
    ['Alice', 95, 'A'],
295
    ['Bob', 87, 'B'],
296
    ['Charlie', 92, 'A']
297
]
298

299
for row_num, row_data in enumerate(data):
300
    for col_num, cell_data in enumerate(row_data):
301
        # Use utility function to get cell reference
302
        cell_ref = xl_rowcol_to_cell(row_num, col_num)
303
        worksheet.write(cell_ref, cell_data)
304

305
# Create chart using utility functions for ranges
306
chart = workbook.add_chart({'type': 'column'})
307

308
# Use utility functions to create ranges
309
categories_range = xl_range_formula('Sheet1', 1, 0, 3, 0)  # Names
310
values_range = xl_range_formula('Sheet1', 1, 1, 3, 1)     # Scores
311

312
chart.add_series({
313
    'categories': f'={categories_range}',
314
    'values': f'={values_range}',
315
    'name': 'Student Scores'
316
})
317

318
worksheet.insert_chart('E2', chart)
319
workbook.close()
320
```
321

322
### Working with Large Datasets
323

324
```python
325
# Generate large dataset with utility functions
326
num_rows = 1000
327
num_cols = 10
328

329
workbook = xlsxwriter.Workbook('large_data.xlsx')
330
worksheet = workbook.add_worksheet()
331

332
# Write headers
333
for col in range(num_cols):
334
    col_name = xl_col_to_name(col)
335
    worksheet.write(0, col, f'Column {col_name}')
336

337
# Write data using row/col coordinates
338
for row in range(1, num_rows + 1):
339
    for col in range(num_cols):
340
        worksheet.write(row, col, row * col)
341

342
# Create summary using range functions
343
summary_range = xl_range(1, 0, num_rows, num_cols - 1)
344
worksheet.write(num_rows + 2, 0, 'Data Range:')
345
worksheet.write(num_rows + 2, 1, summary_range)
346

347
workbook.close()
348
```
349

350
### Worksheet Name Handling
351

352
```python
353
# Handle worksheet names with special characters
354
names = [
355
    'Sheet1',           # No quoting needed
356
    'My Data',          # Contains space - will be quoted
357
    'Sales[2023]',      # Contains brackets - will be quoted  
358
    '2023_Results',     # Starts with digit - will be quoted
359
    'Summary'           # No quoting needed
360
]
361

362
workbook = xlsxwriter.Workbook('sheet_names.xlsx')
363

364
for name in names:
365
    worksheet = workbook.add_worksheet(name)
366
    
367
    # Create reference to this sheet using utility function
368
    quoted_name = quote_sheetname(name)
369
    range_formula = f'{quoted_name}!A1:B5'
370
    
371
    worksheet.write('A1', f'This is {name}')
372
    worksheet.write('A2', f'Range: {range_formula}')
373

374
workbook.close()
375
```
376

377
### Error Handling
378

379
```python
380
try:
381
    workbook = xlsxwriter.Workbook('test.xlsx')
382
    worksheet = workbook.add_worksheet()
383
    
384
    # This might raise an exception
385
    worksheet.add_table(0, 0, 5, 5, {'name': 'Table1'})
386
    worksheet.add_table(3, 3, 8, 8, {'name': 'Table1'})  # Duplicate name
387
    
388
except DuplicateTableName as e:
389
    print(f"Table name error: {e}")
390
except XlsxInputError as e:
391
    print(f"Input error: {e}")
392
except XlsxWriterException as e:
393
    print(f"XlsxWriter error: {e}")
394
finally:
395
    if 'workbook' in locals():
396
        workbook.close()
397
```
398

399
### Column Width Calculations
400

401
```python
402
# Calculate column widths based on content
403
workbook = xlsxwriter.Workbook('autofit_demo.xlsx')
404
worksheet = workbook.add_worksheet()
405

406
data = [
407
    ['Short', 'Medium Length Text', 'This is a very long text that needs more space'],
408
    ['A', 'Some data here', 'Even longer text content that definitely needs sizing'],
409
    ['B', 'More content', 'Another example of lengthy text in cells']
410
]
411

412
# Write data and calculate column widths
413
for row_num, row_data in enumerate(data):
414
    for col_num, cell_data in enumerate(row_data):
415
        worksheet.write(row_num, col_num, cell_data)
416

417
# Calculate and set optimal column widths
418
for col in range(len(data[0])):
419
    max_width = 0
420
    for row in range(len(data)):
421
        cell_width = xl_pixel_width(str(data[row][col]))
422
        max_width = max(max_width, cell_width)
423
    
424
    # Convert pixels to Excel width units (approximately)
425
    excel_width = max_width / 7.0
426
    worksheet.set_column(col, col, min(excel_width, 50))  # Cap at 50
427

428
workbook.close()
429
```