PHPExcel cookbook in PHPExcel 8.3
Same name and namespace in other branches
- 7.3 phpexcel.api.php \phpexcel_cookbook
This section provides several real-life examples of using the PHPExcel module with your own modules and sites. For the basics, see the PHPExcel API topic.
Table of contents
- Download the exported result Download the exported result
- Export data in a batch Export data in a batch
- Import data in a batch Import data in a batch
- TODO: adding styles to a cell
Download the exported result
It is possible that an exported result must be downloaded straight away, after which the exported file should be deleted. One way to achieve this is using Drupal's "managed files". We can register files with Drupal, and tell it to mark them as temporary. These temporary files will be garbage collected at regular intervals, on cron runs. The following example shows the code that exports the data to an Excel file, and then registers it with Drupal as a temporary file, and triggers the file transfer. Notice that using the built-in file_download_headers() function requires a hook_file_download() implementation.
This code could be the content of a page callback, for example:
module_load_include('inc', 'phpexcel');
// Prepare the file path. The PHPExcel library doesn't handle PHP stream
// wrappers, so we need the real path.
$wrapper = file_stream_wrapper_get_instance_by_uri('temporary://');
// Generate a file name. If it's unique, it's less likely to conflict with an
// existing file. You could also put up some more checks on this, if it's likely
// to conflict (like, when you have many export/download requests).
$filename = 'mymodule--download-' . uniqid() . '.xls';
$filepath = $wrapper
->realpath() . '/' . $filename;
// Export, and store to file.
$result = phpexcel_export(array(
'Header 1',
'Header 2',
), array(
array(
'A1',
'B1',
),
array(
'A2',
'B2',
),
), $filepath);
if ($result === PHPEXCEL_SUCCESS) {
// Exported successfully. Let's register the file with Drupal. We simply
// tell Drupal to copy the file over the existing one, by passing in
// temporary://$filename.
$file = file_save_data(file_get_contents($filepath), "temporary://{$filename}", FILE_EXISTS_REPLACE);
// By default, the file is stored as a permanent file. Let's make it
// temporary, so Drupal will remove it (in 6 hours, if your cron is set up
// correctly).
$file->status = 0;
file_save($file);
// Start downloading. This requires a hook_file_download() implementation!
$headers = file_download_headers($file->uri);
file_transfer($file->uri, $headers);
}
else {
// Error.
}To complement this, we need a hook_file_download() implementation. Add this to your module's .module file:
function mymodule_file_download($uri) {
if (preg_match('/mymodule--download-(.+?)\\.xls$/', $uri)) {
return array(
'Content-type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'Content-Disposition' => 'attachment; filename="export.xls"',
);
}
}Export data in a batch
It is possible to append data to existing files, allowing modules to export Excel files in a batch operation. This requires the preparation of an Excel file before data starts to be exported. In the following example, the file can be downloaded at the end via a status message and a link. For this to work, we need a hook_file_download() implementation. This is not required, however, if you simply need to treat the file in a different way after the export.
First, provide the batch operation and finish callbacks:
function mymodule_batch_process(&$context) {
module_load_include('inc', 'phpexcel');
if (!isset($context['sandbox']['progress'])) {
// Store the file in the temporary directory.
$wrapper = file_stream_wrapper_get_instance_by_uri('temporary://');
$context['sandbox']['filename'] = 'mymodule--download-' . uniqid() . '.xls';
$context['sandbox']['file'] = $wrapper
->realpath() . '/' . $context['sandbox']['filename'];
// Prepare the Excel file.
$result = phpexcel_export(array(
'Header 1',
'Header 2',
), array(
// Provide some empty data. We will append data later on.
array(),
), $context['sandbox']['file']);
if ($result !== PHPEXCEL_SUCCESS) {
drupal_set_message("Something went wrong", 'error');
$context['sandbox']['finished'] = 1;
$context['success'] = FALSE;
return;
}
$context['sandbox']['progress'] = 0;
$context['sandbox']['max'] = 40;
// Trick to pass the filepath to the finished callback.
$context['results'] = "temporary://{$context['sandbox']['filename']}";
}
$limit = 10;
while ($limit) {
$result = phpexcel_export(array(
'Header 1',
'Header 2',
), array(
// Append some data to the file.
array(
'Some value',
'Some other value',
),
), $context['sandbox']['file'], array(
// Use our previously prepared file as a "template", which means we
// will append data to it, instead of starting from scratch again.
'template' => $context['sandbox']['file'],
));
if ($result !== PHPEXCEL_SUCCESS) {
drupal_set_message(t("Something went wrong on pass !pass", array(
'!pass' => $context['sandbox']['progress'],
)), 'error');
$context['sandbox']['finished'] = 1;
$context['success'] = FALSE;
return;
}
$context['sandbox']['progress']++;
$limit--;
}
if ($context['sandbox']['progress'] != $context['sandbox']['max']) {
$context['finished'] = $context['sandbox']['progress'] / $context['sandbox']['max'];
}
}
function mymodule_batch_finished($success, $results, $operations) {
if ($success) {
$wrapper = file_stream_wrapper_get_instance_by_uri($results);
drupal_set_message(t("Download it here: !link", array(
'!link' => l($results, $wrapper
->getExternalUrl()),
)), 'status', FALSE);
}
}Now, we can set a batch operation like so:
batch_set(array(
'operations' => array(
array(
'mymodule_batch_process',
array(),
),
),
'finished' => 'mymodule_batch_finished',
));Again, for this example to work, we need a hook_file_download() implementation:
function mymodule_file_download($uri) {
if (preg_match('/mymodule--download-(.+?)\\.xls$/', $uri)) {
return array(
'Content-type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'Content-Disposition' => 'attachment; filename="export.xls"',
);
}
}Import data in a batch
When dealing with very large PHPExcel files, we sometimes want to import data in a batch instead of everything at once in order to save memory. We can achieve this using a custom PHPExcel_Reader_IReadFilter class. A filter can be used by PHPExcel to only read certain rows, which saves memory.
First, provide the batch operation and finish callbacks:
function mymodule_batch_process($filepath, &$context) {
module_load_include('inc', 'phpexcel');
if (!isset($context['sandbox']['progress'])) {
$context['sandbox']['progress'] = 0;
// We have no idea how many lines we have to load. Provide some large
// number, and we'll adapt as we go along.
$context['sandbox']['max'] = 10000;
}
// We need to load the library before we can instantiate our
// ChunkReaderFilter class.
$library = libraries_load('PHPExcel');
if (empty($library['loaded'])) {
drupal_set_message(t("Couldn't load the PHPExcel library."), 'error');
$context['sandbox']['finished'] = 1;
$context['success'] = FALSE;
return;
}
$limit = 10;
// See our module's info file below.
$chunk_filter = new ChunkReadFilter();
$chunk_filter
->setRows($context['sandbox']['progress'], $limit);
$data = phpexcel_import($filepath, TRUE, FALSE, array(
'setReadFilter' => array(
$chunk_filter,
),
));
if (!is_array($data)) {
drupal_set_message(t("Something went wrong on pass !pass", array(
'!pass' => $context['sandbox']['progress'],
)), 'error');
$context['sandbox']['finished'] = 1;
$context['success'] = FALSE;
return;
}
// Get rid of the worksheet.
$data = $data[0];
$i = 0;
while ($i < $limit) {
if (!empty($data[$i])) {
// Do something with the data, like creating a node...
$node = (object) array(
'type' => 'page',
'title' => $data[$i]['Header 1'],
);
node_save($node);
$context['results'][] = $node;
$context['sandbox']['progress']++;
$i++;
}
else {
// We have reached the end of our file. Finish now.
$context['sandbox']['finished'] = 1;
$context['success'] = TRUE;
return;
}
}
if ($context['sandbox']['progress'] != $context['sandbox']['max']) {
$context['finished'] = $context['sandbox']['progress'] / $context['sandbox']['max'];
}
}
function mymodule_batch_finished($success, $results, $operations) {
if ($success) {
// Here we do something meaningful with the results.
$message = t("!count items were processed.", array(
'!count' => count($results),
));
$message .= theme('item_list', array(
'items' => array_map(function ($node) {
return l($node->title, "node/{$node->nid}");
}, $results),
));
drupal_set_message($message);
}
}In our batch, we use a custom class called ChunkReadFilter. We must define it in its own file:
// Put this in a separate file, like src/ChunkReadFilter.php.
class ChunkReadFilter implements PHPExcel_Reader_IReadFilter {
protected $start = 0;
protected $end = 0;
public function setRows($start, $chunk_size) {
$this->start = $start;
$this->end = $start + $chunk_size;
}
public function readCell($column, $row, $worksheetName = '') {
// Only read the heading row, and the rows that are between
// $this->start and $this->end.
if ($row == 1 || $row >= $this->start && $row < $this->end) {
return TRUE;
}
return FALSE;
}
}We need to register this file with Drupal's autoloader, which is done by adding a files[] directive to the module's .info file:
; mymodule.info
name = My module
core = 7.x
files[] = src/ChunkReadFilter.php
Now, we can set a batch operation like so:
batch_set(array(
'operations' => array(
array(
'mymodule_batch_process',
array(
'/path/to/file.xls',
),
),
),
'finished' => 'mymodule_batch_finished',
));This will import all the rows from the /path/to/file.xls Excel file, and create a node for each row.
See also
file_download_headers()
file_transfer()
File
- ./
phpexcel.api.php, line 279 - Module API documentation.