addCell

addCell

BASIC / ADVANCED / PREMIUM

Adds cell contents into the Excel spreadsheet.

Description
public addCell($contents, $position, $cellStyles = array(), $options = array())

This method allows adding contents into cells setting a position.

Parameters

contents

This could be:

  • a string
  • an array with the following keys and values:
Key Type Description
text string The content to be inserted.
bold bool If true the content will be shown in bold characters.
color string Hexadecimal color value: 'FF0000', '000000'...
font string Font family: 'Arial', 'Calibri'...
fontSize int Font size in points.
italic bool If true displays the content in italics.
strikethrough bool If true displays text in strikethrough.
subscript bool If true displays text in subscript.
superscript bool If true displays text in superscript.
underline string Underlines text: single, double.
  • a multidimensional array to add rich text contents

position

Cell position in the active sheet: A1, C3, AB7...

cellStyles

Key Type Description
backgroundColor string Hexadecimal color value: 'FFFF00', 'CCCCCC'...
border string Border type: thin, thick, dashed, double, mediumDashDotDot, hair... This value can be overridden for each side with 'borderTop', 'borderRight', 'borderBottom', 'borderLeft' and 'borderDiagonal' properties.
borderColor string Hexadecimal color value: 'FFFF00', 'CCCCCC'... This value can be overridden for each side with 'borderColorTop', 'borderColorRight', 'borderColorBottom', 'borderColorLeft' and 'borderColorDiagonal' properties.
cellStyleName string Cell style name.
  • Custom style name.
  • Preset style names: Bad, Calculation, Check Cell, Comma, Currency, Explanatory Text, Good, Heading 1, Heading 2, Heading 3, Heading 4, Input, Linked Cell, Neutral, Normal, Note, Output, Percent, Warning Text.
Applying a cell style name ignores other cell styles.
horizontalAlign string left, center, right.
indent int Indent value.
isFunction bool Set the content as function. Default as false. If not set, if the content starts with = , isFunction is set as true (set isFunction as false to avoid this).
locked bool Locked property.
rotation int Orientation degrees.
shrinkToFit bool If true enables shrink to fit.
textDirection string context, ltr, rtl.
type string Cell type: general (default), number, currency, accounting, date, time, percentage, fraction, scientific, text, special, boolean.
typeOptions array Keys and values:
  • formatCode (string)
verticalAlign string top, center, bottom.
wrapText bool If true enables wrap text.

options

Key Type Description
insertMode string Insert mode if the position contains an existing content: 'replace', 'ignore'. Default as replace.
raw bool Add the raw content if true. Default as false.
useCellStyles bool Use existing cell styles. Default as false.
Code samples

Example #1

x
 
1
require_once 'classes/CreateXlsx.php';
2
3
$xlsx = new CreateXlsx();
4
5
$content = array(
6
    'text' => 'Lorem ipsum dolor sit amet',
7
);
8
$xlsx->addCell($content, 'A1');
9
$content = array(
10
    'text' => 'Lorem ipsum',
11
);
12
$xlsx->addCell($content, 'A2');
13
14
$content = array(
15
    'text' => 'Sed ut perspiciatis unde omnis',
16
    'italic' => true,
17
    'underline' => 'single',
18
);
19
$xlsx->addCell($content, 'B5');
20
21
$content = array(
22
    'text' => 'At vero eos et accusamus et iusto',
23
    'color' => '13775F',
24
);
25
$xlsx->addCell($content, 'F2');
26
27
$content = array(
28
    'text' => 'Lorem ipsum dolor sit amet',
29
    'bold' => true,
30
    'font' => 'Times New Roman',
31
    'strikethrough' => true,
32
);
33
$xlsx->addCell($content, 'AA3');
34
35
$xlsx->saveXlsx('output');
36

The resulting XLSX looks like:

Example #2

35
 
1
require_once 'classes/CreateXlsx.php';
2
3
$xlsx = new CreateXlsx();
4
5
$content = array(
6
    'text' => 'Lorem ipsum dolor sit amet',
7
    'bold' => true,
8
    'font' => 'Arial',
9
);
10
$xlsx->addCell($content, 'A1');
11
12
$content = array(
13
    'text' => 'Sed ut perspiciatis unde omnis',
14
    'italic' => true,
15
);
16
$xlsx->addCell($content, 'B5');
17
18
// add a new sheet
19
$xlsx->addSheet(array('removeSelected' => true, 'selected' => true, 'active' => true));
20
21
$content = array(
22
    'text' => 'At vero eos et accusamus et iusto',
23
);
24
$xlsx->addCell($content, 'F2');
25
26
$content = array(
27
    'text' => 'Lorem ipsum dolor sit amet',
28
    'bold' => true,
29
    'font' => 'Times New Roman',
30
    'strikethrough' => true,
31
);
32
$xlsx->addCell($content, 'A1');
33
34
$xlsx->saveXlsx('output');
35

The resulting XLSX looks like:

Example #3

39
 
1
require_once 'classes/CreateXlsx.php';
2
3
$xlsx = new CreateXlsx();
4
5
$content = array(
6
    array(
7
        'text' => 'Lorem ipsum',
8
        'bold' => true,
9
        'font' => 'Arial',
10
    ),
11
    array(
12
        'text' => ' dolor sit',
13
        'italic' => true,
14
    ),
15
    array(
16
        'text' => ' amet',
17
        'underline' => 'single',
18
    ),
19
);
20
$xlsx->addCell($content, 'A1');
21
22
$content = array(
23
    array(
24
        'text' => 'Lorem ipsum',
25
        'bold' => true,
26
    ),
27
    array(
28
        'text' => ' dolor sit',
29
        'italic' => true,
30
    ),
31
    array(
32
        'text' => ' amet',
33
        'underline' => 'single',
34
    ),
35
);
36
$xlsx->addCell($content, 'B4');
37
38
$xlsx->saveXlsx('output');
39

The resulting XLSX looks like:

Example #4

36
 
1
require_once 'classes/CreateXlsx.php';
2
3
$xlsx = new CreateXlsx();
4
5
$content = array(
6
    'text' => 'Lorem ipsum',
7
    'fontSize' => 8,
8
);
9
$cellStyles = array(
10
    'backgroundColor' => 'FFFF00',
11
    'verticalAlign' => 'top',
12
);
13
$xlsx->addCell($content, 'A1', $cellStyles);
14
15
$content = array(
16
    'text' => 'Lorem ipsum dolor',
17
);
18
$cellStyles = array(
19
    'horizontalAlign' => 'center',
20
    'wrapText' => true,
21
);
22
$xlsx->addCell($content, 'B1', $cellStyles);
23
24
$content = array(
25
    'text' => 'Border',
26
);
27
$cellStyles = array(
28
    'border' => 'dashed',
29
    'borderBottom' => 'double',
30
    'borderColorBottom' => 'FF0000',
31
    'borderColorTop' => 'FF0000',
32
);
33
$xlsx->addCell($content, 'C4', $cellStyles);
34
35
$xlsx->saveXlsx('output');
36

The resulting XLSX looks like:

Example #5

86
 
1
require_once 'classes/CreateXlsx.php';
2
3
$xlsx = new CreateXlsx();
4
5
// text as general type
6
$xlsx->addCell('Lorem ipsum dolor sit amet', 'A1');
7
8
// text as general type with styles
9
$content = array(
10
    'text' => 'Sed ut perspiciatis unde omnis',
11
    'italic' => true,
12
    'underline' => 'single',
13
);
14
$xlsx->addCell($content, 'D1');
15
16
// number type
17
$xlsx->addCell(10, 'A2');
18
19
// number type with styles
20
$content = array(
21
    'text' => 500.5,
22
    'bold' => true,
23
);
24
$xlsx->addCell($content, 'B2');
25
26
// date type
27
$date = new DateTime('2011-03-18');
28
$xlsx->addCell($date, 'A3');
29
30
// date type with text styles
31
$content = array(
32
    'text' => new DateTime('2011-03-18'),
33
    'italic' => true,
34
    'underline' => 'single',
35
);
36
$xlsx->addCell($content, 'C3');
37
38
// date type with text styles and cell styles
39
$content = array(
40
    'text' => new DateTime('2011-03-18 12:28'),
41
    'bold' => true,
42
);
43
$cellStyles = array(
44
    'typeOptions' => array(
45
        'formatCode' => 'd\-m\-yy\ h:mm;@',
46
    ),
47
);
48
$xlsx->addCell($content, 'E3', $cellStyles);
49
50
// boolean type
51
$xlsx->addCell(true, 'A4');
52
53
// boolean type with styles
54
$content = array(
55
    'text' => true,
56
    'bold' => true,
57
);
58
$xlsx->addCell($content, 'B4');
59
60
// percentage type
61
$xlsx->addCell('10%', 'A5');
62
63
// percentage type with styles
64
$content = array(
65
    'text' => '80%',
66
    'bold' => true,
67
);
68
$xlsx->addCell($content, 'B5');
69
70
// function
71
$xlsx->addCell('=SUM(A2:B2)', 'A6');
72
73
// function setting number type applying text styles and cell styles. The recommended method to add functions is addFunction
74
$content = array(
75
    'text' => '=SUM(A2:B2)',
76
    'bold' => true,
77
    'font' => 'Arial',
78
);
79
$cellStyles = array(
80
    'backgroundColor' => 'FFFF00',
81
    'type' => 'number',
82
);
83
$xlsx->addCell($content, 'B6', $cellStyles);
84
85
$xlsx->saveXlsx('output');
86

The resulting XLSX looks like:

Example #6

282
 
1
require_once 'classes/CreateXlsx.php';
2
3
$xlsx = new CreateXlsx();
4
5
// general type is the default one
6
$content = array(
7
    'text' => 'Lorem',
8
);
9
$xlsx->addCell($content, 'A1');
10
11
// numeric contents are detected as numbers if no custom type is set
12
$content = array(
13
    'text' => 100,
14
);
15
$xlsx->addCell($content, 'A2');
16
$content = array(
17
    'text' => 10.50,
18
);
19
$cellStyles = array(
20
    'type' => 'generic',
21
);
22
$xlsx->addCell($content, 'B2', $cellStyles);
23
24
// number content
25
$cellStyles = array(
26
    'type' => 'number',
27
);
28
$content = array(
29
    'text' => 100,
30
);
31
$xlsx->addCell($content, 'A3', $cellStyles);
32
$content = array(
33
    'text' => 10.50,
34
);
35
$xlsx->addCell($content, 'B3', $cellStyles);
36
37
// number content with a custom format
38
$content = array(
39
    'text' => 1200,
40
);
41
$cellStyles = array(
42
    'type' => 'number',
43
    'typeOptions' => array(
44
        'formatCode' => '#,##0.00',
45
    ),
46
);
47
$xlsx->addCell($content, 'A4', $cellStyles);
48
49
// currency content
50
$content = array(
51
    'text' => 1200,
52
);
53
$cellStyles = array(
54
    'type' => 'currency',
55
    'typeOptions' => array(
56
        'formatCode' => '[$$-409]#,##0.00', // $
57
    ),
58
);
59
$xlsx->addCell($content, 'A5', $cellStyles);
60
61
$content = array(
62
    'text' => 1200,
63
);
64
$cellStyles = array(
65
    'type' => 'currency',
66
    'typeOptions' => array(
67
        'formatCode' => '[$£-809]#,##0.00', // £
68
    ),
69
);
70
$xlsx->addCell($content, 'B5', $cellStyles);
71
72
$content = array(
73
    'text' => 1200,
74
);
75
$cellStyles = array(
76
    'type' => 'currency',
77
    'typeOptions' => array(
78
        'formatCode' => '#,##0.00\ "€"', // €
79
    ),
80
);
81
$xlsx->addCell($content, 'C5', $cellStyles);
82
83
// accounting content
84
$content = array(
85
    'text' => 1200,
86
);
87
$cellStyles = array(
88
    'type' => 'accounting',
89
    'typeOptions' => array(
90
        'formatCode' => '_-[$$-409]* #,##0.00_ ;_-[$$-409]* \-#,##0.00\ ;_-[$$-409]* "-"??_ ;_-@_ ', // $
91
    ),
92
);
93
$xlsx->addCell($content, 'A6', $cellStyles);
94
95
$content = array(
96
    'text' => 1200,
97
);
98
$cellStyles = array(
99
    'type' => 'accounting',
100
    'typeOptions' => array(
101
        'formatCode' => '_-[$£-809]* #,##0.00_-;\-[$£-809]* #,##0.00_-;_-[$£-809]* "-"??_-;_-@_-', // £
102
    ),
103
);
104
$xlsx->addCell($content, 'B6', $cellStyles);
105
106
$content = array(
107
    'text' => 1200,
108
);
109
$cellStyles = array(
110
    'type' => 'accounting',
111
    'typeOptions' => array(
112
        'formatCode' => '_-* #,##0.00\ "€"_-;\-* #,##0.00\ "€"_-;_-* "-"??\ "€"_-;_-@_-', // €
113
    ),
114
);
115
$xlsx->addCell($content, 'C6', $cellStyles);
116
117
// date and time content
118
$content = array(
119
    'text' => '2010-02-26',
120
);
121
$cellStyles = array(
122
    'type' => 'date',
123
);
124
$xlsx->addCell($content, 'A7', $cellStyles);
125
126
$content = array(
127
    'text' => '1980-08-08',
128
);
129
$cellStyles = array(
130
    'type' => 'date',
131
    'typeOptions' => array(
132
        'formatCode' => 'yyyy\-mm\-dd;@',
133
    ),
134
);
135
$xlsx->addCell($content, 'B7', $cellStyles);
136
137
$content = array(
138
    'text' => '10:30',
139
);
140
$cellStyles = array(
141
    'type' => 'time',
142
);
143
$xlsx->addCell($content, 'C7', $cellStyles);
144
145
$content = array(
146
    'text' => '12:14:31',
147
);
148
$cellStyles = array(
149
    'type' => 'time',
150
    'typeOptions' => array(
151
        'formatCode' => '[$-409]h:mm:ss\ AM/PM;@',
152
    ),
153
);
154
$xlsx->addCell($content, 'D7', $cellStyles);
155
156
$content = array(
157
    'text' => '1980-08-08 22:31',
158
);
159
$cellStyles = array(
160
    'type' => 'date',
161
    'typeOptions' => array(
162
        'formatCode' => 'd\-m\-yy\ h:mm;@',
163
    ),
164
);
165
$xlsx->addCell($content, 'E7', $cellStyles);
166
167
// percentage content
168
$content = array(
169
    'text' => 0.9,
170
);
171
$cellStyles = array(
172
    'type' => 'percentage',
173
);
174
$xlsx->addCell($content, 'A8', $cellStyles);
175
176
$content = array(
177
    'text' => 0.263,
178
);
179
$cellStyles = array(
180
    'type' => 'percentage',
181
    'typeOptions' => array(
182
        'formatCode' => '0.00%',
183
    ),
184
);
185
$xlsx->addCell($content, 'B8', $cellStyles);
186
187
// fraction content
188
$content = array(
189
    'text' => 1/2,
190
);
191
$cellStyles = array(
192
    'type' => 'fraction',
193
);
194
$xlsx->addCell($content, 'A9', $cellStyles);
195
196
$content = array(
197
    'text' => 2/4,
198
);
199
$cellStyles = array(
200
    'type' => 'fraction',
201
    'typeOptions' => array(
202
        'formatCode' => '#\ ?/4',
203
    ),
204
);
205
$xlsx->addCell($content, 'B9', $cellStyles);
206
207
$content = array(
208
    'text' => 1/3,
209
);
210
$cellStyles = array(
211
    'type' => 'fraction',
212
);
213
$xlsx->addCell($content, 'C9', $cellStyles);
214
215
// scientific content
216
$cellStyles = array(
217
    'type' => 'scientific',
218
);
219
$content = array(
220
    'text' => 150,
221
);
222
$xlsx->addCell($content, 'A10', $cellStyles);
223
224
$content = array(
225
    'text' => 5000,
226
);
227
$xlsx->addCell($content, 'B10', $cellStyles);
228
229
// text content
230
$cellStyles = array(
231
    'type' => 'text',
232
);
233
$content = array(
234
    'text' => 'Lorem',
235
);
236
$xlsx->addCell($content, 'A11', $cellStyles);
237
$content = array(
238
    'text' => 100,
239
);
240
$xlsx->addCell($content, 'B11', $cellStyles);
241
$content = array(
242
    'text' => 10.50,
243
);
244
$xlsx->addCell($content, 'C11', $cellStyles);
245
246
// special content
247
$content = array(
248
    'text' => '2802',
249
);
250
$cellStyles = array(
251
    'type' => 'special',
252
    'typeOptions' => array(
253
        'formatCode' => '00000',
254
    ),
255
);
256
$xlsx->addCell($content, 'A12', $cellStyles);
257
258
// boolean content
259
$cellStyles = array(
260
    'type' => 'boolean',
261
);
262
$content = array(
263
    'text' => true,
264
);
265
$xlsx->addCell($content, 'A13', $cellStyles);
266
267
$content = array(
268
    'text' => false,
269
);
270
$xlsx->addCell($content, 'B13', $cellStyles);
271
$content = array(
272
    'text' => 1,
273
);
274
$xlsx->addCell($content, 'C13', $cellStyles);
275
276
$content = array(
277
    'text' => 0,
278
);
279
$xlsx->addCell($content, 'D13', $cellStyles);
280
281
$xlsx->saveXlsx('output');
282

The resulting XLSX looks like:

Release notes
  • phpxlsx 4.0:
    • clean calcChain values of the chosen position automatically.
  • phpxlsx 1.0:
    • new method.
­
­