PhpSpreadsheet

https://phpspreadsheet.readthedocs.io/en/latest/
Admin
PhpSpreadsheet is a pure PHP library that allows you to read and write various spreadsheet file...
Tokens:235,655
Snippets:1,653
Trust Score:9.6
Update:1 week ago
Show doc for...
Tokens:
Raw
### After: Install mitoteam/jpgraph via Composer

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/migration-from-PHPExcel

Command to install the recommended chart rendering library via Composer.

```bash
composer require mitoteam/jpgraph

```

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

### Install PhpSpreadsheet with Composer

Source: https://phpspreadsheet.readthedocs.io/en/latest

Run this command in your project directory to install PhpSpreadsheet via Composer.

```bash
composer require phpoffice/phpspreadsheet
```

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

### Install Rector and initialize config

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/migration-from-PHPExcel

Install the required Rector packages and initialize the rector.php configuration file. This uses an earlier release of rector because support for PHPOffice migration has been dropped from current releases.

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

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

```

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

### Install PhpSpreadsheet with Source for Documentation and Samples

Source: https://phpspreadsheet.readthedocs.io/en/latest

Use --prefer-source to also download documentation and samples. Requires git to be in your PATH.

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

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

### Complete CellValue conditional formatting example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/conditional-formatting

Full example using pre-defined Style objects to apply equals, greaterThan, and lessThan rules to a cell range. It creates a Wizard, adds conditionals to the array, and sets them on the active sheet.

```php
$cellRange = 'A2:E5';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\CellValue $cellWizard */
$cellWizard = $wizardFactory->newRule(Wizard::CELL_VALUE);

$cellWizard->equals(0)
    ->setStyle($yellowStyle);
$conditionalStyles[] = $cellWizard->getConditional();

$cellWizard->greaterThan(0)
    ->setStyle($greenStyle);
$conditionalStyles[] = $cellWizard->getConditional();

$cellWizard->lessThan(0)
    ->setStyle($redStyle);
$conditionalStyles[] = $cellWizard->getConditional();

$spreadsheet->getActiveSheet()
    ->getStyle($cellWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);
```

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

### MINUTE static call example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Shows calling MINUTE statically via call_user_func_array with a time string, returning the minutes as an integer.

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

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

### DAYS360 worksheet example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Sets up a worksheet with start and end dates, then calculates DAYS360 with default and FALSE method. The calculated values are 1558 and 1557 respectively.

```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
```

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

### EDATE worksheet example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Sets up a worksheet with date strings and calculates EDATE with positive and negative month offsets. Uses RETURNDATE_EXCEL to get Excel timestamps: 39600.0 (1-Jun-2008) and 39141.0 (28-Feb-2007).

```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)
```

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

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

Source: https://phpspreadsheet.readthedocs.io/en/latest

Starts a PHP built-in webserver on localhost:8000, serving the PhpSpreadsheet samples directory. Requires samples downloaded with the --prefer-source composer flag.

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

```

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

### setFitToWidth and setFitToHeight

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes

Sets the fit-to-width and fit-to-height scaling options for the page setup. These methods trigger setFitToPage(TRUE) automatically.

```APIDOC
## setFitToWidth and setFitToHeight

### Description
Sets the fit-to-width and fit-to-height scaling options for the page setup. These methods trigger setFitToPage(TRUE) automatically.

### Method
setFitToWidth, setFitToHeight

### Parameters
- **value** (int) - Required - The number of pages wide/tall to fit. A value of 0 means do-not-fit-to-width/height.

### Request Example
```php
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);
```

### Response
No return value.
```

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

### Instantiate PDF writer with specific library name

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file

Creates a PDF writer using the 'Mpdf' library via IOFactory. Requires a PDF rendering library to be installed.

```php
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
```

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

### Output of reading cell values for C4 and H4

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/Behind%20the%20Mask

Shows the output of the previous example. For C4 (a numeric value formatted as currency) and H4 (a formula evaluating to a numeric value formatted as currency). Note that getting the formatted value always evaluates a formula.

```php
float(26.19)
float(26.19)
string(9) "€ 26.19"

string(8) "=$F4*$D4"
float(-170)
string(11) "€ -170.00"

```

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

### DSUM example with PhpSpreadsheet

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Example showing how to use the DSUM function with a database and criteria array, and how to retrieve the calculated value. 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
```

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

### DSTDEV example using PhpSpreadsheet

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Example showing how to use the DSTDEV function with a database and criteria array in PhpSpreadsheet. The 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
```

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

### DSTDEVP example using PhpSpreadsheet

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Example showing how to use the DSTDEVP function with a database and criteria array in PhpSpreadsheet. The 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
```

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

### Simple Named Formula Example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/defined-names

Defines named formulae TAX_RATE and TAX, a named range PRICE, and uses them in worksheet cells to calculate tax and gross price. The example outputs the calculated values.

```php
// Add some Named Formulae
// The first to store our tax rate
$spreadsheet->addNamedFormula(new NamedFormula('TAX_RATE', $worksheet, '=19%'));
// The second to calculate the Tax on a Price value (Note that `PRICE` is defined later as a Named Range)
$spreadsheet->addNamedFormula(new NamedFormula('TAX', $worksheet, '=PRICE*TAX_RATE'));

// Set up some basic data
$worksheet
    ->setCellValue('A1', 'Tax Rate:')
    ->setCellValue('B1', '=TAX_RATE')
    ->setCellValue('A3', 'Net Price:')
    ->setCellValue('B3', 19.99)
    ->setCellValue('A4', 'Tax:')
    ->setCellValue('A5', 'Price including Tax:');

// Define a named range that we can use in our formulae
$spreadsheet->addNamedRange(new NamedRange('PRICE', $worksheet, '=$B$3'));

// Reference the defined formulae in worksheet formulae
$worksheet
    ->setCellValue('B4', '=TAX')
    ->setCellValue('B5', '=PRICE+TAX');

echo sprintf(
    'With a Tax Rate of %.2f and a net price of %.2f, Tax is %.2f and the gross price is %.2f',
    $worksheet->getCell('B1')->getCalculatedValue(),
    $worksheet->getCell('B3')->getValue(),
    $worksheet->getCell('B4')->getCalculatedValue(),
    $worksheet->getCell('B5')->getCalculatedValue()
), PHP_EOL;
```

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

### DPRODUCT Example with PhpSpreadsheet

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

PHP example using PhpSpreadsheet to calculate DPRODUCT. Sets up a database and criteria array, writes them to the worksheet, sets a formula in cell A12, and retrieves the calculated value (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
```

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

### MINUTE worksheet example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Demonstrates using MINUTE in a worksheet with cell references, returning the minutes as an integer (0-59).

```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
```

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

### Example TextGrid output

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file

Shows the plaintext grid output produced by the TextGrid render method, with column headers A-D and row headers 1-3.

```text
    +-----+------------------+---+----------+
    | A   | B                | C | D        |
+---+-----+------------------+---+----------+
| 1 | 6   | 1900-01-06 00:00 |   | 0.572917 |
| 2 | 6   | TRUE             |   | 1<>2     |
| 3 | xyz | xyz              |   |          |
+---+-----+------------------+---+----------+
```

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

### DMIN Example with PhpSpreadsheet

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

PHP example using PhpSpreadsheet to calculate DMIN. Sets up a database and criteria array, writes them to the worksheet, sets a formula in cell A12, and retrieves the calculated value (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
```

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

### HOUR static call example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Shows calling HOUR statically via call_user_func_array with a time string, returning the hour as an integer.

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

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

### DATEDIF Example with PHP Timestamps

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Uses PHP timestamps and calls the DATEDIF function directly via call_user_func_array. Shows results for each unit.

```php
$date1 = 1193317015; // PHP timestamp for 25-Oct-2007
$date2 = 1449579415; // PHP timestamp for 8-Dec-2015

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DATEDIF'],
    [$date1, $date2, 'd']
);
// $retVal = 2966

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DATEDIF'],
    [$date1, $date2, 'm']
);
// $retVal = 97

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DATEDIF'],
    [$date1, $date2, 'y']
);
// $retVal = 8

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DATEDIF'],
    [$date1, $date2, 'ym']
);
// $retVal = 1

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DATEDIF'],
    [$date1, $date2, 'yd']
);
// $retVal = 44

$retVal = call_user_func_array(
    ['\PhpOffice\PhpSpreadsheet\Calculation\Functions', 'DATEDIF'],
    [$date1, $date2, 'md']
);
// $retVal = 13
Copy
```

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

### Error Handling: Load Spreadsheet with Try/Catch

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files

Wrap file loading in a try/catch block to handle \PhpOffice\PhpSpreadsheet\Reader\Exception. The example loads a file and dies with an error message on failure.

```php
$inputFileName = './sampleData/example-1.xls';

try {
    /** Load $inputFileName to a Spreadsheet Object  **/
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
} catch(\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
    die('Error loading file: '.$e->getMessage());
}
```

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

### TAKE

Source: https://phpspreadsheet.readthedocs.io/en/latest/references/function-list-by-name-compact

Returns a specified number of contiguous rows or columns from the start or end of an array. Implemented by LookupRef\ChooseRowsEtc::take.

```APIDOC
## TAKE

### Description
Returns a specified number of contiguous rows or columns from the start or end of an array.

### Method
`LookupRef\ChooseRowsEtc::take`

### Parameters
- **array** (array) - Required - The array from which to take rows or columns.
- **rows** (int) - Required - The number of rows to take. Negative values take from the end.
- **columns** (int) - Optional - The number of columns to take. Negative values take from the end.

### Return Value
- **array** - The extracted array.
```

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

### EOMONTH static call example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Shows calling EOMONTH statically via call_user_func_array with a date string and month offset, returning an Excel timestamp.

```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)
```

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

### SECOND function static call

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Example of calling the SECOND function statically using call_user_func_array. The result is 17 for the time '09:30:17'.

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

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

### HOUR worksheet example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Demonstrates using HOUR in a worksheet with cell references, returning the hour as an integer (0-23).

```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
```

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

### Set the images root for HTML output

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file

Sets the base URL for images in the generated HTML. For example, instead of './images/logo.jpg', you can have 'http://www.example.com/images/logo.jpg'.

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

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

### DATEDIF Example with Worksheet Cells

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Sets up date values in cells and calculates DATEDIF with different units (d, m, y, ym, yd, md). The expected results are shown in comments.

```php
$worksheet->setCellValue('A1', 'Year')
    ->setCellValue('A2', 'Month')
    ->setCellValue('A3', 'Day');

$worksheet->setCellValue('B1', 2001)
    ->setCellValue('C1', 2009)
    ->setCellValue('B2', 7)
    ->setCellValue('C2', 12)
    ->setCellValue('B3', 1)
    ->setCellValue('C3', 31);

$worksheet->setCellValue('D1', '=DATEDIF(DATE(B1,B2,B3),DATE(C1,C2,C3),"d")')
    ->setCellValue('D2', '=DATEDIF(DATE(B1,B2,B3),DATE(C1,C2,C3),"m")')
    ->setCellValue('D3', '=DATEDIF(DATE(B1,B2,B3),DATE(C1,C2,C3),"y")')
    ->setCellValue('D4', '=DATEDIF(DATE(B1,B2,B3),DATE(C1,C2,C3),"ym")')
    ->setCellValue('D5', '=DATEDIF(DATE(B1,B2,B3),DATE(C1,C2,C3),"yd")')
    ->setCellValue('D6', '=DATEDIF(DATE(B1,B2,B3),DATE(C1,C2,C3),"md")');

$retVal = $worksheet->getCell('D1')->getCalculatedValue();
// $retVal = 3105

$retVal = $worksheet->getCell('D2')->getCalculatedValue();
// $retVal = 101

$retVal = $worksheet->getCell('D3')->getCalculatedValue();
// $retVal = 8

$retVal = $worksheet->getCell('D4')->getCalculatedValue();
// $retVal = 5

$retVal = $worksheet->getCell('D5')->getCalculatedValue();
// $retVal = 183

$retVal = $worksheet->getCell('D6')->getCalculatedValue();
// $retVal = 30
Copy
```

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

### DAY worksheet example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Demonstrates using DAY in a worksheet with two date strings. Expects 31 for '31-Dec-2008' and 14 for '14-Feb-2008'.

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

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

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

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

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

### MONTH function static call

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Example of calling the MONTH function statically using call_user_func_array. The result is 7 for the date '14-July-2008'.

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

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

### Skip heading rows with getRowIterator start row

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/Looping%20the%20Loop

Pass a start row to getRowIterator() to skip heading rows at the top of the worksheet. This example starts iteration at row 3.

```php
$rowIterator = $worksheet->getRowIterator(3, $maxDataRow);
```

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

### Run a PhpSpreadsheet sample from the command line

Source: https://phpspreadsheet.readthedocs.io/en/latest

Executes the Basic/01_Simple.php sample directly from the command line.

```bash
php vendor/phpoffice/phpspreadsheet/samples/Basic/01_Simple.php

```

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

### Get calculated value for a single cell in array

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes

When returning arrays is not enabled, getCalculatedValue returns the top-left value of the array formula. This example shows retrieving the value for cell A1.

```php
// Will return integer 1, the value for that cell within the array
$a1result = $spreadsheet->getActiveSheet()->getCell('A1')->getCalculatedValue();
```

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

### Helper Method: listWorksheetInfo

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files

Use the reader's listWorksheetInfo() method to get worksheet metadata (name, dimensions, state) without loading the entire file. The example outputs the information as an HTML list.

```php
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

$worksheetData = $reader->listWorksheetInfo($inputFileName);

echo '<h3>Worksheet Information</h3>';
echo '<ol>';
foreach ($worksheetData as $worksheet) {
    echo '<li>', $worksheet['worksheetName'], '<br />';
    echo 'Rows: ', $worksheet['totalRows'],
         ' Columns: ', $worksheet['totalColumns'], '<br />';
    echo 'Cell Range: A1:',
         $worksheet['lastColumnLetter'], $worksheet['totalRows'], '<br />';
    echo 'Sheet state: ', $worksheet['sheetState'];
    echo '</li>';
}
echo '</ol>';
```

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

### Helper Method: listWorksheetNames

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files

Use the reader's listWorksheetNames() method to get an array of worksheet names without loading the entire file. The example creates a reader, retrieves the names, and outputs them as an HTML list.

```php
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

$worksheetNames = $reader->listWorksheetNames($inputFileName);

echo '<h3>Worksheet Names</h3>';
echo '<ol>';
foreach ($worksheetNames as $worksheetName) {
    echo '<li>', $worksheetName, '</li>';
}
echo '</ol>';
```

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

### Create and save a spreadsheet with PhpSpreadsheet

Source: https://phpspreadsheet.readthedocs.io/en/latest

Minimal example that creates a new spreadsheet, sets cell A1 to 'Hello World !', and saves it as 'hello world.xlsx' using the Xlsx writer. Requires Composer autoload and the PhpSpreadsheet package.

```php
<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();
$activeWorksheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

```

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

### Create reader with options using createReaderForFile

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file

Creates a reader for a file, sets the readDataOnly option to true, then loads the file. Useful when you need to configure the reader before loading.

```php
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile("05featuredemo.xlsx");
$reader->setReadDataOnly(true);
$reader->load("05featuredemo.xlsx");
```

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

### Detached reference example with getCell()

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/accessing-cells

Demonstrates the detached reference issue: assigning the result of getCell() to a variable and then retrieving another cell detaches the link to the collection, causing an exception when trying to get the coordinate of the original cell.

```php
$spreadSheet = new Spreadsheet();
$workSheet = $spreadSheet->getActiveSheet();

// Set details for the formula that we want to evaluate, together with any data on which it depends
$workSheet->fromArray(
    [1, 2, 3],
    null,
    'A1'
);

$cellC1 = $workSheet->getCell('C1');
echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;

$cellA1 = $workSheet->getCell('A1');
echo 'Value: ', $cellA1->getValue(), '; Address: ', $cellA1->getCoordinate(), PHP_EOL;

echo 'Value: ', $cellC1->getValue(), '; Address: ', $cellC1->getCoordinate(), PHP_EOL;
```

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

### EOMONTH worksheet example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Demonstrates using EOMONTH in a worksheet with cell references and setting the return date type to Excel. The output shows the calculated values as Excel timestamps.

```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)
```

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

### EDATE direct function call example

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Calls EDATE directly with a date string and 25 months, using RETURNDATE_EXCEL. Returns 40512.0 (30-Nov-2010).

```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)
```

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

### Generic Read Filter with Configurable Row and Column Range

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files

Defines a MyReadFilter class that accepts start row, end row, and columns via its constructor, making it reusable for different ranges. The example shows creating an instance with rows 9-15 and columns G-K.

```php
/**  Define a Read Filter class implementing \PhpOffice\PhpSpreadsheet\Reader\IReadFilter  */
class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    private $startRow = 0;
    private $endRow   = 0;
    private $columns  = [];

    /**  Get the list of rows and columns to read  */
    public function __construct($startRow, $endRow, $columns) {
        $this->startRow = $startRow;
        $this->endRow   = $endRow;
        $this->columns  = $columns;
    }

    public function readCell($columnAddress, $row, $worksheetName = '') {
        //  Only read the rows and columns that were configured
        if ($row >= $this->startRow && $row <= $this->endRow) {
            if (in_array($columnAddress,$this->columns)) {
                return true;
            }
        }
        return false;
    }
}

/**  Create an Instance of our Read Filter, passing in the cell range  **/
$filterSubset = new MyReadFilter(9,15,range('G','K'));
Copy
```

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

### Use Date/Time Wizards to format date, time, and datetime cells

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes

Demonstrates using the Date, Time, and DateTime wizards to create format codes for cells containing =NOW(). The example sets three cells (A1, A2, A3) and applies yyyy-mm-dd, hh:mm, and yyyy-mm-dd hh:mm formats respectively.

```php
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Date as DateWizard;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Time as TimeWizard;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\DateTime as DateTimeWizard;

$spreadsheet->getActiveSheet()->setCellValue('A1', '=NOW()')
$spreadsheet->getActiveSheet()->setCellValue('A2', '=NOW()')
$spreadsheet->getActiveSheet()->setCellValue('A3', '=NOW()')

// yyyy-mm-dd
$dateFormat = new DateWizard(
    DateWizard::SEPARATOR_DASH,
    DateWizard::YEAR_FULL,
    DateWizard::MONTH_NUMBER_LONG,
    DateWizard::DAY_NUMBER_LONG
);

$spreadsheet->getActiveSheet()->getStyle('A1')
    ->getNumberFormat()
    ->setFormatCode($dateFormat);

// hh:mm
$timeFormat = new TimeWizard(
    TimeWizard::SEPARATOR_COLON,
    TimeWizard::HOURS_LONG,
    TimeWizard::MINUTES_LONG,
);

$spreadsheet->getActiveSheet()->getStyle('A2')
    ->getNumberFormat()
    ->setFormatCode($timeFormat);

// yyyy-mm-dd hh:mm
$dateTimeFormat = new DateTimeWizard(' ', $dateFormat, $timeFormat);

$spreadsheet->getActiveSheet()->getStyle('A3')
    ->getNumberFormat()
    ->setFormatCode($dateTimeFormat);
```

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

### NETWORKDAYS function syntax

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Syntax for the NETWORKDAYS function, which returns the number of whole working days between a start and end date, excluding weekends and holidays.

```excel
NETWORKDAYS(startDate, endDate [, holidays])
```

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

### Open template, fill fields, and save as Xls

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file

Loads an existing xlsx template, sets values in cells A1 and A2, and saves as an xls file. Demonstrates that loading xlsx and saving as xls is allowed.

```php
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('template.xlsx');

$worksheet = $spreadsheet->getActiveSheet();

$worksheet->getCell('A1')->setValue('John');
$worksheet->getCell('A2')->setValue('Smith');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('write.xls');
```

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

### Get Current Return Date Type

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Calls \PhpOffice\PhpSpreadsheet\Calculation\Functions::getReturnDateType() to retrieve the current return date type setting. The default is RETURNDATE_PHP_NUMERIC.

```php
$returnDateType = \PhpOffice\PhpSpreadsheet\Calculation\Functions::getReturnDateType();
Copy
```

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

### Load spreadsheet with charts using IOFactory::load()

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file

Use IOFactory::load() with the LOAD_WITH_CHARTS flag to load a spreadsheet with charts.

```php
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(
    "spreadsheetWithCharts.xlsx",
    \PhpOffice\PhpSpreadsheet\Reader\IReader::LOAD_WITH_CHARTS
);
Copy
```

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

### Combine Named Ranges and Formulae for a Client Timesheet

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/defined-names

Builds a timesheet with named ranges (CHARGE_RATE, HOURS_PER_DAY, COLUMN_DATA_VALUES) and named formulae (DAILY_CHARGE, COLUMN_TOTALS). Demonstrates that named ranges require a worksheet, while named formulae can use null. Also shows that definition order can be flexible as long as all names exist before use.

```php
// Set up some basic data for a timesheet
$worksheet
    ->setCellValue('A1', 'Charge Rate/hour:')
    ->setCellValue('B1', '7.50')
    ->setCellValue('A3', 'Date')
    ->setCellValue('B3', 'Hours')
    ->setCellValue('C3', 'Charge');

// Define named ranges
// CHARGE_RATE is an absolute cell reference that always points to cell B1
$spreadsheet->addNamedRange(new NamedRange('CHARGE_RATE', $worksheet, '=$B$1'));
// HOURS_PER_DAY is a relative cell reference that always points to column B, but to a cell in the row where it is used
$spreadsheet->addNamedRange(new NamedRange('HOURS_PER_DAY', $worksheet, '=$B1'));
// Set up the formula for calculating the daily charge
$spreadsheet->addNamedFormula(new NamedFormula('DAILY_CHARGE', null, '=HOURS_PER_DAY*CHARGE_RATE'));
// Set up the formula for calculating the column totals
$spreadsheet->addNamedFormula(new NamedFormula('COLUMN_TOTALS', null, '=SUM(COLUMN_DATA_VALUES)'));


$workHours = [
    '2020-0-06' => 7.5,
    '2020-0-07' => 7.25,
    '2020-0-08' => 6.5,
    '2020-0-09' => 7.0,
    '2020-0-10' => 5.5,
];

// Populate the Timesheet
$startRow = 4;
$row = $startRow;
foreach ($workHours as $date => $hours) {
    $worksheet
        ->setCellValue("A{$row}", $date)
        ->setCellValue("B{$row}", $hours)
        ->setCellValue("C{$row}", '=DAILY_CHARGE');
    ++$row;
}
$endRow = $row - 1;

// COLUMN_TOTAL is another relative cell reference that always points to the same range of rows but to cell in the column where it is used
$spreadsheet->addNamedRange(new NamedRange('COLUMN_DATA_VALUES', $worksheet, "=A\${$startRow}:A\${$endRow}"));

++$row;
$worksheet
    ->setCellValue("B{$row}", '=COLUMN_TOTALS')
    ->setCellValue("C{$row}", '=COLUMN_TOTALS');

echo sprintf(
    'Worked %.2f hours at a rate of %.2f - Charge to the client is %.2f',
    $worksheet->getCell("B{$row}")->getCalculatedValue(),
    $worksheet->getCell('B1')->getValue(),
    $worksheet->getCell("C{$row}")->getCalculatedValue()
), PHP_EOL;

```

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

### Remove columns starting at a specific column

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes

Removes 2 columns starting at column 'C' (columns C and D). All subsequent columns are moved left, and formulas referencing those cells are adjusted accordingly.

```php
$spreadsheet->getActiveSheet()->removeColumn('C', 2);
```

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

### Create spreadsheet and write data with Advanced Value Binder

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/The%20Dating%20Game

Creates a new Spreadsheet, sets the Advanced Value Binder to convert string dates/times, writes the heading and data arrays, and adds formulas to calculate durations and total hours.

```php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
// Use the Advanced Value Binder so that our string date/time values will be automatically converted
//     to Excel serialized date/timestamps
// Old method using static property
Cell::setValueBinder(new AdvancedValueBinder());
// Preferred method using dynamic property since 3.4.0
$spreadsheet->setValueBinder(new AdvancedValueBinder());

// Write our data to the worksheet
$worksheet->fromArray($projectHeading);
$worksheet->fromArray($weekHeading, null, 'A3');

// Let Excel calculate the duration for each timesheet entry
$row = 4;
foreach ($timesheetData as $timesheetEntry) {
    ++$row;
    $worksheet->fromArray($timesheetEntry, null, "A{$row}");
    $worksheet->setCellValue("D{$row}", "=C{$row} - B{$row}");
}
$totalRow = $row + 2;
$worksheet->setCellValue("D{$totalRow}", "=SUM(D4:D{$row})");
$worksheet->setCellValue("E{$totalRow}", 'Total Hours');
```

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

### Get Current Excel Calendar

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine

Retrieves the current Excel calendar setting. Shows both the static method on \PhpOffice\PhpSpreadsheet\Shared\Date and the instance method on a spreadsheet object. The default is CALENDAR_WINDOWS_1900.

```php
$baseDate = \PhpOffice\PhpSpreadsheet\Shared\Date::getExcelCalendar(); // static
$baseDate = $spreadsheet->getExcelCalendar(); // instance
Copy
```

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

### Create a reader and load a file with a specified reader

Source: https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files

Instantiates a specific reader class (e.g., Xls) and loads a file into a Spreadsheet object. Commented lines show alternative reader classes for other formats. The file type must match the reader to avoid unpredictable results.

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

/** Create a new Xls Reader  **/
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xml();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Ods();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Slk();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Gnumeric();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
/** Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);
```
Tokens:
Raw