Need some help with POI pls...

[Guest]

Hi,

I'm currently using POI to generate MS Excel documents from CF.

I believe it's a great tool because it gave me the possibility to export the data on different worksheets, etc.

Before using POI, I was just using only CF (see PART 1 in attached code).

The advantage was that I could display all 3 tables in 1 page and control their display using plain html.

The disadvantage was that, when the request was to display each product details on 1 separate worksheet, I was stuck!

And this is where using POI becomes interesting!

However, I've noticed that you can only export 1 query to the POI, and my question is:

Is there a way to pass more than 1 query to the POI, so that it displays in the same way as it would using CF (3 different blocks or tables)?

This is because, even though I have to separate each product on a separate worksheet, I still need to have the same display i.e. 1 table for the Overall, then a blank column (separator), 1 table for the "Entries", then a blank column as separator again, and then 1 table for the "Exits". (Forget about the names Overall, Entries and Exits, this is just an example. I only need to display 3 sections of data on the same sheet).

Now, since I don't know if this is possible or not, I have tried to work around the problem differently.

What I've done is to create 1 query (see PART 2 in attached code).

Now, here I've got a big problem, especially if I'm querying data since inception.

I'm using a MySQL 5.1 database properly partitioned on dates (yearly) and then properly indexed.

I'm currently using SQLYog Enterprise v5.21 as GUI tool for my database.

Here are my observations:
1. Querying the data in SQLYog itself takes miliseconds (around 47ms).
2. Querying the data via a cfquery takes seconds (around 6-7 seconds).
3. Looping through more than 50,000 records to create the myQry takes around 4-5 mins. If I test it on my PC, I even have to restart jrun!

Is there any better way to do the following?
1. Populate the myQry.
2. Display the data in 3 "blocks" on each sheet using POI.

Your help will be greatly appreciated :-)

Thanks and regards,
Yogesh Mahadnac

[Guest]

Would be very much grateful if someone could please show some consideration.

Thanks and regards,
Yogesh Mahadnac

[-__cfSearching__-]

YogeshM,

You could try modifying the CFC (ie add your own code). Do you need an example? Warning, the code sample is large.

[Guest]

Hi -==cfSearching==-

Many thanks for your reply.

At least I've got one now!

Indeed, I would be very much grateful if you could please provide me with an example, if possible, via email to mahadnac@superfund.com

Thanks and regards,
Yogesh Mahadnac.

[-__cfSearching__-]

Okay. I'll try and post the code here first. If its too large, I'll email it to you instead.

[-__cfSearching__-]

Here is an example. Its barely tested 😉 Let me know if it didn't post okay or if you have questions.

[-__cfSearching__-]

I updated the previous post. One of the functions was missing. In case it does not show up, here it is again.

[Guest]

Hi -==cfSearching==-

Many thanks for your prompt reply.

I'll give it a try and let you know if there's anything.

Best regards,
Yogesh

[-__cfSearching__-]

Hi YogeshM,

How did you make you make out?