Format::setNumFormat

Format::setNumFormat – Sets the numeric format.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setNumFormat ( string $num_format )

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'.

The information here presented comes from OpenOffice.org's Documentation of the Microsoft Excel File Format (http://sc.openoffice.org/excelfileformat.pdf).

Parameter

  • string $num_format - The numeric format.

Note

This function can not be called statically.

Example

Using setNumFormat()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

// We'll show dates with a three letter month and four digit year format
$date_format =& $workbook->addFormat();
$date_format->setNumFormat('D-MMM-YYYY');

// number of seconds in a day
$seconds_in_a_day 86400;
// Unix timestamp to Excel date difference in seconds
$ut_to_ed_diff $seconds_in_a_day 25569;

// show Excel's day zero date
$worksheet->write(00"Excel's day zero");
$worksheet->write(010$date_format);

// show today's date
$now time();
$worksheet->write(10"Today's date:");
$worksheet->write(11, ($now $ut_to_ed_diff) / $seconds_in_a_day$date_format);

$workbook->send('num_formatting.xls');
$workbook->close();
?>