PhpSpreadsheet

https://github.com/phpoffice/phpspreadsheet
Admin
PhpSpreadsheet is a PHP library that enables reading, creating, and writing spreadsheet documents in...
Tokens:868,454
Snippets:4,911
Trust Score:8.8
License:MIT
Update:4 days ago
Show doc for...
Tokens:
Raw
### Before: PHPExcel PDF writer setup

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/migration-from-PHPExcel.md

Shows the old PHPExcel way to configure and create a PDF writer using the MPDF renderer. This code is provided as a 'Before' example for migration to PhpSpreadsheet.

```php
\PHPExcel_Settings::setPdfRendererName(PHPExcel_Settings::PDF_RENDERER_MPDF);
\PHPExcel_Settings::setPdfRenderer($somePath);
$writer = \PHPExcel_IOFactory::createWriter($spreadsheet, 'PDF');
```

--------------------------------

### Before: PHPExcel chart renderer setup

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/migration-from-PHPExcel.md

Shows the old PHPExcel way to set the JpGraph chart renderer. This is a 'Before' example for migrating to PhpSpreadsheet's chart rendering.

```php
$rendererName = \PHPExcel_Settings::CHART_RENDERER_JPGRAPH;
$rendererLibrary = 'jpgraph3.5.0b1/src/';
$rendererLibraryPath = '/php/libraries/Charts/' . $rendererLibrary;

\PHPExcel_Settings::setChartRenderer($rendererName, $rendererLibraryPath);
```

--------------------------------

### Install PhpSpreadsheet with Source and Samples

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/index.md

Installs PhpSpreadsheet with the --prefer-source flag to also download documentation and samples. Requires git to be in your path.

```sh
composer require phpoffice/phpspreadsheet --prefer-source
```

--------------------------------

### Install Memcache cache packages

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/memory_saving.md

Install the required Composer packages for using Memcache as a cache backend.

```sh
composer require cache/simple-cache-bridge cache/memcache-adapter
```

--------------------------------

### Install development dependencies with composer install

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/CONTRIBUTING.md

Run this command inside your PhpSpreadsheet clone to install development dependencies.

```bash
composer install
```

--------------------------------

### Install RectorPHP and initialize config

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/migration-from-PHPExcel.md

Commands to install RectorPHP and its PHPOffice extension, then create a rector.php config file. Requires an earlier release of rector (0.15.10) because support has been dropped from current releases.

```sh
composer require rector/rector:0.15.10 rector/rector-phpoffice phpoffice/phpspreadsheet --dev

# this creates rector.php config
vendor/bin/rector init 
```

--------------------------------

### Install Redis cache packages

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/memory_saving.md

Install the required Composer packages for using Redis as a cache backend.

```sh
composer require cache/simple-cache-bridge cache/redis-adapter
```

--------------------------------

### Install PhpSpreadsheet with Composer

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/index.md

Installs PhpSpreadsheet into your project using Composer. This is the standard installation command.

```sh
composer require phpoffice/phpspreadsheet
```

--------------------------------

### DPRODUCT function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DPRODUCT function. It sets up a database and criteria array, writes them to the worksheet, and calculates the product of matching cells. The expected result is 140.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DPRODUCT(A4:E10,"Yield",A1:B2)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 140
```

--------------------------------

### Set the images root for HTML output

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/reading-and-writing-to-file.md

Explicitly set the root for included images, e.g., to use an absolute URL instead of a relative one. The example uses 'http://two.example.com'.

```html
<img src="./images/ code>
```

```html
<img src="http://php www.example.com/images/logo.jpg">
```

```php
$writer->setImagesRoot('http://www disable example.com');
```

--------------------------------

### SECOND

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the SECOND function, including syntax, parameters, return value, and examples.

```APIDOC
## SECOND(datetime)

### Description
Returns the seconds of a time value as an integer from 0 to 59.

### Method
Function call

### Endpoint
SECOND(datetime)

### Parameters
#### Parameters
- **datetime** (time) - Required - An Excel date/time value, PHP date timestamp, PHP DateTime object, or a date/time represented as a string.

### Return Value
**integer** - An integer value that reflects the seconds within the minute, ranging from 0 to 59.

### Examples
```php
$worksheet->setCellValue('A1', 'Time String')
    ->setCellValue('A2', '31-Dec-2008 17:30:20')
    ->setCellValue('A3', '14-Feb-2008 4:20 AM')
    ->setCellValue('A4', '14-Feb-2008 4:45:59 PM');

$worksheet->setCellValue('B2', '=SECOND(A2)')
    ->setCellValue('B3', '=SECOND(A3)');
    ->setCellValue('B4', '=SECOND(A4)');

$retVal = $worksheet->getCell('B2')->getCalculatedValue();
// $retVal = 20

$retVal = $worksheet->getCell('B3')->getCalculatedValue();
// $retVal = 0

$retVal = $worksheet->getCell('B4')->getCalculatedValue();
// $retVal = 59
```

```php
$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'SECOND'],
    ['09:30:17']
);
// $retVal = 17
```

### Notes
Note that the PhpSpreadsheet function is `\PhpOffice\PhpSpreadsheet\Calculation\Functions::SECOND()` when the method is called statically.
```

--------------------------------

### DGET function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DGET function. Sets up a database and criteria array, writes them to the worksheet, sets a formula using DGET, and calculates the result. The expected result is 14.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=GET(A4:E10,"Age",A1:F2)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 14
```

--------------------------------

### MINUTE

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the MINUTE function, including syntax, parameters, return value, examples, and notes.

```APIDOC
## MINUTE

### Description
The MINUTE function returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.

### Syntax
MINUTE(datetime)

### Parameters
- **datetime** (mixed) - Required - Time. An Excel date/time value, PHP date timestamp, PHP `DateTime` object, or a date/time represented as a string.

### Return Value
**integer** - An integer value that reflects the minutes within the hour. This is an integer ranging from 0 to 59.

### Examples
```php
$worksheet->setCellValue('A1', 'Time String')
    ->setCellValue('A2', '31-Dec-2008 17:30')
    ->setCellValue('A3', '14-Feb-2008 4:20 AM')
    ->setCellValue('A4', '14-Feb-2008 4:45 PM');

$worksheet->setCellValue('B2', '=MINUTE(A2)')
    ->setCellValue('B3', '=MINUTE(A3)')
    ->setCellValue('B4', '=MINUTE(A4)');

$retVal = $worksheet->getCell('B2')->getCalculatedValue();
// $retVal = 30

$retVal = $worksheet->getCell('B3')->getCalculatedValue();
// $retVal = 20

$retVal = $worksheet->getCell('B4')->getCalculatedValue();
// $retVal = 45
```

```php
$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'MINUTE'],
    ['09:30']
);
// $retVal = 30
```

### Notes
Note that the PhpSpreadsheet function is `\PhpOffice\PhpSpreadsheet\Calculation\Functions::MINUTE()` when the method is called statically.
```

--------------------------------

### Serve PhpSpreadsheet samples with PHP built-in server

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/index.md

Starts PHP's built-in web server to serve the PhpSpreadsheet sample files. Point your browser to http://localhost:8000/ to view them.

```sh
php -S localhost:8000 -t vendor/phpoffice/phpspreadsheet/samples
```

--------------------------------

### DMAX function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DMAX function. Sets up a database and criteria array, writes them to the worksheet, sets a formula using DMAX, and calculates the result. The expected result is 105.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DMAX(A4:E10,"Profit",A1:B2)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 105
```

--------------------------------

### Install Symfony Cache for APCu

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/memory_saving.md

Install the Symfony Cache package via Composer to use APCu as a cache backend. Requires the APCu PHP extension to be installed and enabled.

```sh
composer require symfony/cache
```

--------------------------------

### DCOUNT function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DCOUNT function. It sets up a database and criteria array, writes them to a worksheet, and calculates the DCOUNT formula. The expected result is 3.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DCOUNT(A4:E10,"Height",A1:B3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();

// $retVal = 3
```

--------------------------------

### DCOUNTA function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DCOUNTA function. It sets up a database and criteria array, writes them to a worksheet, and calculates the DCOUNTA formula. The expected result is 5.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DCOUNTA(A4:E10,"Yield",A1:A3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();

// $retVal = 5
```

--------------------------------

### Install mitoteam/jpgraph via Composer

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/migration-from-PHPExcel.md

Installs the mitoteam/jpgraph package via Composer, which is fully compatible with Jpgraph and recommended for rendering charts in PhpSpreadsheet.

```sh
composer require mitoteam/jpgraph
```

--------------------------------

### Non-fluent property setting example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/architecture.md

Shows the traditional way to set document properties by calling getProperties() repeatedly. Each call returns the same properties object, so the method is invoked seven times.

```php
$spreadsheet->getProperties()->setCreator("Maarten Balliauw");
$spreadsheet->getProperties()->setLastModifiedBy("Maarten Balliauw");
$spreadsheet->getProperties()->setTitle("Office 2007 XLSX Test Document");
$spreadsheet->getProperties()->setSubject("Office 2007 XLSX Test Document");
$spreadsheet->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$spreadsheet->getProperties()->setKeywords("office 2007 openxml php");
$spreadsheet->getProperties()->setCategory("Test result file");
```

--------------------------------

### DMIN function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DMIN function. It sets up a database and criteria array, writes them to the worksheet, and calculates the minimum value of matching cells. The expected result is 6.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DMIN(A4:E10,"Yield",A1:A3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 6
```

--------------------------------

### DSUM function example with database and criteria arrays

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DSUM function. It sets up a database array and a criteria array, writes them to the worksheet, then calculates the DMIN formula (note: the example uses DMIN, not DSUM) and retrieves the result. The expected result is 225.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DMIN(A4:E10,"Profit",A1:A2)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 225
```

--------------------------------

### Identify file type and load with IOFactory

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/reading-files.md

Identify the file type with IOFactory::identify() and then create a reader and load the file. See samples/Reader/04_Simple_file_reader_using_the_IOFactory_to_identify_a_reader_to_use.php for a working example.

```php
$inputFileName = './sampleData/example1.xls';

/**
 * Identify the type of $inputFileName.
 * See below for a possible improvement for release 4.1.0+.
 */
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
/**  Create a new Reader of the type that has been identified  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);
```

--------------------------------

### MONTH

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the MONTH function, including syntax, parameters, return value, and examples.

```APIDOC
## MONTH(datetime)

### Description
Returns the month of a date as an integer from 1 to 12.

### Method
Function call

### Endpoint
MONTH(datetime)

### Parameters
#### Parameters
- **datetime** (date) - Required - An Excel date value, PHP date timestamp, PHP DateTime object, or a date represented as a string.

### Return Value
**integer** - An integer value that reflects the month of the year, ranging from 1 to 12.

### Examples
```php
$worksheet->setCellValue('A1', 'Date String');
$worksheet->setCellValue('A2', '31-Dec-2008');
$worksheet->setCellValue('A3', '14-Feb-2008');

$worksheet->setCellValue('B2', '=MONTH(A2)');
$worksheet->setCellValue('B3', '=MONTH(A3)');

$retVal = $worksheet->getCell('B2')->getCalculatedValue();
// $retVal = 12

$retVal = $worksheet->getCell('B3')->getCalculatedValue();
// $retVal = 2
```

```php
$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'MONTHOFYEAR'],
    ['14-July-2008']
);
// $retVal = 7
```

### Notes
Note that the PhpSpreadsheet function is `\PhpOffice\PhpSpreadsheet\Calculation\Functions::MONTHOFYEAR()` when the method is called statically.
```

--------------------------------

### EOMONTH

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the EOMONTH function, including syntax, parameters, return value, examples, and notes.

```APIDOC
## EOMONTH

### Description
The EOMONTH function returns an Excel timestamp or a PHP timestamp or `DateTime` object representing the date of the last day of the month that is the indicated number of months before or after a specified date (the start_date). Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

### Syntax
EOMONTH(baseDate, months)

### Parameters
- **baseDate** (mixed) - Required - Start Date. An Excel date value, PHP date timestamp, PHP `DateTime` object, or a date represented as a string.
- **months** (integer) - Required - Number of months to add. An integer value indicating the number of months before or after baseDate. A positive value for months yields a future date; a negative value yields a past date.

### Return Value
**mixed** - A date/time stamp that corresponds to the last day of basedate + months. This could be a PHP timestamp value (integer), a PHP `DateTime` object, or an Excel timestamp value (real), depending on the value of `\PhpOffice\PhpSpreadsheet\Calculation\Functions::getReturnDateType()`.

### Examples
```php
$worksheet->setCellValue('A1', 'Date String')
    ->setCellValue('A2', '1-Jan-2000')
    ->setCellValue('A3', '14-Feb-2009');

$worksheet->setCellValue('B2', '=EOMONTH(A2,5)')
    ->setCellValue('B3', '=EOMONTH(A3,-12)');

\PhpOffice\PhpSpreadsheet\Calculation\Functions::setReturnDateType(\PhpOffice\PhpSpreadsheet\Calculation\Functions::RETURNDATE_EXCEL);

$retVal = $worksheet->getCell('B2')->getCalculatedValue();
// $retVal = 39629.0 (30-Jun-2008)

$retVal = $worksheet->getCell('B3')->getCalculatedValue();
// $retVal = 39507.0 (29-Feb-2008)
```

```php
\PhpOffice\PhpSpreadsheet\Calculation\Functions::setReturnDateType(
    \PhpOffice\PhpSpreadsheet\Calculation\Functions::RETURNDATE_EXCEL
);

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'EOMONTH'],
    ['31-Oct-2008', 13]
);
// $retVal = 40147.0 (30-Nov-2010)
```

### Notes
**WARNING:-** This function is currently not supported by the Xls Writer because it is not a standard function within Excel 5, but an add-in from the Analysis ToolPak.
```

--------------------------------

### Memory and timing output for toArray() example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/Looping the Loop.md

Output from the toArray() example showing memory usage before and after building the array, and the time to build and iterate. The array adds 10240KB (10MB) of memory for a worksheet with about 120,000 cells.

```text
Current memory usage: 47104 KB

Call time to build array was 0.6603 seconds

Call time to iterate array rows was 0.0130 seconds
Current memory usage: 57344 KB
   Peak memory usage: 57344 KB
```

--------------------------------

### DSTDEV function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DSTDEV function. It sets up a database and criteria array, populates the worksheet, and calculates the estimated standard deviation of the 'Yield' column for matching rows. The expected result is 2.97.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DSTDEV(A4:E10,"Yield",A1:A3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 2.97
```

--------------------------------

### DSTDEV

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the DSTDEV function, including syntax, parameters, return value, and an example.

```APIDOC
## DSTDEV

### Description
The DSTDEV function estimates the standard deviation of a population based on a sample by using the numbers in a column of a list or database that match conditions that you specify.

### Syntax
`DSTDEV(database, field, criteria)`

### Parameters
- **database** (range) - Required - The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
- **field** (string or number) - Required - Indicates which column of the database is used in the function. Enter the column label as a string (enclosed between double quotation marks), such as "Age" or "Yield," or as a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
- **criteria** (range) - Required - The range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

### Return Value
**float** - The estimated standard deviation of the matching cells.

### Example
```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DSTDEV(A4:E10,"Yield",A1:A3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 2.97
```

### Notes
There are no additional notes on this function.
```

--------------------------------

### DSTDEVP

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the DSTDEVP function, including syntax, parameters, return value, and an example.

```APIDOC
## DSTDEVP

### Description
The DSTDEVP function calculates the standard deviation of a population based on the entire population by using the numbers in a column of a list or database that match conditions that you specify.

### Syntax
`DSTDEVP(database, field, criteria)`

### Parameters
- **database** (range) - Required - The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
- **field** (string or number) - Required - Indicates which column of the database is used in the function. Enter the column label as a string (enclosed between double quotation marks), such as "Age" or "Yield," or as a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
- **criteria** (range) - Required - The range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

### Return Value
**float** - The estimated standard deviation of the matching cells.

### Example
```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DSTDEVP(A4:E10,"Yield",A1:A3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 2.65
```

### Notes
There are no additional notes on this function.
```

--------------------------------

### Write a spreadsheet to an HTML file

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/reading-and-writing-to-file.md

Use this to write a .htm file. The writer only outputs the example file '05featuredemo.htm'.

```php
$writer = new \Sfotware\PhpSpreadsheet\

$writer->save("05featuredemo.htm");
```

--------------------------------

### SECOND function example with worksheet

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Demonstrates the SECOND function in a worksheet: sets time strings, applies the SECOND formula, and retrieves the calculated seconds (20, 0, 59).

```php
$worksheet->setCellValue('A1', 'Time String')
    ->setCellValue('A2', '31-Dec-2008 17:30:20')
    ->setCellValue('A3', '14-Feb-2008 4:20 AM')
    ->setCellValue('A4', '14-Feb-2008 4:45:59 PM');

$worksheet->setCellValue('B2', '=SECOND(A2)')
    ->setCellValue('B3', '=SECOND(A3)');
    ->setCellValue('B4', '=SECOND(A4)');

$retVal = $worksheet->getCell('B2')->getCalculatedValue();
// $retVal = 20

$retVal = $worksheet->getCell('B3')->getCalculatedValue();
// $retVal = 0

$retVal = $worksheet->getCell('B4')->getCalculatedValue();
// $retVal = 59
```

--------------------------------

### EDATE

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the EDATE function, including parameters, return value, examples, and notes.

```APIDOC
## EDATE

### Description
The EDATE function returns an Excel timestamp or a PHP timestamp or `DateTime` object representing the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

### Method
Function Call

### Endpoint
`\PhpOffice\PhpSpreadsheet\Calculation\Functions::EDATE(baseDate, months)`

### Parameters
- **baseDate** (mixed) - Required - Start Date. An Excel date value, PHP date timestamp, PHP `DateTime` object, or a date represented as a string.
- **months** (integer) - Required - Number of months to add. A positive value for months yields a future date; a negative value yields a past date.

### Return Value
**mixed** - A date/time stamp that corresponds to the basedate + months. This could be a PHP timestamp value (integer), a PHP `DateTime` object, or an Excel timestamp value (real), depending on the value of `\PhpOffice\PhpSpreadsheet\Calculation\Functions::getReturnDateType()`.

### Examples
```php
$worksheet->setCellValue('A1', 'Date String')
    ->setCellValue('A2', '1-Jan-2008')
    ->setCellValue('A3', '29-Feb-2008');

$worksheet->setCellValue('B2', '=EDATE(A2,5)')
    ->setCellValue('B3', '=EDATE(A3,-12)');

\PhpOffice\PhpSpreadsheet\Calculation\Functions::setReturnDateType(
    \PhpOffice\PhpSpreadsheet\Calculation\Functions::RETURNDATE_EXCEL
);

$retVal = $worksheet->getCell('B2')->getCalculatedValue();
// $retVal = 39600.0 (1-Jun-2008)

$retVal = $worksheet->getCell('B3')->getCalculatedValue();
// $retVal = 39141.0 (28-Feb-2007)
```

```php
\PhpOffice\PhpSpreadsheet\Calculation\Functions::setReturnDateType(
    \PhpOffice\PhpSpreadsheet\Calculation\Functions::RETURNDATE_EXCEL
);

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'EDATE'],
    ['31-Oct-2008', 25]
);
// $retVal = 40512.0 (30-Nov-2010)
```

### Notes
**WARNING:-** This function is currently not supported by the Xls Writer because it is not a standard function within Excel 5, but an add-in from the Analysis ToolPak.
```

--------------------------------

### HOUR

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the HOUR function, including syntax, parameters, return value, examples, and notes.

```APIDOC
## HOUR

### Description
The HOUR function returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

### Syntax
HOUR(datetime)

### Parameters
- **datetime** (mixed) - Required - Time. An Excel date/time value, PHP date timestamp, PHP `DateTime` object, or a date/time represented as a string.

### Return Value
**integer** - An integer value that reflects the hour of the day. This is an integer ranging from 0 to 23.

### Examples
```php
$worksheet->setCellValue('A1', 'Time String')
    ->setCellValue('A2', '31-Dec-2008 17:30')
    ->setCellValue('A3', '14-Feb-2008 4:20 AM')
    ->setCellValue('A4', '14-Feb-2008 4:20 PM');

$worksheet->setCellValue('B2', '=HOUR(A2)')
    ->setCellValue('B3', '=HOUR(A3)')
    ->setCellValue('B4', '=HOUR(A4)');

$retVal = $worksheet->getCell('B2')->getCalculatedValue();
// $retVal = 17

$retVal = $worksheet->getCell('B3')->getCalculatedValue();
// $retVal = 4

$retVal = $worksheet->getCell('B4')->getCalculatedValue();
// $retVal = 16
```

```php
$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'HOUROFDAY'],
    ['09:30']
);
// $retVal = 9
```

### Notes
Note that the PhpSpreadsheet function is `\PhpOffice\PhpSpreadsheet\Calculation\Functions::HOUROFDAY()` when the method is called statically.
```

--------------------------------

### SECOND static call example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Calls the static SECOND method directly with a time string, returning the seconds as an integer (17).

```php
$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'SECOND'],
    ['09:30:17']
);
// $retVal = 17
```

--------------------------------

### DAYS360

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Documentation for the DAYS360 function, including parameters, return value, examples, and notes.

```APIDOC
## DAYS360

### Description
Calculates the number of days between two dates based on a 360-day year.

### Method
Function Call

### Endpoint
`\PhpOffice\PhpSpreadsheet\Calculation\Functions::DAYS360(date1, date2, method)`

### Parameters
- **date1** (mixed) - Required - First Date. An Excel date value, PHP date timestamp, PHP `DateTime` object, or a date represented as a string.
- **date2** (mixed) - Required - Second Date. An Excel date value, PHP date timestamp, PHP `DateTime` object, or a date represented as a string.
- **method** (boolean) - Optional - A boolean flag (TRUE or FALSE) that determines which method to use in the calculation. Defaults to FALSE.

### Return Value
**integer** - An integer value that reflects the difference between the two dates. This is the number of full days between the two dates, based on a 360 day year.

### Examples
```php
$worksheet->setCellValue('B1', 'Start Date')
    ->setCellValue('C1', 'End Date')
    ->setCellValue('A2', 'Year')
    ->setCellValue('A3', 'Month')
    ->setCellValue('A4', 'Day');

$worksheet->setCellValue('B2', 2003)
    ->setCellValue('B3', 2)
    ->setCellValue('B4', 3);

$worksheet->setCellValue('C2', 2007)
    ->setCellValue('C3', 5)
    ->setCellValue('C4', 31);

$worksheet->setCellValue('E2', '=DAYS360(DATE(B2,B3,B4),DATE(C2,C3,C4))')
    ->setCellValue('E4', '=DAYS360(DATE(B2,B3,B4),DATE(C2,C3,C4),FALSE)');

$retVal = $worksheet->getCell('E2')->getCalculatedValue();
// $retVal = 1558

$retVal = $worksheet->getCell('E4')->getCalculatedValue();
// $retVal = 1557
```

```php
$date1 = 37655.0; // Excel timestamp for 25-Oct-2007
$date2 = 39233.0; // Excel timestamp for 8-Dec-2015

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DAYS360'],
    [$date1, $date2]
);
// $retVal = 1558

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DAYS360'],
    [$date1, $date2, TRUE]
);
// $retVal = 1557
```

### Notes
**WARNING:-** This function does not currently work with the Xls Writer when a PHP Boolean is used for the third (optional) parameter (as shown in the example above), and the writer will generate and error. It will work if a numeric 0 or 1 is used for the method parameter; or if the Excel `TRUE()` and `FALSE()` functions are used instead.
```

--------------------------------

### DSTDEVP function example

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/topics/calculation-engine.md

Example for the DSTDEVP function. It uses the same database and criteria setup as the DSTDEV example but calculates the population standard deviation. The expected result is 2.65.

```php
$database = [
    [ 'Tree',  'Height', 'Age', 'Yield', 'Profit' ],
    [ 'Apple',  18,       20,    14,      105.00  ],
    [ 'Pear',   12,       12,    10,       96.00  ],
    [ 'Cherry', 13,       14,     9,      105.00  ],
    [ 'Apple',  14,       15,    10,       75.00  ],
    [ 'Pear',    9,        8,     8,       76.80  ],
    [ 'Apple',   8,        9,     6,       45.00  ],
];

$criteria = [
    [ 'Tree',      'Height', 'Age', 'Yield', 'Profit', 'Height' ],
    [ '="=Apple"', '>10',    NULL,  NULL,    NULL,     '<16'    ],
    [ '="=Pear"',  NULL,     NULL,  NULL,    NULL,     NULL     ],
];

$worksheet->fromArray( $criteria, NULL, 'A1' )
    ->fromArray( $database, NULL, 'A4' );

$worksheet->setCellValue('A12', '=DSTDEVP(A4:E10,"Yield",A1:A3)');

$retVal = $worksheet->getCell('A12')->getCalculatedValue();
// $retVal = 2.65
```

--------------------------------

### Run a PhpSpreadsheet sample from the command line

Source: https://github.com/phpoffice/phpspreadsheet/blob/master/docs/index.md

Runs a PhpSpreadsheet sample directly from the command line.

```sh
php vendor/phpoffice/phpspreadsheet/samples/Basic/01_Simple.php
```
Tokens:
Raw