How to set money format in PhpSpreadSheet?

How to set money format in PhpSpreadSheet?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Migrating Currency Formatting from PHPEExcel to PhpSpreadsheet

Transitioning from PHPEExcel to PhpSpreadsheet often reveals subtle differences in how number formatting is handled, particularly when dealing with currency. The migration path isn't always a direct copy-paste; underlying logic has shifted between the two libraries. If you are attempting to replicate legacy code that relies on predefined constants like FORMAT_CURRENCY_USD_SIMPLE, you will likely encounter unexpected behavior unless you adjust your approach accordingly.

In PHPEExcel, developers frequently relied on named format constants. The snippet provided in older documentation shows accessing a cell style via $phpExcelObject->getActiveSheet()->getStyle('D4'), retrieving the number format object, and setting it to FORMAT_CURRENCY_USD_SIMPLE. This constant represented a specific string pattern internally designed for US dollars with simple formatting rules.

When moving to PhpSpreadsheet, this constant-based approach is no longer the primary or recommended method. The library has moved towards explicit string definitions for number formats. While you might be tempted to simply replace the constant with a hardcoded string like '$ #,##0.00', as seen in attempts to replicate the old behavior, the result may not match your expectations or may fail to render correctly depending on the Excel locale settings of the viewer.

The core issue often lies in understanding that number formats in PhpSpreadsheet are interpreted by the underlying OpenXML standard used by Microsoft Excel. The string '$ #,##0.00' is a valid custom format code, but it assumes specific behavior regarding negative numbers and parentheses. For instance, this pattern typically displays positive numbers with a dollar sign and two decimal places, but it often leaves negative numbers in brackets without explicitly defining how the minus sign should appear relative to the currency symbol.

To achieve robust currency formatting in PhpSpreadsheet, you should define the format string explicitly on the getNumberFormat() object. Here is how you implement this:

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

// Set data for D4
$sheet->setCellValue('D4', 1234.56);

// Apply currency format
$style = $sheet->getStyle('D4');
$numberFormat = $style->getNumberFormat();

// Explicitly define the format string
$numberFormat->setFormatCode('$ #,##0.00');

However, relying solely on this string can lead to inconsistencies if you need to handle negative values or different currencies. A more flexible approach involves using predefined format codes that Excel recognizes natively. For example, instead of hardcoding the string, you might use a format code like _($ * #,##0.00_);_($ (* ##0.00_)_) which handles positive and negative numbers distinctly. The first part defines the positive style, while the section after the semicolon defines the negative style.

If you are building an application that generates reports for international users, hardcoding USD symbols might not be the best practice. It is often better to rely on locale-specific format strings or allow the user's regional settings to dictate the currency symbol. This ensures that a European user sees € while an American user sees $, maintaining consistency across different environments without requiring multiple configuration files.

One common pitfall during this migration is assuming that the internal representation of numbers in the PHP object matches the display format exactly. PhpSpreadsheet stores values as floating-point numbers or strings, but the visual representation is entirely controlled by the NumberFormat object attached to the cell style. If you forget to apply the style after setting the value, the cell will default to the generic number format, which might strip leading zeros or alter decimal precision unexpectedly.

Additionally, be aware that PhpSpreadsheet handles multi-cell ranges differently than single cells when applying styles. If you are formatting a column of financial data, passing a range like 'D4:D100' to the getStyle method is efficient and ensures uniformity across the dataset. This batch application prevents the common mistake of looping through rows individually without ensuring the style object is correctly cached or applied in the final XML export.

For complex scenarios where you need dynamic currency symbols based on user preferences, consider storing the desired currency code in your data row and mapping it to a specific format string during the rendering phase. This separation of data and presentation logic keeps your spreadsheet generation clean and maintainable. While resources like https://laravelcompany.com often discuss broader Laravel ecosystem integrations, understanding the low-level mechanics of PhpSpreadsheet is essential when those libraries interact directly with Excel files in financial applications.

Our network