Need Info on Excel file to WM record structure conversion

, ,
1

Hi,

I am Rani and Joined newly to Wmuser group. I need the following information on webMethods

We have a requirement to convert excel file to App schema (WM record structure). Can any one give me some inputs on if this is possible in webMethods through any of the existing services or any procedure to do the same.

Thanks a prior. Waiting for quick response as it is a critical requirement.

2

Search the forums and the software section for “Excel”. You’ll find a few posts and an example. Basically, you use an open-source library, such as POI, to read the Excel file and create an IS document from it.

3

Thank you.

4

I forgot to ask–do you mean an Excel file or a CSV file (which many seem to refer to as an “Excel file”)? There is a big difference in how these are handled.

5

Hi

we have a similar requirement before, where an excel will be uploaded from the DSP pages and it will be converted to the webM document type.

we have used JExcel API which a free download.
[URL]http://jexcelapi.sourceforge.net/[/URL]

take the jar file and but in your lib directory or package specific jar directory and use the API to read and write the excel.

And in webMethods service use transportInfo service to get the excel input.

Best Regards
Hiubert

6

Thank you.

7

Hi ,

I tried to access this link [URL=“http://jexcelapi.sourceforge.net/”][COLOR=#22229c]http://jexcelapi.sourceforge.net/[/color][/URL] but am not able to find any jar files and could you provide me transportInfo service webMethods

8

You can also find an example on handling excel file in shareware section.

9

Hi

The jar file is available in the [URL]http://jexcelapi.sourceforge.net/[/URL] in this link. go to Files in the top, it will take you the page where u can download.

put this jar in packages\YourCustompackage\code\jars\static\jxl.jar
(create a folder static inside this jar and place the file inside static folder)

After placing the jars you need to restart the Integration Server.

create a flow service and give the input of that service as contentStream(object type) and then call http.transportInfo service and extract the excel.

Best Regards.
Hiubert

10

Hi,

I will make my requirement bit clear.Right now am able to create an Schema in webMethods if my input is in XML(i.e.in XSD format).where as my input should be in proper excel/csv.I need the the procedure in java/flow(feasible with webMethods) so that my problem will solved easily.

Thanks in advance
Rani

11

An Excel file (.xls, .xlsx) is not the same thing as a CSV. The two will be handled quite differently.

If you need to process an .xls/.xlsx file, the open source libraries above can be used to read the data. If you intend to translate that to an IS document, then you’ll use the IS Java API IData interface to create to do.

If you need to process a CSV then you can use the flat file facilities–create a flat file schema and use the flat file services to convert the records to IS documents. Refer to the Flat File Schema Developer’s Guide for details.

12

I have found the open-source library POI, but howdo I install it?

The package returns the error:
Load ErrorsNameReasonEXCEL_contentHandler.util:MSExcelWorkSheetToRecordjava.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbookEXCEL_contentHandler.util:createExcelRecordjava.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbookEXCEL_contentHandler.util:MSExcelDocumentToRecordjava.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook