XlsxPath

Introduction

XlsxPath is available in Advanced and Premium licenses. This feature facilitates the low level work with XLSX documents through easy but powerful methods that allow you to get, remove, and return almost any Excel content.

This feature benefits from the flexibility that a set of options adds. This set allows to filter by content type, position, text, attributes or dynamic queries.

For example, it is possible to remove paragraphs that contains a certain text, get elements information, remove images or charts... The possibilities of PptxPath are almost endless.

XlsxPath is compatible with new spreadsheets and templates.

Remove elements

removeElement: this method deletes existing elements in the spreadsheet.

require_once 'classes/CreateXlsx.php';

// it is possible to use XlsxPath with a new spreadsheet or a template
$xlsx = new CreateXlsxFromTemplate('template.xlsx');

// remove the first cell
$referenceToBeRemoved = array(
    'type' => 'cell',
    'occurrence' => 1,
);
$xlsx->removeElement($referenceToBeRemoved);

// remove cells from 2nd position
$referenceToBeRemoved = array(
    'type' => 'cell',
    'occurrence' => '2..',
);
$xlsx->removeElement($referenceToBeRemoved);

// change the active sheet
$xlsx->setActiveSheet(array('position' => 2));

// remove cells that containt a text
$referenceToBeRemoved = array(
    'type' => 'cell',
    'contains' => 'IMAGE',
);
$xlsx->removeElement($referenceToBeRemoved);

$xlsx->saveXlsx('output');

Get XLSX elements and query information

If you need to obtain information about the data the XPath query returns, you can run the getXlsxPathQueryInfo method. Thanks to this new method it is possible to get the number of the concurrent results and the string of the query.

require_once 'classes/CreateXlsx.php';

// it is possible to use XlsxPath with a new document or a template
$xlsx = new CreateXlsxFromTemplate('template.xlsx');

$referenceNode = array(
    'type' => 'cell',
    'occurrence' => 1,
);
$queryInfo = $xlsx->getXlsxPathQueryInfo($referenceNode);
var_dump($queryInfo);

if ($queryInfo['elements']->length > 0) {
    foreach ($queryInfo['elements'] as $element) {
        var_dump($element->ownerDocument->saveXML($element));
    }
}

// change the active sheet
$xlsx->setActiveSheet(array('position' => 1));

$referenceNode = array(
    'type' => 'cell',
    'contains' => 'VAR',
);
$queryInfo = $xlsx->getXlsxPathQueryInfo($referenceNode);
var_dump($queryInfo);

if ($queryInfo['elements']->length > 0) {
    foreach ($queryInfo['elements'] as $element) {
        var_dump($element->ownerDocument->saveXML($element));
    }
}

Tips

The following options indicate the references to the contents: type (cell, drawing, row), contains (string, text value), occurrence ((int) exact occurrence or (string) range of contents (e.g.: 2..9, 2.., ..9) or first() or last(), if empty iterate all elements), attributes (string, contains a specific attribute key and value), customQuery (custom XPath query, overwrites any other reference).
If the option occurrence is not set all references of the query will be changed.
When setting a referenceNode it is mandatory to employ at least one of the available options.
If you need to set the last occurrence of an element you can use the value -1. The first element is always 1, unlike PHP arrays, in which the first element is 0.