Best way to automate the modification of an excel file?

Question

Best way to automate the modification of an excel file?

Answer 1

there is a cpan module to do excel spreadsheet manipulation



search for it at cpan.org or using the cpan module to load



check out XML::Excel and Spreadsheet::ParseExcel



All perl, all the time!



UPDATE:

check out Spreadsheet::WriteExcel



you can alway write the macros from scratch (from the program)

Answer 2

uhh. tough one :)



if you want to make your life hell - you can do it in perl (and spent 3 months learning how the guts of xls look like). The easier way, if you must have it in .xls is to use VBA - it's relatively easy, if you find a good tutorial. Most of the stuff is in excell's help. you just write a vba macro - you can set it so it's tied to that .xls file, or you can set it so anybody who opens that .xls file can install your macro to be used.



but, excell macros don't work in open office (or whatever you're using on unix).



better way for this is csv (comma delimited) format. it's basically a text file that looks like this



a,b,c

d,e,f



comma delimits columns, and enter is new row. you don't have to use comma for delimiter, almost anything will do (ie tab, or ;). you can edit this in any language you want, it's cross platform (every spreadsheet in the world knows how to open csv). you're loosing macros and cell coloring and other excell eye candy. for macros you can write your utilities to take their place.



my advice is take the csv path - manuals for .xls format are nonexistent and it's not cross-platform - i guarantee you you'll sweat blood if you don't :)



khm - vba then.

Answer 3

if you'd like to do it in Java..... there is a package called POI made by apache that will handle this for you.



Update:



There are several additional features to poi that allow for reading and editing, I'd look more into.