
PHPUnit: Spreadsheet as a data provider
- Tutorial
- Recovery mode
There is a small section in the documentation of PHPUnit devoted to data sources (data provider), which allow you to feed a large amount of data to the test, and just below there is even an example of a data source for a CSV file.
Of course, use a full spreadsheet!
We agree that:
And immediately get down to business (minimum text, maximum code), all code is also available at the link at the bottom of the note:
We will need (
It all comes down to adding your own method that will receive data from a file and turn it into a data source for tests.
Receiving:
Two points deserve attention (the rest, I hope, is obvious):
Transformation:
Of the features, it is worth noting the possibility of using formulas in cells, but you still can’t refuse a separate method for converting values - firstly, not all data types have the necessary functions (the same

All the magic is in the annotation
I hope this recipe is useful to someone :)
Project: yadi.sk/d/AyegnPCqf7i9Y
Of course, use a full spreadsheet!
We agree that:
- Each test file has its own data file (just one, instead of a bunch of CSV files)
- The data inside the file is stored on separate pages, one for each test, the name of the page matches the name of the test (which is why it was all about)
And immediately get down to business (minimum text, maximum code), all code is also available at the link at the bottom of the note:
Step # 1: Dependencies
We will need (
composer.json
):{
"name": "PHPUnitSpreadsheetDataProvider",
"require": {
"php": ">=5.5.0",
"phpunit/phpunit": "4.5.*",
"phpoffice/phpexcel": "1.8.*"
},
"autoload": {
"classmap": [
"src/"
]
}
}
Step # 2: Basic Logic
It all comes down to adding your own method that will receive data from a file and turn it into a data source for tests.
Receiving:
getFileName();
$dirname = pathinfo($path, PATHINFO_DIRNAME);
$filename = pathinfo($path, PATHINFO_FILENAME);
$resource = $resource ?: 'xml';
return "{$dirname}/{$filename}.{$resource}";
}
/**
* Возвращает данные для теста.
*
* @param string $test
*
* @return array|Iterator
*/
public function getTestDataProvider($test) {
// Файл
$position = 2;
$resource = $this->getTestResource('data.ods');
// Reader?
if (is_null($this->_reader)) {
$this->_reader = PHPExcel_IOFactory::createReaderForFile($resource);
}
// Настройки
$this->_reader->setReadDataOnly(true);
$this->_reader->setLoadSheetsOnly($test);
// Данные
return new PHPUnitSDP_PHPExcelWorksheetRowIterator(
$this->_reader->load($resource)->getActiveSheet(), $position);
}
}
Two points deserve attention (the rest, I hope, is obvious):
$position = 2;
- the first line (numbering starts with 1) with data, everything that can be used before it for comments (see example below)$resource
- defines the name of the data file, in this case it is "ИмяТеста.data.ods
"
Transformation:
getCellIterator() as $cell) {
/* @var $cell PHPExcel_Cell */
$current[] = $this->getValue($cell->getCalculatedValue());
}
return $current;
}
/**
* @param mixed $value
*
* @return mixed
*/
protected function getValue($value) {
switch (mb_strtolower(trim($value))) {
case 'null':
$value = null;
break;
case 'true':
$value = true;
break;
case 'false':
$value = false;
break;
default:
/* empty */
break;
}
return $value;
}
}
Of the features, it is worth noting the possibility of using formulas in cells, but you still can’t refuse a separate method for converting values - firstly, not all data types have the necessary functions (the same
NULL
), and secondly, calculating formulas takes time and resources.Step # 3: Data

Step # 4: Test
// SDPTest.php
class SDPTest extends PHPUnit_Framework_TestCase {
use PHPUnitSDP_PHPUnitTestCase;
/**
* @dataProvider getTestDataProvider
*
* @param number $base
* @param number $exp
* @param number $expected
*
* @return void
*/
public function testPow($base, $exp, $expected) {
$this->assertEquals($expected, pow($base, $exp));
}
/**
* @dataProvider getTestDataProvider
*
* @param number $arg
* @param number $expected
*
* @return void
*/
public function testSqrt($arg, $expected) {
$this->assertEquals($expected, sqrt($arg));
}
}
All the magic is in the annotation
@dataProvider getTestDataProvider
- before starting the test, PHPUnit will call the previously defined method PHPUnitSDP_PHPUnitTestCase::getTestDataProvider()
with an argument that contains the name of the test and gets the necessary data source.Step # 5: Result
PHPUnitSpreadsheetDataProvider> phpunit
PHPUnit 4.5.0 by Sebastian Bergmann and contributors.
Configuration read from PHPUnitSpreadsheetDataProvider/phpunit.xml
......F
Time: 158 ms, Memory: 8.75Mb
There was 1 failure:
1) SDPTest::testSqrt with data set #4 (4.0, 3.0)
Failed asserting that 2.0 matches expected 3.0.
PHPUnitSpreadsheetDataProvider/tests/SDPTest.php:28
phar://PHPUnitSpreadsheetDataProvider/phpunit.phar/phpunit/TextUI/Command.php:152
phar://PHPUnitSpreadsheetDataProvider/phpunit.phar/phpunit/TextUI/Command.php:104
FAILURES!
Tests: 7, Assertions: 7, Failures: 1.
Conclusion
I hope this recipe is useful to someone :)
Project: yadi.sk/d/AyegnPCqf7i9Y