Visual LANSA Forum

Community Forum for Visual LANSA
https://forum.developer.lansa.com/

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

https://forum.developer.lansa.com/viewtopic.php?t=2745

Page 1 of 1

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

Posted: Tue Jun 06, 2023 11:56 am
by 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
CreatedExcel.PNG (76.27 KiB) Viewed 8187 times
Thanks in advance.
Best regards, Yukiko Ikeo

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

Posted: Wed Jun 07, 2023 6:31 am
by 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
excelcolors.jpg (94.39 KiB) Viewed 8156 times

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

Posted: Wed Jun 07, 2023 4:50 pm
by Yukiko Ikeo
Hi Dino,

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

Best regards, Yukiko Ikeo

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

Posted: Wed Jun 07, 2023 10:56 pm
by 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
excelcolors2.jpg (88.47 KiB) Viewed 8134 times

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

Posted: Thu Jun 08, 2023 6:24 pm
by Yukiko Ikeo
Hi Dino,

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

Best regards, Yukiko Ikeo
All times are UTC+11:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited