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