Home

eiseXLSX reference

This class was designed for server-side manipulations with preliminary uploaded spreadsheets in Microsoft® Excel™ 2007-2011-2013 file format – OpenXML SpereadsheetML. As of Nov 2013 this library allows to perform the following operations:

• to read a file contents
• to write a data: o change existing cell data o clone rows and fill-in new rows with data o clone sheets within workbook o colorization of cells.

This library offers an easiest way to make Excel™-based EDI with web-based information system, for data input and output:

• 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. Functional description As you may know, file format of Microsoft® Office™ Excel™ became open since 2007 and it is based on standard OpenXML SpreadsheetML. Briefly speaking, file with .xlsx extension is just a zip archive with set of XML files. Directory structure of this file and XML files format are substitute of this standard.

eiseXLSX works in the following way:

1. class constructor reads uploaded file, preliminary uploaded template file or a directory with unzipped content
2. when you manipulate data it changes corresponding XML nodes in memory using SimpleXML and DOM
3. to save changed template you need to call Output() method – it will send data to the script output or save it as a file on a server.

Requirements

PHP5.2+ with zlib, SimpleXML and DOM.

methods

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

This function reads or sets data on specified cell. Parameters are:

• $cellAddress – cell address in format notation A1 or R1C1;
• $data – if set, this function sets this data to specified cell;
• $t – stands for type of data to set: o s – strings o all other types are ignored. This function returns cell value right before this method was called. Some notes about data type conversion:
• Strings are set and retrieved as is. Multilingual contents should be encoded as UTF-8 without BOM. Rich text is possible, according to spreadsheetML specification.
• Numbers are set as is. It can be integer, double or string with period (“.”) as decimal separator.
• Date/time values are to be set as strings formatted according to locale you need. Current version of eiseXLSX doesn’t support date formatting on write and values of this kind are stored as strings. Values of date/time type are retrieved as ‘Y-m-d H:i:s’ (e.g. 2014-03-01 22:15:45).

public function cloneRow($ySrc, $yDest)

This function clones row from position at $ySrc to the position specified in $yDest These position indexes are actual row numbers as you can see in Excel.

public function getRowCount()

This method returns row count of currently selected sheet of workbook.

public function findSheetByName($name)

This function loops through sheets to find the sheet with external name (specified on its tab) passed by parameter $name. If the sheet is found, it returns its sheetId (internal identifier). Otherwise, it returns null. Located sheetId can be passed as the parameter to method selectSheet() for further data manipulation.

public function selectSheet($id)

This method switches active sheet to the one with sheetId specified in $id parameter. If sheet with supplied sheetId cannot be found in this workbook, this function thrown an exception. If you need to obtain sheetId by sheet name, use findSheetByName($name) method. public function cloneSheet($originSheetId, $newSheetName = '') This method makes exact clone of sheet with sheetId of $originSheetId with new name specified in $newSheetName parameter. WARNING! This method prevents link between cell data and shared strings XML file. So any change in text fields in new sheet may reflect data in the origin!

public function renameSheet($sheetId, $newName)

This function changes the name of sheet with $sheetId to $newName. public function removeSheet($id) This function removes sheet with sheetId supplied by $id parameter.

public function fill($cellAddress, $fillColor)

This function sets cell background color as specified in $fillColor parameter. Parameters are:

• $cellAddress – address of the cell to be painted in A1 or R1C1 format
• $fillColor – color specified as string in W3C format: #RRGGBB (hex pairs for Red, Greeen and Blue channels). This function returns SimpleXMLElement object that corresponds to specified cell.

public function getFillColor($cellAddress)

This function retrieves current paint color of cell specified in $cellAddress parameter in W3C format #RRGGBB (hex pairs for Red, Green and Blue channels). WARNING: Alpha channel that might be used by Excel in some cases is not supported in current version of eiseXLSX.

public function Output($fileName = "", $dest = "D")

This method dumps in-memory XMLs to file or send it to user. It collects all data SimpleXML objects into zip archive and then sends this archive to user (if specified). It works similar to Ouput() method of FPDF class widely used by developer for output document creation. Parameters: $fileName – string with the file name. $dest – string that can have any of the following values:

• “I” (to be discontinued as no longer supported way to work with Excel sheets) inline – workbook is sent to user with ‘Content-disposition: inline’ header. It is deprecated as it is no longer effective with all the modern browsers and platforms;
• “D” – workbook is to be sent to user with ‘Content-disposition: attachment;filename=’ where is basename of $fileName parameter.
• “F” – workbook will saved on the server as the file named as specified in $fileName parameter.

Error handling

In case of any error XLSX methods are throwing an eiseXLSX_Exception objects.

To Do list

1. Ability to save date/time information in internal XLSX format.
2. Alpha channel data should be applied accordingly when getFillColor() method is used.
3. Excel print area specified in the template may crash all workbook if cloneRow() method is used. Print area range should be adjusted in case of cloneRow() is called.