0
# Formatting and Styling
1
2
Complete cell formatting system including fonts, colors, borders, alignment, number formats, patterns, and fill options. The Format class provides comprehensive styling capabilities for Excel cells with support for all Excel formatting features.
3
4
## Capabilities
5
6
### Font Formatting
7
8
Configure font properties including typeface, size, style, and color.
9
10
```python { .api }
11
def set_font_name(self, font_name):
12
"""
13
Set the font name/typeface.
14
15
Args:
16
font_name (str): Font name (e.g., 'Arial', 'Times New Roman', 'Calibri')
17
"""
18
19
def set_font_size(self, font_size=11):
20
"""
21
Set the font size.
22
23
Args:
24
font_size (int): Font size in points (default 11)
25
"""
26
27
def set_font_color(self, font_color):
28
"""
29
Set the font color.
30
31
Args:
32
font_color (str): Color name, hex code, or RGB tuple
33
Examples: 'red', '#FF0000', (255, 0, 0)
34
"""
35
36
def set_bold(self, bold=True):
37
"""
38
Set font to bold.
39
40
Args:
41
bold (bool): True for bold, False for normal weight
42
"""
43
44
def set_italic(self, italic=True):
45
"""
46
Set font to italic.
47
48
Args:
49
italic (bool): True for italic, False for normal style
50
"""
51
52
def set_underline(self, underline=1):
53
"""
54
Set font underline style.
55
56
Args:
57
underline (int): Underline type:
58
- 1: Single underline
59
- 2: Double underline
60
- 33: Single accounting underline
61
- 34: Double accounting underline
62
"""
63
64
def set_font_strikeout(self, font_strikeout=True):
65
"""
66
Set font strikethrough.
67
68
Args:
69
font_strikeout (bool): True for strikethrough, False for normal
70
"""
71
72
def set_font_script(self, font_script=1):
73
"""
74
Set font script (superscript/subscript).
75
76
Args:
77
font_script (int): Script type:
78
- 1: Superscript
79
- 2: Subscript
80
81
def set_font_family(self, font_family):
82
"""
83
Set the font family number.
84
85
Args:
86
font_family (int): Font family number for the format
87
"""
88
89
def set_font_outline(self, font_outline=True):
90
"""
91
Set font outline (Mac only).
92
93
Args:
94
font_outline (bool): True for outline, False for normal
95
"""
96
97
def set_font_shadow(self, font_shadow=True):
98
"""
99
Set font shadow (Mac only).
100
101
Args:
102
font_shadow (bool): True for shadow, False for normal
103
"""
104
```
105
106
### Number Formatting
107
108
Configure how numbers, dates, and times are displayed in cells.
109
110
```python { .api }
111
def set_num_format(self, num_format):
112
"""
113
Set the number format for the cell.
114
115
Args:
116
num_format (str or int): Number format string or built-in format ID
117
Examples:
118
- '0.00': Two decimal places
119
- '#,##0': Thousands separator
120
- '$#,##0.00': Currency format
121
- 'mm/dd/yyyy': Date format
122
- 'h:mm AM/PM': Time format
123
- '@': Text format
124
- '0%': Percentage format
125
"""
126
```
127
128
### Alignment and Text Control
129
130
Control text alignment, wrapping, rotation, and indentation.
131
132
```python { .api }
133
def set_align(self, alignment):
134
"""
135
Set cell alignment.
136
137
Args:
138
alignment (str): Alignment type:
139
Horizontal: 'left', 'center', 'right', 'fill', 'justify',
140
'center_across', 'distributed'
141
Vertical: 'top', 'vcenter', 'bottom', 'vjustify', 'vdistributed'
142
"""
143
144
def set_valign(self, align):
145
"""
146
Set vertical cell alignment property.
147
148
Legacy method for vertical alignment that differentiates from horizontal alignment.
149
150
Args:
151
align (str): Vertical alignment:
152
'top', 'vcenter', 'bottom', 'vjustify', 'vdistributed'
153
"""
154
155
def set_center_across(self, align_type=None):
156
"""
157
Center text across selection.
158
159
Args:
160
align_type: Deprecated parameter (for compatibility)
161
"""
162
163
def set_text_wrap(self, text_wrap=True):
164
"""
165
Enable text wrapping in the cell.
166
167
Args:
168
text_wrap (bool): True to wrap text, False for single line
169
"""
170
171
def set_rotation(self, rotation):
172
"""
173
Set text rotation angle.
174
175
Args:
176
rotation (int): Rotation angle:
177
- 0 to 90: Rotate counterclockwise
178
- -1 to -90: Rotate clockwise
179
- 255: Vertical text
180
"""
181
182
def set_indent(self, indent=1):
183
"""
184
Set text indentation level.
185
186
Args:
187
indent (int): Indentation level (0-15)
188
"""
189
190
def set_shrink(self, shrink=True):
191
"""
192
Shrink text to fit cell width.
193
194
Args:
195
shrink (bool): True to shrink text, False for normal sizing
196
"""
197
198
def set_reading_order(self, direction=0):
199
"""
200
Set text reading order.
201
202
Args:
203
direction (int): Reading direction:
204
- 0: Context (default)
205
- 1: Left-to-right
206
- 2: Right-to-left
207
"""
208
```
209
210
### Fill and Background
211
212
Configure cell background colors and patterns.
213
214
```python { .api }
215
def set_pattern(self, pattern=1):
216
"""
217
Set fill pattern style.
218
219
Args:
220
pattern (int): Pattern type:
221
- 0: No fill
222
- 1: Solid fill
223
- 2-18: Various pattern styles (dots, stripes, etc.)
224
"""
225
226
def set_bg_color(self, bg_color):
227
"""
228
Set background color.
229
230
Args:
231
bg_color (str): Color name, hex code, or RGB tuple
232
Examples: 'yellow', '#FFFF00', (255, 255, 0)
233
"""
234
235
def set_fg_color(self, fg_color):
236
"""
237
Set foreground color (for patterns).
238
239
Args:
240
fg_color (str): Color name, hex code, or RGB tuple
241
"""
242
```
243
244
### Border Formatting
245
246
Configure cell borders including style, color, and position.
247
248
```python { .api }
249
def set_border(self, style=1):
250
"""
251
Set all cell borders to the same style.
252
253
Args:
254
style (int): Border style:
255
- 0: No border
256
- 1: Thin
257
- 2: Medium
258
- 3: Dashed
259
- 4: Dotted
260
- 5: Thick
261
- 6: Double
262
- 7: Hair
263
- 8-13: Various dash/dot combinations
264
"""
265
266
def set_border_color(self, color):
267
"""
268
Set all border colors.
269
270
Args:
271
color (str): Color name, hex code, or RGB tuple
272
"""
273
274
def set_left(self, left=1):
275
"""Set left border style."""
276
277
def set_right(self, right=1):
278
"""Set right border style."""
279
280
def set_top(self, top=1):
281
"""Set top border style."""
282
283
def set_bottom(self, bottom=1):
284
"""Set bottom border style."""
285
286
def set_left_color(self, left_color):
287
"""Set left border color."""
288
289
def set_right_color(self, right_color):
290
"""Set right border color."""
291
292
def set_top_color(self, top_color):
293
"""Set top border color."""
294
295
def set_bottom_color(self, bottom_color):
296
"""Set bottom border color."""
297
298
def set_diag_type(self, diag_type=1):
299
"""
300
Set diagonal border type.
301
302
Args:
303
diag_type (int): Diagonal type:
304
- 1: From bottom-left to top-right
305
- 2: From top-left to bottom-right
306
- 3: Both diagonals
307
"""
308
309
def set_diag_border(self, diag_border=1):
310
"""Set diagonal border style."""
311
312
def set_diag_color(self, diag_color):
313
"""Set diagonal border color."""
314
```
315
316
### Cell Protection
317
318
Configure cell locking and formula hiding for worksheet protection.
319
320
```python { .api }
321
def set_locked(self, locked=True):
322
"""
323
Set cell locked status for protection.
324
325
Args:
326
locked (bool): True to lock cell, False to unlock
327
328
Note: Only takes effect when worksheet protection is enabled
329
"""
330
331
def set_hidden(self, hidden=True):
332
"""
333
Set formula hidden status for protection.
334
335
Args:
336
hidden (bool): True to hide formulas, False to show
337
338
Note: Only takes effect when worksheet protection is enabled
339
"""
340
```
341
342
## Usage Examples
343
344
### Basic Font Formatting
345
346
```python
347
import xlsxwriter
348
349
workbook = xlsxwriter.Workbook('formatting.xlsx')
350
worksheet = workbook.add_worksheet()
351
352
# Create formats
353
title_format = workbook.add_format({
354
'font_name': 'Arial',
355
'font_size': 16,
356
'bold': True,
357
'font_color': 'blue'
358
})
359
360
header_format = workbook.add_format({
361
'bold': True,
362
'italic': True,
363
'underline': True,
364
'font_color': 'red'
365
})
366
367
# Apply formats
368
worksheet.write('A1', 'Title', title_format)
369
worksheet.write('A2', 'Header', header_format)
370
371
workbook.close()
372
```
373
374
### Number Formatting Examples
375
376
```python
377
# Create number formats
378
currency_format = workbook.add_format({'num_format': '$#,##0.00'})
379
percentage_format = workbook.add_format({'num_format': '0.00%'})
380
date_format = workbook.add_format({'num_format': 'mm/dd/yyyy'})
381
time_format = workbook.add_format({'num_format': 'h:mm AM/PM'})
382
fraction_format = workbook.add_format({'num_format': '# ?/?'})
383
384
# Write formatted numbers
385
worksheet.write(0, 0, 1234.56, currency_format) # $1,234.56
386
worksheet.write(1, 0, 0.75, percentage_format) # 75.00%
387
worksheet.write(2, 0, datetime.now(), date_format) # 12/25/2023
388
worksheet.write(3, 0, datetime.now(), time_format) # 2:30 PM
389
worksheet.write(4, 0, 1.25, fraction_format) # 1 1/4
390
```
391
392
### Alignment and Text Control
393
394
```python
395
# Create alignment formats
396
center_format = workbook.add_format({
397
'align': 'center',
398
'valign': 'vcenter'
399
})
400
401
wrap_format = workbook.add_format({
402
'text_wrap': True,
403
'align': 'left',
404
'valign': 'top'
405
})
406
407
rotated_format = workbook.add_format({
408
'rotation': 45,
409
'align': 'center'
410
})
411
412
# Apply formatting
413
worksheet.write('A1', 'Centered', center_format)
414
worksheet.write('A2', 'This is a long text that will wrap', wrap_format)
415
worksheet.write('A3', 'Rotated Text', rotated_format)
416
```
417
418
### Border and Fill Formatting
419
420
```python
421
# Create border format
422
border_format = workbook.add_format({
423
'border': 2, # Medium border all around
424
'border_color': 'black',
425
'top': 5, # Thick top border
426
'top_color': 'red'
427
})
428
429
# Create fill format
430
fill_format = workbook.add_format({
431
'pattern': 1, # Solid fill
432
'bg_color': 'yellow',
433
'font_color': 'black'
434
})
435
436
# Combine formatting
437
combined_format = workbook.add_format({
438
'bg_color': 'lightblue',
439
'font_color': 'darkblue',
440
'border': 1,
441
'bold': True,
442
'align': 'center'
443
})
444
445
worksheet.write('A1', 'Bordered', border_format)
446
worksheet.write('A2', 'Filled', fill_format)
447
worksheet.write('A3', 'Combined', combined_format)
448
```
449
450
### Conditional Formatting Integration
451
452
```python
453
# Create formats for conditional formatting
454
red_format = workbook.add_format({
455
'bg_color': '#FFC7CE',
456
'font_color': '#9C0006'
457
})
458
459
green_format = workbook.add_format({
460
'bg_color': '#C6EFCE',
461
'font_color': '#006100'
462
})
463
464
# Apply conditional formatting
465
worksheet.conditional_format('A1:A10', {
466
'type': 'cell',
467
'criteria': '>',
468
'value': 50,
469
'format': green_format
470
})
471
472
worksheet.conditional_format('A1:A10', {
473
'type': 'cell',
474
'criteria': '<',
475
'value': 25,
476
'format': red_format
477
})
478
```
479
480
### Format Properties Dictionary
481
482
```python
483
# Create format using properties dictionary
484
complex_format = workbook.add_format({
485
# Font properties
486
'font_name': 'Times New Roman',
487
'font_size': 12,
488
'bold': True,
489
'italic': False,
490
'underline': 1,
491
'font_strikeout': False,
492
'font_color': '#0000FF',
493
494
# Number format
495
'num_format': '#,##0.00',
496
497
# Alignment
498
'align': 'center',
499
'valign': 'vcenter',
500
'text_wrap': True,
501
'rotation': 0,
502
'indent': 0,
503
'shrink': False,
504
505
# Pattern/Fill
506
'pattern': 1,
507
'bg_color': '#FFFF00',
508
'fg_color': '#FF0000',
509
510
# Borders
511
'border': 1,
512
'left': 1,
513
'right': 1,
514
'top': 1,
515
'bottom': 1,
516
'border_color': '#000000',
517
'left_color': '#000000',
518
'right_color': '#000000',
519
'top_color': '#000000',
520
'bottom_color': '#000000',
521
522
# Protection
523
'locked': True,
524
'hidden': False
525
})
526
```