eiseXLSX

XLSX file data read-write library that operates with native cell addresses like A1 or R1C1.

This class was designed for server-side manipulations with uploaded spreadsheets in Microsoft® Excel™ 2007-2011-2013 file format – OpenXML SpreadsheetML.

Current version of this library allows to read user-uploaded file contents and to write data to preliminary uploaded template file and send it back to the user:

it allows to change existing cell data
clone rows and fill-in new rows with data
clone sheets within workbook, remove unnecessary sheets
colorization of cells.

This library offers the easiest way to make Excel™-based data exchange with information systems written in PHP.

Users are no longer need to convert Excel™ spreadsheets to CSV and other formats, they can simply upload data to the website using their worksheets.

You can use files received from users as your website’s output document templates with 100% match of cell formats, sheet layout, design, etc. With eiseXLSX you can stop wasting your time working on output documents layout – you can just ask your customer staff to prepare documents they’d like to see in XLSX format. Then you can put these files to the server and fill them with necessary data using PHP.

Unlike other PHP libraries for XLSX files manipulation eiseXLSX is simple, compact and laconic. You don’t need to learn XLSX file format to operate with it. Just use only cell addresses in any formats (A1 and R1C1 are supported) and data from your website database. As simple as that.

Based on:
Simple XLSX
@author kirik mail@kirik.ws
@version 0.1
Developed under GNU General Public License, version 3: http://www.gnu.org/licenses/lgpl.txt

Package: eiseXLSX https://github.com/easyise/eiseXLSX
Copyright: (c) 2012-2017 Ilya S. Eliseev
License: GNU Public License http://opensource.org/licenses/gpl-license.php
Version: 1.9beta
Author: Ilya Eliseev ie@e-ise.com, Continutors: Igor Zhuravlev, Dmitry Zakharov
Uses: SimpleXML, DOM

class eiseXLSX

The class that creates objects with Excel workbooks inside. Public class methods are allowing to make any declared manupulations with the workbook.

public function __construct( $templatePath=’’ )

The constructor. It reads Excel workbook supplied as the template or data source to read. It can be either XLSX file or unzipped one, into the directory speicfied in $templatePath parameter. Unzipping XLSX files will reduce your server CPU load ;).

Also it parses all XMLs inside the workbook and makes all the necessary preparations for future data read and manipulations.

If $templatePath parameter is omitted it reads default template. If path’s not found it throws an exception (object of eiseXLSX_Exception class).

Category: Read / Write

Parameters:

$templatePath (string) - The path to Excel workbook file or directory.

public function data($cellAddress, $data = null, $t = “s”)

eiseXLSX::data() function reads or sets data for cell with specified $cellAddress. If parameter $data is omitted, function just returns current cell data. If $data contains something, function tries to set it. Data types note:

strings are to be returned and assigned as strings
numeric values are to be returned and set as strings with numeric values in latin1 locale inside.
date/time values are to be returned and set as strings formatted as ‘YYYY-MM-DD HH:MM:SS’

Category: Sheet manipulations

Parameters:

$cellAddress (string) - both R1C1 and A1 address formats are acceptable. Case-insensitive. Examples: “AI75”, “r10c25”.
$data (mixed) - data to set. If not set at function call, function just returns data. If set, function sets this data for given cell.
$t (string) - if omitted eiseXLSX accepts the data as string and put contents to sharedStrings.xml. Otherwise it tries to re-format date as seconds or number as real one with period as decimal separator. Possible values: ‘n’ - for numeric values like integer or real numbers; ‘s’ (default) - for strings, but if string can be evaluated as number using is_numeric() PHP function, numeric value will be set; ‘d’ - for datetime values.

Returns: string - cell data before new value is set (if any).

public function getDataValidationList($cellAddress)

This function returns contents of drop-down list for given cell, if Data Validation / List option is activated for given cell. If there’s no list, this function returns NULL, if reference to drop-down list cell range is broken, it returns FALSE. Otherwise it returns associative array with origin cell addresses as keys and cell data as values. Function eiseXLSX::getDataByRange() (that uses eiseXLSX::data()) is used to obtain cell data. eiseXLSX::getDataValidatioList() can be useful when you need to obtain contents of reference tables of spreadsheet fields when you try to import the workbook into the database. NOTE: This function supports only local cell range references, within current workbook. If requested cell takes drop-down list values from another workbook, function returns FALSE. NOTE: Function supports only single-row or single-columns references to drop-down cell range. Otherwise it returns FALSE.

Category: Sheet manipulations

Parameters:

$cellAddress (string) - Cell address. Both R1C1 and A1 address formats are acceptable. Case-insensitive. Examples: “AI75”, “r10c25”.

Returns: mixed - NULL if there’s no data validation, associative array of drop-down values with origin cell addresses as keys and FALSE in case of broken/invalid reference to drop-down cell range.

public function getDataByRange($range)

This function returns an array of data obtained from the specified $range. This range can be as well as formula-formatted (e.g. “Sheet 2!$A1:$B12”) as normal particular range (like “B15:B50”). Cell list, range list and other range formats are NOT SUPPORTED (YET). Reference sheets (if any) should exist in the same workbook as current sheet. Empty values are not returned. If range cannot be located, function returns FALSE.

Category: Sheet manipulations

Parameters:

$range (string) - cell range in normal format (like “A14:X14”) or formula-based refrence (“Sheet 3!$Z15:$Y17”).

Returns: array of data obtained from range with R1C1 address as keys and values as they’ve been obtained with data() function. If range cannot be located, function returns FALSE.

public function getRowCount()

This method returns number of rows in active sheet.

Category: Sheet manipulations

Returns: int - row number of the last row.

public function fill($cellAddress, $fillColor)

Fills cell at $cellAddress with color $fillColor or clears cell off any background color, if $fillColor is set to NULL, 0 or ‘’. If cell is not found or color string is wrongly specified, it throws an exception.

Category: Cell decoration

Parameters:

$cellAddress (string) - Cell address, both A1 and R1C1 address formats are acceptable.
$fillColor (string) - HTML-style color in Hex pairs, for example: #FFCC66. Should always start with hash.

Returns: simpleXML object that represents specified cell.

public function getFillColor($cellAddress)

This function returns fill color of cell located at $cellAddress. Color is returned as W3C hexadecimal value that starts with hash symbol. If cell is not found it throws an exception.

WARNING: in current version this function doesn’t take into account alfa channel information stored in first ‘two bytes’ of OpenXML color information string. It presumes that there’s always ‘FF’ mask in alha channel (no transparency).

Category: Cell decoration

Parameters:

$cellAddress (string) - Cell address, both A1 and R1C1 address formats are acceptable.

Returns: string Color in W3C format.

public function cloneRow($ySrc, $yDest)

eiseXLSX::cloneRow() method clones row with number $ySrc to $yDest, other existing cells are moved down by one position. All row contents and styles are simply copied from origin. It returns simpleXML object with cloned row. WARNING: In version 1.6 this method is not friendly to PrintAreas, it they exist and include cells below cloned one. You’ll probalby receive ‘Currupted file’ message from Excel. WARNING: This function wasn’t tested for cloning rows from down to up: it’s recommended to use it only if $ySrc < $yDest, i.e. when your origin row is upper than destination.

Category: Sheet manipulations

Parameters:

$ySrc (int) - row number of origin. Starts from 1, as user can see on Excel screen
$yDest (int) - destination row number.

Returns: object simpleXML object with newly added row