Method: Writeexcel::Format#set_num_format
- Defined in:
- lib/writeexcel/format.rb
#set_num_format(num_format) ⇒ Object
This method is used to define the numerical format of a number in Excel.
Default state: General format
Default action: Format index 1
Valid args: See the following table
It controls whether a number is displayed as an integer, a floating point number, a date, a currency value or some other user defined format.
The numerical format of a cell can be specified by using a format string or an index to one of Excel’s built-in formats:
format1 = workbook.add_format
format2 = workbook.add_format
format1.set_num_format('d mmm yyyy') # Format string
format2.set_num_format(0x0f) # Format index
worksheet.write(0, 0, 36892.521, format1) # 1 Jan 2001
worksheet.write(0, 0, 36892.521, format2) # 1-Jan-01
Using format strings you can define very sophisticated formatting of numbers.
format01.set_num_format('0.000')
worksheet.write(0, 0, 3.1415926, format01) # 3.142
format02.set_num_format('#,##0')
worksheet.write(1, 0, 1234.56, format02) # 1,235
format03.set_num_format('#,##0.00')
worksheet.write(2, 0, 1234.56, format03) # 1,234.56
format04.set_num_format('0.00')
worksheet.write(3, 0, 49.99, format04) # 49.99
# Note you can use other currency symbols such as the pound or yen as well.
# Other currencies may require the use of Unicode.
format07.set_num_format('mm/dd/yy')
worksheet.write(6, 0, 36892.521, format07) # 01/01/01
format08.set_num_format('mmm d yyyy')
worksheet.write(7, 0, 36892.521, format08) # Jan 1 2001
format09.set_num_format('d mmmm yyyy')
worksheet.write(8, 0, 36892.521, format09) # 1 January 2001
format10.set_num_format('dd/mm/yyyy hh:mm AM/PM')
worksheet.write(9, 0, 36892.521, format10) # 01/01/2001 12:30 AM
format11.set_num_format('0 "dollar and" .00 "cents"')
worksheet.write(10, 0, 1.87, format11) # 1 dollar and .87 cents
# Conditional formatting
format12.set_num_format('[Green]General;[Red]-General;General')
worksheet.write(11, 0, 123, format12) # > 0 Green
worksheet.write(12, 0, -45, format12) # < 0 Red
worksheet.write(13, 0, 0, format12) # = 0 Default colour
# Zip code
format13.set_num_format('00000')
worksheet.write(14, 0, '01209', format13)
The number system used for dates is described in “DATES AND TIME IN EXCEL”.
The colour format should have one of the following values:
[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
Alternatively you can specify the colour based on a colour index as follows: [Color n], where n is a standard Excel colour index - 7. See the ‘Standard colors’ worksheet created by formats.rb.
For more information refer to the documentation on formatting in the doc directory of the WriteExcel distro, the Excel on-line help or office.microsoft.com/en-gb/assistance/HP051995001033.aspx
You should ensure that the format string is valid in Excel prior to using it in WriteExcel.
Excel’s built-in formats are shown in the following table:
Index Index Format String
0 0x00 General
1 0x01 0
2 0x02 0.00
3 0x03 #,##0
4 0x04 #,##0.00
5 0x05 ($#,##0_);($#,##0)
6 0x06 ($#,##0_);[Red]($#,##0)
7 0x07 ($#,##0.00_);($#,##0.00)
8 0x08 ($#,##0.00_);[Red]($#,##0.00)
9 0x09 0%
10 0x0a 0.00%
11 0x0b 0.00E+00
12 0x0c # ?/?
13 0x0d # ??/??
14 0x0e m/d/yy
15 0x0f d-mmm-yy
16 0x10 d-mmm
17 0x11 mmm-yy
18 0x12 h:mm AM/PM
19 0x13 h:mm:ss AM/PM
20 0x14 h:mm
21 0x15 h:mm:ss
22 0x16 m/d/yy h:mm
.. .... ...........
37 0x25 (#,##0_);(#,##0)
38 0x26 (#,##0_);[Red](#,##0)
39 0x27 (#,##0.00_);(#,##0.00)
40 0x28 (#,##0.00_);[Red](#,##0.00)
41 0x29 _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
42 0x2a _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
43 0x2b _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
44 0x2c _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
45 0x2d mm:ss
46 0x2e [h]:mm:ss
47 0x2f mm:ss.0
48 0x30 ##0.0E+0
49 0x31 @
For examples of these formatting codes see the ‘Numerical formats’ worksheet created by formats.rb. – See also the number_formats1.html and the number_formats2.html documents in the doc directory of the distro. ++
Note 1. Numeric formats 23 to 36 are not documented by Microsoft and may differ in international versions.
Note 2. In Excel 5 the dollar sign appears as a dollar sign. In Excel 97-2000 it appears as the defined local currency symbol.
Note 3. The red negative numeric formats display slightly differently in Excel 5 and Excel 97-2000.
1440 1441 1442 |
# File 'lib/writeexcel/format.rb', line 1440 def set_num_format(num_format) @num_format = num_format end |