ExcelService - formatting

[stevelee67]

i've been fiddling around with the excel service and am having a heckuva time getting formatting to work.

example:

#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) COLUMN(1,17) RANGE(1,1) BACKGROUND(*BLUE)'
USE BUILTIN(jsmx_command) WITH_ARGS(#JSMXHDLE1 #JSMXCMD) TO_GET(#JSMXSTS #JSMXMSG)

i've tried this with data types as well (e.g., *DATE or *NUMBER)

i don't get an error and the trace shows that the command executed. but the output doesn't show the formatting.

any idea on what i'm missing?

[caseywhite]

Have you set the context first. Forget if that is required.
SET OBJECT(*CONTEXT) SHEET(name)

I am assuming you are doing a
WRITE then a CLOSE as well.

[stevelee67]

yes to the questions... mostly.

i set context right after the sheet was created

i didn't initially have the CLOSE but added it after seeing your suggestion.

i did some re-arranging of the code after working with a coworker... originally my WRITE statements were done first, followed by the ADD OBJECT(*CELLSTYLE). i've switched that around and after moving the WRITEs after the formatting steps most of it is working -

• i'm getting the color to show up in the header row
• column formatting (e.g., date or numeric) seems to be working

horizontal alignment, fontcolors, and bold don't seem to work yet and i'm assuming it's something that i'm doing wrong.

example:
#JSMXCMD := 'ADD OBJECT(*CELLSTYLE) COLUMN(6,15) RANGE(1) HALIGN(*RIGHT)'
USE BUILTIN(jsmx_command) WITH_ARGS(#JSMXHDLE1 #JSMXCMD) TO_GET(#JSMXSTS #JSMXMSG)

this is what i see in the trace SERVICE.TXT:
Process : *COMPONENT
Function : JDE0001
Partition : DEA
Job Name : LWEB_JOB
Job User : APIUSR
Job Number : 981114
Command : ADD HALIGN(*RIGHT) COLUMN(6,15) OBJECT(*CELLSTYLE) RANGE(1,1)
Add cell style to sheet : ARAging_2024-05-25
Command : OK ""

[caseywhite]

I know this sounds strange but what if after you do your WRITE you then attempt to add the CELLSTYLE to do the horizontal alignment. What I can't remember is whether some have to be done before a WRITE and others after.

[stevelee67]

it doesn't sound strange. kinda goes along with the quirkyness of the ExcelService

here is what i've found so far:

• it seems like formatting is a once and done thing - once i applied a style, additional styles didn't do anything. i can set color on a column, but can't later set the horizontal alignment
• not all *CELLSTYLEs work. i can't get BOLD, ITALIC, FONTSIZE, or FONTCOLOR to work

this works:

Code: Select all

('ADD OBJECT(*CELLSTYLE) COLUMN(1) RANGE(1,1) BACKGROUND(*PALEBLUE) HALIGN(*RIGHT)')

this does not (i get the color but not the alignment)

Code: Select all

('ADD OBJECT(*CELLSTYLE) COLUMN(1) RANGE(1,1) BACKGROUND(*PALEBLUE)')
('ADD OBJECT(*CELLSTYLE) COLUMN(1) RANGE(1,1) HALIGN(*RIGHT)')

i've tried mixing and matching where i do the *CELLSTYLE calls - before or after the WRITEs. after doesn't seem to work for me.

this is what i'm seeing and may not reflect other folk's experience.

[caseywhite]

Maybe open up a support ticket if you still can't get it to work. Here is something from the reusable part XJSMExcelServiceCommands I found in the IDE. I wonder if the order you put the properties for the style matters. I doubt it. If you are doing the same things as what is shown below, I would think it should work since the code below is from LANSA.

Code: Select all

Mthroutine Name(ExcelAddCellStyle) Help('Add a cellstyle to a nominated sheet and column.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Sheet) Help('Sheet name associated with the cell style.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Column) Help('Specify the column number or range of column numbers for cell style to act on. Specify a single column or a comma-separated range e.g. COLUMN(3) or COLUMN(3,5).')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Range) Mandatory('') Help('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 e.g. RANGE(3,5)')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Type) Mandatory('*NUMBER') Help('Define the column type - *NUMBER (default), *DATE, *BOOLEAN, *STRING, *BLANK')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Format) Mandatory('') Help('Excel format to be applied to the cell value. The default format depends on the cell type. For cell type *DATE the default format is the builtin format *FORMAT14 (an internationalised date format). The default format for all other cell types is the builtin format *FORMAT0 (the General format).')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Font) Mandatory('') Help('Font is any valid Windows font name. Special values *ARIAL, *CALIBRI, *COURIER, *COURIERNEW, *TAHOMA, *TIMES are substituted to their standard Windows font names.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#FontSize) Mandatory('11') Help('Used to define the font size. The default point size is 11.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#FontColor) Mandatory('') Help('Used to define the font color.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Bold) Mandatory('*NO') Help('Used to set the font to bold - *NO (default), *YES')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Italic) Mandatory('*NO') Help('Used to set the font to italic - *NO (default), *YES')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Border) Mandatory('') Help('Used to define the lines of the border - *ALL, *NONE, *TOP, *BOTTOM, *LEFT, *RIGHT.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#BorderStyle) Mandatory('*THIN') Help('Used to define the borderstyle. The BORDER keyword needs to be specified in conjunction with this keyword. Possible values - *NONE, *MEDIUM, *THICK, *THIN (default), *HAIR, *DOUBLE.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#BorderColor) Mandatory('') Help('Used to define the border color.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Background) Mandatory('') Help('Used to define the background color.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Wrap) Mandatory('*NO') Help('Used to enable text wrap - *NO (default), *YES.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Locked) Mandatory('*NO') Help('Used to enable locked - *NO (default), *YES.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#Indent) Mandatory('0') Help('Used to specify the cell value indent. Default value 0.')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#HAlign) Mandatory('') Help('Used to define the horizontal alignment - *CENTRE, *CENTER, *FILL. *GENERAL, *JUSTIFY, *LEFT, *RIGHT')
Define_Map For(*INPUT) Class(#xjsmstring) Name(#VAlign) Mandatory('') Help('Used to define the vertical alignment - *CENTRE, *CENTER, *JUSTIFY, *TOP, *BOTTOM')
Define_Map For(*OUTPUT) Class(#xjsmstatus) Name(#Status) Help('Return Status from last JSMX_* built in function executed')
Define_Map For(*OUTPUT) Class(#xjsmmessage) Name(#Message) Help('Message associated with return status')
Define_Map For(*RESULT) Class(#prim_boln) Name(#Result) Help('Boolean indicating if routine completed successfully')

#Result := False

#xjsmcmd := 'add Object(*CellStyle) Sheet(' + #Sheet + ') Column(' + #Column + ') Range(' + #Range + ') Type(' + #type + ') Font(' + #Font + ') FontSize(' + #FontSize + ') FontColor(' + #FontColor + ') Bold(' + #Bold + ') Italic(' + #Italic + ') Border(' + #Border + ') BorderStyle(' + #BorderStyle + ') BorderColor(' + #BorderColor + ') BackGround(' + #BackGround + ') Wrap(' + #Wrap + ') Locked(' + #Locked + ') Indent(' + #Indent + ') HAlign(' + #HAlign + ') VAlign(' + #VAlign + ')'

* Only add the format is nominated to ensure correct default is applied
If (#format <> *blank)

#xjsmcmd := #xjsmcmd + ' Format(' + #Format + ')'

Endif

Use Builtin(jsmx_command) With_Args(#xjsmhandle #xjsmcmd) To_Get(#Status #Message)

If (#Status = OK)

#Result := True

Endif

Endroutine