Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Make Multi-tab Spreadsheet from CSV files

by Mr. Muskrat (Canon)
on Aug 22, 2002 at 01:11 UTC ( #191896=CUFP: print w/replies, xml ) Need Help??

Okay, this is something that I have been working on for a while now. It has gradually evolved to this. I have two programs that I wrote to make my job a bit easier. The first one (which is not ready to be posted) extracts data from a MySQL database and creates a CSV file. It is run daily. This file gets imported into OSAS (Open Systems Accounting Software).

Once a week, I run the second program (which I am sharing with you now). It reads in the CSV file created by the first as well as two other CSV files that I created by hand. It then creates a formatted, ready to print Excel spreadsheet with three worksheets, one for each file.

Here are the four necessary files. They are seperated with the dashed lines.

------------- ------------- #!/usr/bin/perl use strict; use warnings; use Spreadsheet::WriteExcel; use Text::CSV_XS; # create the filename based on the date and parameter my (undef, undef, undef, $mday, $mon, $year, undef, undef, undef) = lo +caltime; $mday = $mday < 10 ? "0$mday" : $mday; $mon = ++$mon < 10 ? "0$mon" : $mon; # added the ++ that was missing $year += 1900; my $type = $ARGV[0] || ""; $type = "" if ($type ne "pa"); # it's pa or nothing my $name = $type.$mon.$mday.$year.'.xls'; # other files we are reading my $cfile = $type."cover.txt"; my $bfile = "bible.csv"; my $nfile = "notes.txt"; my $workbook = Spreadsheet::WriteExcel->new($name); # three worksheets my $cover = $workbook->addworksheet("cover"); # the cover page my $worksheet = $workbook->addworksheet("bible"); # the meat of the do +cument my $notes = $workbook->addworksheet("notes"); # and the notes # setup the column formats # I'm in the process of simplifying the format setup # cover stuff my %cf = ( font => 'Arial', ); my %bold = ( bold => 1 ); my %ch = ( align => 'center', valign => 'center', font => 'Times New Roman', size => 24 ); my $ch = $workbook->addformat(%ch, %bold); # header my $cn = $workbook->addformat(%cf, size => 10); # normal text my $cs = $workbook->addformat(%cf, size => 8); # small text # bible stuff my @blanks; # this will hold the row numbers of blank lines # and now the format for the blank lines my $blank = $workbook->addformat(); $blank->set_merge(); $blank->set_border(1); # many more formats my $all = $workbook->addformat(); $all->set_align('center'); $all->set_align('bottom'); $all->set_font('Arial Narrow'); $all->set_size(8); $all->set_color('black'); $all->set_border(1); my $prices = $workbook->addformat(); $prices->copy($all); $prices->set_num_format(0x07); my $dates = $workbook->addformat(); $dates->copy($all); $dates->set_num_format(0x0e); my $title = $workbook->addformat(); $title->copy($all); my $subtitle = $workbook->addformat(); $title->set_align('vcenter'); $title->set_border(5); $title->set_pattern(1); $title->set_fg_color('silver'); $subtitle->copy($title); $subtitle->set_text_wrap(); $title->set_font('Times New Roman'); $title->set_bold(); $title->set_size(16); # setup column widths and enable use formats set_columns($cover, "A", [3.71, 3.43, 17.29, 21.29, 20.43, 5.86, 8.43, 8.43, 31.71, 8.43], [undef, $cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn, undef]); set_columns($worksheet, "A", [undef, 9.33, 49.83, 9.83, 9.83, 8, 45.67, 9.83, 9, 1.33, undef, und +ef], [undef, $all, $all, $dates, $dates, $all, $all, $prices, $prices, $a +ll, undef], [1,0,0,0,0,0,0,0,0,1,1]); # this is where we read the data from csv and write to the xls write_from_csv($cover, $cfile); write_from_csv($worksheet, $bfile); write_from_csv($notes, $nfile); # setup the header rows set_rows($cover, 0, [34.5, 34.5, 38.25], [$ch, $ch, undef]); $cover->set_row(13, 12.75, $cs); $cover->set_row(28, 12.75, $cs); $cover->merge_cells('B1:I1'); $cover->merge_cells('B2:I2'); set_rows($worksheet, 0, [24.75, 39.75], [$title, $subtitle]); $worksheet->merge_cells('B1:I1'); # Merge the first eleven cells of these blank rows foreach my $row (@blanks) { $worksheet->merge_cells($row, 0, $row, 10); } # setup the print options for each worksheet print_setup($cover); print_setup($worksheet); print_setup($notes); $worksheet->repeat_rows(0,1); # first two rows is my header $worksheet->set_print_scale(85); # had to scale it down to fit one pag +e width $notes->center_horizontally(0); # undo the horizontal centering $worksheet->activate(); # I'd like the second worksheet to be active $cover->select(); # but I'd like all three selected so that when my $notes->select(); # I need to print I don't have to select them $workbook->close(); # play nice exit(0); # Setup the print options # usage: # print_setup($worksheet); # $worksheet is a reference to a worksheet object # # These are the print options that I needed sub print_setup { my $worksheet = shift; $worksheet->set_landscape(); $worksheet->center_horizontally(); $worksheet->set_margins_LR(0.25); $worksheet->set_margin_top(0.5); $worksheet->set_margin_bottom(0.36); $worksheet->set_footer('&L&D&R&P of &N', 0.17); $worksheet->hide_gridlines(); } # Write from a comma seperated values file # usage: # write_from_csv($worksheet, $file); # $worksheet is a reference to a worksheet object # $file should contain the filename of the .csv to read (including pat +h if necessary) # # This subroutine will go through the .csv and write the values in the + same places in # the selected worksheet. # # This subroutine started out as written by John McNamara sub write_from_csv { my ($worksheet, $file) = @_; # Create a new CSV parsing ojbect with the CSV options that I needed my $csv = Text::CSV_XS->new({ 'quote_char' => '', # what? no quote character? you got it! 'escape_char' => '\\', # a backslash 'sep_char' => ',', 'binary' => 0 }); # Row and column are zero indexed! my $row = 0; open (CSVFILE, "<", $file) || die "Unable to open $file for reading: + $!, stopped"; while (<CSVFILE>) { if ($csv->parse($_)) { my @Fld = $csv->fields; my $col = 0; # keep that line (row)? my $keep = 1; if ($_ =~ /,,,,,,,,,,,/) { # is it blank? # the first two lines are the header that is repeated on eve +ry page # there are 44 rows printed after the header if ((($row - 2) % 44) == 0 && $row > 2) { # it's a blank line at the top of the page so don't keep i +t $keep = 0; } else { # this will help with formatting the remaining blank lines + later push(@blanks, $row); } } if ($keep) { foreach my $token (@Fld) { # I added the ability to store a .bmp with a .csv and impo +rt it # not the most elegant solution but it works # <img src="test.bmp" loc="D18"> # I have not tested if it has to be in the same place in t +he .csv file # a later version will remove the need for the loc="Xnn" p +art # read: quick hack if ($token =~ /^<img/) { $token =~ /<img src="(.*)" loc="(.*)">/i; my ($img, $loc) = ($1, $2); $worksheet->insert_bitmap($loc, $img); } else { $worksheet->write($row, $col, $token); } $col++; } } $row++ if ($keep); } else { my $err = $csv->error_input; print "Text::CSV_XS parse() failed on argument: ", $err, "\n"; } } } # Set the column properties on many columns # usage: # set_columns($worksheet, $column, $width, $format[, $hidden]); # $worksheet is a reference to a worksheet object # $column is the column letter to start with (it could have been done +different) # $width is a reference to an array of column widths # $format is a reference to an array of column formats # $hidden is a reference to an array of column hidden properties (0 or + 1) # $hidden is optional # See the docs for Spreadsheet::WriteExcel for more on $worksheet->set +_column() sub set_columns { my ($ws, $col, $width, $format, $hidden) = @_; for (my $i=0; $i < @$width; $i++) { my $column = "$col:$col"; $ws->set_column($column, $$width[$i], $$format[$i], $$hidden[$i]); $col++; } } # Set row properties on multiple rows # usage: # set_rows($worksheet, $row, $height, $format); # $worksheet is a reference to a worksheet object # $row is the number of the row to start with # $height is a reference to an array of row heights # $format is a reference to an array of row formats # see the docs for Spreadsheet::WriteExcel for more on $worksheet->set +_row() sub set_rows { my ($ws, $row, $height, $format) = @_; for (my $i=0; $i < @$height; $i++) { $ws->set_row($row, $$height[$i], $$format[$i]); $row++; } } ------------- cover.txt ------------- ,Company Name Here,,,,,,, ,Ad Activity Report,,,,,,, ,,,,,,,, ,To:,ROUTE SALES,,,From:,MR. MUSKRAT,, ,,,,,,,, ,cc:,SOME GUY,A. CHICK (4),CITY 1 (4),,,, ,,BIG DUDE,Q. T. PIE,CITY 2 (4),,,, ,,DING DONG,RECEPTIONIST,FAR AWAY (via U.S. Mail),,,, ,,FINANCE MAN,ANOTHER PERSON,NOT HERE (via U.S. Mail),,,, ,,MIS TYPE **,DA DITZ,,,,, ,,I. B. MEAN,MR. MUSKRAT,,,,, ,,,,,,,, ,,,,,,,, ,,,,** Notes recipient receives report electronically in addition to h +ard copy,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,<img src="logo.bmp" loc="D17">,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,Additional copies of this report are available; please contact RECEP +TIONIST,,,,,, ,,,,,,,, ,,,,,,,, ------------- bible.csv ------------- 8/21/02,Company Name Here Ad Activity Report,,,,,,,,,, ,CHAIN OR CUSTOMER NO.,CUSTOMER NAME + + SPECIAL INSTRUCTIONS + + (MISC INFORMATION),AD or ALLOW START DATE,AD + or ALLOW END DATE,OUR PRODUCT NUMBER,PRODUCT + + BRAND & + + DESCRIPTION,OFF- INVOICE A +LLOWANCE,STORE RETAIL (IF KNOWN),A,<<<<<<<<<<< +<<<<<<<<ALLOWANCE CONTROL CODE, ,1,GAS STATION SPECIAL PROMO,07/08/02,09/02/02,9990AF,SOME KINDA ICE C +REAM HG,1.50,2.99,, ,1,GAS STATION SPECIAL PROMO,07/08/02,09/02/02,9991AF,SOME KINDA HOMEM +ADE ICE CREAM HG,1.50,2.99,, ,1,GAS STATION SPECIAL PROMO,07/08/02,09/02/02,9992AF,SOME KINDA BEST +ICE CREAM HG,1.50,2.99,, ,,,,,,,,,,, ,3,SUPER D'DUPER TPR,08/04/02,08/20/02,9993AF,SOME KINDA FRUIT BARS,0. +52,2/$5,T, ,3,SUPER D'DUPER TPR,08/04/02,08/20/02,8884AF,SPECIALTY TYPE BARS,0.36 +,2/$5,T, ,3,SUPER D'DUPER TPR,08/04/02,08/20/02,8885AF,SPECIALTY TYPE QTS,0.88, +2/$6,T, ,,,,,,,,,,, ... yadda yadda yadda ... ------------- notes.txt ------------- FSI:, ,SOME BRAND 9/01 - "THIS IS REALLY GOOD" - $0.50 COUPON FOR ANY SIZE I +CE CREAM ,BRAND X FSI AUGUST 25 - BARS\, CONES & SANDWICHES ,BEST ICE CREAM IN THE WORLD $1.00 OFF PINTS AND BARS - 9/08 ------------- end of code -------------

Now for some a few notes about this... bible.csv is currently 521 lines long but it is sometimes longer. ;) The first two lines are the header. And yes, the second line is really that long (don't ask). I encourage you to copy the parts after it ten or twenty times (removing the yadda yadda line of course). Oh and you will notice that in cover.txt there is a reference to logo.bmp. Any smallish sized bitmap will work. It's only there to show what can be done.

<Update>Although I did not actually time this, it appears to take between 1 and 2 seconds to run. The previous script that I wrote using Win32::OLE would take 3 or more minutes to run!</Update>

<Update>Added a missing ++ at the beginning of the code... how did I miss that?</Update>

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://191896]
Approved by ybiC
Front-paged by gmax
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2017-01-23 02:37 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (190 votes). Check out past polls.