Jspreadsheet Api

Insert columns

Add a new column in a specific position

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Insert before flag
$insertBefore = true;

// Column properties
$properties = [['title' => 'new A', 'type' => 'text']];

// Add a new column on the second position. Position start on zero.
$result = $spreadsheet->getColumn(1)->insert($insertBefore, $properties);

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

Add a new column at the end with data

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Insert before flag
$insertBefore = false;

// Column properties
$properties = [['title' => 'new A', 'type' => 'text']];

// Add a new column on the second position. Position start on zero.
$result = $spreadsheet->getColumns()->insert($insertBefore, $properties);

// Result
print_r($result);

Delete columns

Delete the column from a specific position

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Delete first column
$result = $spreadsheet->getColumn(0)->delete();

// Result
print_r($result);

Delete multiple columns

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Delete the first 2 columns
$result = $spreadsheet->getColumn(0)->delete(2);

// Result
print_r($result);

Column position

Change a column position

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Move the column zero to the second position
$result = $spreadsheet->getColumn(0)->moveTo(1);

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

Column width

The developer can read or write the column width from one or more columns as below.

Define the width of a column

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Set the position of the third column to 200px
$result = $spreadsheet->getColumn(2)->setWidth(200);

// Result
print_r($result);

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

Define the width of multiple columns

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Define the width of the forth and fifth columns to 250px
$result = $spreadsheet->getColumns([3,4])->setWidth(250);

// Result
print_r($result);

Get the width from multiple columns

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Get the width from multiple columns
$result = $spreadsheet->getColumns([3, 4])->getWidth();

// Result
print_r($result);

// Array ( [3] => 250 [4] => 250 )

Column properties

Update the column properties

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// New properties
$properties = [ 'type' => 'checkbox', 'title' => 'Column A', 'width' => '100px' ];

// Change the properties from the forth column
$result = $spreadsheet->getColumn(3)->setProperties($properties);

// Result
print_r($result);

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

Read the properties of a column

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Get the properties from the first column
$result = $spreadsheet->getColumn(0)->getProperties();

// Result
print_r($result);

// {"type":"text","align":"center","width":"47","source":[],"options":[]}

Read the properties of multiple columns

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

use jspreadsheet/Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// Get the properties from multiple columns
$result = $spreadsheet->getColumns([0,1])->getProperties();

// Result
print_r($result);

Sorting a column

Sorting the data in a column

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

use jspreadsheet\Jspreadsheet;

// Access token
$token = 'MSwzMTJmZWQzMWYyYTI1OWQ5OGVhMWYxOWNhMDNhYWY3ZTA2ZmVmMWQz';

// Spreadsheet Guid
$guid = '15eb1171-5a64-45bf-be96-f52b6125a045';

// Create the client instance
$client = new Jspreadsheet($token);

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

// \jspreadsheet\Columns::ASC = Ascending order
// \jspreadsheet\Columns::DESC = Descending order

// Sort column
$result = $spreadsheet->getColumn(2)->orderBy(\jspreadsheet\Columns::ASC);

// Result with the new order applied
print_r($result);

// {"success": 1, "message": "Updated", "order": [8,7,6,5,4,3,2,1,0,9]}