Documentation

Jexcel Api

Summary

Insert columns

Add a new column in a specific position

<?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');

// 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.
print_r($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 jexcel\Jexcel;

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

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

// Insert before flag
$insertBefore = true;

// Properties
$properties = [['title' => 'new C', 'type' => 'text']];

// Column data
$data = [['C1'], ['C2'], ['C3']];

// Result
print_r($spreadsheet->getColumn()->insert($insertBefore, $properties, $data));

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

Delete columns

Delete the column from a specific position

<?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');

// Delete the first column
print_r($spreadsheet->getColumn(0)->delete());

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

Delete multiple columns

<?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');

// Delete from three columns from the first column
print_r($spreadsheet->getColumn(0)->delete(3));

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

Column position

Change a column position

<?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');

// Move the column zero to the second position
print_r($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 jexcel\Jexcel;

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

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

// Update the width of the third column to 200px
print_r($spreadsheet->getColumn(2)->setWidth(200));

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

Define the width of multiple columns

<?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');

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

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

Get the width from multiple columns

<?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->getColumns([3, 4])->getWidth());

// {"3": 208, "4": 208}

Column properties

Update the column properties

<?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');

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

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

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

Read the properties of a 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');

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

// Expected result: [{"type": "checkbox", "title": "Column A", "width": "100px"}]

Read the properties of multiple columns

<?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 properties from multiple columns (first and second)
print_r($spreadsheet->getColumns([0, 1])->getProperties());

// [{"type": "checkbox", "title": "Column A", "width": "100px"}, {"type": "text"}]

Sorting a column

Sorting the data in a 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');

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

// Result
print_r($spreadsheet->getColumn(2)->orderBy(\jexcel\Columns::ASC));

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