5 4 8 ADD

You are here:

5.4.8 ADD

The ADD command is used to add sheets, cell styles, named areas, images, formulas, hyperlinks, comments and cell merges to an Excel document. The action taken will depend on the value of the object keyword.

ADD SHEET

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*SHEET
ADD	SHEET	value	Required. Sheet name.

Example

ADD OBJECT(*SHEET) SHEET(Sales)

ADD COMMENT

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*COMMENT
	SHEET	value	Optional. A specific sheet name can be entered otherwise the current context sheet is used.
	R1C1	n,n	Required. Cell row and column.
	AREA	n,n	Optional. Display rectangle. Default value is 2,4.
	COMMENT	value	Optional. The comment texts. If the comment keyword is not used, then the cell comment is removed.

Example

ADD OBJECT(*COMMENT) R1C1(10,5) AREA(4,4) COMMENT(important data)

ADD CELL MERGE

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*CELLMERGE
	SHEET	value	Optional. A specific sheet name can be entered otherwise the current context sheet is used.
	R1C1	n,n	Required. Cell row and column.
	R2C2	n,n	Required. Cell row and column.

Example

ADD OBJECT(*CELLMERGE) R1C1(10,5) R2C2(15,5)

ADD IMAGE

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*IMAGE
	SHEET	value	Optional. A specific sheet name can be entered otherwise the current context sheet is used.
	FILE	value	Required. Path to the PNG or JPEG image file.
	R1C1	n,n	Required. First anchor upper left position.
	R2C2	n,n	Optional. Second anchor bottom right position. Used by the RESIZE(*CELL) option. The default is the R1C1 value.
	WIDTH	integer	Optional. Scale image to specified width. Used by RESIZE(*IMAGE) The default value is 100 pixels, if no WIDTH or HEIGHT specified.
	HEIGHT	integer	Optional. Scale image to specified height. Used by RESIZE(*IMAGE).
	DX1DY1	n,n	Optional. Sets the x and y coordinate within the first cell.
	DX2DY2	n,n	Optional. Sets the x and y coordinate within the second cell.
	ROWHEIGHT	integer	Optional. Set the row height in twips or calculate row height. The default is to not set the row height.
	ROWHEIGHT	*CALC	This value only works for RESIZE(*IMAGE).
	COLUMNWIDTH	integer	Optional. Set the column width in units of 1/256 of a character or calculate column width. The default is to not set the column width.
	COLUMNWIDTH	*CALC	This value only works for RESIZE(*IMAGE).
	RESIZE	numeric	Optional. Specify how the image is resized. The default value is 1.0 and the image is scaled by the specified amount and anchored at the R1C1 cell.
		*CELL	Allows an image to anchored to a single cell or range of cells.
		*IMAGE	Allows an image to be scaled to width or scaled to height within the single anchor cell specified by R1C1.
	ANCHOR	*MOVE	Move with cells but do not resize.
		*NOMOVE	Do not move or resize with underlying rows/columns.
		*MOVEANDRESIZE	Default. Move and resize with anchor cells.

Example

ADD OBJECT(*IMAGE) R1C1(5,8) FILE(boat.png) RESIZE(0.5)

ADD OBJECT(*IMAGE) R1C1(2,3) R2C2(4,5) FILE(boat.png) RESIZE(*CELL) ROWHEIGHT(3000) COLUMNWIDTH(4000)

ADD OBJECT(*IMAGE) R1C1(6,3) R2C2(8,4) FILE(boat.jpg) RESIZE(*CELL) ANCHOR(*MOVEANDRESIZE) ROWHEIGHT(800) COLUMNWIDTH(4000) DX1DY1(10,20) DX2DY2(-10,-20)

ADD OBJECT(*IMAGE) R1C1(6,7) R2C2(8,8) FILE(boat.jpg) RESIZE(*CELL) ANCHOR(*NOMOVE) ROWHEIGHT(800) COLUMNWIDTH(4000)

ADD OBJECT(*IMAGE) R1C1(6,3) FILE(boat.jpg) RESIZE(*IMAGE) HEIGHT(100) ANCHOR(*MOVE) ROWHEIGHT(*CALC) COLUMNWIDTH(*CALC) DX1DY1(10,10)

ADD OBJECT(*IMAGE) R1C1(7,3) FILE(boat.jpg) RESIZE(*IMAGE) WIDTH(100) DX1DY1(10,10) ROWHEIGHT(*CALC) ANCHOR(*MOVE)

ADD FORMULA

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*FORMULA
	SHEET	value	Optional. A specific sheet name can be entered otherwise the current context sheet is used.
	R1C1	n,n	Required. Cell row and column.
	FORMULA	value	Required. Excel formula.

Example

ADD OBJECT(*FORMULA) R1C1(4,2) FORMULA("SUM(H5:H44)")

ADD OBJECT(*FORMULA) R1C1(2,12) FORMULA("HYPERLINK(\"http://www.lansa.com\",\"Test Link\")")

ADD HYPERLINK

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*HYPERLINK
	SHEET	value	Optional. A specific sheet name can be entered otherwise the current context sheet is used.
	R1C1	n,n	Required. Cell row and column.
	TYPE	*URL	Default. Type of hyperlink.
		*FILE
		*EMAIL
		*DOCUMENT
	ADDRESS	value	Required. Hyperlink address.
	LABEL	value	Optional. Hyperlink label.

Example

ADD OBJECT(*HYPERLINK) R1C1(10,2) TYPE(*URL) ADDRESS(http://www.lansa.com) LABEL(LANSA Home)

ADD OBJECT(*HYPERLINK) R1C1(13,1) TYPE(*DOCUMENT) ADDRESS(MYSHEET!A2) LABEL(Employees)

ADD NAME

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*NAME
	SHEET	value	Optional. Sheet name to prefix reference, if sheet name not included in reference.
	NAME	value	Required. Name
	REFERENCE	value	Required. FORMULA reference.
	COMMENT	value	Optional. Comment

Example

ADD OBJECT(*NAME) NAME(TOTAL) REFERENCE(A1:C5)

ADD OBJECT(*NAME) NAME(TOTAL) REFERENCE(Sheet1!A1:C5) COMMENT(Total Amount)

ADD OBJECT(*NAME) NAME(TOTAL) REFERENCE(SUM(Sheet1!I$2:I$6)) COMMENT(Total Amount)

ADD CELL STYLE

Syntax:

Command	Keyword	Value	Developer notes
ADD	OBJECT	*CELLSTYLE
	SHEET	value	Required. Sheet name associated with the cell style.
	COLUMN	n n.n	Required. Specify the column number or range of column numbers you wish your cell style to act on. You can specify a single column number or a comma-separated range.
	RANGE	n,n	Optional. Define the range of rows within the specified column on which this cell style will act. If left blank, then the entire column will be acted upon.
	TYPE	*NUMBER	Optional. Definition of the column type. Default.
		*DATE
		*BOOLEAN
		*STRING
		*BLANK
	FORMAT	value	Optional. Excel format to be applied to the cell value. The default format depends on the cell type. If the cell type is DATE the default format is the built-in format FORMAT14 which is an internationalised date format. The default format for all other cell types is the built-in format *FORMAT0 which is the General format.
		*FORMAT0	General format
		*FORMAT1	0
		*FORMAT2	0.00
		*FORMAT3	#,##0
		*FORMAT4	#,##0.00
		*FORMAT5	"$"#,##0_);("$"#,##0)
		*FORMAT6	"$"#,##0_);[Red]("$"#,##0)
		*FORMAT7	"$"#,##0.00_);("$"#,##0.00)
		*FORMAT8	"$"#,##0.00_);[Red]("$"#,##0.00)
		*FORMAT9	0%
		*FORMAT10	0.00%
		*FORMAT11	0.00E+00
		*FORMAT12	# ?/?
		*FORMAT13	# ??/??
		*FORMAT14	m/d/yy
		*FORMAT15	d-mmm-yy
		*FORMAT16	d-mmm
		*FORMAT17	mmm-yy
		*FORMAT18	h:mm AM/PM
		*FORMAT19	h:mm:ss AM/PM
		*FORMAT20	h:mm
		*FORMAT21	h:mm:ss
		*FORMAT22	m/d/yy h:mm
		*FORMAT37	#,##0_);(#,##0)
		*FORMAT38	#,##0_);[Red](#,##0)
		*FORMAT39	#,##0.00_);(#,##0.00)
		*FORMAT40	#,##0.00_);[Red](#,##0.00)
		*FORMAT41	_("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)
		*FORMAT42	_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
		*FORMAT43	_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
		*FORMAT44	_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
		*FORMAT45	mm:ss
		*FORMAT46	[h]:mm:ss
		*FORMAT47	mm:ss.0
		*FORMAT48	##0.0E+0
		*FORMAT49	@
	FONT	value	Any valid Windows font name.
		*ARIAL
		*CALIBRI
		*COURIER
		*COURIERNEW
		*TAHOMA
		*TIMES
	FONTSIZE	value	Optional. The font size. The default point size is 11.
	FONTCOLOR	color	Optional. Specify the font color. Refer to the color list at the end of this table.
	BOLD	*YES	Optional. Set the font to bold.
	BOLD	*NO	Default.
	ITALIC	*YES	Optional. Set the font to italics or not.
	ITALIC	*NO	Default.
	BORDER	*ALL	Optional. Define the lines of the border. There is no default value.
		*NONE
		*TOP
		*BOTTOM
		*LEFT
*RIGHT
BORDERSTYLE	*NONE	Conditional. Define the border style. The BORDER keyword needs to be specified in conjunction with this keyword.
	*MEDIUM
	*THICK
	*THIN	Default.
	*HAIR
	*DOUBLE
BORDERCOLOR	color	Conditional. Define the border color. The BORDER keyword needs to be specified in conjunction with this keyword. Refer to the color list at the end of this table.
BACKGROUND	color	Optional. Define the background color. Refer to the color list at the end of this table. There is no default value.
WRAP	*YES	Optional. Enable text wrap.
WRAP	*NO	Default.
LOCKED	*YES	Optional. Enable locked.
LOCKED	*NO	Default.
INDENT	value	Optional. Specify the cell value indent. The default value is 0.
HALIGN	*CENTER	Optional. Define the horizontal alignment. There is no default value.
	*FILL
	*GENERAL
	*JUSTIFY
	*LEFT
	*RIGHT
VALIGN	*CENTER	Optional. Define the vertical alignment. There is no default value.
	*JUSTIFY
	*TOP
	*BOTTOM

FONT, BORDER and BACKGROUND COLORS

*AQUA	*DARKTEAL	*LIGHTCORNFLOWERBLUE	*PLUM
*AUTOMATIC	*DARKYELLOW	*LIGHTGREEN	*RED
*BLACK	*GOLD	*LIGHTORANGE	*ROSE
*BLUE	*GREEN	*LIGHTTURQUOISE	*SEAGREEN
*BLUEGREY	*GREY25	*LIGHTYELLOW	*SKYBLUE
*BRIGHTGREEN	*GREY40	*LIME	*TAN
*BROWN	*GREY50	*MAROON	*TEAL
*CORAL	*GREY80	*OLIVEGREEN	*TURQUOISE
*CORNFLOWERBLUE	*INDIGO	*ORANGE	*VIOLET
*DARKBLUE	*LAVENDAR	*ORCHID	*WHITE
*DARKGREEN	*LEMONCHIFFON	*PALEBLUE	*YELLOW
*DARKRED	*LIGHTBLUE	*PINK

Example

ADD OBJECT(*CELLSTYLE) COLUMN(1,5) RANGE(3,5) FONT(*TAHOMA)

ADD OBJECT(*CELLSTYLE) TYPE(*NUMBER) COLUMN(8) FORMAT(*FORMAT4)

ADD OBJECT(*CELLSTYLE) TYPE(*STRING) COLUMN(1) RANGE(10,15) FONT(*ARIAL) BOLD(*YES)

ADD OBJECT(*CELLSTYLE) TYPE(*DATE) COLUMN(7) RANGE(20,25) FORMAT(dd mm yyyy)

ADD OBJECT(*CELLSTYLE) TYPE(*NUMBER) COLUMN(2) RANGE(10,15) FORMAT(#.##;#.##;-) HALIGN(*CENTRE) BACKGROUND(*YELLOW) FONT(*ARIAL) FONTSIZE(14.5) FONTCOLOR(*RED) ITALIC(*YES) BORDER(*ALL) BORDERCOLOR(*BLUE) BORDERSTYLE(*MEDIUM)