DEFINE
The DEFINE command is used to define cell formats and column widths to be used by the WRITE command.
Required
DEFINE ---------- OBJECT ---------- *COLUMNVIEW ----------------->
*CELLFORMAT
COLUMN ---------- n --------------------------->
n,n
Conditional
If the OBJECT keyword is set to *COLUMNVIEW
CLEAR ----------- *NO ------------------------->
*YES
WIDTH ----------- value ----------------------->
If the OBJECT keyword is set to *CELLFORMAT
CLEAR ----------- *NO ------------------------->
*YES
RANGE ----------- value ----------------------->
TYPE ------------ *NUMBER --------------------->
*DATE
*BOOLEAN
*STRING
*BLANK
FORMAT----------------------------------------->
Cell formats for TYPE *NUMBER
*ACCOUNTINGFLOAT
*ACCOUNTINGREDFLOAT
*ACCOUNTINGINTEGER
*ACCOUNTINGREDINTEGER
*DEFAULT
*EXPONENTIAL
*FLOAT
*FORMAT1
*FORMAT2
*FORMAT3
*FORMAT4
*FORMAT5
*FORMAT6
*FORMAT7
*FORMAT8
*FORMAT9
*FORMAT10
*FRACTIONONEDIGIT
*FRACTIONTWODIGITS
*INTEGER
*PERCENTFLOAT
*PERCENTINTEGER
*TEXT
*THOUSANDSFLOAT
*THOUSANDSINTEGER
Any valid NumberFormat - i.e. #.##
Cell formats for TYPE *DATE
*DEFAULT
*FORMAT1
*FORMAT2
*FORMAT3
*FORMAT4
*FORMAT5
*FORMAT6
*FORMAT7
*FORMAT8
*FORMAT9
*FORMAT10
*FORMAT11
*FORMAT12
Any valid DateFormat i.e. dd MM yyyy
COMPLEXFORMAT---- value ----------------------->
FONT ------------ *ARIAL ---------------------->
*COURIER
*TAHOMA
*TIMES
FONTCOLOR ------- *AQUA ----------------------->
*AUTOMATIC
*BLACK
*BLUE
*BLUEGRAY
*BLUE2
*BRIGHTGREEN
*BROWN
*CORAL
*DARKBLUE
*DARKBLUE2
*DARKGREEN
*DARKPURPLE
*DARKRED
*DARKRED2
*DARKTEAL
*DARKYELLOW
*DEFAULTBACKGROUND
*DEFAULTBACKGROUND1
*GOLD
*GRAY25
*GRAY50
*GRAY80
*GREEN
*GRAY25
*GRAY50
*GRAY80
*ICEBLUE
*INDIGO
*IVORY
*LAVENDER
*LIGHTBLUE
*LIGHTGREEN
*LIGHTORANGE
*LIGHTTURQUOISE
*LIGHTTURQUOISE2
*LIME
*OCEANBLUE
*OLIVEGREEN
*ORANGE
*PALEBLUE
*PALETTEBLACK
*PERIWINKLE
*PINK
*PINK2
*PLUM
*PLUM2
*RED
*ROSE
*SEAGREEN
*SKYBLUE
*TAN
*TEAL
*TEAL2
*TURQUOISE
*TURQUOISE2
*VERYLIGHTYELLOW
*VIOLET
*VIOLET2
*WHITE
*YELLOW
*YELLOW2
FONTSIZE --------- value ---------------------->
BORDER ---------- *ALL ------------------------>
*NONE
*TOP
*BOTTOM
*LEFT
*RIGHT
BORDERSTYLE ----- *NONE ----------------------->
*MEDIUM
*THICK
*THIN
*HAIR
*DOUBLE
BORDERCOLOR ----- *AQUA ----------------------->
*AUTOMATIC
*BLACK
*BLUE
*BLUEGRAY
*BLUE2
*BRIGHTGREEN
*BROWN
*CORAL
*DARKBLUE
*DARKBLUE2
*DARKGREEN
*DARKPURPLE
*DARKRED
*DARKRED2
*DARKTEAL
*DARKYELLOW
*DEFAULTBACKGROUND
*DEFAULTBACKGROUND1
*GOLD
*GRAY25
*GRAY50
*GRAY80
*GREEN
*GRAY25
*GRAY50
*GRAY80
*ICEBLUE
*INDIGO
*IVORY
*LAVENDER
*LIGHTBLUE
*LIGHTGREEN
*LIGHTORANGE
*LIGHTTURQUOISE
*LIGHTTURQUOISE2
*LIME
*OCEANBLUE
*OLIVEGREEN
*ORANGE
*PALEBLUE
*PALETTEBLACK
*PERIWINKLE
*PINK
*PINK2
*PLUM
*PLUM2
*RED
*ROSE
*SEAGREEN
*SKYBLUE
*TAN
*TEAL
*TEAL2
*TURQUOISE
*TURQUOISE2
*VERYLIGHTYELLOW
*VIOLET
*VIOLET2
*WHITE
*YELLOW
*YELLOW2
BACKGROUND ------ *AQUA ----------------------->
*AUTOMATIC
*BLACK
*BLUE
*BLUEGRAY
*BLUE2
*BRIGHTGREEN
*BROWN
*CORAL
*DARKBLUE
*DARKBLUE2
*DARKGREEN
*DARKPURPLE
*DARKRED
*DARKRED2
*DARKTEAL
*DARKYELLOW
*DEFAULTBACKGROUND
*DEFAULTBACKGROUND1
*GOLD
*GRAY25
*GRAY50
*GRAY80
*GREEN
*GRAY25
*GRAY50
*GRAY80
*ICEBLUE
*INDIGO
*IVORY
*LAVENDER
*LIGHTBLUE
*LIGHTGREEN
*LIGHTORANGE
*LIGHTTURQUOISE
*LIGHTTURQUOISE2
*LIME
*OCEANBLUE
*OLIVEGREEN
*ORANGE
*PALEBLUE
*PALETTEBLACK
*PERIWINKLE
*PINK
*PINK2
*PLUM
*PLUM2
*RED
*ROSE
*SEAGREEN
*SKYBLUE
*TAN
*TEAL
*TEAL2
*TURQUOISE
*TURQUOISE2
*VERYLIGHTYELLOW
*VIOLET
*VIOLET2
*WHITE
*YELLOW
*YELLOW2
HALIGN ---------- *CENTRE --------------------->
*CENTER
*FILL
*GENERAL
*JUSTIFY
*LEFT
*RIGHT
VALIGN ---------- *CENTRE --------------------->
*CENTER
*JUSTIFY
*TOP
*BOTTOM
ITALIC ---------- *YES ------------------------>
*NO
BOLD ------------ *YES ------------------------|
*NO
Keywords
|
OBJECT |
This keyword is used to specify whether you want to define the width of a column view or a column cell format. The possible values are: *COLUMNVIEW - if you want to define the width of a specified column view. *CELLFORMAT - if you want to define the format of the cells. Both of these options has a different set of keywords available for it. This keyword is mandatory. |
|
COLUMN |
This keyword is used to specify the column number or range of column numbers you wish your DEFINE command to act on. You can specify a single column number or a comma-separated range. The format of this keyword is as follows: COLUMN(column), for example, COLUMN(3). COLUMN(start column, end column), for example, COLUMN(3,5). For the *COLUMNVIEW option, the action acts on the whole column, while for the *CELLFORMAT option you can also specify a specific row or rows to act on. See the RANGE keyword below for more details. This keyword is mandatory. |
If the OBJECT keyword is being set to *COLUMNVIEW then the following keywords are available.
|
CLEAR |
This keyword is used to clear all defined column views. This keyword is always used by itself. It cannot be used in conjunction with any other keywords. The possible values are: *YES - to clear all defined column views. *NO - to not clear all defined column views. The default value is *NO. |
|
WIDTH |
This keyword is used to define the column width. You must specify a numeric value here. This keyword is mandatory. |
If the OBJECT keyword is being set to *CELLFORMAT then the following keywords are available.
|
CLEAR |
This keyword is used to clear all defined cell formats. This keyword is always used by itself. It cannot be used in conjunction with any other keywords. The possible values are: *YES - to clear all defined column views. *NO - to not clear all defined column views. The default value is *NO. |
|
RANGE |
This keyword is used to define the range of rows within the specified column on which this DEFINE will act. If left blank, then the entire column will be acted upon. The format of this keyword is as follows: RANGE(start row, end row), for example, RANGE(3,5). This keyword is optional. |
|
TYPE |
This keyword is used to define the column type. The possible values are: *NUMBER *DATE *BOOLEAN *STRING *BLANK The default value is *NUMBER This keyword is optional. |
|
FORMAT |
This keyword is used to define the cell format. If the cell is of type *NUMBER, then the possible values you may use are as follows. Any valid NumberFormat may be used for these (i.e. #.##): *ACCOUNTINGFLOAT ("$#,##0;($#,##0)") *ACCOUNTINGINTEGER ("$#,##0;($#,##0)") *ACCOUNTINGREDFLOAT ("$#,##0;[Red]($#,##0)") *ACCOUNTINGREDINTEGER ("$#,##0;[Red]($#,##0)") *DEFAULT ("#") *EXPONENTIAL ("0.00E00") *FLOAT ("0.00") *FORMAT1 ("#,##0;(#,##0)") *FORMAT2 ("#,##0;[Red](#,##0)") *FORMAT3 ("#,##0.00;(#,##0.00)") *FORMAT4 ("#,##0.00;[Red](#,##0.00)") *FORMAT5 ("#,##0;(#,##0)") *FORMAT6 ("#,##0;[Red](#,##0)") *FORMAT7 ("#,##0.00;(#,##0.00)") *FORMAT8 ("#,##0.00;[Red](#,##0.00)") *FORMAT9 ("#,##0.00;(#,##0.00)") *FORMAT10 ("##0.0E0") *FRACTIONONEDIGIT ("?/?") *FRACTIONTWODIGITS ("??/??") *INTEGER ("0") *PERCENTFLOAT ("0.00%") *PERCENTINTEGER ("0%") *TEXT ("@") *THOUSANDSFLOAT ("#,##0.00") *THOUSANDSINTEGER ("#,##0") The default value is *DEFAULT If the cell is of type *DATE, then the possible values you may use are as follows. Any valid DateFormat may be used for these (i.e. dd mm yyyy): *DEFAULT ("m/d/yy") *FORMAT1 ("m/d/yy") *FORMAT2 ("d-mmm-yy") *FORMAT3 ("d-mmm") *FORMAT4 ("mmm-yy ") *FORMAT5 ("h:mm AM") *FORMAT6 ("h:mm:ss AM") *FORMAT7 ("H:mm") *FORMAT8 ("H:mm:ss") *FORMAT9 ("m/d/yy H:mm") *FORMAT10 ("mm:ss") *FORMAT11 ("H:mm:ss") *FORMAT12 ("H:mm:ss.0") The default value is *DEFAULT This keyword is optional. |
|
COMPLEXFORMAT |
This keyword is used to define the cell format of a type *NUMBER cell. No validation checks are done on the format value, so the format must be a valid Excel number format. If it is not a valid format corruption to the Excel document can occur.
|
|
FONT |
This keyword is used to define the font of the cell value. The possible values are as follows: *ARIAL *COURIER *TAHOMA *TIMES The JXL default font is Arial. This keyword is optional. |
|
FONTCOLOR |
This keyword is used to define the font color. The possible values are as follows: *AQUA, *AUTOMATIC, *BLACK, *BLUE, *BLUEGRAY, *BLUE2, *BRIGHTGREEN, *BROWN, *CORAL, *DARKBLUE, *DARKBLUE2, *DARKGREEN, *DARKPURPLE, *DARKRED, *DARKRED2, *DARKTEAL, *DARKYELLOW, *DEFAULTBACKGROUND, *DEFAULTBACKGROUND1, *GOLD, *GRAY25, *GRAY50, *GRAY80, *GREEN, *GRAY25, *GRAY50, *GRAY80, *ICEBLUE, *INDIGO, *IVORY, *LAVENDER, *LIGHTBLUE, *LIGHTGREEN, *LIGHTORANGE, *LIGHTTURQUOISE, *LIGHTTURQUOISE2, *LIME, *OCEANBLUE, *OLIVEGREEN, *ORANGE, *PALEBLUE, *PALETTEBLACK, *PERIWINKLE, *PINK, *PINK2, *PLUM, *PLUM2, *RED, *ROSE, *SEAGREEN, *SKYBLUE, *TAN, *TEAL, *TEAL2, *TURQUOISE, *TURQUOISE2, *VERYLIGHTYELLOW, *VIOLET, *VIOLET2, *WHITE, *YELLOW, *YELLOW2 The default value is *BLACK This keyword is optional. |
|
FONTSIZE |
The keyword is used to define the font size. The JXL default point size is 10. This keyword is optional. |
|
BORDER |
This keyword is used to define the lines of the border. The possible values are as follows: *ALL *NONE *TOP *BOTTOM *LEFT *RIGHT There is no default value. |
|
BORDERSTYLE |
This keyword is used to define the borderstyle. The BORDER keyword needs to be specified in conjunction with this keyword. Possible values for this keyword are as follows: *NONE *MEDIUM *THICK *THIN *HAIR *DOUBLE The default value is *THIN This keyword is optional. |
|
BORDERCOLOR |
This keyword is used to define the border color. The BORDER keyword needs to be specified in conjunction with this keyword. The possible values are as follows: *AQUA, *AUTOMATIC, *BLACK, *BLUE, *BLUEGRAY, *BLUE2, *BRIGHTGREEN, *BROWN, *CORAL, *DARKBLUE, *DARKBLUE2, *DARKGREEN, *DARKPURPLE, *DARKRED, *DARKRED2, *DARKTEAL, *DARKYELLOW, *DEFAULTBACKGROUND, *DEFAULTBACKGROUND1, *GOLD, *GRAY25, *GRAY50, *GRAY80, *GREEN, *GRAY25, *GRAY50, *GRAY80, *ICEBLUE, *INDIGO, *IVORY, *LAVENDER, *LIGHTBLUE, *LIGHTGREEN, *LIGHTORANGE, *LIGHTTURQUOISE, *LIGHTTURQUOISE2, *LIME, *OCEANBLUE, *OLIVEGREEN, *ORANGE, *PALEBLUE, *PALETTEBLACK, *PERIWINKLE, *PINK, *PINK2, *PLUM, *PLUM2, *RED, *ROSE, *SEAGREEN, *SKYBLUE, *TAN, *TEAL, *TEAL2, *TURQUOISE, *TURQUOISE2, *VERYLIGHTYELLOW, *VIOLET, *VIOLET2, *WHITE, *YELLOW, *YELLOW2 The default value is*BLACK This keyword is optional. |
|
BACKGROUND |
The possible values are as follows: *AQUA, *AUTOMATIC, *BLACK, *BLUE, *BLUEGRAY, *BLUE2, *BRIGHTGREEN, *BROWN, *CORAL, *DARKBLUE, *DARKBLUE2, *DARKGREEN, *DARKPURPLE, *DARKRED, *DARKRED2, *DARKTEAL, *DARKYELLOW, *DEFAULTBACKGROUND, *DEFAULTBACKGROUND1, *GOLD, *GRAY25, *GRAY50, *GRAY80, *GREEN, *GRAY25, *GRAY50, *GRAY80, *ICEBLUE, *INDIGO, *IVORY, *LAVENDER, *LIGHTBLUE, *LIGHTGREEN, *LIGHTORANGE, *LIGHTTURQUOISE, *LIGHTTURQUOISE2, *LIME, *OCEANBLUE, *OLIVEGREEN, *ORANGE, *PALEBLUE, *PALETTEBLACK, *PERIWINKLE, *PINK, *PINK2, *PLUM, *PLUM2, *RED, *ROSE, *SEAGREEN, *SKYBLUE, *TAN, *TEAL, *TEAL2, *TURQUOISE, *TURQUOISE2, *VERYLIGHTYELLOW, *VIOLET, *VIOLET2, *WHITE, *YELLOW, *YELLOW2 There is no default value. This keyword is optional. |
|
HALIGN |
This keyword is used to define the horizontal alignment. The possible values for this keyword are as follows: *CENTRE *CENTER *FILL *GENERAL *JUSTIFY *LEFT *RIGHT There is no default value. This keyword is optional. |
|
VALIGN |
This keyword is used to define the vertical alignment. The possible values for this keyword are as follows: *CENTRE *CENTER *JUSTIFY *TOP *BOTTOM There is no default value. This keyword is optional. |
|
ITALIC |
This keyword is used to set the font to italics or not. The possible values for this keyword are: *NO *YES The default value is *NO This keyword is optional. |
|
BOLD |
This keyword is used to set the font to bold or not. The possible values for this keyword are: *NO *YES The default value is *NO This keyword is optional. |