Using perl to control spreadsheets

I have been a longtime programmer in VBA due to my job needs for manipulating data in Excel. I am in the process of moving most of my computing activities to Linux; specifically Ubuntu. I am new to Perl, and to PerlMonks; so when I was browsing the monastery the other day, I saw a post about using Perl to control Excel. The concept hit me like a ton of bricks; the idea that I could use a language that has extensive applications beyond Excel to program Excel was an aha! moment. No more using a Microsoft lock in language for me. Going forward I am going to do all of my spreadsheet programming in Perl. The bonus to me is that the Perl knowledge will be useful in other computing areas that I am interested in, unlike VBA.

Comment on Using perl to control spreadsheets

Replies are listed 'Best First'.
Re: Using perl to control spreadsheets by davidrw (Prior) on Nov 15, 2005 at 16:43 UTC
If you haven't seen it yet, the node Index of Spreadsheet FAQs is a very good resource. There's also Using Win32::OLE and Excel - Tips and Tricks from the Tutorials section. The best general advice i can give is that if you can write it in vb (or have excel record a macro for you), you can probably directly port it to perl/Win32::OLE code. Beyond controlling excel itself, Spreadsheet::WriteExcel is very good for creating (but can't update) xls files as well (and doesn't require windows or excel).	[reply]
Re: Using perl to control spreadsheets by tirwhan (Abbot) on Nov 15, 2005 at 16:17 UTC
It's good that you've made this realisation, and I wish you luck on your way forward. But maybe you should even go one step further and, instead of doing spreadsheet programming, use a proper database as your backend data storage, and only use spreadsheets as your input/output/report format. This obviously depends on what exactly you're doing, but I have found that a lot of the things people do with spreadsheets are actually better and easier to do if you're not tied to the simple grid table-format and have a relational database as your data store. Moving from one concept to the other can be a real eye-opener as well. Just a thought. Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan	[reply]
Re: Using perl to control spreadsheets by planetscape (Chancellor) on Nov 16, 2005 at 02:19 UTC
There are good resources in this thread. HTH, planetscape	[reply]
Re: Using perl to control spreadsheets by jonix (Friar) on Nov 15, 2005 at 16:46 UTC
Yeah, Perl is really great for this kind of job. Use it for extracting and transforming data from a database as tirwhan suggested and know your regular expressions and SQL to unleash even more of its powers :) Have fun with it!	[reply]