Format Numbers and Dates in box/spout Excel Spreadsheets
I recently moved some of our PHP code from PhpSpreadsheet (phpoffice/phpspreadsheet) to Spout (box/spout) to address memory consumption issues while generating XLSX-formatted Spreadsheets. I have experience writing Excel Spreadsheets using several other PHP packages, so I was able to dive into the Spout source code and figure out how to access number and date formatting features.
Format Numbers
If you cast a PHP value to an (int) or a (float), Spout will save the value as a number, but your spreadsheet application will display an unformatted number. For example, the number “12,345,678.90” would be displayed in your spreadsheet application as “12345678.9”.
You can use setFormat() to format any (int) or (float) value. Find Excel number formatting examples here. See Sample Code below.
Format Dates
If you save a Date to Spout as a (string) value (e.g. “12/31/1999”), your spreadsheet application will display the pre-formatted Date, BUT the date cannot be re-formatted or used for calculations because it is being stored as a String value instead of a Date value.
You can use setFormat() to format a date, ONLY IF the date is passed as an Excel numeric (int) or (float) date value. Several PHP libraries can convert a PHP date to an Excel numeric date value. Since our project uses both Spout and PhpSpreadsheet, we use PhpSpreadsheet’s Date::PHPToExcel() to convert a Carbon value to an Excel numeric value. Find Excel date and time formatting examples here and here. See Sample Code below.
Sample Code
The following sample code produces this output in my spreadsheet application (Numbers):
use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
// Open Spreadsheet
$writer = WriterEntityFactory::createXLSXWriter();
$writer->openToFile('example.xlsx');
// Build Header Row
$styleBold = (new StyleBuilder())->setFontBold()->build();
$writer->addRow(
WriterEntityFactory::createRow([
WriterEntityFactory::createCell('Num1'),
WriterEntityFactory::createCell('Num2'),
WriterEntityFactory::createCell('Num3'),
WriterEntityFactory::createCell('Date1'),
WriterEntityFactory::createCell('Date2'),
], $styleBold)
);
// Define Number and Date Formats
$styleNum1 = (new StyleBuilder())->setFormat('#,##0.00')->build();
$styleNum2 = (new StyleBuilder())->setFormat('0.0')->build();
$styleNum3 = (new StyleBuilder())->setFormat('00000')->build();
$styleDate1 = (new StyleBuilder())->setFormat('mm/dd/yy')->build();
$styleDate2 = (new StyleBuilder())->setFormat('mm/dd/yyyy')->build();
// Define Number and Date Values
$num1 = 12345678.9;
$num2 = 1234;
$date = Date::PHPToExcel(Carbon::parse('03/14/2000'));
// Build Row of Formatted Numbers and Dates
$writer->addRow(
WriterEntityFactory::createRow([
// Output 12,345,678.90
WriterEntityFactory::createCell($num1, $styleNum1),
// Output 1234.0
WriterEntityFactory::createCell($num2, $styleNum2),
// Output 01234
WriterEntityFactory::createCell($num2, $styleNum3),
// Output 03/14/00
WriterEntityFactory::createCell($date, $styleDate1),
// Output 03/14/2000
WriterEntityFactory::createCell($date, $styleDate2),
])
);
// Close Spreadsheet
$writer->close();Bonus: Custom Column Widths
The box/spout project has an open Pull Request (PR#715) that adds Custom Column Width support. I forked the contributor’s repository (aphofstede/spout) to my own repo (e.g. “example/spout-custom-column-widths”) and added the following snippet to the “repositories” section of our composer.json file, which instructs composer to use the “custom-column-widths” branch in my forked copy of the repository.
When I run “composer require box/spout” in our project, composer uses the “custom-column-widths” branch from my forked copy of the contributor’s repository. Replace “example/spout-custom-column-widths” with the actual path to your forked copy of the contributor’s repository.
{
"type": "package",
"package": {
"name": "box/spout",
"version": "3.2",
"source": {
"url": "https://github.com/example/spout-custom-column-widths",
"type": "git",
"reference": "custom-column-widths"
},
"type": "library",
"autoload": {
"psr-4": {
"Box\\Spout\\": "src/Spout"
}
}
}
}Sample Code
You can use the setDefaultColumnWidth() method to set default column width for all of the worksheets in your spreadsheet.
TIP: Use method_exists() to make sure the custom method exists in case you need to temporarily deploy your project without the custom PR.
// XLS Worksheet Default Column Width
// NOTE: setDefaultColumnWidth() may only be called BEFORE openToFile()
if (method_exists($writer, 'setDefaultColumnWidth')) {
// NOTE: setDefaultColumnWidth() provided by PR#715
$writer->setDefaultColumnWidth(12);
}You can use the setColumnWidth() method to set specific column widths for individual columns. In the example below, we set widths for Column A to Column H.
TIP: Use method_exists() to make sure the custom method exists in case you need to temporarily deploy your project without the custom PR.
// XLS Worksheet Column Widths
// NOTE: setColumnWidth() may only be called AFTER openToFile()
if (method_exists($writer, 'setColumnWidth')) {
// NOTE: setColumnWidth() provided by PR#715
$writer->setColumnWidth(8, 1); // Column A
$writer->setColumnWidth(12, 2); // Column B
$writer->setColumnWidth(12, 3); // Column C
$writer->setColumnWidth(12, 4); // Column D
$writer->setColumnWidth(12, 5); // Column E
$writer->setColumnWidth(50, 6); // Column F
$writer->setColumnWidth(12, 7); // Column G
$writer->setColumnWidth(12, 8); // Column H
}The Custom Column Width PR may also provide these setColumnWidthForRange() and setDefaultRowHeight() methods? I have not tested these methods, but thought others may appreciate knowing they exist.
// Set Column Width to 12 for Columns A-F (1-6)
$writer->setColumnWidthForRange(12, 1, 6);
// Set Default Row Height to 3 for all rows
$writer->setDefaultRowHeight(3);Did you find this helpful? Let me know by sending me a comment. I tend to update and maintain posts more frequently if I know others find them helpful. Thanks for visiting!
