0
# Charts and Visualization
1
2
Comprehensive charting system supporting all major Excel chart types including column, line, pie, scatter, area, bar, radar, doughnut, and stock charts with full customization options for axes, legends, titles, and data series.
3
4
## Capabilities
5
6
### Chart Creation and Types
7
8
Create charts with various types and configurations.
9
10
```python { .api }
11
# Chart types available via workbook.add_chart()
12
CHART_TYPES = {
13
'area': 'Area charts',
14
'bar': 'Bar charts (horizontal)',
15
'column': 'Column charts (vertical)',
16
'doughnut': 'Doughnut charts',
17
'line': 'Line charts',
18
'pie': 'Pie charts',
19
'radar': 'Radar charts',
20
'scatter': 'Scatter (XY) charts',
21
'stock': 'Stock charts'
22
}
23
24
# Chart creation through workbook
25
def add_chart(self, options):
26
"""
27
Create a chart object.
28
29
Args:
30
options (dict): Chart configuration:
31
- type (str): Chart type ('column', 'line', 'pie', etc.)
32
- subtype (str, optional): Chart subtype for variations
33
34
Returns:
35
Chart: Chart object of the specified type
36
"""
37
```
38
39
### Data Series Management
40
41
Add and configure data series for charts.
42
43
```python { .api }
44
def add_series(self, options=None):
45
"""
46
Add a data series to the chart.
47
48
Args:
49
options (dict, optional): Series configuration:
50
- categories (str): Range for category labels (X-axis)
51
- values (str): Range for data values (Y-axis)
52
- name (str): Name for the series (legend)
53
- name_formula (str): Formula for series name
54
- line (dict): Line formatting options
55
- border (dict): Border formatting options
56
- fill (dict): Fill formatting options
57
- marker (dict): Data point marker options
58
- trendline (dict): Trendline configuration
59
- y_error_bars (dict): Y error bars configuration
60
- x_error_bars (dict): X error bars configuration
61
- data_labels (dict): Data labels configuration
62
- points (list): Individual point formatting
63
- smooth (bool): Smooth line for line charts
64
- invert_if_negative (bool): Invert colors for negative values
65
"""
66
```
67
68
### Chart Titles and Labels
69
70
Configure chart titles and labeling.
71
72
```python { .api }
73
def set_title(self, options=None):
74
"""
75
Set the chart title.
76
77
Args:
78
options (dict, optional): Title configuration:
79
- name (str): Title text
80
- name_font (dict): Font formatting for title
81
- overlay (bool): Allow title to overlay chart
82
- layout (dict): Position and size settings
83
- none (bool): Remove default title
84
"""
85
```
86
87
### Axis Configuration
88
89
Configure chart axes including labels, scaling, and formatting.
90
91
```python { .api }
92
def set_x_axis(self, options):
93
"""
94
Configure the primary X-axis.
95
96
Args:
97
options (dict): X-axis configuration:
98
- name (str): Axis title
99
- name_font (dict): Title font formatting
100
- num_font (dict): Number font formatting
101
- line (dict): Axis line formatting
102
- fill (dict): Axis area fill
103
- min (float): Minimum axis value
104
- max (float): Maximum axis value
105
- minor_unit (float): Minor tick interval
106
- major_unit (float): Major tick interval
107
- crossing (float): Axis crossing point
108
- reverse (bool): Reverse axis direction
109
- log_base (int): Logarithmic scale base
110
- major_gridlines (dict): Major gridline formatting
111
- minor_gridlines (dict): Minor gridline formatting
112
- visible (bool): Show/hide axis
113
- position_axis (str): Axis position ('on_tick'/'between_tick')
114
- label_position (str): Label position ('high'/'low'/'next_to')
115
- major_tick_mark (str): Major tick mark type
116
- minor_tick_mark (str): Minor tick mark type
117
- num_format (str): Number format for axis labels
118
- text_axis (bool): Treat as text axis
119
- date_axis (bool): Treat as date axis
120
"""
121
122
def set_y_axis(self, options):
123
"""Configure the primary Y-axis (same options as set_x_axis)."""
124
125
def set_x2_axis(self, options):
126
"""Configure the secondary X-axis (same options as set_x_axis)."""
127
128
def set_y2_axis(self, options):
129
"""Configure the secondary Y-axis (same options as set_x_axis)."""
130
```
131
132
### Legend Configuration
133
134
Configure chart legends including position and formatting.
135
136
```python { .api }
137
def set_legend(self, options):
138
"""
139
Configure the chart legend.
140
141
Args:
142
options (dict): Legend configuration:
143
- position (str): Legend position:
144
'bottom', 'top', 'right', 'left', 'overlay_right', 'overlay_left', 'none'
145
- font (dict): Legend font formatting
146
- border (dict): Legend border formatting
147
- fill (dict): Legend background fill
148
- layout (dict): Custom position and size
149
- delete_series (list): Hide specific series from legend
150
"""
151
```
152
153
### Plot and Chart Area
154
155
Configure plot area and chart area appearance.
156
157
```python { .api }
158
def set_plotarea(self, options):
159
"""
160
Configure the plot area (data area of the chart).
161
162
Args:
163
options (dict): Plot area configuration:
164
- border (dict): Plot area border formatting
165
- fill (dict): Plot area background fill
166
- layout (dict): Custom position and size
167
"""
168
169
def set_chartarea(self, options):
170
"""
171
Configure the chart area (entire chart including axes and legend).
172
173
Args:
174
options (dict): Chart area configuration:
175
- border (dict): Chart area border formatting
176
- fill (dict): Chart area background fill
177
"""
178
```
179
180
### Chart Styling and Appearance
181
182
Configure chart appearance including styles and data display options.
183
184
```python { .api }
185
def set_style(self, style_id):
186
"""
187
Set a built-in chart style.
188
189
Args:
190
style_id (int): Style ID (1-48) corresponding to Excel's chart styles
191
"""
192
193
def show_blanks_as(self, option):
194
"""
195
Configure how blank cells are displayed in the chart.
196
197
Args:
198
option (str): Display option:
199
- 'gap': Show as gaps in the data
200
- 'zero': Treat as zero values
201
- 'span': Connect across blank cells
202
"""
203
204
def show_na_as_empty_cell(self):
205
"""Display #N/A errors as empty cells in the chart."""
206
207
def show_hidden_data(self):
208
"""Include hidden cell data in the chart."""
209
```
210
211
### Chart Size and Position
212
213
Configure chart dimensions and positioning.
214
215
```python { .api }
216
def set_size(self, options=None):
217
"""
218
Set chart size and position.
219
220
Args:
221
options (dict, optional): Size configuration:
222
- width (int): Chart width in pixels
223
- height (int): Chart height in pixels
224
- x_scale (float): X-axis scaling factor
225
- y_scale (float): Y-axis scaling factor
226
"""
227
```
228
229
### Chart Enhancements
230
231
Add enhancements like data tables and bars to charts.
232
233
```python { .api }
234
def set_table(self, options=None):
235
"""
236
Add a data table to the chart.
237
238
Args:
239
options (dict, optional): Table configuration:
240
- show_keys (bool): Show legend keys in table
241
- horizontal (bool): Show horizontal lines
242
- vertical (bool): Show vertical lines
243
- outline (bool): Show table outline
244
- font (dict): Table font formatting
245
"""
246
247
def set_up_down_bars(self, options=None):
248
"""
249
Add up/down bars to line charts.
250
251
Args:
252
options (dict, optional): Up/down bars configuration:
253
- up_bar_line (dict): Up bar line formatting
254
- up_bar_fill (dict): Up bar fill formatting
255
- down_bar_line (dict): Down bar line formatting
256
- down_bar_fill (dict): Down bar fill formatting
257
"""
258
259
def set_drop_lines(self, options=None):
260
"""
261
Add drop lines to line charts.
262
263
Args:
264
options (dict, optional): Drop lines configuration:
265
- line (dict): Drop line formatting
266
"""
267
268
def set_high_low_lines(self, options=None):
269
"""
270
Add high-low lines to line charts.
271
272
Args:
273
options (dict, optional): High-low lines configuration:
274
- line (dict): High-low line formatting
275
"""
276
```
277
278
### Chart Combination
279
280
Combine multiple charts into a single chart display.
281
282
```python { .api }
283
def combine(self, chart=None):
284
"""
285
Combine this chart with another chart.
286
287
Args:
288
chart (Chart): Another chart object to combine with
289
290
This allows creating combination charts like column-line charts.
291
"""
292
```
293
294
## Usage Examples
295
296
### Basic Chart Creation
297
298
```python
299
import xlsxwriter
300
301
workbook = xlsxwriter.Workbook('charts.xlsx')
302
worksheet = workbook.add_worksheet()
303
304
# Add sample data
305
data = [
306
['Quarter', 'Sales', 'Expenses'],
307
['Q1', 1000, 600],
308
['Q2', 1200, 700],
309
['Q3', 1500, 800],
310
['Q4', 1800, 900]
311
]
312
313
for row, row_data in enumerate(data):
314
worksheet.write_row(row, 0, row_data)
315
316
# Create a column chart
317
chart = workbook.add_chart({'type': 'column'})
318
319
# Add data series
320
chart.add_series({
321
'categories': '=Sheet1!$A$2:$A$5',
322
'values': '=Sheet1!$B$2:$B$5',
323
'name': 'Sales'
324
})
325
326
chart.add_series({
327
'categories': '=Sheet1!$A$2:$A$5',
328
'values': '=Sheet1!$C$2:$C$5',
329
'name': 'Expenses'
330
})
331
332
# Configure chart
333
chart.set_title({'name': 'Quarterly Results'})
334
chart.set_x_axis({'name': 'Quarter'})
335
chart.set_y_axis({'name': 'Amount ($)'})
336
337
# Insert chart
338
worksheet.insert_chart('E2', chart)
339
340
workbook.close()
341
```
342
343
### Advanced Chart Formatting
344
345
```python
346
# Create a line chart with full formatting
347
chart = workbook.add_chart({'type': 'line'})
348
349
# Add series with extensive formatting
350
chart.add_series({
351
'categories': '=Sheet1!$A$2:$A$5',
352
'values': '=Sheet1!$B$2:$B$5',
353
'name': 'Revenue',
354
'line': {
355
'color': 'blue',
356
'width': 3,
357
'dash_type': 'dash'
358
},
359
'marker': {
360
'type': 'circle',
361
'size': 8,
362
'border': {'color': 'red'},
363
'fill': {'color': 'yellow'}
364
},
365
'data_labels': {
366
'value': True,
367
'font': {'name': 'Arial', 'size': 10}
368
}
369
})
370
371
# Comprehensive chart formatting
372
chart.set_title({
373
'name': 'Sales Trends',
374
'name_font': {
375
'name': 'Arial',
376
'size': 16,
377
'bold': True,
378
'color': 'blue'
379
}
380
})
381
382
chart.set_x_axis({
383
'name': 'Time Period',
384
'name_font': {'size': 12, 'bold': True},
385
'num_font': {'size': 10, 'italic': True},
386
'major_gridlines': {
387
'visible': True,
388
'line': {'color': 'gray', 'width': 0.5}
389
}
390
})
391
392
chart.set_legend({
393
'position': 'bottom',
394
'font': {'size': 10}
395
})
396
397
chart.set_plotarea({
398
'border': {'color': 'black', 'width': 1},
399
'fill': {'color': '#FFFFCC'}
400
})
401
402
worksheet.insert_chart('E2', chart, {'x_scale': 1.5, 'y_scale': 1.2})
403
```
404
405
### Chart Types Examples
406
407
```python
408
# Pie chart
409
pie_chart = workbook.add_chart({'type': 'pie'})
410
pie_chart.add_series({
411
'categories': '=Sheet1!$A$2:$A$5',
412
'values': '=Sheet1!$B$2:$B$5',
413
'data_labels': {'percentage': True}
414
})
415
416
# Scatter chart
417
scatter_chart = workbook.add_chart({'type': 'scatter'})
418
scatter_chart.add_series({
419
'categories': '=Sheet1!$B$2:$B$5',
420
'values': '=Sheet1!$C$2:$C$5',
421
'marker': {'type': 'circle', 'size': 10}
422
})
423
424
# Area chart
425
area_chart = workbook.add_chart({'type': 'area'})
426
area_chart.add_series({
427
'categories': '=Sheet1!$A$2:$A$5',
428
'values': '=Sheet1!$B$2:$B$5'
429
})
430
431
# Bar chart (horizontal)
432
bar_chart = workbook.add_chart({'type': 'bar'})
433
bar_chart.add_series({
434
'categories': '=Sheet1!$A$2:$A$5',
435
'values': '=Sheet1!$B$2:$B$5'
436
})
437
```
438
439
### Combination Charts
440
441
```python
442
# Create primary chart (column)
443
primary_chart = workbook.add_chart({'type': 'column'})
444
primary_chart.add_series({
445
'categories': '=Sheet1!$A$2:$A$5',
446
'values': '=Sheet1!$B$2:$B$5',
447
'name': 'Sales Volume'
448
})
449
450
# Create secondary chart (line)
451
secondary_chart = workbook.add_chart({'type': 'line'})
452
secondary_chart.add_series({
453
'categories': '=Sheet1!$A$2:$A$5',
454
'values': '=Sheet1!$C$2:$C$5',
455
'name': 'Profit Margin',
456
'y2_axis': True # Use secondary Y-axis
457
})
458
459
# Combine charts
460
primary_chart.combine(secondary_chart)
461
462
# Configure axes
463
primary_chart.set_y_axis({'name': 'Volume'})
464
primary_chart.set_y2_axis({'name': 'Margin (%)'})
465
466
worksheet.insert_chart('E2', primary_chart)
467
```
468
469
### Chart Subtypes
470
471
```python
472
# Stacked column chart
473
stacked_chart = workbook.add_chart({
474
'type': 'column',
475
'subtype': 'stacked'
476
})
477
478
# 100% stacked column chart
479
percent_stacked_chart = workbook.add_chart({
480
'type': 'column',
481
'subtype': 'percent_stacked'
482
})
483
484
# 3D pie chart
485
pie_3d_chart = workbook.add_chart({
486
'type': 'pie',
487
'subtype': '3d'
488
})
489
490
# Smooth line chart
491
smooth_line_chart = workbook.add_chart({
492
'type': 'line',
493
'subtype': 'smooth'
494
})
495
```