Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Make Multi-tab Spreadsheet from CSV files

by Mr. Muskrat (Abbot)
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.

------------- makebible.pl ------------- #!/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 csv2xls.pl 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>

Comment on Make Multi-tab Spreadsheet from CSV files
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://191896]
Approved by ybiC
Front-paged by gmax
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (11)
As of 2015-07-29 09:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (263 votes), past polls