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>