0
# Advanced Features
1

2
Advanced Excel functionality including data validation, conditional formatting, autofilters, tables, sparklines, images, textboxes, comments, and worksheet protection. These features provide professional Excel capabilities for data analysis and presentation.
3

4
## Capabilities
5

6
### Data Validation
7

8
Create dropdown lists and input validation rules for cells.
9

10
```python { .api }
11
def data_validation(self, first_row, first_col, last_row, last_col, options=None):
12
    """
13
    Add data validation to a cell range.
14
    
15
    Args:
16
        first_row (int): First row of range (0-indexed)
17
        first_col (int): First column of range (0-indexed)
18
        last_row (int): Last row of range (0-indexed)
19
        last_col (int): Last column of range (0-indexed)
20
        options (dict, optional): Validation options:
21
            - validate (str): Validation type:
22
                'any', 'integer', 'decimal', 'list', 'date', 'time',
23
                'length', 'custom', 'whole'
24
            - criteria (str): Validation criteria:
25
                'between', 'not between', 'equal to', 'not equal to',
26
                'greater than', 'less than', 'greater than or equal to',
27
                'less than or equal to'
28
            - value (str/int/float): Primary validation value
29
            - minimum (str/int/float): Minimum value for 'between'
30
            - maximum (str/int/float): Maximum value for 'between'
31
            - source (str): List source for 'list' validation
32
            - dropdown (bool): Show dropdown arrow
33
            - input_title (str): Input message title
34
            - input_message (str): Input message text
35
            - error_title (str): Error alert title
36
            - error_message (str): Error alert message
37
            - error_type (str): Error alert type:
38
                'stop', 'warning', 'information'
39
    """
40
```
41

42
### Conditional Formatting
43

44
Apply dynamic formatting based on cell values and formulas.
45

46
```python { .api }
47
def conditional_format(self, first_row, first_col, last_row, last_col, options=None):
48
    """
49
    Add conditional formatting to a cell range.
50
    
51
    Args:
52
        first_row (int): First row of range (0-indexed)
53
        first_col (int): First column of range (0-indexed)
54
        last_row (int): Last row of range (0-indexed)
55
        last_col (int): Last column of range (0-indexed)
56
        options (dict, optional): Formatting options:
57
            - type (str): Condition type:
58
                'cell', 'text', 'time_period', 'date', 'average',
59
                'duplicate', 'unique', 'top', 'bottom', 'blanks',
60
                'no_blanks', 'errors', 'no_errors', 'formula',
61
                'data_bar', 'color_scale', 'icon_set'
62
            - criteria (str): Condition criteria (for 'cell' type)
63
            - value (str/int/float): Comparison value
64
            - minimum (str/int/float): Minimum value for ranges
65
            - maximum (str/int/float): Maximum value for ranges
66
            - format (Format): Format to apply when condition is met
67
            - multi_range (str): Apply to multiple ranges
68
            - stop_if_true (bool): Stop processing other rules
69
    """
70
```
71

72
### Tables and Autofilters
73

74
Create Excel tables and autofilter functionality.
75

76
```python { .api }
77
def add_table(self, first_row, first_col, last_row, last_col, options=None):
78
    """
79
    Add an Excel table to a cell range.
80
    
81
    Args:
82
        first_row (int): First row of table (0-indexed)
83
        first_col (int): First column of table (0-indexed)
84
        last_row (int): Last row of table (0-indexed)
85
        last_col (int): Last column of table (0-indexed)
86
        options (dict, optional): Table options:
87
            - data (list): 2D list of table data
88
            - autofilter (bool): Enable autofilter (default True)
89
            - header_row (bool): First row contains headers (default True)
90
            - banded_columns (bool): Apply banded column formatting
91
            - banded_rows (bool): Apply banded row formatting (default True)
92
            - first_column (bool): Emphasize first column
93
            - last_column (bool): Emphasize last column
94
            - style (str): Table style name
95
            - name (str): Table name (auto-generated if not provided)
96
            - total_row (bool): Add total row
97
            - columns (list): Column specifications with headers and formulas
98
    """
99

100
def autofilter(self, first_row, first_col, last_row, last_col):
101
    """
102
    Add autofilter to a cell range.
103
    
104
    Args:
105
        first_row (int): First row of range (0-indexed)
106
        first_col (int): First column of range (0-indexed)
107
        last_row (int): Last row of range (0-indexed)
108
        last_col (int): Last column of range (0-indexed)
109
    """
110

111
def filter_column(self, col, criteria):
112
    """
113
    Set filter criteria for an autofilter column.
114
    
115
    Args:
116
        col (int): Column number (0-indexed)
117
        criteria (str): Filter criteria or comparison
118
    """
119

120
def filter_column_list(self, col, filters):
121
    """
122
    Set a list of filter values for an autofilter column.
123
    
124
    Args:
125
        col (int): Column number (0-indexed)
126
        filters (list): List of values to show
127
    """
128
```
129

130
### Sparklines
131

132
Add small charts within cells for data visualization.
133

134
```python { .api }
135
def add_sparkline(self, row, col, options=None):
136
    """
137
    Add a sparkline chart to a cell.
138
    
139
    Args:
140
        row (int): Cell row (0-indexed)
141
        col (int): Cell column (0-indexed)
142
        options (dict, optional): Sparkline options:
143
            - range (str): Data range for the sparkline
144
            - type (str): Sparkline type ('line', 'column', 'win_loss')
145
            - style (int): Built-in sparkline style (1-36)
146
            - markers (bool): Show data point markers
147
            - negative_points (bool): Highlight negative points
148
            - axis (bool): Show axis line
149
            - reverse (bool): Plot data right-to-left
150
            - weight (float): Line weight for line sparklines
151
            - high_point (bool): Highlight highest point
152
            - low_point (bool): Highlight lowest point
153
            - first_point (bool): Highlight first point
154
            - last_point (bool): Highlight last point
155
            - max (float): Maximum scale value
156
            - min (float): Minimum scale value
157
            - empty_cells (str): How to plot empty cells ('gaps'/'zero'/'connect')
158
            - show_hidden (bool): Include hidden cell data
159
            - date_axis (str): Date range for X-axis
160
    """
161
```
162

163
### Images and Graphics
164

165
Insert and manage images and graphics in worksheets.
166

167
```python { .api }
168
def insert_image(self, row, col, source, options=None):
169
    """
170
    Insert an image into the worksheet.
171
    
172
    Args:
173
        row (int): Cell row for image position (0-indexed)
174
        col (int): Cell column for image position (0-indexed)
175
        source (str or BytesIO): Image file path or image data stream
176
        options (dict, optional): Image options:
177
            - x_offset (int): Horizontal offset in pixels
178
            - y_offset (int): Vertical offset in pixels
179
            - x_scale (float): Horizontal scaling factor
180
            - y_scale (float): Vertical scaling factor
181
            - object_position (int): How image moves/sizes with cells (1-4)
182
            - url (str): Hyperlink URL for the image
183
            - tip (str): Hyperlink tooltip
184
            - description (str): Alt text description
185
            - decorative (bool): Mark as decorative (accessibility)
186
    """
187

188
def embed_image(self, row, col, source, options=None):
189
    """
190
    Embed an image in the workbook (reduces file duplication).
191
    
192
    Args:
193
        row (int): Cell row for image position (0-indexed)
194
        col (int): Cell column for image position (0-indexed)  
195
        source (str or BytesIO): Image file path or image data stream
196
        options (dict, optional): Same options as insert_image
197
    """
198

199
def set_background(self, source, is_byte_stream=False):
200
    """
201
    Set a background image for the worksheet.
202
    
203
    Args:
204
        source (str or BytesIO): Image file path or image data
205
        is_byte_stream (bool): True if source is BytesIO object
206
    """
207
```
208

209
### Text Boxes and Shapes
210

211
Add text boxes and drawing shapes to worksheets.
212

213
```python { .api }
214
def insert_textbox(self, row, col, text, options=None):
215
    """
216
    Insert a text box into the worksheet.
217
    
218
    Args:
219
        row (int): Cell row for textbox position (0-indexed)
220
        col (int): Cell column for textbox position (0-indexed)
221
        text (str): Text content for the textbox
222
        options (dict, optional): Textbox options:
223
            - width (int): Textbox width in pixels
224
            - height (int): Textbox height in pixels
225
            - x_offset (int): Horizontal offset in pixels
226
            - y_offset (int): Vertical offset in pixels
227
            - font (dict): Font formatting for text
228
            - align (dict): Text alignment options
229
            - border (dict): Textbox border formatting
230
            - fill (dict): Textbox background fill
231
            - gradient (dict): Gradient fill options
232
            - object_position (int): How textbox moves/sizes with cells
233
    """
234
```
235

236
### Comments and Annotations
237

238
Add comments and annotations to cells.
239

240
```python { .api }
241
def write_comment(self, row, col, comment, options=None):
242
    """
243
    Add a comment to a worksheet cell.
244
    
245
    Args:
246
        row (int): Cell row (0-indexed)
247
        col (int): Cell column (0-indexed)
248
        comment (str): Comment text
249
        options (dict, optional): Comment options:
250
            - author (str): Comment author name
251
            - visible (bool): Show comment by default
252
            - width (int): Comment box width
253
            - height (int): Comment box height
254
            - x_scale (float): Horizontal scaling factor
255
            - y_scale (float): Vertical scaling factor
256
            - color (str): Comment background color
257
            - start_cell (str): Cell reference for comment position
258
            - start_row (int): Row offset for comment position
259
            - start_col (int): Column offset for comment position
260
            - x_offset (int): Horizontal pixel offset
261
            - y_offset (int): Vertical pixel offset
262
    """
263

264
def show_comments(self):
265
    """Show all comments on the worksheet by default."""
266

267
def set_comments_author(self, author):
268
    """
269
    Set the default author for comments.
270
    
271
    Args:
272
        author (str): Default author name for new comments
273
    """
274
```
275

276
### Form Controls
277

278
Add interactive form controls to worksheets.
279

280
```python { .api }
281
def insert_button(self, row, col, options=None):
282
    """
283
    Insert a form control button.
284
    
285
    Args:
286
        row (int): Cell row for button position (0-indexed)
287
        col (int): Cell column for button position (0-indexed)
288
        options (dict, optional): Button options:
289
            - macro (str): VBA macro to run when clicked
290
            - caption (str): Button text
291
            - width (int): Button width in pixels
292
            - height (int): Button height in pixels
293
            - x_offset (int): Horizontal offset in pixels
294
            - y_offset (int): Vertical offset in pixels
295
            - x_scale (float): Horizontal scaling factor
296
            - y_scale (float): Vertical scaling factor
297
    """
298

299
def insert_checkbox(self, row, col, boolean, cell_format=None):
300
    """
301
    Insert a checkbox form control.
302
    
303
    Args:
304
        row (int): Cell row for checkbox position (0-indexed)
305
        col (int): Cell column for checkbox position (0-indexed)
306
        boolean (bool): Initial checked state
307
        cell_format (Format, optional): Cell formatting
308
    """
309
```
310

311
### Cell Merging
312

313
Merge cells across ranges for layout and formatting.
314

315
```python { .api }
316
def merge_range(self, first_row, first_col, last_row, last_col, data, cell_format=None):
317
    """
318
    Merge a range of cells and add data.
319
    
320
    Args:
321
        first_row (int): First row of range (0-indexed)
322
        first_col (int): First column of range (0-indexed)
323
        last_row (int): Last row of range (0-indexed)  
324
        last_col (int): Last column of range (0-indexed)
325
        data: Data to write to the merged range
326
        cell_format (Format, optional): Format for merged cells
327
    """
328
```
329

330
## Usage Examples
331

332
### Data Validation Examples
333

334
```python
335
# Dropdown list validation
336
worksheet.data_validation('A1:A10', {
337
    'validate': 'list',
338
    'source': ['Option 1', 'Option 2', 'Option 3'],
339
    'dropdown': True
340
})
341

342
# Number range validation
343
worksheet.data_validation('B1:B10', {
344
    'validate': 'integer',
345
    'criteria': 'between',
346
    'minimum': 1,
347
    'maximum': 100,
348
    'input_title': 'Enter a number',
349
    'input_message': 'Please enter a number between 1 and 100'
350
})
351

352
# Date validation
353
worksheet.data_validation('C1:C10', {
354
    'validate': 'date',
355
    'criteria': 'greater than',
356
    'value': datetime(2023, 1, 1),
357
    'error_title': 'Invalid Date',
358
    'error_message': 'Date must be after 2023-01-01'
359
})
360
```
361

362
### Conditional Formatting Examples
363

364
```python
365
# Create formats for conditional formatting
366
red_format = workbook.add_format({'bg_color': '#FFC7CE'})
367
green_format = workbook.add_format({'bg_color': '#C6EFCE'})
368
yellow_format = workbook.add_format({'bg_color': '#FFEB9C'})
369

370
# Value-based formatting
371
worksheet.conditional_format('A1:A10', {
372
    'type': 'cell',
373
    'criteria': 'greater than',
374
    'value': 50,
375
    'format': green_format
376
})
377

378
# Text-based formatting  
379
worksheet.conditional_format('B1:B10', {
380
    'type': 'text',
381
    'criteria': 'containing',
382
    'value': 'Error',
383
    'format': red_format
384
})
385

386
# Data bars
387
worksheet.conditional_format('C1:C10', {
388
    'type': 'data_bar',
389
    'bar_color': '#638EC6'
390
})
391

392
# Color scales
393
worksheet.conditional_format('D1:D10', {
394
    'type': 'color_scale',
395
    'min_color': '#FF0000',
396
    'max_color': '#00FF00'
397
})
398
```
399

400
### Excel Tables
401

402
```python
403
# Create table with data
404
data = [
405
    ['Name', 'Age', 'City', 'Salary'],
406
    ['Alice', 25, 'New York', 50000],
407
    ['Bob', 30, 'London', 60000],
408
    ['Charlie', 35, 'Tokyo', 70000]
409
]
410

411
# Write data
412
for row, row_data in enumerate(data):
413
    worksheet.write_row(row, 0, row_data)
414

415
# Add table
416
worksheet.add_table(0, 0, 3, 3, {
417
    'style': 'Table Style Medium 2',
418
    'name': 'EmployeeTable',
419
    'total_row': True,
420
    'columns': [
421
        {'header': 'Name'},
422
        {'header': 'Age'},
423
        {'header': 'City'},
424
        {'header': 'Salary', 'total_function': 'average'}
425
    ]
426
})
427
```
428

429
### Sparklines
430

431
```python
432
# Add data for sparklines
433
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
434
sales_data = [100, 120, 110, 150, 140, 180]
435

436
worksheet.write_row(0, 0, months)
437
worksheet.write_row(1, 0, sales_data)
438

439
# Add sparkline
440
worksheet.add_sparkline(1, 6, {
441
    'range': 'A2:F2',
442
    'type': 'line',
443
    'markers': True,
444
    'high_point': True,
445
    'low_point': True,
446
    'style': 12
447
})
448
```
449

450
### Images and Graphics
451

452
```python
453
# Insert image with positioning
454
worksheet.insert_image('A1', 'logo.png', {
455
    'x_offset': 10,
456
    'y_offset': 10,
457
    'x_scale': 0.5,
458
    'y_scale': 0.5,
459
    'url': 'https://www.example.com',
460
    'description': 'Company Logo'
461
})
462

463
# Insert textbox
464
worksheet.insert_textbox('C3', 'Important Notice!', {
465
    'width': 200,
466
    'height': 100,
467
    'font': {'bold': True, 'size': 14, 'color': 'red'},
468
    'border': {'color': 'black', 'width': 2},
469
    'fill': {'color': 'yellow'}
470
})
471
```
472

473
### Comments
474

475
```python
476
# Add comments with formatting
477
worksheet.write_comment('A1', 'This is a comment', {
478
    'author': 'John Doe',
479
    'visible': False,
480
    'width': 300,
481
    'height': 100,
482
    'color': 'lightblue'
483
})
484

485
# Set default comment author
486
worksheet.set_comments_author('Default Author')
487
```
488

489
### Form Controls
490

491
```python
492
# Insert button (requires VBA project)
493
worksheet.insert_button('A10', {
494
    'macro': 'ButtonClick',
495
    'caption': 'Click Me!',
496
    'width': 100,
497
    'height': 30
498
})
499

500
# Insert checkbox
501
worksheet.insert_checkbox('B10', True)  # Initially checked
502
```