Spreadsheet::WriteExcel, draw circles or a pie chart?

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

Hy there,

I'm using Spreadsheet::WriteExcel for a couple of days now, because the PHPExcel Class, that I used before, didn't had some features I need ;-)

So far I'm really happy with Spreadsheet::WriteExcel. The only Problem I got is the following:

I have several worksheets, generated by content from a db. Each worksheet has, besides from the content, four columns with data_validation lists. So in the data-validation cells (it's like a drop-down-box) there are only values valid that are predefined (e.g. high, medium, low). When someone works with this worksheets he can now select this predefined values for every row that has content from the db.

The last worksheet that is created is called summary. In this worksheet I use the Excel-Formula "countif" to count how often a certain value is choosen in a certain worksheet (e.g. high 4 times, medium 2 times and so on). This all works so far.

What I want is a function that generates a pie chart out of this values. So every time the values are changing (e.g. high changes to medium) I want that this pie chart is redrawn.

I didn't find any functions in Spreadsheet::WriteExcel. Do you know how I can do this, without using VBA-Stuff?

Thx in advance and I hope I made the situation clear ;-)

Comment on Spreadsheet::WriteExcel, draw circles or a pie chart?

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel, draw circles or a pie chart? by jrsimmon (Hermit) on Jul 16, 2009 at 13:57 UTC
The only way that I see to do what you're wanting with Spreadsheet::WriteExcel is to import the chart from another source. That would be cumbersome, however, since it looks like import only pulls in the image, not the formula which creates the chart (ie, you'd have to export your data somewhere, create a chart, then import that chart -- ugh). Have you considered using Win32::OLE instead? It provides a very high level of control over Excel itself -- you can essentially do anything with it that you can do within the excel application itself. Using Win32::OLE and excel, I actually do something quite similar. Using that module, you can define the chart in the excel spreadsheet and simply tell it to refresh once you've updated the data. I've placed the subroutine that implements the refresh on my scratch pad. You may also find RFC: Win32::OLE and Excel's RefreshAll worthwhile.	[reply]
Re: Spreadsheet::WriteExcel, draw circles or a pie chart? by biohisham (Priest) on Jul 16, 2009 at 13:26 UTC
Unfortunately, SpreadSheet::WriteExcel has not any functions available of that caliber, it has, however, functionalities that try to as closely as possible mimic excel environment and it provides excel with a sort of a programming interface that offers to employ many excel features in its methods. You can add externally created charts to SpreadSheet::WriteExcel, hope I am giving you good tidings. Check this page for some Perl examples of different SpreadSheet::WriteExcel programs http://www.koders.com/info.aspx?c=ProjectInfo&pid=WZKBNF1X3ERMTDSA4BGQNFCQSF another thing, there could be other Statistical packages in the CPAN, they give some chart capabilities but I haven't used any of them to suggest something. Take a look at the CPAN Excellence is an Endeavor of Persistence. Chance Favors a Prepared Mind	[reply] [d/l]
Re: Spreadsheet::WriteExcel, draw circles or a pie chart? by softwarejanitor (Novice) on Dec 18, 2009 at 16:28 UTC
Spreadsheet::WriteExcel has the ability to insert charts created in Excel into spreadsheets. Unfortunately at the moment it is somewhat limited because the chart has to be created in Excel, and then extracted with an external utility. Once extracted you can't change much about the chart including the data range selections of the series, which is a fairly major limitation. I am currently working on a module to be used in combination with Spreadsheet::WriteExcel to generate Excel (actually they will be more like oocalc) charts dynamically and allow many (hopefully eventually most) of the properties of the chart to be changed on the fly. Right now it is partially working for some of the simple types of charts (Column, Bar & Line), but it still has a lot of bugs and a lot of things aren't implemented yet. Unfortunately Pie charts are one of the types I have spent little to no time working on so far. I've got limited time to work on it at the moment, so if anyone is interested in helping with development/testing, that'd be great... I've been in contact with John M., the maintainer of Spreadsheet::WriteExcel, and it is possible that some of my code may eventually be integrated into that module, but probably not anytime soon.	[reply]
Re: Spreadsheet::WriteExcel, draw circles or a pie chart? by sushi2k (Novice) on Jul 16, 2009 at 15:17 UTC
Hy, and thx for the answers. I thought so that there is no easy way to do this ;-) @biohisham: Yeah, I'm aware of Spreadsheet::WriteExcel::Chart, but it's not quite that what I'm looking for. Because then I would have to make the chart in another document... But I will look at the CPAN Archive, thx. @jrsimmon: I will have a closer lock at Win32::OLE, sounds like it's the Module I've looking for ;-)	[reply]