Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Re: spreadsheet::writeExcel Grouping/Outline

by runrig (Abbot)
on Sep 16, 2013 at 22:51 UTC ( #1054343=note: print w/replies, xml ) Need Help??

in reply to spreadsheet::writeExcel Grouping/Outline

Excel can automatically add Group totals by inserting a row in-between, can spreadsheet::writeExcel do it in similar fashion?

No, you'll have to detect the change in column values and write the subtotal rows yourself. The library won't execute macros for you.

  • Comment on Re: spreadsheet::writeExcel Grouping/Outline

Replies are listed 'Best First'.
Re^2: spreadsheet::writeExcel Grouping/Outline
by omikirani (Initiate) on Sep 17, 2013 at 14:55 UTC

    Thanks for your response. I am actually trying to program a generic Excel reporting module which (for now) does not know how many columns/rows it will have to write to Excel and will not know what columns it should group with.
    Any suggestions on any other perl lib which could run excel macros if I could programatically tell it which column(s) it should use while grouping..
    (At this time I am not aware if the macro commands could be lined up for future execution when the excel file is opened)

      Any suggestions on any other perl lib which could run excel macros..
      Win32::OLE perhaps...if you are running on Windows, and have Excel installed. But really, as you are creating a report, you should know which columns to group by, and which columns to subtotal. Even Excel asks you to confirm if it has guessed correctly when you hit the subtotal button. You could write a 'group by'/subtotal 'these' columns subroutine if you want to be generic about it. But then you would have to write the subtotal rows as you are writing the report, because the non-OLE library (AFAIK) cannot 'insert' rows between already written rows.
        I read about Win32::OLE, unfortunately I am on a UNIX box and that is when I had to ruled it out.. I was just trying save some of my coding time if there was something already built for a work-around. But I can absolutely do a similar thing while inserting rows to Excel..
        Thanks for looking.. :)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1054343]
[marto]: night marioroy

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (3)
As of 2018-01-23 10:11 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (242 votes). Check out past polls.