PHPExcel

The PHPExcel module allows developers to use the PHPExcel PHP library to export and import data.

Installation

For this module to work, you must download the PHPExcel library (version ~1.7)

Move the library code to your sites/*/libraries folder, so that you have sites/*/libraries/PHPExcel/Classes/PHPExcel.php.

IMPORTANT: more up-to-date documentation

The module ships with a pretty comprehensive documentation, parsable by the api module. You can find the API documentation here. This will give a more in-depth overview of the inner workings of the module, as well as practical examples to the different parameters.

The module also ships with a cookbook, containing several real-life examples. These can be found here.

Usage

The following gives you a brief overview of what the module can do. For a far more in depth guide to this module, however, please refer to the module's phpexcel.api.php file.

The module lacks a UI or built-in functionality, except for a form for configuring performance settings. What it provides is a simple API for exporting and importing data in Excel format (xls and xlsx). You must include the phpexcel.inc file in your code and use the phpexcel_import() and phpexcel_export() functions.

This module is only meant for data. To format your cells (bold, italic, colors, borders, etc), use the phpexcel hooks to control each step of the export or import.

Configuration

PHPExcel (the library) can use different caching mechanisms to limit memory usage. PHPExcel (the module) provides a settings page at admin/config/system/phpexcel. Because this is relevant to the installation, and not the module that calls the import/export functions, it is not possible to specify these caching options through the function calls; they have to be configured through this UI,

Options

The phpexcel_export() function can take an optional 4th parameter. This is an array which can contain options for the export.

The possible options are:

• template: a path to a template Excel file. If omitted, the exporter will create a new, blank Excel file
• ignore_headers: will ignore the headers. See below for more information about the headers
• format: the format of the resulting Excel file. Can either be 'xls' or 'xlsx'. Defaults to 'xls'.
• creator: the "creator" metadata
• title: the "title" metadata
• subject: the "subject" metadata
• description: the "description" metadata
• all other keys will be ignored

The $options array is passed as-is throughout the hook calls. This allows developers to set any necessary information in this array and reuse it as they see fit.

Return value

The return value corresponds to one of the following constants:

• PHPEXCEL_ERROR_NO_HEADERS (export)
• PHPEXCEL_ERROR_NO_DATA (export)
• PHPEXCEL_ERROR_PATH_NOT_WRITABLE (export)
• PHPEXCEL_ERROR_LIBRARY_NOT_FOUND (export and import)
• PHPEXCEL_ERROR_FILE_NOT_WRITTEN (export)
• PHPEXCEL_ERROR_FILE_NOT_READABLE (import)
• PHPEXCEL_SUCCESS (export)

In case of the import function, on success the data will be returned, instead of one of the above constants.

Examples

Export

This will simply export the site's nodes to an Excel file. It will contain two worksheets: one with the nodes (nid, type and status) and one with the latest revisions (nid, vid, title and body). Note that keying the $headers array with strings (e.g. $headers['Nodes'], $headers['Revisions'] - see below) will make phpexcel name the worksheets accordingly instead of Worksheet 1, Worksheet 2, etc.

function mymodule_export_stuff() {
  module_load_include('inc', 'phpexcel');
  
  $data = array();
  $headers = array();
  
  // First worksheet
  
  // Get the nodes
  $result = db_select('node', 'n')
                 ->fields('n', array('nid', 'type', 'status'))
                 ->execute();
  
  while($row = $result->fetchAssoc()) {
    if (!count($headers)) {
      // Add the headers for the first worksheet
      $headers['Nodes'] = array_keys($row);
    }
    
    // Add the data
    $data['Nodes'][] = array_values($row);
  }
  
  // Second worksheet
  
  // Get the latest revisions
  $query = db_select('node_revision', 'v');
  $query->leftJoin('node', 'n', 'n.vid = v.vid');
  $result = $query->fields('v', array('nid', 'vid', 'title'))
                 ->execute();

  while($row = $result->fetchAssoc()) {
    if (count($headers) == 1) {
      // Add the headers for the second worksheet
      $headers['Revisions'] = array_keys($row);
    }
    
    // Add the data
    $data['Revisions'][] = array_values($row);
  }
  
  // Store the file in sites/default/files
  $dir = file_stream_wrapper_get_instance_by_uri('public://')->realpath();
  $filename = 'export.xls';
  $path = "$dir/$filename";

  // Use the .xls format
  $options = array('format' => 'xls');
  
  $result = phpexcel_export($headers, $data, $path, $options);
  if ($result == PHPEXCEL_SUCCESS) {
    drupal_set_message(t("We did it !"));
  }
  else {
    drupal_set_message(t("Oops ! An error occured !"), 'error');
  } 
}

Formatting

The PHPExcel library has many options available for formatting cell content. Please refer to the hook documentation for more information, under "Set cell styles".

Import

It is also possible to parse an Excel file and return its content as a multidimensional array.

function mymodule_import_stuff() {
  module_load_include('inc', 'phpexcel');
  
  // The path to the excel file
  $path = 'sites/default/files/export.xls';
  
  $result = phpexcel_import($path);
  
  if (is_array($result)) {
    drupal_set_message(t("We did it !"));
  }
  else {
    drupal_set_message(t("Oops ! An error occured !"), 'error');
  }
}

The phpexcel_import() function can take three additional, optional parameters.

The second defaults to TRUE. If TRUE, the first row (headers) will be ignored and each row's cells will be keyed with the header value. If FALSE, the entire data array will be non-associative, and the first row (headers) will be present as well.

The third defaults to FALSE. If TRUE, the first level of the data keys will be the Worksheet names. If FALSE, the keys will be numerical.

The fourth is an array of methods to call on the PHPExcel reader. See phpexcel.api.php for more information.

Debugging

You can check the return values of the import/export functions, which are constants. These will provide information on the status of the process (whether it succeeded or not).