Using value binders to facilitate data entry

Internally, PHPExcel uses a default PHPExcel_Cell_IValueBinder implementation (PHPExcel_Cell_DefaultValueBinder) to determine data types of entered data using a cell’s setValue() method.

Optionally, the default behaviour of PHPExcel can be modified, allowing easier data entry. For example, a PHPExcel_Cell_AdvancedValueBinder class is present. It automatically converts percentages and dates entered as strings to the correct format, also setting the cell’s style information. The following example demonstrates how to set the value binder in PHPExcel:

/** PHPExcel */

require_once 'PHPExcel.php';

/** PHPExcel_Cell_AdvancedValueBinder */

require_once 'PHPExcel/Cell/AdvancedValueBinder.php';

/** PHPExcel_IOFactory */

require_once 'PHPExcel/IOFactory.php';

// Set value binder

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

// Create new PHPExcel object

$objPHPExcel = new PHPExcel();

// ...

// Add some data, resembling some different data types

$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Percentage value:');

$objPHPExcel->getActiveSheet()->setCellValue('B4', '10%');
// Converts to 0.1 and sets percentage cell style

$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Date/time value:');

$objPHPExcel->getActiveSheet()->setCellValue('B5', '21 December 1983');
// Converts to date and sets date format cell style

i Creating your own value binder is easy.
When advanced value binding is required, you can implement the PHPExcel_Cell_IValueBinder interface or extend the PHPExcel_Cell_DefaultValueBinder or PHPExcel_Cell_AdvancedValueBinder classes.

PHPExcel recipes

The following pages offer you some widely-used PHPExcel recipes. Please note that these do NOT offer complete documentation on specific PHPExcel API functions, but just a bump to get you started. If you need specific API functions, please refer to the API documentation.

For example, 4.4.7 Setting a worksheet’s page orientation and size covers setting a page orientation to A4. Other paper formats, like US Letter, are not covered in this document, but in the PHPExcel API documentation.

4.6.1. Setting a spreadsheet’s metadata

PHPExcel allows an easy way to set a spreadsheet’s metadata, using document property accessors. Spreadsheet metadata can be useful for finding a specific document in a file repository or a document management system. For example Microsoft Sharepoint uses document metadata to search for a specific document in its document lists.

Setting spreadsheet metadata is done as follows:

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");

$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");

$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");

$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");

$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");

$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");

$objPHPExcel->getProperties()->setCategory("Test result file");

4.6.2. Setting a spreadsheet’s active sheet

The following line of code sets the active sheet index to the first sheet:

$objPHPExcel->setActiveSheetIndex(0);

Date: 2016-03-03; view: 1497