8 Replies Latest reply on Oct 25, 2007 2:25 PM by -==cfSearching==-

    Need some help with POI pls...


      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