More NumberFormatting Improvements for CSV/HTML/PDF Writers

Topics: Developer Forum, User Forum
Aug 19, 2009 at 9:28 PM
Edited Aug 19, 2009 at 9:28 PM

Hello,

I'm new to PHPExcel, but I want to start by saying that this library has been a real life saver!
I'm working on an accounting project where our end-users want MySQL-driven dynamic
data either in CSV, Excel5 or HTML format, and they expect it to look just like their current
spreadsheets.  The PHPExcel project has saved me a lot of time, so I thank you.

With that said, I was disappointed at the lack of number formatting options available for
non-Excel formats.  I added my own to the source code, in case anyone is interested.  It is
for formatting comma-separated numbers with no digits after the decimal place and
negative numbers in parentheses.  E.g.:

1000.01 --> 1,000
-1000.01 --> (1,000)

Here is the code I inserted into Classes/PHPExcel/Style/NumberFormat.php:

54:  const FORMAT_NUMBER_COMMA_SEPARATED3  = '#,##0_);(#,##0)';
516:				else if ($format === self::FORMAT_NUMBER_COMMA_SEPARATED3)
517:						return ($value >= 0 ? number_format($value, 0, '.', ',') : "(" . number_format(abs($value), 0, '.', ',') . ")");

I also noticed that the other comma-separated formats are wrong.  They both use:
number_format($value, 2, ",", ".")
which sets the '.' as the thousands separator and the ',' as the decimal, which doesn't
match the definition: '#,##0.00'
I'm thinking that I would be willing to help contribute to this, especially because this
is such a nice way to format HTML pages.  I would propose a revised
toFormattedString() function that could actually parse any $format and return an
array with an optional color field.  Then the writers could interpret the color
information any way that is appropriate.
Thanks again for a really useful library!
Matt

Developer
Aug 19, 2009 at 10:16 PM

Can you tell us whether you are using PHPExcel 1.6.7 or 1.7.0 ? Your comment about the wrong number_format for comma-separated formats leads me to believe that you are using PHPExcel 1.6.7. In PHPExcel 1.7.0 this has been changed so that the decimal/thousands separator is retrieved from the locale settings on the server. This affects output by CSV/HTML/PDF writers in PHPExcel.

You have some valid points with multi-part number formats. Therefore please check your version of PHPExcel so we can discuss this based on latest version. The documentation has also been updated in PHPExcel 1.7.0 with a section "Number Formats" to address your worries in the first part concerning custom number formats. Be sure also to read that. Users were understandably not aware that they were allowed to use custom number formats apart from the predefined class constants.

Aug 20, 2009 at 12:00 PM
Edited Aug 20, 2009 at 12:19 PM

I am using 1.6.7...wasn't aware that a new version was out.  I'll take a look at it and the documentation.

 

Thanks for your quick reply.

Matt

EDIT:  After taking a quick look at the new routines in toFormattedString(), I agree with the comment!

514     // We should really fetch the relevant part depending on whether we have a positive number,
515     // negative number, zero, or text. But for now we just use first part

I would also like to see someway of either returning the color codes with the formatted value, or the function taking an additional type argument and having the function imbed the appropriate color information depending on the Writer type.  For the time being, I'll have to continue with a hack, because our accountants want to see the negative numbers in parentheses.  I can also get away with using the PHP_Style_Conditional class to color negative cells red.  I think it would be easy to implement the missing features laid out in that comment however, and I'd love to see that in a future release.

 

Developer
Aug 20, 2009 at 12:40 PM

>> I would also like to see someway of either returning the color codes with the
>> formatted value, or the function taking an additional type argument and having the
>> function imbed the appropriate color information depending on the Writer type.


Agreed. PHPExcel_Style_NumberFormat::toFormattedString() is something we have neglected a bit although it has been improved through recent versions. I will create the necessary work items later this week to get some of this resolved for next release.

Developer
Aug 21, 2009 at 12:45 AM

Work item created:

Multi-section number format support in HTML/PDF/CSV writers
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10492

Colored number format support, e.g. [Red], in HTML/PDF output
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10493