Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

If you're on a Win32 platform you can also use Win32::OLE I don't know if it is bulkier or less efficient than Spreadsheet::WriteExcel, but it's what I have used, in the past (well yesterday really).

#!/Perl/bin/perl use strict; use warnings; use DBI; use Win32::OLE; use Win32::OLE::Const; use constant {TRUE => -1, FALSE => 0}; #Hash that defines the months ending days my %monthEnd = ('January' => '-01-31 23:59:59', 'February' => '-02-28 23:59:59', 'February_leap'=> '-02-29 23:59:59', 'March' => '-03-31 23:59:59', 'April' => '-04-30 23:59:59', 'May' => '-05-31 23:59:59', 'June' => '-06-30 23:59:59', 'July' => '-07-31 23:59:59', 'August' => '-08-31 23:59:59', 'September' => '-09-30 23:59:59', 'October' => '-10-31 23:59:59', 'November' => '-11-30 23:59:59', 'December' => '-12-31 23:59:59'); #Hash that defines the months starting days my %monthStart = ('January' => '-01-01 00:00:00', 'February' => '-02-01 00:00:00', + 'March' => '-03-01 00:00:00', 'April' => '-04-01 00:00:00', 'May' => '-05-01 00:00:00', 'June' => '-06-01 00:00:00', 'July' => '-07-01 00:00:00', 'August' => '-08-01 00:00:00', 'September' => '-09-01 00:00:00', 'October' => '-10-01 00:00:00', 'November' => '-11-01 00:00:00', 'December' => '-12-01 00:00:00'); #Array that defines the order of months my @months = qw(January February March April May June July August September October November December); my $client = 'x'; my $year = '2002'; #Create default loop counter my $i = 0; my $dbh = DBI->connect('DBI:ODBC:x,'x','x'); ##Create an Excel Application object my $xl = Win32::OLE->new('Excel.Application'); $xl->{DisplayAlerts} = FALSE; ##Load a hash reference to Excel's constants in the scalar ref $xlCons +t my $xlConst = Win32::OLE::Const->Load('Microsoft Excel 8.0 Object Libr +ary'); #Create a new workbook my $book = $xl->Workbooks->Add(); ##Renames Sheet1 to Production-XXX renameSheet($book,'Sheet1',"Production-".$client); ##Delete Sheets 2 and 3 deleteSheets($book,['Sheet2','Sheet3']); ##Get a reference to the current_sheet being worked on my $sheet = $book->Worksheets("Production-".$client); #Create and initialize loop variables my ($sth,$start,$end) = ('','',''); ##Main Work Loop## for('C','D','E','F','H','I') { #Grab query and use appropriate number formatting for the column COLUMN: { ##Invoices $_ eq 'C' and do{$sth = $dbh->prepare(invoiceAmounts()); + for my $j(0..13) { $sheet->Range($_.($j+5))->{NumberFormat} += "\$#,0.00"; } print "Processing Monthly last COLUMN;}; ##Created $_ eq 'D' and do{$sth = $dbh->prepare(Created()); + for my $j(0..13) { $sheet->Range($_.($j+5))->{NumberFormat} += "#,0"; } print last COLUMN;}; ##Blocked $_ eq 'E' and do{$sth = $dbh->prepare(membersBlocked()); for my $j(0..13) { $sheet->Range($_.($j+5))->{NumberFormat} = + "#,0"; } print last COLUMN;}; ##Killed $_ eq 'F' and do{$sth = $dbh->prepare(membersKilled()); + for my $j(0..13) { $sheet->Range($_.($j+5))->{NumberFormat} = + "#,0"; } print "Processing last COLUMN}; ##Investigated $_ eq 'H' and do{$sth = $dbh->prepare(membersInvestigated()); + for my $j(0..13) { $sheet->Range($_.($j+5))->{NumberFormat} += "#,0"; } print last COLUMN}; ##Invoiced $_ eq 'I' and do{$sth = $dbh->prepare(membersInvoiced()); for my $j(0..13) { $sheet->Range($_.($j+5))->{NumberFormat} += "#,0"; } print last COLUMN}; } #For each month in the year foreach $i (0..scalar(@months)-1) { #Construct the start and end dates $start = $year.$monthStart{$months[$i]}; $end = $year.$monthEnd{$months[$i]}; #Check for leap years if($months[$i] eq 'February') { $end = $year.$monthEnd{'February_leap'} if !($year % 4); } #Call execute() with the appropriate placeholders PARAMS: { ##Created,Invoiced,Invoice Amount ($_ eq 'C' || $_ eq 'D' || $_ eq 'I') and do{$sth->execute($client,$start,$end); + last PARAMS;}; ##Blocked $_ eq 'E' and do{$sth->execute('Blocked',$client,$star +t,$end); last PARAMS;}; ##Killed $_ eq 'F' and do{$sth->execute('Killed',$client,$start +,$end); last PARAMS}; ##Investigated $_ eq 'H' and do{$sth->execute($client,$start,$end,'mm +'); last PARAMS}; } #Retrieve the value my $count = $sth->fetchrow_arrayref; #Add the value to the worksheet $sheet->Range($_.($i+5))->{Value} = $$count[0]; } } #Precautionary finish call $sth->finish(); #Disconnect from the database $dbh->disconnect(); #Reset the counter $i = 0; #Create the headings and borders for ('A'..'J') { #Write the heading $sheet->Range($_."4")->{Value} = $headings[$i]; #Bold the heading $sheet->Range($_."4")->Font->{Bold} = TRUE; #Color the heading $sheet->Range($_."4")->Interior->{ColorIndex} = 20; #Align the cells to the center $sheet->Range($_."4:".$_."16")->{HorizontalAlignment} = $$xlConst{ +'xlHAlignCenter'}; #Border the cells for my $j(4..16) { $sheet->Range($_.$j)->BorderAround($$xlConst{'xlBorderLineStyl +eContinuous'}, $$xlConst{'xlThin'} +, 1); } $i++; } #Sum the columns where appropriate for ('C'..'J') { #Sum the column if($_ ne 'J') { $sheet->Range($_."17")->{Value} = "=SUM(".$_."5:".$_."16)"; } #Average $sheet->Range($_."18")->{Value} = "=SUM(".$_."5:".$_."16)/12"; #Bold the figure $sheet->Range($_."17:".$_."18")->Font->{Bold} = TRUE; } #Label the months,potential, and invoice rate in the sheet for (0..11) { #Month label $sheet->Range("A".($_+5))->{Value} = $months[$_]; #Potential Audits $sheet->Range("G".($_+5))->{Value} = "=D".($_+5)."-E".($_+5)."-F". +($_+5); #Invoice Rate $sheet->Range("J".($_+5))->{Value} = "=H".($_+5)."/I".($_+5); } #Format the numbers for (0..13) { $sheet->Range("J".($_+5))->{NumberFormat} = "#,0.00"; } #Set the font size $sheet->Range("A1:J18")->Font->{Size} = 9; #Center the sheet $sheet->Range("A1:J18")->{HorizontalAlignment} = $$xlConst{'xlHAlignCe +nter'}; #Label Averages and Totals $sheet->Range("A17")->{Value} = "Sums"; $sheet->Range("A18")->{Value} = "Averages"; #Put the values in $sheet->Range("A1")->{Value} = "Performance:"; $sheet->Range("C1")->{Value} = $client; #Format the fonts $sheet->Range("A1")->Font->{Bold} = TRUE; $sheet->Range("A17:A18")->Font->{Bold} = TRUE; $sheet->Range("C1")->Font->{Italic} = TRUE; #Merge A1 & B1 $sheet->Range("A1:B1")->{MergeCells} = TRUE; #Create an embedded chart object my $chart = $sheet->ChartObjects->Add(72,250,400,213); #Set the Chart Type $chart->Chart->{ChartType} = $$xlConst{'xlLine'}; #Create the series $chart->Chart->SeriesCollection->Add($sheet->Range($_."4:".$_."16"), $$xlConst{'xlColumns'}, TRUE) for('D'..'I'); #Autofit the cells by column $sheet->Columns->AutoFit(); #Orient the page to landscape for printing $sheet->PageSetup->{Orientation} = $$xlConst{'xlLandscape'}; #Save the sheet $book->SaveAs("test.xls"); #Close the Application object $xl->quit(); #Sub to rename an xl sheet given the #Workbook object, the old name, the new name sub renameSheet { my ($book,$old,$new) = @_; $book->Worksheets($old)->{Name} = $new; return; } sub deleteSheets { my ($book,$sheets) = @_; DELETE_SHEET: { $sheets =~ m/SCALAR/ and do{$book->Worksheets($$sheets )->Dele +te(); last DELETE_SHEET;}; $sheets =~ m/ARRAY/ and do{$book->Worksheets($_)->Delete() fo +r @{$sheets}; last DELETE_SHEET;}; warn "**syntax error from sub deleteSheet(\$workbook,\$sheet|\ +@sheets)**\n"; warn "deleteSheet needs a scalar or array ref\n"; } + return; }
This script runs some queries against a SQL Server Database to create the report that is then created and formatted via Win32::OLE. This is really more than you need, but its an example of a report that uses a decent amount of features and gives you an idea of what kind of code is needed to properly format something. Writing your own is more rewarding than gutting this one and making it fit your needs, so please do so if you find this one useful.

Grygonos

In reply to Re: Re: Re: Generating an Excel Report by Grygonos
in thread Generating an Excel Report by kasmot

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, 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, 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.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others chanting in the Monastery: (6)
    As of 2020-11-26 08:03 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?