Integrator - Copy a template sheet to create a new sheet on ExcelService

[MegumiSawada]

Hi,

I'm not sure I can post a question regarding Integrator here...

Is it possible to create a new sheet based on the specified sheet as a template?
I'm using LANSA Integrator ExcelService.

I know I can create a new "blank" sheet.
I know I can write data to a specified sheet(withe template).
However, I am not able to copy a template sheet and create a new sheet with it, and write data to that sheet.

Could you please let me know how to if you've done this before?

I appreciate your kind advice.

Best Regards,
Megumi Sawada

[René Houba]

Hi Megumi,

As far as I know this is not possible.
Please send this as an enhancement request to support

Kind regards,
René

[jyoung]

Hi Megumi,

I think it depends on what type of template you are using.
I don't think formulas and things like that work, but you can have a template and use that as a starting point.
That is how we handle a couple different reports.

For example, I have three templates defined in a "templates" folder in Integrator.

templates.PNG (29.71 KiB) Viewed 1072 times

The "PTODepartmentTemplate" has some header cells defined.

You can use the "using" option outlined at https://docs.lansa.com/14/en/lansa093/i ... 7_4050.htm

Then you can write in a specific cell using the "WRITE" command with the "R1C1" option outlined at https://docs.lansa.com/14/en/lansa093/i ... 7_4085.htm

Here are a couple examples we use:

Create from the template

Code: Select all

mthroutine name(CreateFile)
define_map for(*OUTPUT) class(#PRIM_ALPH) name(#filePath)
define_map for(*RESULT) class(#wk_Status) name(#status)

define_com class(#PRIM_ALPH) name(#fileName)
define_com class(#PRIM_ALPH) name(#templatePath)
define_com class(#PRIM_DATE) name(#date)

#status := ER

if (*OSAPI = IBMI)
#templatePath := 'templates/PTODepartmentTemplate.xlsx'
else
#templatePath := 'templates\PTODepartmentTemplate.xlsx'
endif

#fileName := ('Department-PTO-Requests-&1-&2.xlsx').Substitute( #DepartmentId.AsString #WeekEndDate.AsString )
#filePath := *PART_DIR + #fileName

#JSMXCMD := ('CREATE USING(&1)').Substitute( #templatePath )
#SYS_APPLN.TraceMessageData( #JSMXCMD )
use builtin(JSMX_COMMAND) with_args(#JSMXHDLE #JSMXCMD) to_get(#JSMXSTS #JSMXMSG)
if (#COM_OWNER.CheckStatus( #JSMXSTS #JSMXMSG ) = ER)
return
endif

#status := OK
endroutine

Writing a header line with values in a specific cell (B4) and (B6) on the "Master" sheet.

Code: Select all

mthroutine name(AddHeader)
define_map for(*RESULT) class(#wk_Status) name(#status)

#status := ER

clr_list named(#ExcelData)

fetch fields(#DepartmentName) from_file(PTODepartment) with_key(#DepartmentId)
#wk_String1 := #DepartmentName

add_entry to_list(#ExcelData)

#JSMXCMD := 'WRITE SHEET(Master) R1C1(4,2)'
#SYS_APPLN.TraceMessageData( #JSMXCMD )
use builtin(JSMX_COMMAND) with_args(#JSMXHDLE #JSMXCMD) to_get(#JSMXSTS #JSMXMSG #ExcelData)
if (#COM_OWNER.CheckStatus( #JSMXSTS #JSMXMSG ) = ER)
return
endif

clr_list named(#ExcelData)
#wk_String1 := ("&1 thru &2").Substitute( #wk_StartDate.AsDate( CCYYMMDD ).AsDisplayString( MMsDDsCCYY ) #wk_EndDate.AsDate( CCYYMMDD ).AsDisplayString( MMsDDsCCYY ) )
add_entry to_list(#ExcelData)

#JSMXCMD := 'WRITE SHEET(Master) R1C1(4,6)'
#SYS_APPLN.TraceMessageData( #JSMXCMD )
use builtin(JSMX_COMMAND) with_args(#JSMXHDLE #JSMXCMD) to_get(#JSMXSTS #JSMXMSG #ExcelData)
if (#COM_OWNER.CheckStatus( #JSMXSTS #JSMXMSG ) = ER)
return
endif

#status := OK
endroutine

Hope this helps,
Joe

EDIT
When I read your post, I was like wait a minute I have done this.
I should have read it a bit closer. lol

Unfortunately, I have not seen a way to copy and create a new sheet.

[MegumiSawada]

Hi René and Joe,

Thank you for your reply!
I now understand it is not possible...
I will send an enhancement request via support.
Thank you again!

Best Regards,
Megumi Sawada