Overview of ExcelService commands

Date:7 November 2013
Product/Release:LANSA Integrator - V13 SP1 EPC131300
Abstract:Typical usage of the ExcelService shipped in EPC131300 for LANSA Integrator
Submitted By:LANSA Technical Support

EPC131300 introduces a new service ExcelService to read and write *.xlsx files. This service uses the open source Apache POI 3.1.0 classes so a specific set of jar files are required for running the excel service. This set of required jar files can be downloaded here. The download also includes some ExcelService examples.

The following list is a simple overview of the ExcelService commands and their typical usage:

==============================
Open file for reading.

OPEN FILE(path)
...
CLOSE
==============================
Open file for modifying and save changes.

OPEN FILE(path)
...
SAVE
CLOSE
==============================
Open file for modifying and save changes to another file.

OPEN FILE(path)
...
SAVE FILE(path)
CLOSE
==============================
Create new document and save to file.

CREATE
...
SAVE FILE (path)
CLOSE
==============================
Create new file using contents of another file.

CREATE USING(path)
...
SAVE FILE (path)
CLOSE
==============================
Get list of sheets in opened or created file.

GET OBJECT(*SHEETS) #WRKLST
==============================
Get max number of rows in specified sheet.

GET OBJECT(*ROWCOUNT) SHEET(name)

If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Set the context/current working sheet.

SET OBJECT(*CONTEXT) SHEET(name)

If sheet does not exist, NOSHEET status is returned.
==============================
Set width of column or a range of columns.

SET OBJECT(*COLUMNWIDTH) SHEET(name) RANGE(c1,c2) WIDTH(integer)

RANGE keyword is mandatory.
WIDTH keyword is optional, the default is 2560.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Set sheet properties.

SET OBJECT(*SHEET) SHEET(name)

If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.

Optional keywords

SELECTED(*YES|*NO)
DISPLAYGRID(*YES|*NO)
PRINTGRID(*YES|*NO)
PRINTAREA($A$1:$B$2)
PRINTTOFIT(*YES|*NO)
PROTECT(*YES|*NO) PASSWORD()
FREEZE(n,n) or FREEZE(n,n,n,n)
MARGIN(top,bottom,left,right,header,footer)
HEADER(*LEFT|*RIGHT|*CENTER) CONTENT()
FOOTER(*LEFT|*RIGHT|*CENTER) CONTENT()
==============================
Add a workbook name, this name is scoped to the workbook.

ADD OBJECT(*NAME) NAME(name) SHEET() REFERENCE(A1:C5) COMMENT(text)

NAME keyword is mandatory.
REFERENCE keyword is mandatory.
COMMENT keyword is optional.
SHEET keyword is optional and used to prefix the reference value with 'sheetname'!.
==============================
Create a new sheet

ADD OBJECT(*SHEET) SHEET()

SHEET keyword is mandatory.
==============================
Add image to sheet.

ADD OBJECT(*IMAGE) SHEET() R1C1() FILE(path) RESIZE(1.0)

FILE keyword is mandatory.
R1C1 keyword is mandatory.
RESIZE keyword is optional and is used to scale the image, the default is 1.0.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Add comment to cell.

ADD OBJECT(*COMMENT) SHEET() R1C1() COMMENT() AREA(2,4)

R1C1 keyword is mandatory.
AREA keyword is optional and sizes the display rectangle, the default is 2,4.
If no COMMENT keyword, the comment is removed from the cell.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Add formula to cell.

ADD OBJECT(*FORMULA) SHEET() R1C1() FORMULA()

R1C1 keyword is mandatory.
FORMULA keyword is mandatory.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Add hyperlink to cell.

ADD OBJECT(*HYPERLINK) SHEET() R1C1() LABEL() TYPE(*URL|*FILE|*EMAIL|*DOCUMENT) ADDRESS()

R1C1 keyword is mandatory.
ADDRESS keyword is mandatory.
TYPE keyword is optional and defaults to *URL.
LABEL keyword is optional and defaults to empty string.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Add a cell style for the specified sheet.

You can now define cell styles for a specified sheet.

This is used when a cell is written to a sheet.

ADD OBJECT(*CELLSTYLE)
SHEET(name)
TYPE(*NUMBER|*BOOLEAN|*DATE|*STRING|*BLANK)
RANGE(r1,r2)
COLUMN(c1) or COLUMN(c1,c2)
FORMAT(*FORMAT0|*FORMAT1|...|pattern)
BACKGROUND(*BLACK|...)
BORDER(*ALL|*NONE|*TOP|*BOTTOM|*LEFT|*RIGHT)
BORDERSTYLE(*NONE|*MEDIUM|*THICK|*THIN|*HAIR|*DOUBLE)
BORDERCOLOR(*BLACK|...)
FONT(*ARIAL|*CALIBRI|*COURIER|*COURIERNEW|*TAHOMA|*TIMES|name)
FONTSIZE(11)
FONTCOLOR(*BLACK|...)
BOLD(*YES|*NO)
ITALIC(*YES|*NO)
WRAP(*YES|*NO)
LOCKED(*YES|*NO)
INDENT()
HALIGN(*LEFT|*RIGHT|*CENTER|*JUSTIFY|*FILL|*GENERAL)
VALIGN(*CENTER|*JUSTIFY|*TOP|*BOTTOM)

If no SHEET keyword then the current context sheet is used.

RANGE keyword is mandatory.
COLUMN keyword is mandatory.
TYPE keyword defaults to *NUMBER.
FORMAT keyword defaults to *FORMAT14 for *DATE and *FORMAT0 (General) for other types.

FONT keyword is optional.
FONTSIZE keyword is optional and defaults to 11.
FONTCOLOR keyword is optional and defaults to BLACK.
BORDERCOLOR keyword is optional and defaults to BLACK.
BACKGROUND keyword is optional.
BOLD keyword is optional and defaults to *NO.
ITALIC keyword is optional and defaults to *NO.
WRAP keyword is optional and defaults to *NO.
LOCKED keyword is optional and defaults to *NO.
INDENT keyword is optional.
HALIGN keyword is optional.
VALIGN keyword is optional.

Excel built-in formats

*FORMAT0 General
*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 @
==============================
Remove workbook name.

REMOVE OBJECT(*NAME) NAME()

NAME keyword is mandatory.
==============================
Remove all cell styles.

REMOVE OBJECT(*CELLSTYLE) SHEET(*ALL)

Remove cell styles for specified sheet.

REMOVE OBJECT(*CELLSTYLE) SHEET(name)

If no SHEET keyword then the current context sheet is used.
==============================
Remove sheet.

REMOVE OBJECT(*SHEET) SHEET()

If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Removed rows from sheet.

REMOVE OBJECT(*ROW) SHEET() RANGE(r1,r2)

RANGE keyword is mandatory.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Removed columns from sheet.

REMOVE OBJECT(*COLUMN) SHEET() RANGE(c1,c2)

RANGE keyword is mandatory.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
Remove print area from sheet.

REMOVE OBJECT(*PRINTAREA) SHEET()

If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
READ SHEET() R1C1(1,1) ROWCOUNT() IGNORE(*NONE|*EMPTY)

R1C1 keyword is optional, defaults to 1,1.
ROWCOUNT keyword is optional.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================
WRITE SHEET() R1C1(1,1) EXCLUDE(field,field,...)

EXCLUDE keyword is optional.
R1C1 keyword is optional, defaults to 1,1.
If no SHEET keyword then the current context sheet is used.
If sheet does not exist, NOSHEET status is returned.
==============================