Unknown number of fields to export to excel

[mickyrox]

Hi all,

Our Stored Procedure returns a single field.

The value in the field is a string with comma separated values.

We need to know the possibility of exporting the report to a spreadsheet so that the comma separated values should be printed in adjacent cells in the spreadsheet. The complexity is that the number of values in the string for each iteration of the field may vary. There is no particular format for the result set returned by the Stored Procedure. Sometime we may miss some columns or we may get some extra columns.

Example: for $F{Field value}

1st Field value = abc,name1,city,id,first

2nd Field value2 = bcd,city,name2,idt,second,85tm,7reds

3rd Field value3 = cde,14tfh,id,name3,third,ysde

 

Spread Sheet output should look like:

 

abc	name1	city	id	first
bcd	city	name2	idt	second	85tm	7reds
cde	14tfh	id	name3	third	ysde

 

Please ask for any specific information,

Any guidance will be very much appreicated,

Thanks in Advance

Post Edited by mickyrox at 07/21/2010 07:41

[slow]

is there a max number of the words in a single row ( a superior limit)?
must all the words in a row be printed in a schema without empty spaces (empty columns) ?

[mickyrox]

Hi Slow,
Thanks for your reply :)

I'm negative about having a limit to the number of values, but we could take the liberty to say that the number of values doesn't go beyond 200.
In that case how do we go ahead?

I'd like to know(exporting comma seperated values to spreadsheet) in which procedure we need to step ahead in iReport.

Thanks,
Michael.

Post Edited by mickyrox at 07/21/2010 09:03

[slow]

Uhm... i think the best way is to work with simple report structure, without columns (200 columns?), crosstab or table...

I suggest to create a report (maybe programmatically) with 200 contiguous cells (you can create 2-3 cell of 100-150 pixel width and then add the others generating the code that prints the XML portion to append the other cells with the appropriate position with Java (any programming language is good to do this)

In the cell expression I suggest to set an expression like:

getStringAt($F{yourVeryLongString}, position)  

where postionis a number from 0 to 199 (1 to 200), that is different from each cell.
and getStringAt(String, int) is a simple Java method that split the input string (using che comma char) returning one at "position" position, or "" (blank) if there isn't a value at this position.
putting this method in scriplet or in a Java class (in a jar library) in the IReport classpath.

all this cell must be placed in the detail band.

pay attention to the perfect position of the cell, without pixel gap between cell borders, so you can extract a perfect excel file.


if you need more performance,than you may try to optimize you report trying to work with a function that creates a String array only one time, passing it to the cell expression to avoid the splitting of the mega-string 200 times per report-row!

I made 7-8 months ago a similiar work for a multipage excel report with a fixed number of column (i remember something like 115 columns)... but doing it for 50, 100, or 1000 columns is the same.
 

_________________________________________

if you like it... give me KARMA points please!    : ) 
_________________________________________

listening:  Nine Inch Nails Survalism

"You see your world on fire
Don't try to act surprised
We did just what you told us
Lost our faith along the way
   and found ourselves believing your lies" (t.r.)

 

 

Post Edited by slow at 07/21/2010 09:37

[mickyrox]

Hi Slow,

 

Thanks for the reply . That was a brilliant contribution.

We'll start looking into the possibilities.

 

I'm very new to iReports. If you don't mind can you send a rough sample jrxml which exposes this logic?

 

Thanks again

michael

Post Edited by mickyrox at 07/29/2010 09:18

[slow]

Unfortunately I work for a large company, "very jealous" of its code, that will not let me "spread it.

I believe that studying the ideas I gave you can get a good report without much effort...

In any case here there is a great forum that will be able to help you.

[mickyrox]

Thanks Slow,

its totally fine.

 

Yeah we're analyzing your ideas and proceeding in that direction. Once we're done i'll put up a post of the procedure. :-)

 

 

[mickyrox]

Hi Slow,

 

We're working on getting the string(with comma separated values) from the SP and changing each value into an item in the 'list' data type in Java.

 

Looks like we won't have 200 as the maximum number of text field elements. As data is being inserted into the database the number of values keeps increasing and this(assuming we will have a maximum of 200 elements) will not be a permanent solution.

 

Any suggestion on how we could dynamically specify the number of elements in iReport?? Or is there any other fix that we can try out?

 

 

Thanks in advance,

Michael.

 

 

 

 

 

[slow]

Unfortunately, as far as I know, Working in iReport horizontally, thit this I mean creating  very large horizontal dynamic structures is impossible unless you create report template programmatically using a programming language (like Java).

after all is as if you created a table structure in html with a jsp/asp/php etc etc ... is not impossible, just a little boring.