PHPUnit: Spreadsheet as a data provider

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:
  • 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": [

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.

        $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);
        // Настройки
        // Данные
        return new PHPUnitSDP_PHPExcelWorksheetRowIterator(
            $this->_reader->load($resource)->getActiveSheet(), $position);

Two points deserve attention (the rest, I hope, is obvious):
  1. $position = 2; - the first line (numbering starts with 1) with data, everything that can be used before it for comments (see example below)
  2. $resource- defines the name of the data file, in this case it is " ИмяТеста.data.ods"

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;
            case 'true':
                $value = true;
            case 'false':
                $value = false;
                /* empty */
        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
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.
Tests: 7, Assertions: 7, Failures: 1.


I hope this recipe is useful to someone :)


Also popular now: