Well, to answer your question first (and it's a little bit difficult without seeing some sample data) - I'd probably suggest using something like selectall_hashref and load all of your data into a hashref. You could then write directly to the Excel worksheets.

A few comments about your code: 

# execute the sql statement
$sth=$dbh->prepare("@sql");

[download]
... looks a little odd to me. It seems to suggest that you are passing a list of statements, rather than a single one?

Also, you appear to be selecting at least 14 rows of data, yet you only use 6 of them. What is the point of that? Are you doing select * from ... or something?

I strongly suspect that your code could be significantly simplified by improving your SQL select statement to only return the data that you actually need to use. Perhaps you could post that along with a sample set of data?

Cheers,
Darren :)

Update: One thing that I forgot to mention is that your choice of variable names makes your code a little difficult to follow. Stuff like $aa = $rec[0]; doesn't really tell you much about the data. It's a personal thing, but I usually try to make my variable names as descriptive as possible - makes it easier for the next person who comes along and has to debug/maintain my code (usually me) :)

That's one reason why I like to use selectall_hashref on the results of my DB queries. I can then refer to the data in a very descriptive way.

In reply to Re: Direct to spreadsheet by McDarren
in thread Direct to spreadsheet by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.