Oscript to read and write text to Microsoft Excel

I have a custom developed module where the users can add their comments in a grid which are distributes via cs workflow. At some point of the workflow task, the user should have a facility to download all comments within this grid in an Excel format and send it across to external parties to fill-in their response against each commented row. Once received the response from external parties, the user should have a facility to upload excel spreadsheet back to workflow. My Custom Module should read each response and update back to database.

I can achieve this by providing a csv file, but the issue is that csv file will not have a defined format and also I cannot lock a column which carries unique id which reference to database table key fields.

Any help to read and write to excel file is highly appreciated

Regards,

Sminil,

Find more posts tagged with

Comments

Pramod_Mohandas

Hi Sminil,

If it's just CSV, OScript can both read/work with it right out-of-the-box. But if it's a XLS or XLSX manipulation that you are looking for, you might need to write Java objects (not actual JAVA development) and call the same from OScript to help you with the user comment addition and manipulation.

So it works like OScript --> Java Object --> Excel --> Oscript, where you would use the Java Object to read/write/manipulate your Excel sheet and submit it to OScript with details for further processing.

Thanks & Regards,

Pramod Mohandas

Mani_Sumathy_Sminil

Hi Pramod,

Thanks for the valuable response. If you have a small example to share how Oscript can call and operate a java utility would be a great help.

Then I can google & investigate to develop a java utility to manipulate excel spreadsheet.

Regards,

Sminil

eLink User

This example was posted by Rolf Poser,I took his code and made it into something I could understand.I did this many years back so not sure if it will work straight out but hopefully you get the idea
https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=17019239&objAction=download&viewType=1

Kyle Swidrowich has written an article that says how to load the javavm for 10 software , not sure if it is applicable

I dont have the link handy but if you go to forums like this or builder or OTDN and type that name it should show up.I remember they made it into a championship document as well.

some more links and is mostly starter material and experiments
http://livelink.in/2013/04/15/to-err-is-javato-forgive-oscript/

What Pramod did not tell you is OT has a framework called "Document Properties Synchronization" which is used to manipulate MS office docs by oscript .I do not know the implementation,it is quite possible you may find something intereting there as well.

..Appu

Never argue with idiots. They drag you down to their level, and then beat you with experience.
Eat one live toad the first thing in the morning and nothing worse will happen to you the rest of the day

via : Laugh IT Out: Dilbert’s Best Quotes

On Wed, Jun 18, 2014 at 1:52 AM, eLink Entry: Content Server Development Forum <development@elinkkc.opentext.com> wrote:

Oscript to read and write text to Microsoft Excel Posted bysminil.mani@mannai.com.qa (Mani Sumathy, Sminil)On 06/18/2014 02:48 AM

Hi Pramod, Thanks for the valuable response. If you have a small example to share how Oscript can call and operate a java utility would be a great help. Then I can google & investigate to develop a java utility to manipulate excel spreadsheet. Regards, Sminil
[To post a comment, use the normal reply function]
Topic: Oscript to read and write text to Microsoft Excel
Forum: Content Server Development Forum
Content Server: Knowledge Center

John W. Simon, Jr.

A completely different take...out of the box you can export a collection to a spread sheet so you already have an example of code creating a spreadsheet built right in (and no java modules needed)!

Additionally if you save an XLS file as an XML file, then open it with a text editor you will see how to set column widths, heights, as well as the other formatting options available in excel.

Mani_Sumathy_Sminil

Thanks Appu for sharing those useful links. It was really helpful.

By the way, I found the solution and completed this functionality. This was achieved as suggested by Pramod. OScript -->JavaObjcet --> Excel Manipulation -->JavaObject --> OScript.

In builder help file we have a datatype call JavaObject which explains how to call a java executable (jar) file. Little bit tricky, but finally achieved.

Apache provide a free java library called (POI) which contains methods to manipulate Microsoft office application.

I investigated in OpenText DPS module as well, but this uses Microsoft ActiveX libraries. Transmittal module was helpful as this module uses the same approach (JavaObject)

Regards,

Sminil

Mani_Sumathy_Sminil

Thanks Pramod for sharing the idea.

Regards,

Sminil