phpexcel.test in PHPExcel 7.3
Same filename and directory in other branches
Defines the test case for phpexcel
File
tests/phpexcel.testView source
<?php
/**
* @file
* Defines the test case for phpexcel
*/
class PHPExcelTest extends DrupalWebTestCase {
protected $test_files;
protected $directory;
public static function getInfo() {
return array(
'name' => 'PHPExcel',
'description' => 'Test the module API functions',
'group' => 'PHPExcel',
);
}
public function setUp() {
parent::setUp('phpexcel');
$this->directory = variable_get('file_' . file_default_scheme() . '_path', conf_path() . '/files');
// Make sure the directory is writable.
$this
->assertTrue(is_writable($this->directory), t("The !dir directory exists and is writable.", array(
'!dir' => $this->directory,
)));
require_once dirname(__FILE__) . '/../phpexcel.inc';
}
public function tearDown() {
if (!empty($this->test_files)) {
foreach ($this->test_files as $file) {
@unlink($file);
}
}
parent::tearDown();
}
/**
* Test a simple, single worksheet Excel export.
*/
public function testSingleWorksheetExport() {
// Prepare the data.
$headers = array(
'Header 1',
'Header 2',
);
$data = array(
array(
'Data 1.1',
'Data 1.2',
),
array(
'Data 2.1',
'Data 2.2',
),
array(
'Data 3.1',
'Data 3.2',
),
);
// Create a file path.
$correct_path = file_create_filename($this
->getFilename(), $this->directory);
// The filename will be munged by the export function, so:
$this->test_files[] = $actual_path = phpexcel_munge_filename($correct_path);
// Create a wrong path. Should not be able to export.
$wrong_path = 'path/to/nowhere/file.xls';
$this
->assertEqual(PHPEXCEL_ERROR_PATH_NOT_WRITABLE, phpexcel_export($headers, $data, $wrong_path), 'Passed an incorrect path');
$this
->assertEqual(PHPEXCEL_SUCCESS, phpexcel_export($headers, $data, $correct_path), t('Exported data to !path', array(
'!path' => $actual_path,
)));
$this
->assertTrue(filesize($actual_path) > 0, 'Filesize should be bigger than 0');
// Import, not keyed by headers.
$data = phpexcel_import($actual_path, FALSE);
$this
->assertTrue(!!$data, 'Import succeeded');
// Should have 4 rows (3 rows + headers).
$count = !empty($data[0]) ? count($data[0]) : 0;
$this
->assertTrue($count === 4, t('!count rows, expect 4.', array(
'!count' => $count,
)));
// Should only have 2 cells.
$count = !empty($data[0][0]) ? count($data[0][0]) : 0;
$this
->assertTrue($count === 2, t('!count cells, expect 2.', array(
'!count' => $count,
)));
// First row, 1st cell should be 'Header 1'.
$header = !empty($data[0][0][0]) ? $data[0][0][0] : 'no';
$this
->assertTrue($header === 'Header 1', 'First row, 1st cell data should be "Header 1"');
// Second row, 1st cell should be 'Data 1.1'.
$header = !empty($data[0][1][0]) ? $data[0][1][0] : 'no';
$this
->assertTrue($header === 'Data 1.1', 'Second row, 1st cell data should be "Data 1.1"');
}
/**
* A cell with a value of '0' must get exported as such.
*
* @see http://drupal.org/node/1988868
*/
public function testIssue1988868() {
// Prepare the data.
$headers = array(
'Header 1',
'Header 2',
);
$data = array(
array(
'0',
'Data 1.2',
),
array(
'Data 2.1',
'0',
),
array(
'0',
'0',
),
);
// Create a file path.
$correct_path = file_create_filename($this
->getFilename(), $this->directory);
// The filename will be munged by the export function, so:
$this->test_files[] = $actual_path = phpexcel_munge_filename($correct_path);
$this
->assertEqual(PHPEXCEL_SUCCESS, phpexcel_export($headers, $data, $correct_path), t('Exported data to !path', array(
'!path' => $actual_path,
)));
$this
->assertTrue(filesize($actual_path) > 0, 'Filesize should be bigger than 0');
// Import, not keyed by headers.
$data = phpexcel_import($actual_path, FALSE);
$this
->assertTrue(!!$data, 'Import succeeded');
// Second row, 1st cell should be '0'.
$cell = $data[0][1][0];
$this
->assertTrue($cell === '0', 'Second row, 1st cell data should be "0"');
// Second row, 2nd cell should be 'Data 1.2'.
$cell = $data[0][1][1];
$this
->assertTrue($cell === 'Data 1.2', 'Second row, 2nd cell data should be "Data 1.2"');
// Third row, 2nd cell should be '0'.
$cell = $data[0][2][1];
$this
->assertTrue($cell === '0', 'Third row, 2nd cell data should be "0"');
// Fourth row, 1st cell should be '0'.
$cell = $data[0][3][0];
$this
->assertTrue($cell === '0', 'Fourth row, 1st cell data should be "0"');
// Fourth row, 2nd cell should be '0'.
$cell = $data[0][3][1];
$this
->assertTrue($cell === '0', 'Fourth row, 2nd cell data should be "0"');
}
/**
* Empty cells must be imported as empty strings, and not shift adjacent
* cells to the left.
*
* @see http://drupal.org/node/2501147
*/
public function testIssue2501147() {
// Import.
$data = phpexcel_import(dirname(__FILE__) . '/data/phpexcel.test.empty_cells.xlsx', FALSE);
$this
->assertTrue(!!$data, 'Import succeeded');
// Second row, 1st cell should be 'Data 1.1'.
$cell = $data[0][1][0];
$this
->assertTrue($cell === 'Data 1.1', 'Second row, 1st cell data should be "Data 1.1"');
// Second row, 2nd cell should be 'Data 1.2'.
$cell = $data[0][1][1];
$this
->assertTrue($cell === 'Data 1.2', 'Second row, 2nd cell data should be "Data 1.2"');
// Third row, 1st cell should be ''.
$cell = $data[0][2][0];
$this
->assertTrue($cell === '', 'Third row, 2nd cell data should be "" (empty string)');
// Third row, 2nd cell should be 'Data 2.2'.
$cell = $data[0][2][1];
$this
->assertTrue($cell === 'Data 2.2', 'Second row, 2nd cell data should be "Data 2.2"');
// Fourth row, 1st cell should be 'Data 3.1'.
$cell = $data[0][3][0];
$this
->assertTrue($cell === 'Data 3.1', 'Fourth row, 1st cell data should be "Data 3.1"');
// Fourth row, 2nd cell should be ''.
$cell = $data[0][3][1];
$this
->assertTrue($cell === '', 'Fourth row, 2nd cell data should be "" (empty string)');
}
/**
* Test multiple worksheet Excel export.
*/
public function testMultipleWorksheetExport() {
// Prepare data.
$headers = array(
'Sheet 1' => array(
'Header 1.1',
'Header 1.2',
),
'Sheet 2' => array(
'Header 2.1',
'Header 2.2',
),
);
$data = array(
'Sheet 1' => array(
array(
'Data 1.1.1',
'Data 1.1.2',
),
array(
'Data 1.2.1',
'Data 1.2.2',
),
array(
'Data 1.3.1',
'Data 1.3.2',
),
),
'Sheet 2' => array(
array(
'Data 2.1.1',
'Data 2.1.2',
),
array(
'Data 2.2.1',
'Data 2.2.2',
),
array(
'Data 2.3.1',
'Data 2.3.2',
),
),
);
// Create a file path.
$correct_path = file_create_filename($this
->getFilename(), $this->directory);
// The filename will be munged by the export function, so:
$this->test_files[] = $actual_path = phpexcel_munge_filename($correct_path);
$this
->assertEqual(PHPEXCEL_SUCCESS, phpexcel_export($headers, $data, $correct_path), t('Exported data to !path', array(
'!path' => $actual_path,
)));
$this
->assertTrue(filesize($actual_path) > 0, 'Filesize should be bigger than 0');
// Import, keyed by headers.
$data = phpexcel_import($actual_path);
$this
->assertTrue(!!$data, 'Import succeeded');
// Should have 3 rows.
$count = !empty($data[0]) ? count($data[0]) : 0;
$this
->assertTrue($count === 3, t('!count rows, expect 3', array(
'!count' => $count,
)));
// Should only have 2 cells.
$count = !empty($data[0][0]) ? count($data[0][0]) : 0;
$this
->assertTrue($count === 2, t('!count cells, expect 2', array(
'!count' => $count,
)));
// Should be keyed by headers.
$this
->assertTrue(isset($data[0][0]['Header 1.1']), 'Keyed by header ("Header 1.1")');
$this
->assertTrue(isset($data[1][0]['Header 2.2']), 'Keyed by header ("Header 2.2")');
$header = !empty($data[0][0]['Header 1.1']) ? $data[0][0]['Header 1.1'] : 'no';
$this
->assertTrue($header === 'Data 1.1.1', 'Should be "Data 1.1.1"');
$header = !empty($data[1][1]['Header 2.2']) ? $data[1][1]['Header 2.2'] : 'no';
$this
->assertTrue($header === 'Data 2.2.2', 'Should be "Data 2.2.2"');
// Import with worksheet names.
$data = phpexcel_import($actual_path, TRUE, TRUE);
$this
->assertTrue(!!$data, 'Import succeeded');
// Should have 3 rows.
$count = !empty($data['Sheet 1']) ? count($data['Sheet 1']) : 0;
$this
->assertTrue($count === 3, t('!count rows, expect 3', array(
'!count' => $count,
)));
// Should be keyed by Worksheet name.
$this
->assertTrue(isset($data['Sheet 1']), t('Imported with Worksheet names.'));
$this
->assertTrue(isset($data['Sheet 2']), t('Imported with Worksheet names.'));
// Should only have 2 cells.
$count = !empty($data['Sheet 1'][0]) ? count($data['Sheet 1'][0]) : 0;
$this
->assertTrue($count === 2, t('!count cells, expect 2', array(
'!count' => $count,
)));
// Should be keyed by headers.
$this
->assertTrue(isset($data['Sheet 1'][0]['Header 1.1']), 'Keyed by header ("Header 1.1")');
$this
->assertTrue(isset($data['Sheet 2'][0]['Header 2.2']), 'Keyed by header ("Header 2.2")');
$header = !empty($data['Sheet 1'][0]['Header 1.1']) ? $data['Sheet 1'][0]['Header 1.1'] : 'no';
$this
->assertTrue($header === 'Data 1.1.1', 'Should be "Data 1.1.1"');
$header = !empty($data['Sheet 2'][1]['Header 2.2']) ? $data['Sheet 2'][1]['Header 2.2'] : 'no';
$this
->assertTrue($header === 'Data 2.2.2', 'Should be "Data 2.2.2"');
}
/**
* Test "ignore_headers" option.
*/
public function testIgnoreHeaders() {
// Prepare data.
$data = array(
0 => array(
array(
'Data 1.1.1',
'Data 1.1.2',
),
array(
'Data 1.2.1',
'Data 1.2.2',
),
array(
'Data 1.3.1',
'Data 1.3.2',
),
),
'Sheet 2' => array(
array(
'Data 2.1.1',
'Data 2.1.2',
),
array(
'Data 2.2.1',
'Data 2.2.2',
),
array(
'Data 2.3.1',
'Data 2.3.2',
),
),
);
// Create a file path.
$correct_path = file_create_filename($this
->getFilename(), $this->directory);
// The filename will be munged by the export function, so:
$this->test_files[] = $actual_path = phpexcel_munge_filename($correct_path);
// Should pass
$this
->assertEqual(PHPEXCEL_SUCCESS, phpexcel_export(NULL, $data, $correct_path, array(
'ignore_headers' => TRUE,
)), t('Exported data to !path', array(
'!path' => $actual_path,
)));
$this
->assertTrue(filesize($actual_path) > 0, 'Filesize should be bigger than 0');
// Import, not keyed by headers.
$data = phpexcel_import($actual_path, FALSE);
// Debug data.
$this
->verbose(print_r($data, 1));
$this
->assertTrue(!!$data, 'Import succeeded');
// Should have 3 rows.
$count = !empty($data[0]) ? count($data[0]) : 0;
$this
->assertTrue($count === 3, t('!count rows, expect 3', array(
'!count' => $count,
)));
// Should only have 2 cells.
$count = !empty($data[0][0]) ? count($data[0][0]) : 0;
$this
->assertTrue($count === 2, t('!count cells, expect 2', array(
'!count' => $count,
)));
$header = !empty($data[0][0][0]) ? $data[0][0][0] : 'no';
$this
->assertTrue($header === 'Data 1.1.1', t('!data, Should be "Data 1.1.1"', array(
'!data' => $header,
)));
$header = !empty($data[1][1][1]) ? $data[1][1][1] : 'no';
$this
->assertTrue($header === 'Data 2.2.2', t('Should be "Data 2.2.2"', array(
'!data' => $header,
)));
}
/**
* Test a simple, single worksheet Excel export.
*/
public function testTemplateExport() {
// Prepare the data.
$data = array(
array(
'Data 1.1',
'Data 1.2',
),
array(
'Data 2.1',
'Data 2.2',
),
array(
'Data 3.1',
'Data 3.2',
),
);
// Options.
$options = array(
'format' => 'xlsx',
'template' => drupal_get_path('module', 'phpexcel') . '/tests/data/phpexcel.test.template.xlsx',
'ignore_headers' => TRUE,
);
// Create a file path.
$correct_path = file_create_filename($this
->getFilename('xlsx'), $this->directory);
// The filename will be munged by the export function, so:
$this->test_files[] = $actual_path = phpexcel_munge_filename($correct_path);
$this
->assertEqual(PHPEXCEL_SUCCESS, phpexcel_export(array(), $data, $correct_path, $options), t('Exported data to !path', array(
'!path' => $actual_path,
)));
$this
->assertTrue(filesize($actual_path) > 0, 'Filesize should be bigger than 0');
// Import and check.
$data = phpexcel_import($actual_path, FALSE);
// Debug data.
$this
->verbose(print_r($data, 1));
$this
->assertTrue(!!$data, 'Import succeeded');
// Should have 3 rows (3 rows and no headers).
$count = !empty($data[0]) ? count($data[0]) : 0;
$this
->assertTrue($count === 3, t('!count rows, expect 3', array(
'!count' => $count,
)));
// First row, 1st cell should be 'Data 1.1'.
$header = !empty($data[0][0][0]) ? $data[0][0][0] : 'no';
$this
->assertTrue($header === 'Data 1.1', 'First row, 1st cell data should be "Data 1.1", and is ' . $header);
// Second row, 1st cell should be 'Data 2.1'.
$header = !empty($data[0][1][0]) ? $data[0][1][0] : 'no';
$this
->assertTrue($header === 'Data 2.1', 'Second row, 1st cell data should be "Data 2.1", and is ' . $header);
}
/**
* Test db_result export.
*/
public function testDBResultExport() {
// Create 10 nodes.
for ($i = 10; $i > 0; $i--) {
$this
->createNode();
}
// Get the db query result.
$result = db_select('node', 'n')
->fields('n', array(
'nid',
'title',
))
->execute();
// Create a path.
$correct_path = file_create_filename($this
->getFilename('xlsx'), $this->directory);
// The filename will be munged by the export function, so:
$this->test_files[] = $actual_path = phpexcel_munge_filename($correct_path);
// Try exporting to Excel 2007.
$options = array(
'format' => 'xlsx',
'creator' => 'SimpleTest',
'title' => 'DBResult',
'subject' => 'test',
'description' => 'my description',
);
$this
->assertEqual(PHPEXCEL_SUCCESS, phpexcel_export_db_result($result, $correct_path), t('Exported data to !path', array(
'!path' => $actual_path,
)));
$this
->assertTrue(filesize($actual_path) > 0, 'Filesize should be bigger than 0');
// Import, cells keyed by headers.
$data = phpexcel_import($actual_path);
$this
->assertTrue(!!$data, 'Import succeeded');
// Should have 10 rows.
$count = count($data[0]);
$this
->assertTrue($count === 10, t('!count rows, expect 10', array(
'!count' => $count,
)));
// Should only have 2 cells.
$count = count($data[0][0]);
$this
->assertTrue($count === 2, t('!count cells, expect 2', array(
'!count' => $count,
)));
// Should be keyed by headers (nid & title).
$this
->assertTrue(isset($data[0][0]['nid']), 'Keyed by header (nid)');
$this
->assertTrue(isset($data[0][1]['title']), 'Keyed by header (title)');
}
/**
* Test the ability to pass custom methods and arguments on import.
*/
public function testCustomMethodCalls() {
// Prepare data.
$headers = array(
'Sheet 1' => array(
'Header 1.1',
'Header 1.2',
),
'Sheet 2' => array(
'Header 2.1',
'Header 2.2',
),
);
$data = array(
'Sheet 1' => array(
array(
'Data 1.1.1',
'Data 1.1.2',
),
array(
'Data 1.2.1',
'Data 1.2.2',
),
array(
'Data 1.3.1',
'Data 1.3.2',
),
),
'Sheet 2' => array(
array(
'Data 2.1.1',
'Data 2.1.2',
),
array(
'Data 2.2.1',
'Data 2.2.2',
),
array(
'Data 2.3.1',
'Data 2.3.2',
),
),
);
// Create a file path.
$correct_path = file_create_filename($this
->getFilename(), $this->directory);
// The filename will be munged by the export function, so:
$this->test_files[] = $actual_path = phpexcel_munge_filename($correct_path);
$this
->assertEqual(PHPEXCEL_SUCCESS, phpexcel_export($headers, $data, $correct_path), t('Exported data to !path', array(
'!path' => $actual_path,
)));
$this
->assertTrue(filesize($actual_path) > 0, 'Filesize should be bigger than 0');
// Import and check. Pass a method call to only import a specific worksheet.
$data = phpexcel_import($actual_path, TRUE, TRUE, array(
'setLoadSheetsOnly' => array(
'Sheet 1',
),
));
$this
->assertTrue(!!$data, 'Import succeeded');
// Should have 3 rows.
$count = !empty($data['Sheet 1']) ? count($data['Sheet 1']) : 0;
$this
->assertTrue($count === 3, t('!count rows, expect 3', array(
'!count' => $count,
)));
// Should be empty.
$count = !empty($data['Sheet 2']) ? count($data['Sheet 2']) : 0;
$this
->assertTrue($count === 0, t('!count rows, expect 0', array(
'!count' => $count,
)));
}
protected function getFilename($extension = 'xls') {
return 'phpexcel_' . uniqid() . '.' . $extension;
}
protected function createNode() {
$node = (object) array(
'type' => 'page',
'title' => $this
->randomName(8),
'body' => $this
->randomName(20),
);
node_save($node);
}
}
Classes
Name | Description |
---|---|
PHPExcelTest | @file Defines the test case for phpexcel |