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 a cell value

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

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Get the spreadsheet instance and request configuration
$result = $spreadsheet->getCell('C4')->setValue('New value');

// { "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:

Get values from cells

It is possible to retrieve the value from one or multiple cells in a single request. To get multiple values, you can separete the cell names by comma, or query for a column range, such as A1:A9.
<?php
require 'vendor/autoload.php';

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Get the spreadsheet instance and request configuration
$result = $spreadsheet->getCell('C4,D4,D5')->getValue();

// [{"x":2,"y":3,"name":"C4","value":"C4"},{"x":3,"y":3,"name":"D4","value":null},{"x":3,"y":4,"name":"D5","value":null}]

Read the data from multiple cells by range

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Get the spreadsheet instance and request configuration
$result = $spreadsheet->getCell('C1:C2')->getValue();

// [{"x":2,"y":0,"name":"C1","value":null},{"x":2,"y":1,"name":"C2","value":null}]

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 jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Update comments
$result = $spreadsheet->getCell('C3')->setComment('this is a comment');

// Result
print_r($result);

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

Add comments to multiple cells

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

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

// Result
print_r($result);

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

Read comments

Get all comments from a cell on your spreadsheet

It is possible get the comments from multiple cells using comma or a range, such as D1:D4.
<?php
require 'vendor/autoload.php';

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Get comments from A1
$result = $spreadsheet->getCells('A1')->getComments(); // ['A1','A2','C3'] or D1:D4 or A1,A2,A3 for multiple cells

print_r($result);

// {"A1": "comments"}

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 jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Set meta information
$result = $spreadsheet->getCell('C3')->setMeta(['name' => 'The Name']);

// Result
print_r($result);

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

Add meta information for multiple cells

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Result
$meta = [
    'B2' => ['key' => 'value'],
    'D4' => ['key2' => 'value 2']
];

// Update meta information
$result = $spreadsheet->getCells()->setMeta($meta);

// Result
print_r($result);

// { "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 jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Reset
$result = $spreadsheet->getCells()->resetMeta();

// Result
print_r($result);

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

Read the meta information

Get all meta information from a spreadsheet

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Get all meta information
$result = $spreadsheet->getCells()->getMeta();

// Result
print_r($result);

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

Get the meta information from a cell

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Get the meta information
$result = $spreadsheet->getCells('A1')->getMeta(); // A1,A2,A3 or A1:A3 for multiple cells in the same request.

Cell styles

Defining cell styles

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

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

// Apply style
$result = $spreadsheet->setStyle($styles);

// Result
print_r($result);

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

Retrieving the style of a cell

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid, 0); // Zero for the first worksheet, 1 for the second, etc.

// Get style
$result = $spreadsheet->getStyle('A1'); // Array for multiple requests

// Result
print_r($result);

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

Reset style

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Reset
$result = $spreadsheet->resetStyle('A1');

// Result
print_r($result);

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

Merge cells

Defining the merging of cells

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Merge of A1 with B1, A2 and B2:
$result = $spreadsheet->setMerge('A1', 2, 2); // Colspan and Rowspan From A1

// Result
print_r($result);

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

Retrieving the merge information

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Get merge information
$result = $spreadsheet->getMerge('A1');

// Result
print_r($result);

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

Remove the merge from a cell

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Remove merge
$result = $spreadsheet->removeMerge('A1');

// Result
print_r($result);

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

Reset all merges

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSxlMjE2MWI5YWNjYTg2MzM4MThmN2Y4NjY0YmQzYzBlOGExMmVkZjVk';

// Spreadsheet Guid
$guid = '79b45919-c751-4e2b-a49a-6c1286e2fc03';

// Create a new client
$client = new Jspreadsheet($token);

// Get the spreadsheet instance
$spreadsheet = $client->getSpreadsheet($guid);

// Reset
$result = $spreadsheet->resetMerge();

// Result
print_r($result);

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