Re^2: Writing to an Excel file

I don't know how many times you need to do this, how often and whether you need a 100% automated process or not. I really like biohisham's suggestions but its not "cheating" to use some of the features of Excel to help you.

I remember one project a long time ago where I had to produce a report in WinWord format once per week. I used Perl as sort of "glue" code to query a weird legacy database on a mainframe and do a bunch of reformatting, sumarizing, translating terms, etc. The output was a text file.

I wrote a Word macro to womp on this Perl generated text file.
So for the process: I ran my Perl program, then opened the resulting file in Word and hit like CTRL-R to make the fancy report. Now of course I designed the text file output to make this Word macro easy to write! But this whole process even with a couple of manual steps took just a minute or two. I never refined the process past that because there was no need...couple of minutes per week was no big deal - it took a lot longer than that to print it and Xerox copies for the management meeting!

You appear to have a simple spreadsheet with 2 columns that would be easily adapatable to this kind of approach. Perl generates a CSV file. Excel macro, imports this file, sizes columns, sets fonts(heading and data), etc. Maybe even this report should be a Word doc with 2 or 3 of these (term, Y/N) paired columns per page?(Word macro can do that).

I don't know the whole picture of what are doing. For all I know just opening this Perl generated CSV file in Excel and then save as "some Excel format" is enough. I use this technique often when doing "one off" reports to send to folks who do wonders with Excel but have never seen any real program code in their lives.

Anyway, when thinking about how fancy to get with say Win32::OLE, you may just need ability to open the file and run a template/macro. Then font type stuff, column width is in this Excel/Word template, not in your Perl code.

This won't solve world hunger, it is just an idea...

Comment on Re^2: Writing to an Excel file

Replies are listed 'Best First'.
Re^3: Writing to an Excel file by kikuchiyo (Hermit) on Oct 22, 2009 at 09:53 UTC
I don't think using Excel or Word macros would be a good idea in this case. It would introduce another component into the process -- an additional possible source of error and an additional part that you'd have to maintain. The point about the compromise between the time to develop a fully automated process and the time spent using a not fully automated solution is a valid one - but in this case, a fully automated, simple solution is well within reach. I'd say Spreadsheet::WriteExcel is the way to go and not Win32::OLE. The latter requires a working installation of Excel and for every manipulation with an Excel object it has to interact with a running instance of Excel - therefore it's slow and wastes resources. On the other hand, S::WE creates the Excel table on the fly, independent from Excel. In other words, it does just what the OP needs.	[reply]
Re^4: Writing to an Excel file by biohisham (Priest) on Oct 22, 2009 at 10:37 UTC
It's great to get this such a task automated once and for all if the OP was going to have to generate this sort of report every other while. Excel alone can be fun to work with of course but not when you want to extend some of its capabilities. Now I don't know which of the modules can be better to work with, I have worked with Spreadsheet::WriteExcel sometime but I faced a problem of not being able to append to an excel file no matter how I tried...so if this is a known issue that should be a disadvantage. Excellence is an Endeavor of Persistence. Chance Favors a Prepared Mind.	[reply] [d/l]