5.36.2 Using the ExcelService

Typical ExcelService Command Usage

Whether you are writing your client application in RDML / RDMLX or in a 3GL such as RPG, you have to complete the same basic steps.

For example, an application that reads an EXCEL spreadsheet would typically issue the following sequence of commands:

JSM(X)_OPEN

JSM(X)_COMMANDs

     SERVICE_LOAD

          OPEN

          READ / WRITE

          CLOSE

     SERVICE_UNLOAD

JSM(X)_CLOSE

Refer to Java Service Manager Clients for the command details that apply to your chosen development language.

Command usage examples

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)

 

Set the context/current working sheet

SET OBJECT(*CONTEXT) SHEET(name)
 

Set width of column or a range of columns

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

 

Create a new sheet

ADD OBJECT(*SHEET) SHEET()

 

Add image to sheet

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

 

Add comment to cell

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

 

Add formula to cell

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

 

Add hyperlink to cell

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

 

Remove all cell styles

REMOVE OBJECT(*CELLSTYLE) SHEET(*ALL)

 

Remove cell styles for specified sheet

REMOVE OBJECT(*CELLSTYLE) SHEET(name)

 

Remove sheet

REMOVE OBJECT(*SHEET) SHEET()

 

Remove rows from sheet

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

 

Remove columns from sheet

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

 

Remove print area from sheet

REMOVE OBJECT(*PRINTAREA) SHEET()