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

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
MegumiSawada
Posts: 52
Joined: Tue Mar 22, 2016 1:45 pm
Location: Tokyo, Japan

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

Post by MegumiSawada » Wed Aug 26, 2020 2:55 pm

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
Posts: 81
Joined: Thu Nov 26, 2015 7:03 am

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

Post by René Houba » Thu Aug 27, 2020 1:53 am

Hi Megumi,

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

Kind regards,
René

jyoung
Posts: 690
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

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

Post by jyoung » Thu Aug 27, 2020 5:10 am

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
templates.PNG (29.71 KiB) Viewed 1073 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 :shock:

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

MegumiSawada
Posts: 52
Joined: Tue Mar 22, 2016 1:45 pm
Location: Tokyo, Japan

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

Post by MegumiSawada » Thu Aug 27, 2020 10:36 am

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

Post Reply