Page 1 of 1
ExcelService - formatting
Posted: Wed May 29, 2024 5:28 am
by 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?
Re: ExcelService - formatting
Posted: Wed May 29, 2024 8:01 am
by 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.
Re: ExcelService - formatting
Posted: Thu May 30, 2024 5:16 am
by 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 ""
Re: ExcelService - formatting
Posted: Thu May 30, 2024 7:59 am
by 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.
Re: ExcelService - formatting
Posted: Fri May 31, 2024 3:23 am
by 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.
Re: ExcelService - formatting
Posted: Fri May 31, 2024 3:55 am
by 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