"Lights Out" Automated Scheduled Batch Creation of Excel Workbook?

Question

Anybody have a good approach to automate the batch creation of custom-formatted Excel workbooks at a regularly scheduled time or after an event occurs (e.g., file created, table loaded)?

To make things even more interesting, let's further assume that the source data is in a relational database or Unix file, and the results need to be e-mailed or transmitted to a Unix web server. Thanks!

Answer 1

Excel can read .csv (comma-seperated variable) or .tsv (tab-seperated variable) files. It's 'trivial' to dump your output into a csv (just make sure you escape any commas or tabs in your input), and excel can then read that.

If you want to produce a .xls file, you'll have to find a library in your language of choice that implements handling of .xls files. For instance, if you're using python, there's an entire mailing list devoted to talking about doing this

Answer 2

In fact this is not a question, but a series of questions. I can see the following distinct questions inside this one (assuming, that you are going to use a scripting language like perl or python or something similar):

• The task must be performed
  • At regular time intervals: use cron
  • After a predefined event: not much to say here, it depends on what exactly you want.
• The data has to be retrieved from:
  • from the database: use the bindings for your language for the specific database you are using (example: python bindings for sqlite3)
  • from a file (what is a Unix file anyway?): depending on the format of the file you can get away by using sed/awk or write a parser in your scripting (or otherwise :P) language of choice.
• The data has to be massaged into an excel workbook. Well, I am not so sure, what you mean by "custom formatted", but the easiest way to create an excel workbook is, indeed, just dumping it to .csv, but you can go further and actually produce a "pseudo-xls", by using the following template and saving the resulting file as .xls (it actually works):

 <table>
 <tr>
   <td>field0</td>
   <td>field1</td>
   ..
   <td>fieldX</td>
 </tr>
 ... ad inf
 </table>

• The resulting file has to be:
  • emailed: use the mail command, which usually points to the default mailer on your system (exim, sendmail, postfix)
  • "transmitted" to a web server, - I am assuming here, that this means "transfered to another machine, so that it can be made accessible via http(s)". In that case you can use ftp, sftp or rsync (my favourite).

Sorry for being extremely non-specific, but it is not easy to deduce what exactly you are trying to achieve from your question.