Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Writing Excel Spreadsheets -- suggestions sought

by blue_cowdawg (Monsignor)
on Sep 13, 2013 at 13:34 UTC ( #1053936=perlquestion: print w/replies, xml ) Need Help??
blue_cowdawg has asked for the wisdom of the Perl Monks concerning the following question:

I recently inherited the dubious honor of being the email delivery police for my company. One of my roles within that is dealing with clients of ours whos email jobs result in UCBE complaints being filed.

This is the facet of the job I take most seriously. Our remailer service is not supposed to be used for UCBE but every once in a while we have a client that doesn't play by the rules and that's where I get involved.

In order to get the client "dealt with" I have to be able to build a case with management and show real data. To that end I am writing an automated solution to collect the complaint data and convert it to a spreadsheet (management loves spreadsheets) along with charts and graphs (they love them even more) and provide the spreadsheets in an email. A weekly report of sorts. </p.

I have 99% of this figured out and working. The last 1% is bugging the hell out of me and I'd like some suggestions on how to solve the issues.

I use SpreadSheet::Excel to generate my spreadsheets and have for years. The one thing I've never been able to figure out is how to auto size the columns. I take a SWAG and just hard set the width which mostly works but occasionally I have to go in and hand tweak the widths before sending the spreadsheet out. I don't want to do that any more.

I'm hoping to eventually take myself out of the picture so far as the reports go and they'll get delivered even when I'm on vacation, out sicke, feeling lazy or whatever.


Peter L. Berghold -- Unix Professional
Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
  • Comment on Writing Excel Spreadsheets -- suggestions sought

Replies are listed 'Best First'.
Re: Writing Excel Spreadsheets -- suggestions sought
by MidLifeXis (Monsignor) on Sep 13, 2013 at 14:31 UTC

      That's just the ticket! I will soon have "management reports" for the manager types.

      Peter L. Berghold -- Unix Professional
      Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re: Writing Excel Spreadsheets -- suggestions sought
by dasgar (Priest) on Sep 13, 2013 at 15:40 UTC

    Not sure if this is necessarily a "good" suggestion, but I'll toss it out anyways.

    I'm assuming that when you "hand tweak the widths" that you're doing so on a Windows system that has Excel. Working off that assumption, here's one crazy (and maybe extreme) idea:

    • Have your current script put the Excel file on a network share and not do the automated email.
    • From a Windows system that has Excel installed and access to the network share from the previous step, have a script that goes opens the Excel file from that share and uses Win32::OLE to handle all of the autofit stuff. You should be able to use task scheduler to configure this script to run at a specific time each week.
    • Then have yet a third script that would handle the emailing of the finalized Excel file.

    If having a Windows box with Excel to handle the autofit stuff is not a viable solution for you, perhaps you can look into using OpenOffice or LibreOffice and automate the autofit through their API - similar to using Win32::OLE to automate Excel.

    I'll admit my ideas probably are a bit crazy and extreme, but thought I'd toss them out in case it proves useful or might give someone an idea for a better solution.

Re: Writing Excel Spreadsheets -- suggestions sought
by glenn (Scribe) on Sep 13, 2013 at 14:08 UTC
    Not sure what your collecting or how the excel module works but could you not just watch for the longest string length and use that value at the end?
    my $col_width = 10; #default ...#code if (length($variable) > $col_width) { my $col_width = length($variable); } #set column width

      The trick is in determining the width of the text in pixels/"inches" rather than characters, so you can set the column width.

      However, there's an easy solution to that; set the text in that column to be a fixed-width font.

Re: Writing Excel Spreadsheets -- suggestions sought
by Albannach (Prior) on Sep 13, 2013 at 21:35 UTC
    Another potentially hair-brained scheme for your consideration:
    • For a one time task using actual Excel, create a workbook with an Auto_Open() macro containing simply Columns.AutoFit
    • Again one time only, using the utility from Excel::Writer::XLSX, extract the macro as a binary file and store it somewhere permanently for use in your periodic spreadsheet generation
    • Use Excel::Writer::XLSX to do all your spreadsheet creation, and also insert the macro using add_vba_project( 'vbaProject.bin' ), then distribute the resulting as an XLSM file. Now I don't know how much this will mess up your spreadsheet creation code, but according to the docs, it should be little problem.

    The downside is depending on your users' setups they may have to authorize the macro to run when they load the file, which may generate lots of questions and put you no farther ahead than you are now.

    I'd like to be able to assign to an luser

Re: Writing Excel Spreadsheets -- suggestions sought
by Anonymous Monk on Sep 13, 2013 at 15:32 UTC
    I recently went through this same problem with an automated 'management spreadsheet' for my boss. My solution was to set all font to Courier since it is fixed width and that took care of the issue for me.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1053936]
Approved by Happy-the-monk
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (8)
As of 2018-03-18 16:27 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (230 votes). Check out past polls.