PHPExcel API in PHPExcel 7.3

Same name and namespace in other branches

1. 8.3 phpexcel.api.php \phpexcel_api

PHPExcel (the library) is a powerful PHP library to export and import data to and from Excel file. It is very flexible, and well built. The PHPExcel Drupal module, on the other hand, provides a "wrapper", a simpler API that behaves in a "Drupal" way. This module simplifies the export or import of data, abstracting much of the complexity, at the cost of flexibility.

Exporting data

The idea is to provide an API very similar to Drupal's theme_table() function.

Using the module's functions requires the phpexcel.inc file to be loaded:

module_load_include('inc', 'phpexcel');

Exporting data is done via phpexcel_export().

phpexcel_export(array(
  'Header 1',
  'Header 2',
), array(
  array(
    'A1',
    'B1',
  ),
  array(
    'A2',
    'B2',
  ),
), 'path/to/file.xls');

It is also possible to pass an array of options to the export function:

phpexcel_export(array(
  'Header 1',
  'Header 2',
), array(
  array(
    'A1',
    'B1',
  ),
  array(
    'A2',
    'B2',
  ),
), 'path/to/file.xls', array(
  'description' => "Some description",
));

If the target file already exists and the option "append" is not explicitly set to FALSE, data will be appended to it, instead of overwriting its content. It is also possible to use an existing file as a template. This is done by specifying the "template" option:

phpexcel_export(array(
  'Header 1',
  'Header 2',
), array(
  array(
    'A1',
    'B1',
  ),
  array(
    'A2',
    'B2',
  ),
), 'path/to/file.xls', array(
  'template' => 'path/to/template.xls',
));

If you don't want that the data to be appended even if the target file already exists, you can set option "append" to FALSE:

phpexcel_export(array(
  'Header 1',
  'Header 2',
), array(
  array(
    'A1',
    'B1',
  ),
  array(
    'A2',
    'B2',
  ),
), 'path/to/file.xls', array(
  'append' => FALSE,
));

It is possible to export data to multiple worksheets. In that case, the headers array becomes a 2-dimensional array, and the data takes 3 dimensions. The 1st dimension represents the Worksheets. They can be keyed by name, or simply numerically. The headers array determines the worksheet names, unless the "ignore_headers", in which case worksheet names are determined by the data array.

phpexcel_export(array(
  'Worksheet 1' => array(
    'Header 1',
    'Header 2',
  ),
), array(
  array(
    array(
      'A1',
      'B1',
    ),
    array(
      'A2',
      'B2',
    ),
  ),
), 'path/to/file.xls');

Or, if ignoring headers:

phpexcel_export(NULL, array(
  'Worksheet 1' => array(
    array(
      'A1',
      'B1',
    ),
    array(
      'A2',
      'B2',
    ),
  ),
), 'path/to/file.xls', array(
  'ignore_headers' => TRUE,
));

phpexcel_export() accepts the following options, which must be given in array format as the 4th parameter:

• ignore_headers: a boolean indicating whether the headers array should be used, or simply ignored. If ignored, worksheet names will be computed based on the data parameter.
• merge_cells: an array with sheets and cell ranges that need to be merged in the end result. For example:

array(
  'merge_cells' => array(
    'Worksheet 1' => array(
      'A1:C1',
      'D1:G1',
    ),
  ),
);

Notice that for merging cells, contrary to the $header and $data parameters, you MUST give at least 1 worksheet, be it an index or a valid worksheet name.

• template: a path to an existing file, to be used as a template.
• format: The EXCEL format. Can be either 'xls', 'xlsx', 'csv', or 'ods'. By default, the extension of the file given as the target path will be used (e.g., 'path/to/file.csv' means a format of 'csv'). If the file has no extension, or an extension that is not supported, it will fallback to 'xls'.
• creator: (metadata) The name of the creator of the file.
• title: (metadata) The title of the file.
• subject: (metadata) The subject of the file.
• description: (metadata) The description of the file.

Any other options will simply be ignored, but can be useful for modules that implement hook_phpexcel_export().

phpexcel_export() will always return an integer. This integer can be one of the following constants:

• PHPEXCEL_SUCCESS: Export was successful.
• PHPEXCEL_ERROR_NO_HEADERS: Error. Returned when no headers are passed, and the "ignore_headers" option is different than true.
• PHPEXCEL_ERROR_NO_DATA: Error. Returned when there is no data to export.
• PHPEXCEL_ERROR_PATH_NOT_WRITABLE: Error. Returned when the path is not writable.
• PHPEXCEL_ERROR_LIBRARY_NOT_FOUND: Error. Returned when the library could not be loaded. Usually this means the library is not in the correct location, or not extracted correctly. Remember that the changelog.txt file that comes with the source code must be present in the library directory.
• PHPEXCEL_ERROR_FILE_NOT_WRITTEN: Error. Even though the path is writable, something prevented PHPExcel from actually saving the file.
• PHPEXCEL_CACHING_METHOD_UNAVAILABLE: Error. This is a configuration error, and happens when the site administrator uses an unavailable caching method, like Memcached, when there's no Memcached server running.

Importing data

Using the module's functions requires the phpexcel.inc file to be loaded:

module_load_include('inc', 'phpexcel');

Importing data is done via phpexcel_import().

$data = phpexcel_import('path/to/file.xls');

This will return the cell data in array format. The array structure is as follows:

array(
  0 => array(
    0 => array(
      'Header 1' => 'A1',
      'Header 2' => 'B1',
    ),
    1 => array(
      'Header 1' => 'A2',
      'Header 2' => 'B2',
    ),
  ),
);

The 1st dimension is the worksheet(s). The 2nd is the rows. Each row is keyed by the table header by default.

It is possible to export the headers as a row of data, and not key the following rows by these header names by passing FALSE as the second parameter:

$data = phpexcel_import('path/to/file.xls', FALSE);

This will return the following format:

array(
  0 => array(
    0 => array(
      'Header 1',
      'Header 2',
    ),
    1 => array(
      'A1',
      'B1',
    ),
    2 => array(
      'A2',
      'B2',
    ),
  ),
);

It is also possible to use the worksheet names as keys for the worksheet data. This is done by passing TRUE as the third parameter.

$data = phpexcel_import('path/to/file.xls', TRUE, TRUE);

This will return the following format:

array(
  'Worksheet 1' => array(
    0 => array(
      'Header 1' => 'A1',
      'Header 2' => 'B1',
    ),
    1 => array(
      'Header 1' => 'A2',
      'Header 2' => 'B2',
    ),
  ),
);

It is possible to specify method calls to the PHPExcel reader before processing the file data. This is done via the fourth parameter, which is an array, keyed by method name, and whose value is the parameters. For instance, if you only want to load specific worksheets to save memory:

$data = phpexcel_import('path/to/file.xls', TRUE, TRUE, array(
  'setLoadSheetsOnly' => array(
    'My sheet',
  ),
));

The specified methods must exist, and are called on an instance of PHPExcel_Reader_IReader.

The returned data is either an array (meaning the import was successful) or an integer. The integer can be one of the following:

• PHPEXCEL_ERROR_FILE_NOT_READABLE: Error. The file was not found or isn't readable.
• PHPEXCEL_ERROR_LIBRARY_NOT_FOUND: Error. Returned when the library could not be loaded. Usually this means the library is not in the correct location, or not extracted correctly. Remember that the changelog.txt file that comes with the source code must be present in the library directory.

File

./phpexcel.api.php, line 8

Module API documentation.

Functions