Generate Excel File

, ,
1

Hi,
I have a document type in my flow. I want to convert the data in that as an excel file and and save it to some location on the hard drive.

How can I do this?

Thanks for your help

2

The easy way to do it is to output the document as a CSV file, which is excel supported. You will probably need to wrap your text in quotes to be sure that it doesn’t break - pretty standard stuff.

I have done this for many clients and no one has complained yet.

If you want to actually populate a “REAL” excel spreadsheet rather than create a file that excel can read, then you will need to write a com object that can receive the output from the com interface in Integration Server.

Also, there is the Jakarta POI project which is a “java” excel solution. In any case, good luck.

Ray

3

Here’s the link in case you are really hardcore on this thing:

[url=“Apache POI - the Java API for Microsoft Documents”]http://jakarta.apache.org/poi/[/url]

and the official site is:

Jakarta POI - Java API To Access Microsoft Format Files

The POI project consists of APIs for manipulating various file formats based upon Microsoft’s OLE 2 Compound Document format using pure Java. In short, you can read and write MS Excel files using Java. Soon, you’ll be able to read and write Word files using Java. POI is your Java Excel solution as well as your Java Word solution. However, we have a complete API for porting other OLE 2 Compound Document formats and welcome others to participate.

So now you have your work cut out for you.

Cheers.

Ray

4

Thanks for reply,
But the problem is, I am mapping the results from dbSQL (service “execSQL”) to this document and the data from sql dynamically changes and depends on user selection.
How can I use csv file in this case? Do I have to create a flat file schema.

Thanks

5

If possible, try and opt for the simplest approach - CSV.

I used POI in a previous role with partial success - the Excel file could be created quite easily within webMethods but, in this case I was emailing it, after delivery it was corrupted. I didn’t spend a lot of time on the issue but I remember thinking it was needle in a haystack material while looking through it.

Nick

6

Try Tab delimited files with a .xls extension. These files can be easily created and can be opened by Excel as worksheets - No need to worry about quotes.

You can create a template or just use variable substitution for each record to a tab delimited line. And place an Enter after each record.

7

Hi,
I created the flat file schema as u suggested and getting the required result in ffvalues.

I supplied the value to ffSchama and mapped the resulted document to ffvalues, but String in the Pipeout out is empty.

Everything is ok buy I dont know why its empty. Please, let me know how to correct it.

Thanks

8

The tab delimited idea sounds good, maybe I’ll use it next time.

NN,

I don’t use the flatfile schema because I’ve had too many nightmares with it. I’ll use it if a client demands it.

I make my csv files the old fashion way, which is string concatenation and is the method that webMethods used in their older EDI adapter model.

What I do, is I make a document that contains all the field value holders for each row in the csv document. Each field is a column value and all the values when concatenated make up your csv record row.

I make a separate flow that generates a csvLine. I usually call it genCSVLine. The signature input is the document and the output is the string. I also wrap my values in quotes, although the tab delimited idea would negate the need for this. Then, I concatenate each output csv line to make up the file. First item is concatenated to second, result is concatenated to third, and so on until all are consumed into one line. Make sure that your delimiter is concatenated as well.

One other way you would do this is to make a flow with a signature input of all fields. Then, append each field value into a new string array and then pass the string list into pub.string:makeString with the separator as a comma. This will also return a string representation of all of the column values.

Many ways to do this.

Ray

9

Hi Ray,
Thanks for the detail explanation. Your second idea sounds good. But, I have a problem here. When I execute sql, I am getting multiple records. How can I pass it to the string array and then makeString, so that I can get an output string file. Should I use a loop here?
Can you please steps the services I should use in my flow.

Thanks for your help

10

Finally, I got the output an an excel file with all fields in seperate columns.

Now, I want to know how can I format that data in that excel file automatically. Like, I want the heading to be in bold and format all columns accordiong to the length of the data when the file is generated from the flow. Is it possible?

Thanks

11

I don’t think that formatting is possible with CSV or Tab-Delimited files. You need to use POI or Jacob API’s to format .xls worksheets.

12

Hi NN,

The problem you have just described can be easily solved using Itemfield’s ContentMaster. ContentMaster is fully equipped to parse/serialize any binary/textual format (one of which is MS Excel) into any other format. With ContentMaster you can easily create Excel files formated to your specific need.

Itemfield is a new webMethods partner. Its unique “Example-Based Parsing” technology offers an intuitive graphical development environment for easy creation of parser scripts.

ContentMaster is easily integrated into WM. See [url=“http://www.itemfield.com/solutions/sol_webmeth.shtml”]http://www.itemfield.com/solutions/sol_webmeth.shtml[/url] for more details.

Please feel free to contact me with any questions.

Meitav Harpaz

13

Hi Nasir,

I have the same kind of problem which is generate an excel fle from the output of an SQL select query whose results can vary depending on the search criteria.

Request you to please let me know how you have solved your problem.

Thanks in advance.

Regards,
Kiran.