Practical phpxlsx

Adding contents

Introduction

To explain how the library works, let's create a new XLSX and add different contents to it.

New XLSX

As a first step, create the XLSX, instantiating the class CreateXlsx:

require_once 'classes/CreateXlsx.php';

$xlsx = new CreateXlsx();

Now, add the new contents for this $xlsx object. Contents are added to the active sheet.

Sheets

The default XLSX includes one sheet. To add a new sheet, call addSheet:

public function addSheet($options = array())

For this spreadsheet sample, insert a new sheet:

$xlsx->addSheet();

A default name is added to the new sheet, that can be changed using the 'name' option:

$xlsx->addSheet(array('name' => 'New sheet'));

Cells

After at least one sheet exists in the spreadsheet, new contents can be added using addCell:

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

This method includes the following parameters:

$contents: string or array with contents and styles
$position: cell position in the active sheet (A1, C3, AB7...)
$cellStyles: array with cell styles to be applied
$options: extra options

Add to the active sheet two cell contents with styles at positions 'A1', 'A2' and 'C3':

$xlsx->addCell('Content', 'A1');

$content = array(
    'text' => 'Content 2',
    'italic' => true,
    'underline' => 'single',
);
$xlsx->addCell($content, 'A2');

$content = array(
    'text' => 'Content 3',
    'fontSize' => 8,
);
$cellStyles = array(
    'backgroundColor' => 'FFFF00',
    'verticalAlign' => 'top',
);
$xlsx->addCell($content, 'C3', $cellStyles);

The cell format type is detected automatically if the 'type' option is not set. The following code adds numeric and boolean format types autodetecting them:

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

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

The cell format type can be changed using the 'type' option, so a custom format can be used instead of the autodetected one:

// int value as generic type
$cellStyles = array(
    'type' => 'generic',
);
$xlsx->addCell(10, 'A4', $cellStyles);

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

To set a new cell value keeping existing styles, the addCell method can be used:

$xlsx->addCell($content, 'B5', array(), array('useCellStyles' => true));

Or setCellValue, that is an alias of the previous code:

$xlsx->setCellValue($content, 'B5');

Instead of adding each cell content individually with addCell, the addCellRange method can be used to add a range of cell contents:

public function addCellRange($contents, $position, $cellStyles = array(), $options = array())

The available options of the method are the same as addCell, but $contents that is always an array with the new contents.

These contents can be added horizontally from the chosen position, each array value fills one cell:

$contents = array(
    array(
        array('text' => 'New'), // first cell
        array('text' => 'text', 'bold' => true), // second cell
        array('text' => 'content.', 'italic' => true), // third cell
    ),
);
$xlsx->addCellRange($contents, 'B6');

Contents can also be added vertically from the chosen position:

$contents = array(
    array(
        array('text' => 'New'), // first cell
    ),
    array(
        array('text' => 'text', 'italic' => true, 'underline' => 'single'), // second cell
    ),
    array(
        array('text' => 'content.'), // third cell
    ),
);
$xlsx->addCellRange($contents, 'B8');

Horizontally and vertically contents can be added at the same time:

$contents = array(
    array(
        array('text' => 'New'), // first cell
        array('text' => 'text'), // second cell
    ),
    array(
        array('text' => 'and more', 'italic' => true), // third cell
        array('text' => 'content.'), // fourth cell
    ),
);
$xlsx->addCellRange($contents, 'D10');

New contents are always added to the active sheet. An XLSX only has one active sheet.

phpxlsx reads and uses the active sheet from the XLSX: the first sheet when creating a new XLSX from scratch or the active sheet of the workbook when using an XLSX template.

phpxlsx includes an internal active sheet value that can be changed using the setActiveSheet method. This internal active sheet value doesn't change the active sheet of the workbook, so it can be used to add contents to sheets keeping the preset active sheet.

After creating a new sheet, the active sheet can be changed to the new one. The following code adds contents to the active sheet (the first one), inserts a new sheet and then changes the internal active sheet to the second sheet to include another content:

$xlsx = new CreateXlsx();

$xlsx->addCell('Content added to the first sheet.', 'A1');

$xlsx->addSheet(array('name' => 'New sheet'));

// the internal active sheet has not changed, so the content is added in the first sheet
$xlsx->addCell('Other content added to the first sheet.', 'A2');

// set the second sheet as the internal active sheet (0 is the first sheet)
$xlsx->setActiveSheet(array('position' => 1));
// the active sheet can also be set with the sheet name
//$xlsx->setActiveSheet(array('name' => 'New sheet'));

$xlsx->addCell('Content added to the second sheet.', 'A2');

$xlsx->saveXlsx('output.xlsx');

Functions

addFunction is the method for adding functions:

public function addFunction($function, $position, $contentStyles = array(), $cellStyles = array(), $options = array())

This method includes the following parameters:

$function: function to be added
$position: cell position in the current active sheet (A1, C3, AB7...)
$contentStyles: array with the content styles to be applied
$cellStyles: array with the cell styles to be applied
$options: extra options

Let's add two numbers and a SUM function:

$content = array(
    'text' => 100,
);
$xlsx->addCell($content, 'E1');
$content = array(
    'text' => 10.50,
);
$xlsx->addCell($content, 'E2');

$contentStyles = array(
    'bold' => true,
    'font' => 'Arial',
);
$cellStyles = array(
    'backgroundColor' => 'FFFF00',
    'verticalAlign' => 'top',
);
$xlsx->addFunction('=SUM(E1:E2)', 'E4', $contentStyles, $cellStyles);

Breaks

To add breaks, use addBreak:

public function addBreak($position, $type = 'row')

You just need to choose the type of break, 'row' or 'col', and the numeric position.

For example, to add row and col breaks:

$xlsx->addBreak(2, 'row');

$xlsx->addBreak(4, 'col');

Links

To add a link, use the addLink method:

public function addLink($link, $position, $contents = array(), $cellStyles = array(), $options = array())

This method includes the following parameters:

$link: URL or #location
$position: cell position in the current active sheet (A1, C3, AB7...)
$contents: array with the text contents and styles
$cellStyles: array with the cell styles to be applied
$options: extra options

Here's a simple example:

$content = array(
    'text' => 'Link to phpxlsx',
);
$xlsx->addLink('https://www.phpxlsx.com', 'H1', $content);

Tips and tricks

The value of the internal active sheet can be returned using the getActiveSheet method.

A link fills the whole cell. The same cell can't contain a link and a text content without the link.

The addCellRange method returns the range of the added contents.

If a content is added in a cell position where another content exists, the new content replaces the old one. The 'insertMode' option allows changing this default behavior choosing 'replace' (default) or 'ignore'.

setActiveSheet allows using -1 as position to choose the last sheet.

Defined names can be added using addDefinedName.

Next - Working with templates