Formats contain format information which is applied to a cell, or group of of cells, in an Excel object.
Each of the write attributes listed below has a corresponding read attribute.
Format#align=(value = "off")Sets the horizontal and vertical alignment within a cell. Vertical and horizontal alignments can be combined. Valid arguments are:
Horizontal: left, right, fill, justify, merge Vertical: top, vcenter, bottom, vjustify
Text can be aligned across two or more adjacent cells using the merge property.
The vjustify (vertical justify) option can be used to provide automatic text wrapping in a cell. The height of the cell will be adjusted to accommodate the wrapped text. To specify where the text wraps use the text_wrap method.
Format#bg_color=(value = "off")Format#bold=(weight = 1)Sets the bold property of the font.values in the range 100..1000 are also valid. 400 is normal, 700 is bold and 1000 is very bold.
You can also specify true or false, which sets the value to 1 and 0, respectively.
Format#border=(integer = 0)Sets the border for a given cell. A cell border is comprised of a border on the bottom, top, left and right.
Valid arguments are as follows: 0 - No border 1 - Thin single border 2 - Medium single border 3 - Dashed border 4 - Dotted border 5 - Thick single border 6 - Double line border 7 - Hair border
Format#border_color=(value = "black")Sets the color of the cell borders. Valid values are the same as for the color method.
Also see the bottom_color, top_color, left_color, and right_color methods.
Format#bottom=(integer = 0)Format#bottom_color=(value = "black")Format#color=(value = "black")Sets the font color of text within a cell (not the cell itself). Any integer from 8..63 is valid. The following strings are also recognized:
black, blue, brown, cyan, gray/grey, green, lime, magenta, navy, orange, purple, red, silver, white, yellow
Format#fg_color=(value = "off")Format#font=(name = "Arial")Format#font_shadow=(value = 0)Format#italic=(value = 0)Sets the italic property of the font.
You can also specify true or false, which sets the value to 1 and 0, respectively.
Format#left=(integer = 0)Format#left_color=(value = "black")Format#num_format=(value = 1)This method is used to define the numerical format of a number in Excel. 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, e.g.
format1 = Format.new(:num_format => "d mmm yyyy") format2 = Format.new(:num_format => 0x0f) workbook.addformat(format1) workbook.addformat(format2) 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 (assume each of these is separate and has been added to a workbook).
format.num_format = '0.000' worksheet.write(0,0,3.1415926,format) -> 3.142 format.num_format = '#,##0' worksheet.write(1,0,1234.56,format) -> 1,235 format.num_format = '#,##0.00' worksheet.write(2,0,1234.56,format) -> 1,234.56 format.num_format = '0.00' worksheet.write(3,0,49.99,format) -> 49.99 format.num_format = '£0.00' worksheet.write(4,0,49.99,format) -> £49.99 format.num_format = '¥0.00' worksheet.write(5,0,49.99,format) -> ¥49.99 format.num_format = 'mm/dd/yy' worksheet.write(6,0,36892.521,format) -> 01/01/01 format.num_format = 'mmm d yyyy' worksheet.write(7,0,36892.521,format) -> Jan 1 2001 format.num_format = 'd mmmm yyyy' worksheet.write(8,0,36892.521,format) -> 1 January 2001 format.num_format = 'dd/mm/yyyy hh:mm AM/PM' worksheet.write(9,0,36892.521,format) -> 01/01/2001 12:30 AM format.num_format = '0 "dollar and" .00 "cents"' worksheet.write(10,0,1.87,format) -> 1 dollar and .87 cents # Conditional formatting format.num_format = '[Green]General;[Red]-General;General' worksheet.write(11,0,123,format) -> > 0 Green worksheet.write(12,0,-45,format) -> < 0 Red worksheet.write(13,0, 0, format) -> = 0 Default colour # Zip code format.num_format = '00000' worksheet.write(14,0,'01209',format)
Format#outline=(value = 0)Format#pattern=(integer = 0)Format#right=(integer = 0)Format#right_color=(value = "black")Format#rotation=(integer = 0)Sets the rotation of the text in the cell. Valid arguments are:
0 - No rotation 1 - Top to bottom 2 - 90 degrees counter-clockwise 3 - 90 degrees clockwise
Note that fractional rotation is not possible in the Excel 95 format.
Format#script=(integer = 0)Sets the subscript/superscript property of the font.
Valid arguments:
0 = Normal 1 = Superscript 2 = Subscript
Format#shadow=(value = 0)Format#size=(size = 10)Format#text_justlast=(value = 0)Format#text_wrap=(value = 0)Turn text wrap on. Excel will adjust the height of the row to accommodate the wrapped text. A similar effect can be obtained without newlines using the align='vjustify' method.
Accepts 1/0 or true/false as valid values.
Format#top=(integer = 0)Format#top_color=(value = "black")Format#underline=(value = 0)Sets the underline property of the font.
Valid arguments: 0/false - No underline 1/true - Single underline 2 - Double underline 33 - Single accounting underline 34 - Double accounting underline
Format#strikeout=(value = 0)