Behind the Mask

When we look at a spreadsheet in MS Excel, we normally see it neatly formatted so that it is easy for a human to read.

Internally, that spreadsheet comprises a set of values that are normally either numbers or text (occasionally boolean TRUE or FALSE); or a formula that results in a number, text or boolean value. Unlike PHP, MS Excel doesn't differentiate between integer or float; but all numbers can be presented as integer or with decimals, as dates or percentages, as currency, even made to look like telephone numbers. A zero value can be made to look like 0 or 0.00, like -, or even like a text string zero. Positive values can be displayed in one colour, negative values in another.

 Behind this Stock Portfolio table example, with the exception of the headings and the stock symbols, every value is a number; but each column is rendered in a manner that provides meaning to our human eye - Purchase Date as a day-month-year date; Purchase Price and Current Price as monetary values with a currency code; Purchase Quantity as an integer value and Difference as a float with 2 decimals; % Return as a percentage; and Profit/Loss as a monetary value with a currency code, thousands separator, and negative values highlighted in red; Stdev with 3 decimals - and all styled by using a Number Format Mask.

Reading a Cell Value

PhpSpreadsheet provides three methods for reading a Cell value. If we use the Cell's getValue() method, we are retrieving the underlying value (or the formula) for that cell. If the Cell contains a formula, then we can use the getCalculatedValue() method to see the result of evaluating that formula. If we want to see the value as it is displayed in MS Excel, then we need to use getFormattedValue().

Reading Cells from the Worksheet shown above:

var_dump($worksheet->getCell('C4')->getValue());
var_dump($worksheet->getCell('C4')->getCalculatedValue());
var_dump($worksheet->getCell('C4')->getFormattedValue());

var_dump($worksheet->getCell('H4')->getValue());
var_dump($worksheet->getCell('H4')->getCalculatedValue());
var_dump($worksheet->getCell('H4')->getFormattedValue());

we see the different results for cell C4 (a simple numeric value formatted as a Currency) and cell H4 (a formula that evaluates to a numeric value, and formatted as a Currency):

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

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

Note that getting the formatted value will always evaluate a formula to render the result.

Reading a Cell's Formatting Mask

PhpSpreadsheet also provides methods that allow us to look at the format mask itself:

var_dump($worksheet->getCell('C4')
    ->getStyle()->getNumberFormat()->getFormatCode());

var_dump($worksheet->getCell('H4')
    ->getStyle()->getNumberFormat()->getFormatCode());

and we can see the Format Masks for those cells:

string(20) "[$€-413]\ #,##0.00"

string(48) "[$€-413]\ #,##0.00;[Red][$€-413]\ \-#,##0.00"

Note: that the space and sign in the mask are non-breaking characters, so they are rendered to output as "\ " and "-" respectively when var_dumped. This prevents breaking the displayed value across two lines.

Setting a Cell's Formatting Mask

When you are using a spreadsheet application like MS Excel, the application will try to decide what Format Mask should be used for a cell as you enter the value, based on that value and your locale settings; and with varying degrees of success. If the value looks like a Currency, then it will be converted to a number and an appropriate Currency Mask set; similarly if you type something that looks like a percentage; and it is often a joke that Excel identifies many values as Dates (even if that was never the intent), and sets a Date Format Mask. The default Mask if no specific type can be identified from the value is "General".

PhpSpreadsheet doesn't do this by default. If you enter a value in a cell, then it will not convert that value from a string containing a currency symbol to a number: it will remain a string. Nor will it change any existing Format Mask: and if that value is a new cell, then it will be assigned a default Format Mask of "General". It will convert a string value to a numeric if it looks like a number with or without decimals (but without leading zeroes), or in scientific format; but it still won't change the Format Mask.

// Set Cell C21 using a formatted string value
$worksheet->getCell('C20')->setValue('€ -1234.567');

// The Cell value should be the string that we set
var_dump($worksheet->getCell('C20')->getValue());
// The Format Mask should be "General"
var_dump($worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()->getFormatCode());
// The formatted value should still be the string that we set
var_dump($worksheet->getCell('C20')->getFormattedValue());

// Set Cell C21 using a numeric value
$worksheet->getCell('C21')->setValue('-1234.567');

// The numeric string value should have been converted to a float
var_dump($worksheet->getCell('C21')->getValue());
// The Format Mask should be "General"
var_dump($worksheet->getCell('C21')
    ->getStyle()->getNumberFormat()->getFormatCode());
var_dump($worksheet->getCell('C21')->getFormattedValue());

// Change the Format Mask for C21 to a Currency mask
$worksheet->getCell('C21')
        ->getStyle()->getNumberFormat()->setFormatCode('€ #,##0;€ -#,##0');

// The float value should still be the same
var_dump($worksheet->getCell('C21')->getValue());
// The Format Mask should be the new mask that we set
var_dump($worksheet->getCell('C21')
    ->getStyle()->getNumberFormat()->getFormatCode());
// The value should now be formatted as a Currency
var_dump($worksheet->getCell('C21')->getFormattedValue());

giving

string(13) "€ -1234.567"
string(7) "General"
string(13) "€ -1234.567"

float(-1234.567)
string(7) "General"
string(9) "-1234.567"

float(-1234.567)
string(20) "€ #,##0;€ -#,##0"
string(10) "€ -1,235"

If you wish to emulate the MS Excel behaviour, and automatically convert string values that represent Currency, Dates, Fractions, Percentages, etc. then the Advanced Value Binder attempts to identify these, to convert to a number, and to set an appropriate Format Mask.

You can do this by changing the Value Binder, which will then apply every time you set a Cell value.

// Old method using static property
Cell::setValueBinder(new AdvancedValueBinder());
// Preferred method using dynamic property since 3.4.0
$spreadsheet->setValueBinder(new AdvancedValueBinder());

// Set Cell C21 using a formatted string value
$worksheet->getCell('C20')->setValue('€ -12345.6789');

// The Cell value is a float of -12345.6789
var_dump($worksheet->getCell('C20')->getValue());
// The format code is "[$€]#,##0.00_-"
var_dump($worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()->getFormatCode());
// The formatted value is  "€-12,345.68 "
var_dump($worksheet->getCell('C20')->getFormattedValue());

Or (since version 1.28.0) you can specify a Value Binder to use just for that one call to set the Cell's value.

// Set Cell C21 using a formatted string value, but using a Value Binder
$worksheet->getCell('C20')->setValue('€ -12345.6789', new AdvancedValueBinder());

// The Cell value is a float of -12345.6789
var_dump($worksheet->getCell('C20')->getValue());
// The format code is "[$€]#,##0.00_-"
var_dump($worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()->getFormatCode());
// The formatted value is  "€-12,345.68 "
var_dump($worksheet->getCell('C20')->getFormattedValue());

While PhpSpreadsheet's Advanced Value Binder isn't as "sophisticated" as MS Excel at recognising formats that should be converted to numbers, or at setting a mask that exactly matches the entered value, it can simplify entering data from formatted strings; and is particularly useful when reading untyped or loosely formatted files like a CSV.

Warning: Remember that setting a Cell value explicitly bypasses the Value Binder, so you will always have to set the Format Mask manually if you are using setValueExplicit() to set Cell values.

Using Formatting Masks in the TEXT() Function

We can also use Number Formatting Masks directly in Excel's TEXT() Function, without setting the mask for a Cell.

$worksheet->getCell('A1')->setValue(12345.678);
$worksheet->getCell('B1')
    ->setValue('#.00" Surplus";-#.00" Deficit";"Out of Stock"');
$worksheet->getCell('C1')->setValue('=TEXT(A1,B1)');

var_dump($worksheet->getCell('C1')->getCalculatedValue()); // 12,345.68 Surplus


$worksheet->getCell('A2')->setValue(-12345.678);
$worksheet->getCell('C2')
    ->setValue('=TEXT(A2,"#,##0.00"" Surplus"";-#,##0.00"" Deficit"";""Out of Stock""")');

var_dump($worksheet->getCell('C2')->getCalculatedValue()); // -12,345.68 Deficit

Remember that you'll need to escape double quotes in the mask argument by double double-quoting them if you pass the mask directly as an string. It's generally easier to read if you store the mask as text in a cell, and then pass the cell reference as the mask argument.

Changing a Cell's Formatting Mask

In PhpSpreadsheet we can change a Cell's Formatting Mask at any time just by setting a new FormatCode for that Cell. The library provides a number of "pre-defined" Masks as Constants in the NumberFormat class, prefixed with 'FORMAT_', but isn't limited to these values - the mask itself is just a string value - and the value passed to setFormatCode() can be any valid Excel Format Mask string.

Note: The Mask value isn't validated: it's up to you, as the developer, to ensure that you set a meaningful Mask value.

And while Excel applies an initial Mask to every Cell when we enter a value (even if it's just the default "General"), we can still always change that Mask. This is managed through the "Number" block in the "Home" ribbon.


This provides us with some simple options for increasing or decreasing the number of decimals displayed, if we want a thousands separator, a currency code to use, etc.

But if we use the "pull down" for that block, we access the "Number" tab of "Format Cells" that provides a lot more options.


This gives us access to a number of "Wizards" for different "Categories" of masking, as well as "Custom", which allows us to build our own masks.

Since version 1.28.0, PhpSpreadsheet has also provided a set of "Wizards", allowing for the easier creation of Mask values for most Categories. In many cases, you will need to enable PHP's Intl extension in order to use the Wizards.

Mask Categories

I'll describe "Custom" Mask values later in this article; but let's take a look at the "Wizard" options for each "Category" first.

General

This is the default Mask, and is "adaptive". Numbers will appear with as many decimals as have been entered for the value (to the limit of a 9 or 10 digit display; additional decimals will be rounded), while very large or very small values will display in Scientific format.

Number

Excel's Number "Wizard" allows you to specify the number of decimals, and whether to use a thousands separator (or not). It also offers a few ways to display negative values (with or without a sign, highlighted in red).


A typical mask will look something like '0.00' (2 decimals, with no thousands separator) or '#,##0.000' (3 decimals with a thousands separator).

The PhpSpreadsheet Number "Wizard" allows you to specify the number of decimals, and the use of a thousands separator. The defaults are 2 decimal places, and to use a thousands separator.

use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Number;

// Set Cell value
$worksheet->getCell('C20')->setValue(-12345.67890);

// Set Cell Style using the Number Wizard to build the Format Mask
$worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()
    ->setFormatCode((string) new Number(3, Number::WITH_THOUSANDS_SEPARATOR));

var_dump($worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()->getFormatCode()); // "#,##0.000"
var_dump($worksheet->getCell('C20')->getFormattedValue()); // "-12,345.679"

PhpSpreadsheet's Number Wizard doesn't yet offer options for displaying negative values; they will simply be masked so that they always display the sign. But alternative masking for negative values is an option that may be added in the future.

Currency

The Currency "Wizard" in MS Excel has similar options to the Number "Wizard", but also requires that you specify a currency code.


The "Symbol" dropdown provides a lot of locale-specific variants of the same currencies - for example '€ Netherlands', where the currency symbol is displayed before the value, and any negative sign appears before the currency "-€ 12,345.68"; or '€ France', where the symbol is displayed after the value "-12,345.68 €".

The PhpSpreadsheet Currency "Wizard" allows you to specify the currency code, number of decimals, and the use of a thousands separator. In addition, optionally, you can also specify whether the currency symbol should be leading or trailing, and whether it should be separated from the value or not. Finally, you have a choice of 4 ways of specifying negative values - minus sign, minus sign with the field in red, parentheses, and parentheses with the field in red.

use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Currency;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Number;

// Set Cell value
$worksheet->getCell('C20')->setValue(-12345.67890);

// Set Cell Style using the Currency Wizard to build the Format Mask
$currencyMask = new Currency(
    '€',
    2,
    Number::WITH_THOUSANDS_SEPARATOR,
    Currency::TRAILING_SYMBOL,
    Currency::SYMBOL_WITH_SPACING
);
$worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()
    ->setFormatCode($currencyMask);

var_dump($worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()->getFormatCode()); // #,##0.00 €
var_dump($worksheet->getCell('C20')->getFormattedValue()); // -12,345.68 €

A typical Currency mask might look something like '#,##0.00 €', with the currency symbol as a literal.

The Currency Code itself may be a literal character, as here with the € symbol; or it can be wrapped in square braces with a $ symbol to indicate that this is a currency and the next character as the currency symbol to use, and then (optionally) a locale code or an LCID (Locale ID) like [$€-de-DE] or [$€-1031].

I wouldn't recommend using LCIDs in your code, a locale code is a lot easier to recognise and understand; but if you do need to reference LCIDs, then you can find a list here.

Alternatively, if you have PHP's Intl extension installed, you can specify a currency code and a locale code. If you use this option, then locale values must be a valid formatted locale string (e.g. en-GB, fr, uz-Arab-AF); and the Wizard will use the format defined in ICU (International Components for Unicode): any values that you provide for placement of the currency symbol, etc. will be ignored. The only argument that won't be ignored is an explicit value of 0 for the decimals, which will create a mask to display only major currency units.

use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Currency;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Number;

// Set Cell value
$worksheet->getCell('C21')->setValue(-12345.67890);

// Set Cell Style using the Currency Wizard to build the Format Mask for a locale
$localeCurrencyMask = new Currency(
    '€',
    locale: 'de_DE'
);
$worksheet->getCell('C21')
    ->getStyle()->getNumberFormat()
    ->setFormatCode($localeCurrencyMask);

var_dump($worksheet->getCell('C21')
    ->getStyle()->getNumberFormat()->getFormatCode()); // #,##0.00 [$€-de-DE]
var_dump($worksheet->getCell('C21')->getFormattedValue()); // -12,345.68 €

If we use the locale in the "Wizard", then a typical mask might look like '#,##0.00 [$€-de-DE]', with the currency wrapped in braces, a $ to indicate that this is a localised value, and the locale included.

Note: The Wizard does not accept LCIDs.

Accounting

Excel's Accounting "Wizard" is like the Currency "Wizard", but without the options for presenting negative values. Presentation of zero and negative values is dependent on the currency and locale.


The options available for the PhpSpreadsheet Accounting "Wizard" are identical to those of the Currency "Wizard"; although the generated Mask is different.

use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Accounting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Currency;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Number;

// Set Cell value
$worksheet->getCell('C20')->setValue(-12345.67890);

// Set Cell Style using the Accounting Wizard to build the Format Mask
$currencyMask = new Accounting(
    '€',
    2,
    Number::WITH_THOUSANDS_SEPARATOR,
    Currency::TRAILING_SYMBOL,
    Currency::SYMBOL_WITH_SPACING
);
$worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()
    ->setFormatCode($currencyMask);

var_dump($worksheet->getCell('C20')
    ->getStyle()->getNumberFormat()->getFormatCode()); // _-#,##0.00 €*_-
var_dump($worksheet->getCell('C20')->getFormattedValue()); //  -12,345.68 €

A typical Accounting mask might look something like '-#,##0.00 €*-', with the currency symbol as a literal; and with placement indicators like _-, that ensure the alignment of the currency symbols and decimal points of numbers in a column.

As with using a locale with the Currency "Wizard", when you use a locale with the Accounting "Wizard" the locale value must be valid, and any additional options will be ignored.

use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Accounting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Currency;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat\Wizard\Number;

// Set Cell value
$worksheet->getCell('C21')->setValue(-12345.67890);

// Set Cell Style using the Accounting Wizard to build the Format Mask for a locale
$localeCurrencyMask = new Accounting(
    '€',
    locale: 'nl_NL'
);
$worksheet->getCell('C21')
    ->getStyle()->getNumberFormat()
    ->setFormatCode($localeCurrencyMask);

var_dump($worksheet->getCell('C21')
    ->getStyle()->getNumberFormat()->getFormatCode()); // [$€-nl-NL] #,##0.00;([$€-nl-NL] #,##0.00)
var_dump($worksheet->getCell('C21')->getFormattedValue()); // (€ 12,345.68)

If we use the locale in the "Wizard", then a typical mask might look like '[$€-nl-NL] #,##0.00;([$€-nl-NL] #,##0.00)', with the currency wrapped in braces, with a $ to indicate that this is a localised value, and the locale included. And in this case, there is masking for zero and for negative values, although without colour. An option to add colour to values is an option that may be added in a future release.

Warning: Not all versions of the ICU (International Components for Unicode) support Accounting formats, so even if your PHP does have 'Intl' enabled, it may still not allow the use of locale for generating an Accounting Mask.

Date

When you use the Excel Date "Wizard", you can select a locale and you'll then be presented with a number of date format options that are appropriate for that locale.


I've written in detail about Date Format Masks elsewhere in "The Dating Game"; but to summarise, here are the Mask codes used for Date formatting.

Time

As with Dates, when you use the Excel Time "Wizard", you can select a locale and you'll then be presented with a number of time format options that are appropriate for that locale.


I've written in detail about Time Format Masks elsewhere in "The Dating Game"; but to summarise, here are the Mask codes used for Time formatting.