If it is an Excel spreadsheet, I would think of opening it as a database-file through DBI and DBD::Excel.

If you want to get to the "bottom" of the spreadsheet, Spreadsheet::ParseExcel is there for you as well.

IMHO, the "DBI-way" is to be preferred as you can use standard SQL to get at your data so you don't have to code that logic into your script.

To make the interface with the spreadsheet, perhaps DBIx::HTMLinterface is a good bet and to make the final (printable) webpage some sort of HTML-templating system will surely help you.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

The spreadsheet is not Excel, but in Star Office. I was going to extract to a flat file, like I have done once before (read in the spreadsheet, tab delimited). DBIx::HTML looks cool though, I will look into playing with that a little. I used Star Office because I run a Linux box and my buddy has a Mac G4. Do you think I could 'print to file' from SO to an Excel spreadsheet and read it that way with Spreadsheet::ParseExcel? I have Never tried that before.
Great input, thanks!

csuhockey3

Well I've never used Star Office and I don't know if it has an Excel compatible output format.

But as it has the possibility to output a flat tab-delimited file, you can use DBD::CSV to read that.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

I've been using Spreadsheet::ParseExcel with OpenOffice on my Linux box with great results. In Open/StarOffice, save the spreadsheet as Excel, then fire up Spreadsheet::ParseExcel and have fun. The version of Excel that you save as doesn't seem to matter, be it Excel5, 95, or 97/2000/xp unless you have cells that have > ~ 254 characters of text, in which case saving it as Excel5 will truncate the contents of those cells.

When I started doing this I was rather amused to be writing Excel spreadsheets on a Linux box using perl to be read/edited using OpenOffice with the results being read using perl-- the only thing Microsoft was the format. That said, it would be nice to have a Spreadsheet::ParseOpenOffice and Spreadsheet::WriteOpenOffice module...

I now had the time to take a better look at DBIx::HTMLinterface.

The idea behind it is very good but it has some serious bugs in its implementation (mainly where it uses the interface to CGI). So if you are strapped for time, it does not seem to be such a good bet after all.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Nothing says you really appreciate a wedding gift like a computer generated form letter.

Ok, now seriously...

Format the list in a spreadsheet, so you have all the information out - who, what gift, address, etc - but make them hand-written, and personalized.

I sent out nearly 100 thank you cards after my wedding. It's really not that bad. You can use a stock thank you text, but it's very good to also put some personal comment to close.

   We really appreciate your gift of <xxx>. It will
 really accent our living room.
   It was so good to see you and the kids at the 
 wedding. We hope you can come visit us again soon.

If you still really need help, you can try pre-generating the text. Use some Template system to fill in the gift name (use a separate money one as you suggested), and select randomly from a list of a few dozen greetings and comments. Just be sure they are appropriate to the person. (Don't say 'good to see you' to someone who couldn't make the wedding but sent a gift.) It won't write the cards for you, but it will help you write them.

Oh, and Congratulations to the new couple. ;-)

Didn't mean to strike a chord. I should have titled this semi-automatic. I agree it is not the best way to do this. We figured, with full time school and work, this wold be the quickest way to make sure everyone was sent a nice note and a picture. Like you said, the personal part would be person/family specific, not from a template. I know it's a lame excuse, but it's good perl experience for me. Thanks again for all of the useful input.

csuhockey3

The following is my patent-pending method for generating documents automatically using Perl:

1. Start OpenOffice.org, go into the options (under the Tools menu) and under Load/Save, click on General. Turn off the option "Size optimization for XML format (no pretty printing)". (This will make a later step much easier.)
2. Use OpenOffice.org Writer to create a sample document with all of the formatting and setup exactly the way you want (tables, columns, image placement, fonts, and so forth), but sample/dummy content (e.g., a thank-you to Jane Doe for the wonderful Widget Set). Save this.
3. Unzip it into a working directory.
4. Copy and paste the contents of content.xml into a big string in your Perl script. Use an editor macro to backslash all the double quotes in it so that it's one big string. (Update: Or you can use a heredoc as PodMaster suggests.)
5. Interpolate your content. You can do this in a loop or whatever. (I often do tables out of a database, so I do the old ".(join"\n",map{tablerow($_)}@rows)." trick, but for what you're doing simple interpolation will probably be fine.) Note that certain characters (notably ampersand) have to be written as entities, so any that you're going to have to deal with be sure to include in your sample document so you can see the encoded form.
6. open XML, ">$workdir/content.xml"; print XML $interpolatedcontent; close XML;
7. Zip up the contents of your working directory into an sxw. You can either use Archive::Zip or just backtick out to info-zip or whatever zip app you use.
8. Doubleclick the sxw and print it. I haven't found a way to automate this step yet. However, while I haven't done this, it would easily be possible to generate multiple items (in a loop) in one document separated by hard page breaks and print them all at one go. If you want to take this approach, be sure to put a hard page break in your sample doc so you have one to copy-and-paste into your string.

$;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}}
split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/
[download]

Wow, having generated items like this using mail merge (with embedded tabs in CSV data which makes Word move to the next table cell; ugh!) in Microsoft Word, you don't know what a relevation it is to see how easy that is. Once you're dealing with a text/XML format you can just interpolate your data.

I've been turned off by OpenOffice's clunky UI but I'll have to look past that now and develop a workflow for Writer.

Thanks a bunch for that recipe. I'll be submitting this node to google.

Use an editor macro to backslash all the double quotes in it so that it's one big string.

That's yucky

my $bob = 1;
print <<"__HELLO_HERE_DOCUMENTS__";

print 1;
die 2;
exit 3;            HELLO $bob!!!!!!!!1

__HELLO_HERE_DOCUMENTS__
[download]

And you can always use an odd delimiter like perl -le" $bob = 1; print qqº i am quoted $bob º, qq¿ me too $bob ¿"

For more info on here-documents, `perldoc perldata' and `perldoc -q "here document"'

MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"

I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).

** The third rule of perl club is a statement of fact: pod is sexy.

Wow. I can use this method for all sorts of home/school/work related prolems. Awesome recipe.

csuhockey3

Comma Seperated Values

--
I'm not belgian but I play one on TV.

PerlScript in Excel

bbfu
Black flowers blossom
Fearless on my breath

Miss Manners says thank-you notes are written by hand, using an anachronistic implement known as a pen.

--
TTTATCGGTCGTTATATAGATGTTTGCA

Miss Manners is a bit off. Some people should NEVER send hand-written letters. If you (or several other people who try) can't read your handwriting, type your "thank you"s, even if Miss Manners says it's bad manners.