0
# Data Writing and Cell Operations
1
2
Comprehensive data writing capabilities for Excel worksheets including support for all Excel data types, formulas, hyperlinks, rich text, and specialized writing methods for rows, columns, and cell ranges.
3
4
## Capabilities
5
6
### Generic Writing Methods
7
8
Universal data writing methods that automatically detect data types.
9
10
```python { .api }
11
def write(self, row, col, *args):
12
"""
13
Write data to a worksheet cell with automatic type detection.
14
15
Args:
16
row (int): Cell row (0-indexed)
17
col (int): Cell column (0-indexed)
18
*args: Data and optional format. Can be:
19
- (data,) - Write data with default formatting
20
- (data, cell_format) - Write data with specific format
21
- (data, cell_format, value) - For formulas with cached value
22
23
The method automatically detects the data type and calls the
24
appropriate write_* method.
25
"""
26
27
def add_write_handler(self, user_type, user_function):
28
"""
29
Add a custom handler for writing user-defined data types.
30
31
Args:
32
user_type (type): The user-defined data type
33
user_function (callable): Function to handle writing this type
34
"""
35
```
36
37
### String and Text Writing
38
39
Methods for writing text data including strings and rich formatted text.
40
41
```python { .api }
42
def write_string(self, row, col, string, cell_format=None):
43
"""
44
Write a string to a worksheet cell.
45
46
Args:
47
row (int): Cell row (0-indexed)
48
col (int): Cell column (0-indexed)
49
string (str): String data to write
50
cell_format (Format, optional): Cell formatting to apply
51
"""
52
53
def write_rich_string(self, row, col, *args):
54
"""
55
Write a rich text string with multiple formats.
56
57
Args:
58
row (int): Cell row (0-indexed)
59
col (int): Cell column (0-indexed)
60
*args: Alternating string and format arguments:
61
- 'string1', format1, 'string2', format2, ...
62
- Final format argument is optional and applies to the cell
63
64
Example:
65
write_rich_string(0, 0, 'Bold', bold_format, ' and normal text')
66
"""
67
```
68
69
### Numeric Data Writing
70
71
Methods for writing numeric data including integers, floats, and special numeric formats.
72
73
```python { .api }
74
def write_number(self, row, col, number, cell_format=None):
75
"""
76
Write a numeric value to a worksheet cell.
77
78
Args:
79
row (int): Cell row (0-indexed)
80
col (int): Cell column (0-indexed)
81
number (int or float): Numeric data to write
82
cell_format (Format, optional): Cell formatting to apply
83
"""
84
85
def write_blank(self, row, col, blank, cell_format=None):
86
"""
87
Write a blank cell with optional formatting.
88
89
Args:
90
row (int): Cell row (0-indexed)
91
col (int): Cell column (0-indexed)
92
blank: Ignored (for compatibility)
93
cell_format (Format, optional): Cell formatting to apply
94
"""
95
```
96
97
### Formula Writing
98
99
Methods for writing Excel formulas including standard, array, and dynamic formulas.
100
101
```python { .api }
102
def write_formula(self, row, col, formula, cell_format=None, value=0):
103
"""
104
Write an Excel formula to a worksheet cell.
105
106
Args:
107
row (int): Cell row (0-indexed)
108
col (int): Cell column (0-indexed)
109
formula (str): Excel formula string (without leading =)
110
cell_format (Format, optional): Cell formatting to apply
111
value (numeric, optional): Cached result value for the formula
112
"""
113
114
def write_array_formula(self, first_row, first_col, last_row, last_col,
115
formula, cell_format=None, value=0):
116
"""
117
Write an array formula to a range of cells.
118
119
Args:
120
first_row (int): First row of the range (0-indexed)
121
first_col (int): First column of the range (0-indexed)
122
last_row (int): Last row of the range (0-indexed)
123
last_col (int): Last column of the range (0-indexed)
124
formula (str): Array formula string (without leading =)
125
cell_format (Format, optional): Cell formatting to apply
126
value (numeric, optional): Cached result value
127
"""
128
129
def write_dynamic_array_formula(self, row, col, formula, cell_format=None, value=0):
130
"""
131
Write a dynamic array formula (Excel 365).
132
133
Args:
134
row (int): Cell row (0-indexed)
135
col (int): Cell column (0-indexed)
136
formula (str): Dynamic array formula string (without leading =)
137
cell_format (Format, optional): Cell formatting to apply
138
value (numeric, optional): Cached result value
139
"""
140
```
141
142
### Date and Time Writing
143
144
Methods for writing date and time data with proper Excel serialization.
145
146
```python { .api }
147
def write_datetime(self, row, col, date, cell_format=None):
148
"""
149
Write a datetime object to a worksheet cell.
150
151
Args:
152
row (int): Cell row (0-indexed)
153
col (int): Cell column (0-indexed)
154
date (datetime): Python datetime object
155
cell_format (Format, optional): Cell formatting (should include date format)
156
157
Note: The cell_format should include a date/time number format
158
"""
159
```
160
161
### Boolean Data Writing
162
163
Methods for writing boolean values and checkbox controls.
164
165
```python { .api }
166
def write_boolean(self, row, col, boolean, cell_format=None):
167
"""
168
Write a boolean value to a worksheet cell.
169
170
Args:
171
row (int): Cell row (0-indexed)
172
col (int): Cell column (0-indexed)
173
boolean (bool): Boolean value (True/False)
174
cell_format (Format, optional): Cell formatting to apply
175
"""
176
```
177
178
### URL and Hyperlink Writing
179
180
Methods for writing hyperlinks and URLs with optional display text and tooltips.
181
182
```python { .api }
183
def write_url(self, row, col, url, cell_format=None, string=None, tip=None):
184
"""
185
Write a hyperlink to a worksheet cell.
186
187
Args:
188
row (int): Cell row (0-indexed)
189
col (int): Cell column (0-indexed)
190
url (str): The URL to link to (http://, https://, ftp://, mailto:,
191
internal:, external:)
192
cell_format (Format, optional): Cell formatting (defaults to hyperlink style)
193
string (str, optional): Display text for the hyperlink
194
tip (str, optional): Tooltip text for the hyperlink
195
"""
196
```
197
198
### Bulk Data Writing
199
200
Methods for writing arrays of data to rows and columns efficiently.
201
202
```python { .api }
203
def write_row(self, row, col, data, cell_format=None):
204
"""
205
Write a row of data starting from the given cell.
206
207
Args:
208
row (int): Starting row (0-indexed)
209
col (int): Starting column (0-indexed)
210
data (list): List of data items to write
211
cell_format (Format, optional): Format to apply to all cells
212
"""
213
214
def write_column(self, row, col, data, cell_format=None):
215
"""
216
Write a column of data starting from the given cell.
217
218
Args:
219
row (int): Starting row (0-indexed)
220
col (int): Starting column (0-indexed)
221
data (list): List of data items to write
222
cell_format (Format, optional): Format to apply to all cells
223
"""
224
```
225
226
## Usage Examples
227
228
### Basic Data Writing
229
230
```python
231
import xlsxwriter
232
from datetime import datetime
233
234
workbook = xlsxwriter.Workbook('data.xlsx')
235
worksheet = workbook.add_worksheet()
236
237
# Write different data types
238
worksheet.write_string(0, 0, 'Hello World')
239
worksheet.write_number(1, 0, 123.45)
240
worksheet.write_boolean(2, 0, True)
241
worksheet.write_datetime(3, 0, datetime.now())
242
243
# Generic write method (auto-detects type)
244
worksheet.write(4, 0, 'Auto-detected string')
245
worksheet.write(5, 0, 42)
246
247
workbook.close()
248
```
249
250
### Formula Writing
251
252
```python
253
# Simple formulas
254
worksheet.write_formula(0, 0, 'SUM(B1:B10)')
255
worksheet.write_formula(1, 0, 'AVERAGE(B1:B10)', currency_format)
256
257
# Array formula
258
worksheet.write_array_formula(0, 0, 2, 0, 'TRANSPOSE(B1:D1)')
259
260
# Dynamic array formula (Excel 365)
261
worksheet.write_dynamic_array_formula(0, 0, 'SEQUENCE(5,1)')
262
```
263
264
### Rich Text Formatting
265
266
```python
267
# Create formats
268
bold = workbook.add_format({'bold': True})
269
italic = workbook.add_format({'italic': True})
270
red = workbook.add_format({'font_color': 'red'})
271
272
# Write rich text
273
worksheet.write_rich_string(0, 0,
274
'This is ', bold, 'bold', ' and this is ', italic, 'italic',
275
' and this is ', red, 'red text')
276
```
277
278
### Hyperlinks
279
280
```python
281
# External URLs
282
worksheet.write_url(0, 0, 'https://www.python.org')
283
worksheet.write_url(1, 0, 'https://www.python.org', None, 'Python Website')
284
285
# Email links
286
worksheet.write_url(2, 0, 'mailto:john@example.com', None, 'Send Email')
287
288
# Internal links
289
worksheet.write_url(3, 0, 'internal:Sheet2!A1', None, 'Go to Sheet2')
290
291
# External file links
292
worksheet.write_url(4, 0, 'external:other.xlsx#Sheet1!A1', None, 'External File')
293
```
294
295
### Bulk Data Operations
296
297
```python
298
# Write row of data
299
headers = ['Name', 'Age', 'City', 'Salary']
300
worksheet.write_row(0, 0, headers, header_format)
301
302
# Write column of data
303
names = ['Alice', 'Bob', 'Charlie', 'Diana']
304
worksheet.write_column(1, 0, names)
305
306
# Write 2D data
307
data = [
308
['Alice', 25, 'New York', 50000],
309
['Bob', 30, 'London', 60000],
310
['Charlie', 35, 'Tokyo', 70000]
311
]
312
313
for row_num, row_data in enumerate(data, 1):
314
worksheet.write_row(row_num, 0, row_data)
315
```
316
317
### Custom Data Types
318
319
```python
320
# Define custom data type handler
321
from decimal import Decimal
322
323
def write_decimal(worksheet, row, col, decimal_val, format=None):
324
return worksheet.write_number(row, col, float(decimal_val), format)
325
326
# Register the handler
327
worksheet.add_write_handler(Decimal, write_decimal)
328
329
# Now Decimal objects are handled automatically
330
worksheet.write(0, 0, Decimal('123.45'))
331
```