Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Thanks for the help provided at - Using Win32::OLE and Excel - Tips and Tricks I could make use of tips and even code for writing data to excel sheets. I have an issue to be resolved in hand. My workbook comprises of 10 sheets (Test.xls). Each one of them has 3 columns of data (Total Testcase executed, Testcases passed, Testcases Failed). I want to collate all the data of 10 sheets and produce a graph depicting the summary. How do i do it using perl (Win32::OLE package) like generating Pie charts, Bar graphs all that good stuff.. for giving me the clear picture of each sheet's data with corresponding sheet title ?? Thanks in Advance. Hemanth Kumar K R

Replies are listed 'Best First'.
Re: Building Charts (PIE)
by holli (Abbot) on Feb 08, 2005 at 11:21 UTC
    When it comes to Office-automation, i normally just do small parts of the job per hand, while monitoring that via the macro-recorder. I then identify the critical parts (The makro-recorder is very talkative) of the macros and convert that to perl.


    holli, /regexed monk/
[reply]
Re: Building Charts (PIE)
by FitTrend (Pilgrim) on Feb 08, 2005 at 14:58 UTC
    Personally, I've had my pains with working with the Win32::OLE module. As an alternative, I've exported the data to CSV and the used GD::Graph to generate my charts. This may not be exactly what you're looking for, but if you do have probems with the module it may be a viable alternative. Hope this helps.

    Marc

[reply]
Re: Building Charts (PIE)
by samizdat (Vicar) on Feb 08, 2005 at 13:32 UTC
    According to the Spreadsheet::WriteExcel docs, graphs can be created, but they need to be done by hand in an Excel sheet and then added to your workbook as inserts. Haven't tackled it myself, but look at function add_chart_ext() in the docs and charts.txt in the module's file directory.

    HTH! :D
[reply]
Re: Building Charts (PIE)
by mattr (Curate) on Feb 09, 2005 at 05:42 UTC
    Hello,

    I am just finishing a wxperl-based app that goes through an excel file that tracks the progress of jobs and draws a bar chart (like a gantt chart with no vertical connectors) for 7 business days. Each bar is colored by department with a stipple over the color for whether it is done or not, and a line of text providing owner, client and other information is drawn above each bar.

    Spreadsheet::ParseExcel to read the sheets (slightly modified to update a progress bar and abort on a button click). After looking around a bit, I settled on using plain GD for drawing and am quite happy with it. Of course you are telling it where to plot each line, but if you just want the commonest graph types GD::Graph I believe. Of course if you are just automating Excel graph creation go for it. But mine draws graphs Excel doesn't have and works cross-platform. There is a PHP based graph drawing system out there called JpGraph but I decided I really didn't want to use PHP. But it can do pies etc.

    As it is the GD drawing is fast, the problem is the Spreadsheet module which takes 1 minute to go through a megabyte-sized file. (I think about 200 rows by 15 columns). Which is okay but a little slow (of course this is on an old Win 98 machine not your screaming multi-gigahertz box).

[reply]
Re: Building Charts (PIE)
by Anonymous Monk on Feb 11, 2005 at 10:41 UTC
    Thanks "Monks" for the valuable suggestions. I have re-designed my work now. I am populating all the data to one sheet of a workbook and need to carry it from now. As mentioned earlier i am using Win32::OLE package only (Windows) as against Spreadsheet::??? and would like to continue using the Win32::OLE package only. My requirement now is drawing a Pie chart using data of columns 1(Module Name) and column 4(Result). Could any of you guys throw some light over this. The chart should show the module name and corresponding result value with distinct colors. Thanks, Hemanth
[reply]
      Hello Guys, My requirement is drawing a Pie chart using data of columns 1(Module Name) and column 4(Result) of a workbook. How to go about doing this? The Pie Chart should show the value of each pie and the Module names should be indexed against respective colour.
[reply]

Back to Seekers of Perl Wisdom