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


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:

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

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

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

*NO

Default.

ITALIC

*YES

Optional. Set the font to italics or not.

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

*NO

Default.

LOCKED

*YES

Optional. Enable 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)