addCell

CONTENTS

LAYOUT AND SETTINGS

TEMPLATES

SAVE

FORMAT CONVERSION

transform

XLSXPATH

XLSXUTILITIES

INDEXER

indexer

PERFORMANCE

zipStream

CRYPTO

DIGITAL SIGNATURE

sign (XLSX)

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

require_once 'classes/CreateXlsx.php';

$xlsx = new CreateXlsx();

$content = array(
    'text' => 'Lorem ipsum dolor sit amet',
);
$xlsx->addCell($content, 'A1');
$content = array(
    'text' => 'Lorem ipsum',
);
$xlsx->addCell($content, 'A2');

$content = array(
    'text' => 'Sed ut perspiciatis unde omnis',
    'italic' => true,
    'underline' => 'single',
);
$xlsx->addCell($content, 'B5');

$content = array(
    'text' => 'At vero eos et accusamus et iusto',
    'color' => '13775F',
);
$xlsx->addCell($content, 'F2');

$content = array(
    'text' => 'Lorem ipsum dolor sit amet',
    'bold' => true,
    'font' => 'Times New Roman',
    'strikethrough' => true,
);
$xlsx->addCell($content, 'AA3');

$xlsx->saveXlsx('output');

The resulting XLSX looks like:

Example #2

require_once 'classes/CreateXlsx.php';

$xlsx = new CreateXlsx();

$content = array(
    'text' => 'Lorem ipsum dolor sit amet',
    'bold' => true,
    'font' => 'Arial',
);
$xlsx->addCell($content, 'A1');

$content = array(
    'text' => 'Sed ut perspiciatis unde omnis',
    'italic' => true,
);
$xlsx->addCell($content, 'B5');

// add a new sheet
$xlsx->addSheet(array('removeSelected' => true, 'selected' => true, 'active' => true));

$content = array(
    'text' => 'At vero eos et accusamus et iusto',
);
$xlsx->addCell($content, 'F2');

$content = array(
    'text' => 'Lorem ipsum dolor sit amet',
    'bold' => true,
    'font' => 'Times New Roman',
    'strikethrough' => true,
);
$xlsx->addCell($content, 'A1');

$xlsx->saveXlsx('output');

The resulting XLSX looks like:

Example #3

require_once 'classes/CreateXlsx.php';

$xlsx = new CreateXlsx();

$content = array(
    array(
        'text' => 'Lorem ipsum',
        'bold' => true,
        'font' => 'Arial',
    ),
    array(
        'text' => ' dolor sit',
        'italic' => true,
    ),
    array(
        'text' => ' amet',
        'underline' => 'single',
    ),
);
$xlsx->addCell($content, 'A1');

$content = array(
    array(
        'text' => 'Lorem ipsum',
        'bold' => true,
    ),
    array(
        'text' => ' dolor sit',
        'italic' => true,
    ),
    array(
        'text' => ' amet',
        'underline' => 'single',
    ),
);
$xlsx->addCell($content, 'B4');

$xlsx->saveXlsx('output');

The resulting XLSX looks like:

Example #4

require_once 'classes/CreateXlsx.php';

$xlsx = new CreateXlsx();

$content = array(
    'text' => 'Lorem ipsum',
    'fontSize' => 8,
);
$cellStyles = array(
    'backgroundColor' => 'FFFF00',
    'verticalAlign' => 'top',
);
$xlsx->addCell($content, 'A1', $cellStyles);

$content = array(
    'text' => 'Lorem ipsum dolor',
);
$cellStyles = array(
    'horizontalAlign' => 'center',
    'wrapText' => true,
);
$xlsx->addCell($content, 'B1', $cellStyles);

$content = array(
    'text' => 'Border',
);
$cellStyles = array(
    'border' => 'dashed',
    'borderBottom' => 'double',
    'borderColorBottom' => 'FF0000',
    'borderColorTop' => 'FF0000',
);
$xlsx->addCell($content, 'C4', $cellStyles);

$xlsx->saveXlsx('output');

The resulting XLSX looks like:

Example #5

require_once 'classes/CreateXlsx.php';

$xlsx = new CreateXlsx();

// text as general type
$xlsx->addCell('Lorem ipsum dolor sit amet', 'A1');

// text as general type with styles
$content = array(
    'text' => 'Sed ut perspiciatis unde omnis',
    'italic' => true,
    'underline' => 'single',
);
$xlsx->addCell($content, 'D1');

// number type
$xlsx->addCell(10, 'A2');

// number type with styles
$content = array(
    'text' => 500.5,
    'bold' => true,
);
$xlsx->addCell($content, 'B2');

// date type
$date = new DateTime('2011-03-18');
$xlsx->addCell($date, 'A3');

// date type with text styles
$content = array(
    'text' => new DateTime('2011-03-18'),
    'italic' => true,
    'underline' => 'single',
);
$xlsx->addCell($content, 'C3');

// date type with text styles and cell styles
$content = array(
    'text' => new DateTime('2011-03-18 12:28'),
    'bold' => true,
);
$cellStyles = array(
    'typeOptions' => array(
        'formatCode' => 'd\-m\-yy\ h:mm;@',
    ),
);
$xlsx->addCell($content, 'E3', $cellStyles);

// boolean type
$xlsx->addCell(true, 'A4');

// boolean type with styles
$content = array(
    'text' => true,
    'bold' => true,
);
$xlsx->addCell($content, 'B4');

// percentage type
$xlsx->addCell('10%', 'A5');

// percentage type with styles
$content = array(
    'text' => '80%',
    'bold' => true,
);
$xlsx->addCell($content, 'B5');

// function
$xlsx->addCell('=SUM(A2:B2)', 'A6');

// function setting number type applying text styles and cell styles. The recommended method to add functions is addFunction
$content = array(
    'text' => '=SUM(A2:B2)',
    'bold' => true,
    'font' => 'Arial',
);
$cellStyles = array(
    'backgroundColor' => 'FFFF00',
    'type' => 'number',
);
$xlsx->addCell($content, 'B6', $cellStyles);

$xlsx->saveXlsx('output');

The resulting XLSX looks like:

Example #6

require_once 'classes/CreateXlsx.php';

$xlsx = new CreateXlsx();

// general type is the default one
$content = array(
    'text' => 'Lorem',
);
$xlsx->addCell($content, 'A1');

// numeric contents are detected as numbers if no custom type is set
$content = array(
    'text' => 100,
);
$xlsx->addCell($content, 'A2');
$content = array(
    'text' => 10.50,
);
$cellStyles = array(
    'type' => 'generic',
);
$xlsx->addCell($content, 'B2', $cellStyles);

// number content
$cellStyles = array(
    'type' => 'number',
);
$content = array(
    'text' => 100,
);
$xlsx->addCell($content, 'A3', $cellStyles);
$content = array(
    'text' => 10.50,
);
$xlsx->addCell($content, 'B3', $cellStyles);

// number content with a custom format
$content = array(
    'text' => 1200,
);
$cellStyles = array(
    'type' => 'number',
    'typeOptions' => array(
        'formatCode' => '#,##0.00',
    ),
);
$xlsx->addCell($content, 'A4', $cellStyles);

// currency content
$content = array(
    'text' => 1200,
);
$cellStyles = array(
    'type' => 'currency',
    'typeOptions' => array(
        'formatCode' => '[$$-409]#,##0.00', // $
    ),
);
$xlsx->addCell($content, 'A5', $cellStyles);

$content = array(
    'text' => 1200,
);
$cellStyles = array(
    'type' => 'currency',
    'typeOptions' => array(
        'formatCode' => '[$£-809]#,##0.00', // £
    ),
);
$xlsx->addCell($content, 'B5', $cellStyles);

$content = array(
    'text' => 1200,
);
$cellStyles = array(
    'type' => 'currency',
    'typeOptions' => array(
        'formatCode' => '#,##0.00\ "€"', // €
    ),
);
$xlsx->addCell($content, 'C5', $cellStyles);

// accounting content
$content = array(
    'text' => 1200,
);
$cellStyles = array(
    'type' => 'accounting',
    'typeOptions' => array(
        'formatCode' => '_-[$$-409]* #,##0.00_ ;_-[$$-409]* \-#,##0.00\ ;_-[$$-409]* "-"??_ ;_-@_ ', // $
    ),
);
$xlsx->addCell($content, 'A6', $cellStyles);

$content = array(
    'text' => 1200,
);
$cellStyles = array(
    'type' => 'accounting',
    'typeOptions' => array(
        'formatCode' => '_-[$£-809]* #,##0.00_-;\-[$£-809]* #,##0.00_-;_-[$£-809]* "-"??_-;_-@_-', // £
    ),
);
$xlsx->addCell($content, 'B6', $cellStyles);

$content = array(
    'text' => 1200,
);
$cellStyles = array(
    'type' => 'accounting',
    'typeOptions' => array(
        'formatCode' => '_-* #,##0.00\ "€"_-;\-* #,##0.00\ "€"_-;_-* "-"??\ "€"_-;_-@_-', // €
    ),
);
$xlsx->addCell($content, 'C6', $cellStyles);

// date and time content
$content = array(
    'text' => '2010-02-26',
);
$cellStyles = array(
    'type' => 'date',
);
$xlsx->addCell($content, 'A7', $cellStyles);

$content = array(
    'text' => '1980-08-08',
);
$cellStyles = array(
    'type' => 'date',
    'typeOptions' => array(
        'formatCode' => 'yyyy\-mm\-dd;@',
    ),
);
$xlsx->addCell($content, 'B7', $cellStyles);

$content = array(
    'text' => '10:30',
);
$cellStyles = array(
    'type' => 'time',
);
$xlsx->addCell($content, 'C7', $cellStyles);

$content = array(
    'text' => '12:14:31',
);
$cellStyles = array(
    'type' => 'time',
    'typeOptions' => array(
        'formatCode' => '[$-409]h:mm:ss\ AM/PM;@',
    ),
);
$xlsx->addCell($content, 'D7', $cellStyles);

$content = array(
    'text' => '1980-08-08 22:31',
);
$cellStyles = array(
    'type' => 'date',
    'typeOptions' => array(
        'formatCode' => 'd\-m\-yy\ h:mm;@',
    ),
);
$xlsx->addCell($content, 'E7', $cellStyles);

// percentage content
$content = array(
    'text' => 0.9,
);
$cellStyles = array(
    'type' => 'percentage',
);
$xlsx->addCell($content, 'A8', $cellStyles);

$content = array(
    'text' => 0.263,
);
$cellStyles = array(
    'type' => 'percentage',
    'typeOptions' => array(
        'formatCode' => '0.00%',
    ),
);
$xlsx->addCell($content, 'B8', $cellStyles);

// fraction content
$content = array(
    'text' => 1/2,
);
$cellStyles = array(
    'type' => 'fraction',
);
$xlsx->addCell($content, 'A9', $cellStyles);

$content = array(
    'text' => 2/4,
);
$cellStyles = array(
    'type' => 'fraction',
    'typeOptions' => array(
        'formatCode' => '#\ ?/4',
    ),
);
$xlsx->addCell($content, 'B9', $cellStyles);

$content = array(
    'text' => 1/3,
);
$cellStyles = array(
    'type' => 'fraction',
);
$xlsx->addCell($content, 'C9', $cellStyles);

// scientific content
$cellStyles = array(
    'type' => 'scientific',
);
$content = array(
    'text' => 150,
);
$xlsx->addCell($content, 'A10', $cellStyles);

$content = array(
    'text' => 5000,
);
$xlsx->addCell($content, 'B10', $cellStyles);

// text content
$cellStyles = array(
    'type' => 'text',
);
$content = array(
    'text' => 'Lorem',
);
$xlsx->addCell($content, 'A11', $cellStyles);
$content = array(
    'text' => 100,
);
$xlsx->addCell($content, 'B11', $cellStyles);
$content = array(
    'text' => 10.50,
);
$xlsx->addCell($content, 'C11', $cellStyles);

// special content
$content = array(
    'text' => '2802',
);
$cellStyles = array(
    'type' => 'special',
    'typeOptions' => array(
        'formatCode' => '00000',
    ),
);
$xlsx->addCell($content, 'A12', $cellStyles);

// boolean content
$cellStyles = array(
    'type' => 'boolean',
);
$content = array(
    'text' => true,
);
$xlsx->addCell($content, 'A13', $cellStyles);

$content = array(
    'text' => false,
);
$xlsx->addCell($content, 'B13', $cellStyles);
$content = array(
    'text' => 1,
);
$xlsx->addCell($content, 'C13', $cellStyles);

$content = array(
    'text' => 0,
);
$xlsx->addCell($content, 'D13', $cellStyles);

$xlsx->saveXlsx('output');

The resulting XLSX looks like:

Release notes

phpxlsx 4.0:
- clean calcChain values of the chosen position automatically.

phpxlsx 1.0:
- new method.