Generating an XLS in Livelink

[eheinen]

Hi there,

I have a good amount of experiencing in Livelink development in Livelink 9.5 and 9.7.

We have a requirement to generate a complex report from the database, and ideally output the results to a MS Excel file, which can then be automatically saved within Livelink. Due to the complexity, this report has already been created as a request handler object, not Live Report.

The user will then be redirected to the Excel file, which they can download or just view, and if needed send the link to other users.

My question is, how can I generate this XLS file within OScript? Once generated, I'm sure adding and redirecting the user is easy enough.

Regards,
Elrico
Certified Developer

 

[ggriffit]

Elrico,
Take a look at

http://www.greggriffiths.org/webdev/both/excel/

which covers generating XLS files from the server side for a web application, am sure that the concepts could be used in Livelink for your situation.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[appnair]

Depending on how complex this excel file should be.
I have done
1)Write a comma separated csv file,and provide a link and if user prefers opening it in excle it should work.
2)on the download file that I am sending I could set mimetype as that of excel this would make the browser think it is receiving a excel worksheet.

However since you say you pretty much know these things my thought is that you really want an instantiation of a excel object server side.the only ways I know (not having done it) is to use the activex package and wrap your code to create an excel file or now with newer livelink code base oscript can call java classes.I am sure you should be able to find some e.g out there that shows you how to create a excel file from java.if you need help on just the bsaic setup of oscript to java check the communities or KB website .I ought to have an e.g. for that,the real work was done by my good friend Rolf ,I just made it a little more readable

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[eheinen]

Thanks, yes. If its the same Rolf I know, he's done some work already for this client of mine.

Greg - I've gone through your link. While my OScript is pretty decent, my HTML is less. Setting the content type - would that be done in the request handlers html file? I assume after that, IE should render and Excel document using the table structure. Your server side tutorial seems to be ASP? In which case the MS classes cannot be used.

appnair - this csv file solution you mention. My idea was to create, then add to Livelink, and provide the user the link to view it inside Livelink. Adding a file and returning the link is not new to me, but creating a file (csv or excel) is. Is there an oscript object for this?

 

[ggriffit]

The content type is set in the Request Handler object, or you could do something like

http://www.freecodecollection.com/livelink-json-output-for-ajax-request-handler/.

My tutorial does use Classic ASP as its serverside example, but the same could be done in any other server side language e.g. Java, OScript etc

For a simple Text type file, see

http://www.greggriffiths.org/livelink/development/oscript/eventscripts/llobjfromform.html

which streams a HTML file into Livelink.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[appnair]

I meant using the file object and writing out a comma separated or tab separated file.If using complex formulas and stuff or things like hyperlink I would just make an e.g file in excel look at what the formula is and then painstakingly write out the same things.I ad a requirement to create hyperlinks so I found that =Hyperlink(E2) would do it so I just used logic in my file writing to do that,nothing rocket science.


Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[eheinen]

Thanks guys. I got it working using Greg's simple example - although a messagebox says that the file is not the correct format everytime. But it works!

I'll try the CSV route, as ideally I'd like the file to reside in Livelink where links can be mailed out - rather than just output to the client's Excel.

As for the contents, its very basic as none of the cells would need any formula's or hyperlinks. I'll let you know how the CSV goes.

 

[appnair]

As I said I do not have time to check it with oscript or lapi butI have for you figured out the instructions how it is laid out in the tables.I would test manaul running and test accuracy of the relation and then call this form lapi using a db query or livereport.

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=18041350&objAction=download

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair

 

[clbr]

If you ever do need to save a LiveReport as an Excel file, I found two products that do this: Resonate KT Web Reports and Palfrey Street ReportPlus. We use the ReportPlus product.