Spreadsheet

The cells class allows the developer to perform read and write operations in the spreadsheet cells. The following operations are available:

Cell values

Update cell values

The method setValue expects an array of rows definitions. Each row definition is represeting by an associative array with two properties: row and data. The property row defines which rowNumber the data is going to be updated starting on number zero for the first row. The data is an array where each key represents the column in which the data should be updated, starting on zero for the first column.

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

$result = $spreadsheet->getCells()->setValues([
    // Update the first, second and third column from the second row.
    ['row' => 1, 'data' => ['4', '5', '6']],
    // Update the third column only from the third row
    ['row' => 2, 'data' => [2 => 'D4']]
]);

// Result
print_r($result);

// { "success": 1, "message": "Updated" }

Retrieve cell values

You can request the data in a spreadsheet by defining a single cell, an array of cells or a range of cells, as below:

Read the data from a single cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCell('D4')->getValues());

// [{"row":3,"data":["D4"]}]

Read the data from multiple cells

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells(['A3', 'B2'])->getValue());

// [[{"row":2,"data":["A3"]}],[{"row":1,"data":["B2"]}]]

Read the data from multiple cells by range

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells('A2:B3')->getValue());

// [{"row":1,"data":["A2","B2"]},{"row":2,"data":["A3","B3"]}]

Cell comments

Manage the comments from cells in your spreadsheet by a single cell, an array of cells or a specified range

Add comments

Add comments to a single cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCell('C3')->setComment('this is a comment'));

// { "success": 1, "message": "Updated" }

Add comments to multiple cells

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells()->setComments([
    'A1' => 'comments',
    'B2' => 'comments B2'
]);

// { "success": 1, "message": "Updated" }

Read comments

Get all comments from a spreadsheet

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells()->getComments());

// {"A1": "comments", "B2": "comments B2", "C3": "this is a comment"}

Get comments from a single cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCell('C3')->getComment());

// {"C3": "this is a comment"}

Get comments from multiple cells

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells(['A1', 'C3'])->getComments());

// {"A1":"comments","C3":"this is a comment"}

Get comments from multiple cells by range

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells('A1:C3')->getComments());

// {"A1":"comments","B2":"comments B2","C3":"this is a comment"}

Cell meta

Meta information are hidden information related to the cells in your spreadsheet. The meta information can be defined as a object with keys and values, and each cell can hold any number of unique keys.

Add meta information

Add meta information for a single cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCell('C3')->setMeta(['name' => 'The Name']));

// { "success": 1, "message": "Updated" }

Add meta information for multiple cells

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

$metas = [
    'B2' => ['key' => 'value'],
    'D4' => ['key2' => 'value 2']
];

// Result
print_r($spreadsheet->getCells()->setMeta($metas));

// { "success": 1, "message": "Updated" }

Reset all meta information

The following code will reset the meta information of the whole table

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells()->resetMeta());

// {"success":1,"message":"Updated"}

Read the meta information

Get all meta information from a spreadsheet

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCells()->getMeta());

// {"B2":{"key":"value"},"C3":{"name":"The Name"},"D4":{"key2":"value 2"}}

Get the meta information from a single cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getCell('B2')->getMeta());

// {"B2":{"key":"value"}}

Get the meta information from multiple cells

There are two ways to get information from multiple cells. By an array of cellNames or by range, as shown in the following example.

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Get the meta information from C3 and B2
print_r($spreadsheet->getCells(['C3', 'B2'])->getMeta());

// Get the meta information from all cells in the folling range
print_r($spreadsheet->getCells('A1:D4')->getMeta());

Cell styles

Defining cell styles

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

$styles = [
    'A1' => 'background-color: #333;color:#fff;',
    'A2' => 'background-color: #ccc'
];

// Result
print_r($spreadsheet->setStyle($styles));

// {"success":1,"message":"Updated"}

Retrieving the style of a cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getStyle('A1'));

// {"A1":"background-color: #333;color:#fff"}

Retrieving the styles of multiple cells

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getStyle(['A1', 'A2']));

// {"A1":"background-color: #333;color:#fff","A2":"background-color: #ccc"}

Retrieving the style of multiple cells by range

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getStyle('A1:D4'));

// {"A1":"background-color: #333;color:#fff","A2":"background-color: #ccc"}

Reset all styles

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->resetStyle());

// {"success":1,"message":"Updated"}

Merge cells

Defining the merging of cells

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

$styles = [
    'A1' => 'background-color: #333;color:#fff;',
    'A2' => 'background-color: #ccc'
];

// Result - Merge of A1 with B1, A2 and B2:
print_r($spreadsheet->setMerge('A1', 2, 2));

// {"success":1,"message":"Updated"}

Retrieving the merge of a cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getMerge('A1'));

// {"A1":[2,2]}

Retrieving the merges of multiple cells

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getMerge(['A1', 'B4']));

// {"A1":[2,2],"B4":[2,3]}

Retrieving the merges of multiple cells by range

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->getMerge('A1:D4'));

// {"A1":[2,2],"B4":[2,3]}

Remove the merge from a cell

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->removeMerge('B4'));

// {"success":1,"message":"Updated"}

Reset all merges

<?php
require 'vendor/autoload.php';

use jexcel\Jexcel;

// Create the jexcel instance
$client = new Jexcel('ZzAsMzQ0YWI1MmMzZjQwZjQ4MmQ6YWM2Njg3MzgwZWVmNDVkYzExZjJlN1==');

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet('946f23e8-ecd5-459c-b377-cc0c93200457');

// Result
print_r($spreadsheet->resetMerge());

// {"success":1,"message":"Updated"}