PHPSpreadsheet: Achieve Right-Aligned Currency Format Quickly

While working on a long-standing CMS (Content Management System) project, I needed a reliable way to export database results into a fully functional Excel document. For years, I created functions that exported .CSV files, which could easily be imported into Excel. However, for this project, a working Excel document was necessary, so I switched to PHPSpreadsheet—the updated library that replaced PHPExcel. It provides extensive functionality, and after spending some time learning it, I developed an export function that I’m very pleased with. You can download it here or install it via composer.

composer require phpoffice/phpspreadsheet

PHPSpreadsheet offers a wide range of Excel-compatible features, including cell formatting, formulas, charts, and more. One specific feature I needed was a right-aligned currency format with a dollar sign. Initially, I found it tricky to achieve the right alignment of numbers while keeping the currency symbol in place, so I came up with a solution that works well.

Creating Right-Aligned Currency Format with a Dollar Sign in PHPSpreadsheet

Here’s how you can create a custom number format for currency formatting with the dollar sign, aligned to the right (this sample is for a range of columns):

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

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

// Apply custom number format for currency with left-aligned symbol but right-aligned numbers
sheet->getStyle('C1:D1')
    ->getNumberFormat()
    ->setFormatCode('"$"* #,##0.00');

// Explicitly set right alignment for the specified range
sheet->getStyle('C1:D1')
    ->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_RIGHT);

Important Notes

• Compatibility: This format is compatible with Microsoft Excel but may not render correctly in alternative programs like OpenOffice due to limitations within those applications.
• Integration with WordPress: Although there isn’t an official WordPress plugin for PHPSpreadsheet, I’ve integrated this functionality into WordPress sites (through custom coding) to export Excel files. It’s a powerful tool, and I hope you find it as useful as I do.