Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

munging csv into excel or otherwise

by deprecated (Priest)
on Dec 18, 2008 at 19:16 UTC ( #731354=perlquestion: print w/replies, xml ) Need Help??
deprecated has asked for the wisdom of the Perl Monks concerning the following question:

I have a PHB who has asked that I submit two excel sheets and a text report, weekly, summarizing, I guess, what I do. At first, I was too verbose, and he said "write less." Then he said I was not being verbose enough, and he said "explain in more detail. consider your audience." Quickly I tired of this, took his example verbatim, and made a perl script out of it.

So now I just run and I fill in the fields it prompts me for. Okay, that's fine. He's now (I really couldn't make this up) insisted that it also be in times new roman, twelve point. Okay, so I'll switch from .txt to .rtf. Word will open that bad boy right up (so much for fixed-width ascii eh).

What bugs me is the excel sheets. Picture a seven by seven matrix, with the columns being days of the week, and the rows being the form of mediocrity, so you'd have "selling ice cream" and "abducting children" and "fixing the van" and "chatting up kids on the internet" and so on as rows.

I can do that pretty simply in a csv, except that I think he will be unhappy if I do not actually include the colors he has so thoughtfully supplied in his tps.xls spreadsheet (grey, green, and yellow). Is there a way to create these sorts of objects in perl?

Bear in mind one caveat. While I hate doing this sort of nonsense ("I wouldn't really say I've been working, Bob..."), let us remember that the reason I am asking is laziness and impatience. by having to invoke some mega module to create a giant Microsoft::Office::2003::Excel::TPS::Colored object, I've essentially defeated the whole point of reducing the amount of time I spend actually filling these crap reports out (although I will admit I will gain a large amount of satisfaction via that clever thinkgeek slogan - go away, or I will replace you with a very small <s>shell</s>perl script).

Anyone have suggestions?

brother dep

(kinda surprised to see it's been, gasp, another year...)

Tilly is my hero.

Replies are listed 'Best First'.
Re: munging csv into excel or otherwise
by Jenda (Abbot) on Dec 18, 2008 at 19:26 UTC
Re: munging csv into excel or otherwise
by tilly (Archbishop) on Dec 18, 2008 at 19:26 UTC
Re: munging csv into excel or otherwise
by Bloodnok (Vicar) on Dec 18, 2008 at 19:26 UTC
    Why not just use Win32::OLE - it's not that hard to use - especially if you use examples on Dave Roths' website


    Thanx to all, specifically ikegami & kennethk for pointing out the error in my typing of the website address.

    A user level that continues to overstate my experience :-))
Re: munging csv into excel or otherwise
by Tux (Abbot) on Dec 18, 2008 at 19:39 UTC

    I pity you. If someone would require me to make my work ugly by requiring Times New Roman or Courier, I would be likely to refuse the job, or to use a nice font instead.

    However, I can maybe help you just a small bit further. Just create the CSV as you were planning and then take csv2xls from my Text::CSV_XS as a base to mould it into your liking, by adding colours and fonts.

    Enjoy, Have FUN! H.Merijn
Re: munging csv into excel or otherwise
by n3toy (Hermit) on Dec 18, 2008 at 19:56 UTC
    What you need to do is trivial with Spreadsheet::WriteExcel, and if you need to parse an excel sheet for your reports, Spreadsheet::ParseExcel works just as well. These two modules save me hours a day at work.

    However, if you are pulling data from a CSV file, you will need a different module for that part.

Re: munging csv into excel or otherwise
by trwww (Priest) on Dec 18, 2008 at 22:10 UTC


    You are a pretty good writer, you should submit this to


      I want to thank everyone for their responses. What really strikes me about the whole endeavor is it never once occurred to me to quit. Well, it did, once. But it should have occurred to me long before I bothered any of you with this insanity.

      So, thank you for the help. When I did provide his properly formatted TPS reports, I was given Yet Another Requirement. He couldn't possibly be a perl hacker; rather I think he was just trying to push limits of what can be generated automatically in less time than it would take to do manually – which was his goal all along.

      Sad, that. My own personal advice would be, if you find yourself in a position where your supervisor finds more and more complicated ways for your to waste your time on his behalf, to just leave. If you're of the skill level that you can write tools to automate your mundane responsibilities, you won't be looking for long, and the supervisor in question is obviously one of those pieces of human effluvia floating about the toilet bowl that is the workplace.

      brother dep

      Tilly is my hero.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://731354]
Approved by Bloodnok
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2017-08-18 21:12 GMT
Find Nodes?
    Voting Booth?
    Who is your favorite scientist and why?

    Results (310 votes). Check out past polls.