------------- 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) = localtime; $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 document 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, undef], [undef, $all, $all, $dates, $dates, $all, $all, $prices, $prices, $all, 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 page 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 path 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 () { 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 every 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 it $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 import it # not the most elegant solution but it works # # I have not tested if it has to be in the same place in the .csv file # a later version will remove the need for the loc="Xnn" part # read: quick hack if ($token =~ /^/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 hard copy,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,Additional copies of this report are available; please contact RECEPTIONIST,,,,,, ,,,,,,,, ,,,,,,,, ------------- 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 ALLOWANCE,STORE RETAIL (IF KNOWN),A,<<<<<<<<<<<<<<<<<<