Help required to automate formatting an Excel file

[ehasler]

I've got a perl script (running on a linux box) that runs some SQL queries against a database, generates a .csv file and then emails this as an attachment.

What I'd like to do is find a way of opening this .csv file in Excel, running a macro on it to sort some columns and format it for printing, and then email out the final file to a number of email addresses.

Can anyone think of a way that this can be done with no user input?

We use Exchange/Outlook 2000 for email, but I can't find any way of setting up a rule to open an attachment in a particular application.

[docevi1]

Excel has a "send to -> mail recipent" option in the file menu (at least Excel2000 does).

How automated do you want to be? i.e. triggered and all you see is the result in your inbox?

[ehasler]

Completely automated - i.e., the extract script is cron'd to run on the unix box, .csv file is produced, loaded into Excel where the macro is run, and the resulting file is then emailed out, with the first human contact being when a user sees a perfectly formatted file popping up in their inbox.

Reason for this is that it's going to some senior management who don't want to mess around in Excel every time they get this report.

[anonymous-user]

If you generate an HTML file instead of a CSV and write all of the data into an HTML table, you can then use CSS to format it as you wish.

Write the file out with a .xls extension and attach it to your email and Robert's your father's brother.

[Bodo]

ehasler said:

I've got a perl script (running on a linux box) that runs some SQL queries against a database, generates a .csv file and then emails this as an attachment. What I'd like to do is find a way of opening this .csv file in Excel, running a macro on it to sort some columns and format it for printing, and then email out the final file to a number of email addresses. Can anyone think of a way that this can be done with no user input? We use Exchange/Outlook 2000 for email, but I can't find any way of setting up a rule to open an attachment in a particular application.

Why not simplify the matter by letting the Linux box generate an already formatted *.csv file, and send it to the final recipeints? Excel will open the csv-file.
If they just need something fancy to look at and print it (and not working with the data-tables), why not generate a PDF from the csv with Perl?

Hang on, what does this script:

link below said:

================================================= NAME Spreadsheet::WriteExcel - Write formatted text and numbers to a cross-platform Excel binary file. ================================================= DESCRIPTION The Spreadsheet::WriteExcel module can be used to create a cross- platform Excel binary file. Multiple worksheets can be added to a workbook and formatting can be applied to cells. Text, numbers, formulas and hyperlinks can be written to the cells. The Excel file produced by this module is compatible with Excel 5, 95, 97 and 2000. The module will work on the majority of Windows, UNIX and Macintosh platforms. Generated files are also compatible with the Linux/UNIX spreadsheet applications OpenOffice, Gnumeric and XESS. The generated files are not compatible with MS Access. This module cannot be used to read an Excel file. See Spreadsheet::ParseExcel or look at the main documentation for some suggestions. This module cannot be uses to write to an existing Excel file. =================================================

see here: http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-0.33/

[Liszt]

The easy way would be to set up a scheduled job which opens an excel .xls file which has a run on open macro which picks up the .csv file, formats it, then emails, then closes excel.

[ehasler]

Liszt said:

The easy way would be to set up a scheduled job which opens an excel .xls file which has a run on open macro which picks up the .csv file, formats it, then emails, then closes excel.

Can't do this automatically due to security settings not allowing macros to send mails.

[ehasler]

Bodo said:

Hang on, what does this script: [quote=link below]================================================= NAME Spreadsheet::WriteExcel - Write formatted text and numbers to a cross-platform Excel binary file.

I actually found this not long after posting my question, and have already produced my first .xls from Perl!

[Bodo]

ehasler said:

I actually found this not long after posting my question, and have already produced my first .xls from Perl!

Well then, emailing from scripts shouldn't be too much of a problem with a Linux box, no?!

[ehasler]

Bodo said:

ehasler said: I actually found this not long after posting my question, and have already produced my first .xls from Perl! Well then, emailing from scripts shouldn't be too much of a problem with a Linux box, no?!

Now I've found this Perl module to create Excel files, it's all a piece of cake!

for Perl and unix!