“熄灯”自动计划批量创建Excel工作簿？

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)?

任何人都有一个很好的方法来自动批量创建自定义格式的Excel工作簿在定期安排或事件发生后(例如,文件创建,表加载)?

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!

为了使事情变得更有趣,让我们进一步假设源数据在关系数据库或Unix文件中,结果需要通过电子邮件发送或传输到Unix Web服务器。谢谢!

2 个解决方案

#1

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):

事实上,这不是一个问题,而是一系列问题。我可以在这个内部看到以下不同的问题(假设您将使用像perl或python或类似的脚本语言):

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.

必须以规则的时间间隔执行任务:使用cron在预定义的事件之后:这里不多说,这取决于你想要什么。

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.

必须从以下数据中检索数据:从文件中使用您正在使用的特定数据库的语言绑定(例如:sqlite3的python绑定)(无论如何是什么Unix文件?):取决于格式您可以使用sed / awk或在您选择的脚本(或其他:P)语言中编写解析器来获取该文件。

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):

必须将数据按摩到excel工作簿中。好吧,我不太确定,你的意思是“自定义格式化”,但创建excel工作簿的最简单方法实际上只是将其转储到.csv,但你可以更进一步,实际上产生一个“伪xls” “,通过使用以下模板并将生成的文件保存为.xls(它实际上有效):

 <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).

生成的文件必须是:通过电子邮件发送:使用mail命令,它通常指向系统上的默认邮件程序(exim,sendmail,postfix)“传输”到Web服务器, - 我在这里假设,这意味着“转移”到另一台机器,以便可以通过http(s)访问它“。在这种情况下,您可以使用ftp,sftp或rsync(我最喜欢的)。

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

很抱歉非常不具体,但要从您的问题中推断出您想要实现的目标并不容易。

#2

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.

Excel可以读取.csv(逗号分隔变量)或.tsv(制表符分隔变量)文件。将输出转储到csv中是“微不足道的”(只需确保转义输入中的任何逗号或制表符),然后excel可以读取它。

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

如果要生成.xls文件,则必须使用您选择的语言查找实现.xls文件处理的库。例如,如果您正在使用python,那么就会有一个完整的邮件列表专门讨论这样做

#1