NumberFormat Class - PhpSpreadsheet

Overview

The NumberFormat class controls how numbers, dates, times, currencies, and percentages are displayed in cells. It provides pre-defined format codes and supports custom format strings.

Namespace

PhpOffice\PhpSpreadsheet\Style\NumberFormat

Getting a NumberFormat Object

// Via Style object
$numberFormat = $spreadsheet->getActiveSheet()->getStyle('A1')->getNumberFormat();

// Via chaining
$spreadsheet->getActiveSheet()->getStyle('A1')
    ->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);

Pre-defined Format Constants

General and Text

FORMAT_GENERAL

string

default:"'General'"

General number format (default)

FORMAT_TEXT

string

default:"'@'"

Text format (displays values as-is)

Number Formats

FORMAT_NUMBER

string

default:"'0'"

Integer format

FORMAT_NUMBER_0

string

default:"'0.0'"

Number with 1 decimal place

FORMAT_NUMBER_00

string

default:"'0.00'"

Number with 2 decimal places

FORMAT_NUMBER_COMMA_SEPARATED1

string

default:"'#,##0.00'"

Number with thousand separators and 2 decimals

FORMAT_NUMBER_COMMA_SEPARATED2

string

default:"'#,##0.00_-'"

Number with thousand separators, 2 decimals, and trailing space

Percentage Formats

FORMAT_PERCENTAGE

string

default:"'0%'"

Percentage (no decimals)

FORMAT_PERCENTAGE_0

string

default:"'0.0%'"

Percentage with 1 decimal place

FORMAT_PERCENTAGE_00

string

default:"'0.00%'"

Percentage with 2 decimal places

Date Formats

FORMAT_DATE_YYYYMMDD

string

default:"'yyyy-mm-dd'"

Date format: 2024-03-04

FORMAT_DATE_DDMMYYYY

string

default:"'dd/mm/yyyy'"

Date format: 04/03/2024

FORMAT_DATE_DMYSLASH

string

default:"'d/m/yy'"

Date format: 4/3/24

FORMAT_DATE_DMYMINUS

string

default:"'d-m-yy'"

Date format: 4-3-24

FORMAT_DATE_DMMINUS

string

default:"'d-m'"

Date format: 4-3

FORMAT_DATE_MYMINUS

string

default:"'m-yy'"

Date format: 3-24

FORMAT_DATE_XLSX14

string

default:"'mm-dd-yy'"

Date format: 03-04-24

FORMAT_DATE_XLSX15

string

default:"'d-mmm-yy'"

Date format: 4-Mar-24

FORMAT_DATE_XLSX16

string

default:"'d-mmm'"

Date format: 4-Mar

FORMAT_DATE_XLSX17

string

default:"'mmm-yy'"

Date format: Mar-24

FORMAT_DATE_LONG_DATE

string

default:"'dddd, mmmm d, yyyy'"

Long date format: Monday, March 4, 2024

Time Formats

FORMAT_DATE_TIME1

string

default:"'h:mm AM/PM'"

Time format: 2:30 PM

FORMAT_DATE_TIME2

string

default:"'h:mm:ss AM/PM'"

Time format: 2:30:45 PM

FORMAT_DATE_TIME3

string

default:"'h:mm'"

Time format: 14:30

FORMAT_DATE_TIME4

string

default:"'h:mm:ss'"

Time format: 14:30:45

FORMAT_DATE_TIME5

string

default:"'mm:ss'"

Time format: 30:45

FORMAT_DATE_TIME6

string

default:"'h:mm:ss'"

Time format: 14:30:45

FORMAT_DATE_TIME_INTERVAL_HMS

string

default:"'[hh]:mm:ss'"

Time interval format: [25]:30:45

DateTime Formats

FORMAT_DATE_DATETIME

string

default:"'d/m/yy h:mm'"

DateTime format: 4/3/24 14:30

FORMAT_DATE_DATETIME_BETTER

string

default:"'yyyy-mm-dd hh:mm'"

DateTime format: 2024-03-04 14:30

FORMAT_DATE_XLSX22

string

default:"'m/d/yy h:mm'"

DateTime format: 3/4/24 14:30

Currency Formats

FORMAT_CURRENCY_USD

string

default:"'$#,##0.00_-'"

US Dollar with 2 decimals: $1,234.56

FORMAT_CURRENCY_USD_INTEGER

string

default:"'$#,##0_-'"

US Dollar integer: $1,235

FORMAT_CURRENCY_EUR

string

default:"'#,##0.00_-[$€]'"

Euro with 2 decimals: 1,234.56€

FORMAT_CURRENCY_EUR_INTEGER

string

default:"'#,##0_-[$€]'"

Euro integer: 1,235€

FORMAT_CURRENCY_GBP

string

default:"'£#,##0.00_-'"

British Pound: £1,234.56

FORMAT_CURRENCY_YEN_YUAN

string

default:"'￥#,##0.00_-'"

Yen/Yuan: ￥1,234.56

FORMAT_ACCOUNTING_USD

string

US Dollar accounting format

FORMAT_ACCOUNTING_EUR

string

Euro accounting format

Methods

getFormatCode()

Get the format code.

public function getFormatCode(bool $extended = false): ?string

extended

bool

default:"false"

Whether to return extended format (converts system formats)

setFormatCode()

Set the format code.

public function setFormatCode(string $formatCode): static

formatCode

string

required

Format code string (use FORMAT_* constants or custom format)

Example:

$numberFormat->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);
$numberFormat->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);
$numberFormat->setFormatCode('0.000'); // Custom format

getBuiltInFormatCode()

Get the built-in format code index.

public function getBuiltInFormatCode(): false|int

Returns: Built-in format index or false if not a built-in format

setBuiltInFormatCode()

Set format using a built-in format code index.

public function setBuiltInFormatCode(int $formatCodeIndex): static

formatCodeIndex

int

required

Built-in format code index (0-49)

applyFromArray()

Apply number format from array.

public function applyFromArray(array $styleArray): static

styleArray

array

required

Array with ‘formatCode’ key

Example:

$numberFormat->applyFromArray([
    'formatCode' => NumberFormat::FORMAT_CURRENCY_EUR
]);

Static Methods

builtInFormatCode()

Get a built-in format code by index.

public static function builtInFormatCode(int $index): string

builtInFormatCodeIndex()

Get the index of a built-in format code.

public static function builtInFormatCodeIndex(string $formatCodeIndex): false|int

toFormattedString()

Convert a value to a formatted string.

public static function toFormattedString(
    mixed $value,
    string $format,
    ?array $callBack = null,
    bool $lessFloatPrecision = false
): string

value

mixed

required

Value to format

format

string

required

Format code

callBack

array

default:"null"

Optional callback function for additional formatting

lessFloatPrecision

bool

default:"false"

Use less precise float conversion

Complete Examples

Number Formatting

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

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

// Integer
$sheet->setCellValue('A1', 1234);
$sheet->getStyle('A1')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_NUMBER);

// 2 decimal places
$sheet->setCellValue('A2', 1234.5678);
$sheet->getStyle('A2')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_NUMBER_00);

// Thousands separator
$sheet->setCellValue('A3', 1234567.89);
$sheet->getStyle('A3')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

// Custom: 3 decimal places
$sheet->setCellValue('A4', 123.456789);
$sheet->getStyle('A4')->getNumberFormat()
    ->setFormatCode('0.000');

Percentage Formatting

// No decimals
$sheet->setCellValue('B1', 0.75); // Stored as 0.75
$sheet->getStyle('B1')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_PERCENTAGE); // Displays as 75%

// 2 decimal places
$sheet->setCellValue('B2', 0.7545);
$sheet->getStyle('B2')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00); // Displays as 75.45%

Currency Formatting

// US Dollars
$sheet->setCellValue('C1', 1234.56);
$sheet->getStyle('C1')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);

// Euros
$sheet->setCellValue('C2', 1234.56);
$sheet->getStyle('C2')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_CURRENCY_EUR);

// British Pounds
$sheet->setCellValue('C3', 1234.56);
$sheet->getStyle('C3')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_CURRENCY_GBP);

// Custom currency
$sheet->setCellValue('C4', 1234.56);
$sheet->getStyle('C4')->getNumberFormat()
    ->setFormatCode('"¥"#,##0.00'); // Japanese Yen

Date Formatting

use PhpOffice\PhpSpreadsheet\Shared\Date;

// Excel date values are days since 1900-01-01
$dateValue = Date::PHPToExcel('2024-03-04');

// yyyy-mm-dd format
$sheet->setCellValue('D1', $dateValue);
$sheet->getStyle('D1')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD);

// dd/mm/yyyy format
$sheet->setCellValue('D2', $dateValue);
$sheet->getStyle('D2')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);

// Long date format
$sheet->setCellValue('D3', $dateValue);
$sheet->getStyle('D3')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_LONG_DATE);

// Custom date format
$sheet->setCellValue('D4', $dateValue);
$sheet->getStyle('D4')->getNumberFormat()
    ->setFormatCode('mmm d, yyyy'); // Mar 4, 2024

Time Formatting

$timeValue = Date::PHPToExcel('2024-03-04 14:30:45');

// 12-hour format with AM/PM
$sheet->setCellValue('E1', $timeValue);
$sheet->getStyle('E1')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_TIME1);

// 24-hour format
$sheet->setCellValue('E2', $timeValue);
$sheet->getStyle('E2')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);

// Time with seconds
$sheet->setCellValue('E3', $timeValue);
$sheet->getStyle('E3')->getNumberFormat()
    ->setFormatCode('h:mm:ss AM/PM');

DateTime Formatting

$dateTimeValue = Date::PHPToExcel('2024-03-04 14:30:45');

// Standard datetime
$sheet->setCellValue('F1', $dateTimeValue);
$sheet->getStyle('F1')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_DATETIME);

// ISO datetime
$sheet->setCellValue('F2', $dateTimeValue);
$sheet->getStyle('F2')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_DATETIME_BETTER);

// Custom datetime
$sheet->setCellValue('F3', $dateTimeValue);
$sheet->getStyle('F3')->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd hh:mm:ss');

Text Format

// Force text format (preserves leading zeros, etc.)
$sheet->setCellValue('G1', '00123');
$sheet->getStyle('G1')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_TEXT);

// ZIP codes
$sheet->setCellValue('G2', '02134');
$sheet->getStyle('G2')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_TEXT);

Custom Number Formats

// Positive/Negative/Zero/Text format
$sheet->setCellValue('H1', 1234.56);
$sheet->getStyle('H1')->getNumberFormat()
    ->setFormatCode('#,##0.00_);[Red](#,##0.00);0.00;@');

// Phone number format
$sheet->setCellValue('H2', '5551234567');
$sheet->getStyle('H2')->getNumberFormat()
    ->setFormatCode('(000) 000-0000');

// Fractions
$sheet->setCellValue('H3', 0.75);
$sheet->getStyle('H3')->getNumberFormat()
    ->setFormatCode('# ?/?'); // Displays as 3/4

// Scientific notation
$sheet->setCellValue('H4', 12345678);
$sheet->getStyle('H4')->getNumberFormat()
    ->setFormatCode('0.00E+00');

Conditional Formatting

// Different colors for positive/negative
$sheet->getStyle('I1')->getNumberFormat()
    ->setFormatCode('[Green]#,##0.00;[Red](#,##0.00)');

// Show different text for different ranges
$sheet->getStyle('I2')->getNumberFormat()
    ->setFormatCode('[>=1000]"High: "#,##0;[>=100]"Medium: "#,##0;"Low: "#,##0');

Apply to Range

// Apply currency format to entire column
$sheet->getStyle('C:C')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);

// Apply date format to range
$sheet->getStyle('D1:D100')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD);

// Using applyFromArray
$sheet->getStyle('E1:E100')->applyFromArray([
    'numberFormat' => [
        'formatCode' => NumberFormat::FORMAT_PERCENTAGE_00
    ]
]);

Custom Format Syntax

Number formats can have up to 4 sections separated by semicolons:

Positive;Negative;Zero;Text

Format codes:

0 - Digit placeholder (shows 0 if no digit)
# - Digit placeholder (shows nothing if no digit)
. - Decimal point
, - Thousands separator
% - Percentage
@ - Text placeholder
[Color] - Color (Red, Blue, Green, etc.)
"text" - Literal text
[$currency] - Currency symbol

Examples:

// Custom formats
'0.00'                              // 2 decimal places
'#,##0.00'                          // Thousands separator
'$#,##0.00'                         // Currency
'0.00%;-0.00%;0.00%;@'             // Percentage with all sections
'[Green]#,##0;[Red](#,##0);0'      // Colored positive/negative
'"Qty: "#,##0'                     // With prefix text
'#,##0.00" units"'                 // With suffix text
'0.00E+00'                          // Scientific notation
'# ?/?'                             // Fraction
'[h]:mm:ss'                         // Time interval

Core Classes

Readers

Writers

Calculation

Styling

Charts & Graphics

​Overview

​Namespace

​Getting a NumberFormat Object

​Pre-defined Format Constants

​General and Text

​Number Formats

​Percentage Formats

​Date Formats

​Time Formats

​DateTime Formats

​Currency Formats

​Methods

​getFormatCode()

​setFormatCode()

​getBuiltInFormatCode()

​setBuiltInFormatCode()

​applyFromArray()

​Static Methods

​builtInFormatCode()

​builtInFormatCodeIndex()

​toFormattedString()

​Complete Examples

​Number Formatting

​Percentage Formatting

​Currency Formatting

​Date Formatting

​Time Formatting

​DateTime Formatting

​Text Format

​Custom Number Formats

​Conditional Formatting

​Apply to Range

​Custom Format Syntax

​See Also

Build docs developers (and LLMs) love

Overview

Namespace

Getting a NumberFormat Object

Pre-defined Format Constants

General and Text

Number Formats

Percentage Formats

Date Formats

Time Formats

DateTime Formats

Currency Formats

Methods

getFormatCode()

setFormatCode()

getBuiltInFormatCode()

setBuiltInFormatCode()

applyFromArray()

Static Methods

builtInFormatCode()

builtInFormatCodeIndex()

toFormattedString()

Complete Examples

Number Formatting

Percentage Formatting

Currency Formatting

Date Formatting

Time Formatting

DateTime Formatting

Text Format

Custom Number Formats

Conditional Formatting

Apply to Range

Custom Format Syntax

See Also