Format::setNumFormat

Synopsis

Description

Sets the numeric format.
It can be date, time, currency, etc...
The following table lists possible values for $num_format
and the corresponding types that a numeric format expects as arguments.

Numeric formats and types

0

Decimal

The amount of zeros specifies the amount of digits that will be shown

0.00

Decimal

The amount of zeros after the decimal dot specifies the amount of decimal digits that will be shown

#.##

Decimal

The amount of sharp signs after the decimal dot specifies the maximum amount of decimal digits that will be shown

0%

Percent

The amount of zeros specifies the amount of digits that will be shown.

0.000%

Percent

The amount of zeros after the decimal dot specifies the amount of decimal digits that will be shown.

$#.#;[Red]($#.#)

Currency

Zeros and sharp signs have the same meaning as in other formats.

??/??

Fraction

The amount of question signs in the denominator determines its precision
(maximum amount of digits in the denominator).

# ??/??

Fraction

A fraction with an integer part. Zeros and sharp signs are used for defining the integer part,
and they have the same meaning as in other formats.

0.00E+#

Scientific

In scientific notation base and exponent are formated according to the same rules applied to decimals.
For scientific notation zeros and sharp signs appear to be equivalent.

D-MMM-YY

Date

A date represented in the given notation. Month can be a one or two digits month, or a three letter month.
Year can have 2 or 4 digits. The argument to be formated as a date is considered to be the number of days since
December 30 1899 (Excel's day zero). For dates preceding day zero, negative numbers can be used.

D/M/YYYY h:mm:ss

Date/Time

A date represented in the given notation. The argument to be formated as a date is considered to be the
number of days since Excel's day zero.

h:mm:ss AM/PM

Time

A time represented in the given notation. Be careful, the argument to be formated as a time has to be given in days.
For example an argument of 0.5 would be presented as '12:00:00 PM'.

I have generated the excel spreadsheet using Spreadsheet_Excel_writer. Every thing is working fine in Openoffice, but in MS Office when I try to change cell format lets say For currency then every time i am getting this error "Style 'Currency' not found" or for comma "Style 'Comma' not found"?
Suggestions are welcome.

After 45 (!) minutes of searching, I finally found the solution to my problem in the PERL original of SEW.
I hope this will help out other desperate folks here.

My problem was that the numbers looked OK in Excel if you didn't touch a thing, but once you moved around in the cells, Excel (I use 2007) always kept transforming the cell to a sort of date format; nor were I able to explicitly set TEXT format, i. e. absolutely static.

The trick is simple, and of course, magnificent: :)

$fmt =& $xls->addFormat();
$fmt->setNumFormat('@');

Now your TEXT will stay TEXT and even Excel will recognize the format as text, if you check back in cell properties.

As the time or strtotime functions generate timestamp according to the timezone setting, the excel date given by the sample code might not be correct for computers not having the timezone as UTC, so add the following codes at the beginning.