LANSA Integrator: ExcelService - adding cellstyle for the sheet which is created using template

[Yukiko Ikeo]

Hi all,

The customer wants to do the followings.
- create the excel using template (template.xlsx)
- adding cellstyles

cellstyle details:
- Set background color and font for odd-numbered rows
- left align for 5th column

However when adding cellstyles, the cell style which is set in template is lost.
Is there any ways to just add the styles for cells? (= not replacing)
The test program and the screenshots are attached.

RDMLX:

Code: Select all

Function Options(*DIRECT)
*
Define Field(#JSMXCMD) Reffld(#STD_NVARC)
Define Field(#JSMXMSG) Reffld(#STD_NVARC)
Define Field(#JSMXHDLE) Type(*CHAR) Length(4)
Define Field(#JSMXSTS) Type(*NVARCHAR) Length(20)

Define Field(#DUMMYC01) Type(*CHAR) Length(1) Desc(DUMMY) Default(*BLANK)
Define Field(#DUMMYC02) Type(*CHAR) Length(1) Desc(DUMMY) Default(*BLANK)
Define Field(#DUMMYC03) Type(*CHAR) Length(1) Desc(DUMMY) Default(*BLANK)
Define Field(#DUMMYC04) Type(*CHAR) Length(1) Desc(DUMMY) Default(*BLANK)
Define Field(#PRDID) Type(*CHAR) Length(10)
Define Field(#PRDNME) Type(*CHAR) Length(20)
Define Field(#PRDAMT) Type(*DEC) Length(10) Decimals(2)
Define Field(#COUNT) Type(*DEC) Length(5) Decimals(0)
Define Field(#COUNT7S) Type(*DEC) Length(5) Decimals(0)
Define Field(#INT) Reffld(#STD_NUM)

Def_List Name(#SWSTFDPFList) Fields(#DUMMYC01 #DUMMYC02 #DUMMYC03 #DUMMYC04 #PRDID #PRDNME #PRDAMT) Type(*WORKING)
Def_List Name(#SWSTFDPFList_WR1) Fields(#DUMMYC01 #DUMMYC02 #DUMMYC03 #DUMMYC04 #PRDID #PRDNME #PRDAMT) Type(*WORKING)

* Initial processing
Clr_List Named(#SWSTFDPFList)
Begin_Loop Using(#COUNT) To(10)
#PRDID := 'ID' + #COUNT.AsString
#PRDNME := 'Product' + #COUNT.AsString
#PRDAMT := 2000.45 + #COUNT
Add_Entry To_List(#SWSTFDPFList)
End_Loop

*
* JSMX_BEGIN
*
Use Builtin(JSMX_BEGIN)

*
* JSMX_OPEN
*
#JSMXCMD := 'OPEN'
Use Builtin(JSMX_OPEN) To_Get(#JSMXSTS #JSMXMSG #JSMXHDLE)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

*
* SERVICE_LOAD
*
#JSMXCMD := 'SERVICE_LOAD SERVICE(ExcelService) TRACE(*YES)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

*
* CREATE
*
#JSMXCMD := 'CREATE USING(template.xlsx)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)


#COUNT7S := 4
Selectlist Named(#SWSTFDPFLIST)
#INT := #COUNT7S.mod( 2 )

* Style set to the 5th column of the current row => Left-aligned
#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) SHEET(Sheet1) COLUMN(5) RANGE(' + (#COUNT7S).AsString + ',' + (#COUNT7S).AsString + ') HALIGN(*LEFT) VALIGN(*CENTER)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)


* Odd numbered lines => Background: White, Font: MS Gothic
If Cond(#INT *NE 0)
#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) SHEET(Sheet1) COLUMN(1,7) RANGE(' + (#COUNT7S).AsString + ',' + (#COUNT7S).AsString + ') BACKGROUND(*WHITE)  FONT(MS Gothic) VALIGN(*CENTER)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)
Endif

Clr_List Named(#SWSTFDPFList_WR1)
Add_Entry To_List(#SWSTFDPFList_WR1)
#JSMXCMD := 'WRITE R1C1(' + (#COUNT7S).AsString + ',1)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG #SWSTFDPFList_WR1)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

#COUNT7S := #COUNT7S + 1

Endselect

*
* SAVE FILE
*
#JSMXCMD := 'SAVE FILE(CheckList.xlsx)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

*
* CLOSE
*
#JSMXCMD := 'CLOSE'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

*
* SERVICE_UNLOAD
*
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'SERVICE_UNLOAD') To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

*
* JSMX_CLOSE
*
Use Builtin(JSMX_CLOSE) With_Args(#JSMXHDLE) To_Get(#JSMXSTS #JSMXMSG)

Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

*
* JSMX_END
*
Use Builtin(JSMX_END)

*
* SUB ROUTINES
*
Subroutine Name(CHECK) Parms((#JSMXSTS *RECEIVED) (#JSMXMSG *RECEIVED))

*
If Cond('#JSMXSTS *NE OK')

*
Use Builtin(JSMX_CLOSE) With_Args(#JSMXHDLE) To_Get(#JSMXSTS #JSMXMSG)

*
* Menu Msgtxt(#JSMXCMD)
Endif

Endroutine

template.xlsx

(39.43 KiB) Downloaded 1411 times

CreatedExcel.PNG (76.27 KiB) Viewed 8183 times

Thanks in advance.
Best regards, Yukiko Ikeo

[Dino]

Hi

In brief, you cannot "overwrite". Then, you have to consider that in the code like this:

Code: Select all

Selectlist Named(#SWSTFDPFList)

#INT := #COUNT7S.Mod( 2 )

* Odd numbered lines => Background: White, Font: MS Gothic
If Cond(#INT *NE 0)
#STD_STRNG := 'BACKGROUND(*WHITE)  FONT(MS Gothic) VALIGN(*CENTER) RANGE(' + (#COUNT7S).AsString + ',' + (#COUNT7S).AsString + ') '

#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) SHEET(Sheet1) COLUMN(1,4) ' + #STD_STRNG
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) SHEET(Sheet1) COLUMN(5) HALIGN(*LEFT) ' + #STD_STRNG
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) SHEET(Sheet1) COLUMN(6,7) ' + #STD_STRNG
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)
Else
#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) SHEET(Sheet1) COLUMN(5) RANGE(' + (#COUNT7S).AsString + ',' + (#COUNT7S).AsString + ') HALIGN(*LEFT)  VALIGN(*CENTER) BACKGROUND(*LIGHTCORNFLOWERBLUE)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)
Endif


Clr_List Named(#SWSTFDPFList_WR1)
Add_Entry To_List(#SWSTFDPFList_WR1)
#JSMXCMD := 'WRITE R1C1(' + (#COUNT7S).AsString + ',1)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE #JSMXCMD) To_Get(#JSMXSTS #JSMXMSG #SWSTFDPFList_WR1)
Execute Subroutine(CHECK) With_Parms(#JSMXSTS #JSMXMSG)

#COUNT7S := #COUNT7S + 1

Endselect

Also, if you are going to manipulate colors, try to use the ones that are possible thru the java interface to excel as indicated at the end of this page:
https://docs.lansa.com/15/en/lansa093/i ... 7_4070.htm

Cant find the exact tone of blue you used in the original document.

excelcolors.jpg (94.39 KiB) Viewed 8152 times

[Yukiko Ikeo]

Hi Dino,

Thank you for your reply.
I understood that setting the style for each cell is necessary.

Best regards, Yukiko Ikeo

[Dino]

While we are in the subject of colors, I did a quick example to see all the possible colors, here for reference:

Code: Select all

Def_List Name(#lista) Fields(#std_textl) Type(*WORKING) Entrys(*MAX)

Use Builtin(JSMX_OPEN) To_Get(#JSMXSTS #JSMXMSG #JSMXHDLE)
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'SERVICE_LOAD SERVICE(EXCELSERVICE)') To_Get(#JSMXSTS #JSMXMSG)
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'CREATE') To_Get(#JSMXSTS #JSMXMSG)
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'ADD OBJECT(*SHEET) SHEET(SHEET1)') To_Get(#JSMXSTS #JSMXMSG #lista)
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'SET OBJECT(*CONTEXT) SHEET(SHEET1)') To_Get(#JSMXSTS #JSMXMSG #lista)

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

Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'SET OBJECT(*COLUMNWIDTH) RANGE(1,1) WIDTH(6000)') To_Get(#JSMXSTS #JSMXMSG)
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'WRITE SHEET(SHEET1) R1C1(1,1)') To_Get(#JSMXSTS #JSMXMSG #lista)

Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE ('SAVE FILE(colors' + #datetimex.Now.AsDisplayString( CCYYMMDDHHMMSS ) + '.xlsx)')) To_Get(#jsmxsts #jsmxmsg)
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'CLOSE') To_Get(#JSMXSTS #JSMXMSG)
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE 'SERVICE_UNLOAD') To_Get(#JSMXSTS #JSMXMSG)
Use Builtin(JSMX_CLOSE) With_Args(#JSMXHDLE) To_Get(#JSMXSTS #JSMXMSG)
Use Builtin(JSMX_END)
return

Subroutine Name(ADDCOLOR) Parms((#STD_TEXTL *RECEIVED))
#STD_INT += 1
Add_Entry
Use Builtin(JSMX_COMMAND) With_Args(#JSMXHDLE ('ADD OBJECT(*CELLSTYLE) COLUMN(1) RANGE(' + #STD_INT.AsString + ',' + #STD_INT.AsString + ') BACKGROUND(' + #STD_TEXTL + ')')) To_Get(#JSMXSTS #JSMXMSG)
Endroutine

excelcolors2.jpg (88.47 KiB) Viewed 8130 times

[Yukiko Ikeo]

Hi Dino,

You are so kind
It's helpful to confirm the color.
Thank you so much!

Best regards, Yukiko Ikeo