Recipes

The following pages offer you some widely-used PhpSpreadsheet recipes. Please note that these do NOT offer complete documentation on specific PhpSpreadsheet API functions, but just a bump to get you started. If you need specific API functions, please refer to the API documentation.

For example, setting a worksheet's page orientation and size covers setting a page orientation to A4. Other paper formats, like US Letter, are not covered in this document, but in the PhpSpreadsheet API documentation.

My apologies if this documentation seems very basic to some of you; but I spend so much time having to provide help lessons in PHP 101 and Excel 101 that I feel I need to provide this level of very simple detail.

Setting a spreadsheet's metadata

PhpSpreadsheet allows an easy way to set a spreadsheet's metadata, using document property accessors. Spreadsheet metadata can be useful for finding a specific document in a file repository or a document management system. For example Microsoft Sharepoint uses document metadata to search for a specific document in its document lists.

These are accessed in MS Excel from the "Info" option on the "File" menu:

Some of these properties can be edited "in situ" in the Properties Block:

For more advanced properties, click on the "Properties" dropdown:

And you will be able to add/edit/delete a lot of different property values.

Properties on the "General", "Statistics" and "Contents" tabs are informational, and cannot be user-defined in Excel itself. Properties on the "Summary" tab are all string values.

The "Custom" tab allows you to define your own properties. More information from the Microsoft Documentation can be found here.

You can select a property name from the dropdown, or type a new name of your choice; select a Type; enter a value; and then click on "Add". The new property will then be created and displayed in the list at the bottom of the form.

While "Text", "Number" (can be an integer or a floating point value) and "Yes or No" types are straightforward to add a value, "Date" types are more difficult, and Microsoft provide very little help. However, you need to enter the date in the format that matches your locale, so an American would enter "7/4/2023 for the 4th of July; but in the UK I would enter "4/7/2023" for the same date. Although typically recognised as a date elsewhere in MS Excel, the almost universally recognised 2022-12-31 date format is not recognised as valid here.

Setting spreadsheet metadata in PhpSpreadsheet is done as follows:

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

You can choose which properties to set or ignore.

PhpSpreadsheet provides specific getters/setters for a number of pre-defined properties.

Note: Not all Spreadsheet File Formats support all of these properties. For example: "Category", "Company" and "Manager" are not supported in xls files.

Additionally, PhpSpreadsheet supports the creation and reading of custom properties for those file formats that accept custom properties. The following methods of the Properties class can be used when working with custom properties.

• getCustomProperties() Will return an array listing the names of all custom properties that are defined.
• isCustomPropertySet(string $propertyName) Will return a boolean indicating if the named custom property is defined.
• getCustomPropertyValue(string $propertyName) Will return the "raw" value of the named custom property; or null if the property doesn't exist.
• getCustomPropertyType(string $propertyName) Will return the datatype of the named custom property; or null if the property doesn't exist.
• setCustomProperty(string $propertyName, $propertyValue = '', $propertyType = null) Will let you set (or modify) a custom property. If you don't provide a datatype, then PhpSpreadsheet will attempt to identify the datatype from the value that you set.

The recognised Property Types are:

When reading property types, you might also encounter:

Other more complex types, such as pointers and filetime, are not supported by PhpSpreadsheet; and are discarded when reading a file.

$spreadsheet->getProperties()
    ->setCustomProperty('Editor', 'Mark Baker')
    ->setCustomProperty('Version', 1.17)
    ->setCustomProperty('Tested', true)
    ->setCustomProperty('Test Date', '2021-03-17', Properties::PROPERTY_TYPE_DATE);

Warning: If the datatype for a date is not explicitly used, then it will be treated as a string.

Note: Although MS Excel doesn't recognise 2022-12-31 as valid date format when entering Custom Date Properties, PhpSpreadsheet will accept it.

Setting a spreadsheet's active sheet

A Spreadsheet consists of (very rarely) none, one or more Worksheets. If you have 1 or more Worksheets, then one (and only one) of those Worksheets can be "Active" (viewed or updated) at a time, but there will always be an "Active" Worksheet (unless you explicitly delete all of the Worksheets in the Spreadsheet).

When you create a new Spreadsheet in MS Excel, it creates the Spreadsheet with a single Worksheet ("Sheet1")

and that is the "Active" Worksheet.

This is the same as

$spreadsheet = new Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();

in PhpSpreadsheet.

And you can then write values to Cells in $activeWorksheet (Sheet1).

To create a new Worksheet in MS Excel, you click on the "+" button in the Worksheet Tab Bar. MS Excel will then create a new Worksheet ("Sheet2") in the Spreadsheet, and make that the current "Active" Worksheet.

Excel always shows the "Active" Worksheet in the Grid, and you can see which Worksheet is "Active" because it is highlighted in the Worksheet Tab Bar at the bottom of the Worksheet Grid.

This is the same as

$activeWorksheet = $spreadsheet->createSheet();

in PhpSpreadsheet.

And you can then write values to Cells in $activeWorksheet (Sheet2).

To switch between Worksheets in MS Excel, you click on the Tab for the Worksheet that you want to be "Active" in the Worksheet Tab Bar. Excel will then set that as the "Active" Worksheet.

In PhpSpreadsheet, you do this by calling the Spreadsheet's setActiveSheetIndex() methods. Either:

$activeWorksheet = $spreadsheet->setActiveSheetIndexByName('Sheet1')

using the name/title of the Worksheet that you want as the "Active" Worksheet.

Or:

$activeWorksheet = $spreadsheet->setActiveSheetIndex(0);

Where you set the "Active" Worksheet by its position in the Worksheet Tab Bar, with 0 as the first Worksheet, 1 as the second, etc.

And you can then write values to Cells in $activeWorksheet (Sheet1) again.

You don't have to assign the return value from calls to createSheet() and setActiveSheetIndex() to a variable, but it means that you can call Worksheet methods directly against $activeWorksheet, rather than having to call $spreadsheet->getActiveSheet() all the time. And, unlike MS Excel where you can only update Cells in the "Active" Worksheet; PhpSpreadsheet allows you to update Cells in any Worksheet:

// Create a Spreadsheet, with Worksheet Sheet1, which is the Active Worksheet
$spreadsheet = new Spreadsheet();
// Assign the Active Worksheet (Sheet1) to $worksheet1
$worksheet1 = $spreadsheet->getActiveSheet();
// Create a new Worksheet (Sheet2) and make that the Active Worksheet
$worksheet2 = $spreadsheet->createSheet();

$worksheet1->setCellValue('A1', 'I am a cell on Sheet1');
$worksheet2->setCellValue('A1', 'I am a cell on Sheet2');

Write a date or time into a cell

In Excel, dates and Times are stored as numeric values counting the number of days elapsed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can verify this in Microsoft Office Excel by entering that date in a cell and afterwards changing the number format to 'General' so the true numeric value is revealed. Likewise, '3:15 AM' is represented as 0.135417.

PhpSpreadsheet works with UST (Universal Standard Time) date and Time values, but does no internal conversions; so it is up to the developer to ensure that values passed to the date/time conversion functions are UST.

Writing a date value in a cell consists of 2 lines of code. Select the method that suits you the best. Here are some examples:

// MySQL-like timestamp '2008-12-31' or date string
// Old method using static property
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
// Preferred method using dynamic property since 3.4.0
$spreadsheet->setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

$spreadsheet->getActiveSheet()
    ->setCellValue('D1', '2008-12-31');

$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

// PHP-time (Unix time)
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$spreadsheet->getActiveSheet()
    ->setCellValue('D1', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($time));
$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

// Excel-date/time
$spreadsheet->getActiveSheet()->setCellValue('D1', 39813)
$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

The above methods for entering a date all yield the same result. The \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel() method will also work with a PHP DateTime object; or with strings containing different well-recognised date formats (although this is limited in the same ways as using the Advanced Value Binder).

Similarly, times (or date and time values) can be entered in the same fashion: just remember to use an appropriate format code.

Note: See section "Using value binders to facilitate data entry" to learn more about the AdvancedValueBinder used in the first example. Excel can also operate in a 1904-based calendar (default for workbooks saved on Mac). Normally, you do not have to worry about this when using PhpSpreadsheet.

\PhpOffice\PhpSpreadsheet\Style\NumberFormat provides a number of pre-defined date formats; but this is just a string value, and you can define your own values as long as they are a valid MS Excel format. PhpSpreadsheet also provides a number of Wizards to help you create Date, Time and DateTime format masks.

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

Write a formula into a cell

Inside the Excel file, formulas are always stored as they would appear in an English version of Microsoft Office Excel, and PhpSpreadsheet handles all formulas internally in this format. This means that the following rules hold:

• Decimal separator is . (period)
• Function argument separator is , (comma)
• Matrix row separator is ; (semicolon)
• English function names must be used

This is regardless of which language version of Microsoft Office Excel may have been used to create the Excel file.

When the final workbook is opened by the user, Microsoft Office Excel will take care of displaying the formula according the applications language. Translation is taken care of by the application!

The following line of code writes the formula =IF(C4>500,"profit","loss") into the cell B8. Note that the formula must start with = to make PhpSpreadsheet recognise this as a formula.

$spreadsheet->getActiveSheet()->setCellValue('B8','=IF(C4>500,"profit","loss")');

If you want to write a string beginning with an = character to a cell, then you should use the setCellValueExplicit() method.

$spreadsheet->getActiveSheet()
    ->setCellValueExplicit(
        'B8',
        '=IF(C4>500,"profit","loss")',
        \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
    );

A cell's formula can be read again using the following line of code:

$formula = $spreadsheet->getActiveSheet()->getCell('B8')->getValue();

If you need the calculated value of a cell, use the following code. This is further explained in the calculation engine.

$value = $spreadsheet->getActiveSheet()->getCell('B8')->getCalculatedValue();

Array Formulas

With version 3.0.0 of PhpSpreadsheet, we've introduced support for Excel "array formulas". It is an opt-in feature. You need to enable it with the following code:

// preferred method
\PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance($spreadsheet)
    ->setInstanceArrayReturnType(
        \PhpOffice\PhpSpreadsheet\Calculation\Calculation::RETURN_ARRAY_AS_ARRAY);
// or less preferred
\PhpOffice\PhpSpreadsheet\Calculation\Calculation::setArrayReturnType(
    \PhpOffice\PhpSpreadsheet\Calculation\Calculation::RETURN_ARRAY_AS_ARRAY);

This is not a new constant, and setArrayReturnType is also not new, but it has till now not had much effect. The instance variable set by the new setInstanceArrayReturnType will always be checked first, and the static variable used only if the instance variable is uninitialized.

As a basic example, let's look at a receipt for buying some fruit:

We can provide a "Cost" formula for each row of the receipt by multiplying the "Quantity" (column B) by the "Price" (column C); so for the "Apples" in row 2 we enter the formula =$B2*$C2. In PhpSpreadsheet, we would set this formula in cell D2 using:

$spreadsheet->getActiveSheet()->setCellValue('D2','=$B2*$C2');

and then do the equivalent for rows 3 to 6.

To calculate the "Total", we would use a different formula, telling it to calculate the sum value of rows 2 to 6 in the "Cost" column:

I'd imagine that most developers are familiar with this: we're setting a formula that uses an Excel function (the SUM() function) and specifying a range of cells to include in the sum ($D$2:$D6)

$spreadsheet->getActiveSheet()->setCellValue('D7','=SUM($D$2:$D6');

However, we could have specified an alternative formula to calculate that result, using the arrays of the "Quantity" and "Cost" columns multiplied directly, and then summed together:

Entering the formula =SUM(B2:B6*C2:C6) will calculate the same result; but because it's using arrays, we need to enter it as an "array formula". In MS Excel itself, we'd do this by using Ctrl-Shift-Enter rather than simply Enter when we define the formula in the formula edit box. MS Excel then shows that this is an array formula in the formula edit box by wrapping it in the {} braces (you don't enter these in the formula yourself; MS Excel does it).

In recent releases of Excel, Ctrl-Shift-Enter is not required, and Excel does not add the braces. PhpSpreadsheet will attempt to behave like the recent releases.

Or to identify the biggest increase in like-for-like sales from one month to the next:

$spreadsheet->getActiveSheet()->setCellValue('F1','=MAX(B2:B6-C2:C6)');

Which tells us that the biggest increase in sales between December and January was 30 more (in this case, 30 more Lemons).

These are examples of array formula where the results are displayed in a single cell; but other array formulas might be displayed across several cells. As an example, consider transposing a grid of data: MS Excel provides the TRANSPOSE() function for that purpose. Let's transpose our shopping list for the fruit:

When we do this in MS Excel, we used to need to indicate all the cells that will contain the transposed data from cells A1 to D7. We do this by selecting the cells where we want to display our transposed data either by holding the left mouse button down while we move with the mouse, or pressing Shift and using the arrow keys. Once we've selected all the cells to hold our data, then we enter the formula TRANSPOSE(A1:D7) in the formula edit box, remembering to...