OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Pie Chart with Data Labels and Percentages Using Arrays

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
ChrisK
General User
General User


Joined: 25 Jan 2006
Posts: 17

PostPosted: Fri Mar 24, 2006 12:03 pm    Post subject: Pie Chart with Data Labels and Percentages Using Arrays Reply with quote

Here's a macro I've created (with lots of sources of help, obviously) that will create a pie chart with labels and the percentage of each segment shown. Hope it's useful.
Bye................Chris

Code:

Sub PieChart(ChartDataArray(), LabelsArray(), CName as String, _
     CSheet as String, Optional YPos as Integer)
' DataArray is an array of data values
' DataArrayLabels is the set of labels for each value
' CName is Chart Name and its Title
' CSheet is the Sheet to use for the chart
' YPos is the Y Position of the diagram
' (or Y value of the rectangle shape)
' http://docs.sun.com/app/docs/doc/819-0439
' http://www.oooforum.org/forum/viewtopic.phtml?t=4996, =6297,
'   =11920, =33640, =29391, =31620, =5328
' http://api.openoffice.org/docs/common/ref/com/sun/star/chart/
'   ChartDataCaption.html
' See http://www.openoffice.org/issues/show_bug.cgi?id=63892 for a problem
' with  deleteing sheets
' Dims
 Dim Length as Integer
 Dim oSheet, oCharts, oChart
 Dim oRect as new com.sun.star.awt.Rectangle
' Sets
 oSheet = ThisComponent.getSheets().getByName(CSheet)
 oCharts = ThisComponent.getSheets().getByName(CSheet).Charts
 If IsMissing(ChartNum) Then ChartNum=0
 With oRect
     .X = 0  : .Y = YPos  : .Width = 15000 : .Height = 10000
 End With
' Remove the CName chart if it exists
 If oSheet.getCharts().hasByName(CName) Then _
    oSheet.getCharts().removeByName(CName)
' Make the Data Array N x 0 for use by oChart.createInstance
 Length = UBound(ChartDataArray) - LBound(ChartDataArray)
 Dim DataArray(Length,0)
 For I = 0 to Length
 DataArray(I,0)=ChartDataArray(I)
 Next I
' Make Chart
 oCharts.addNewByName(CName,oRect,Array(),TRUE, TRUE)
 oChart = oCharts.getByName(CName).embeddedObject
 oChart.diagram = oChart.createInstance _
     ("com.sun.star.chart.PieDiagram")
 oChart.lockControllers()
' Data
 oChart.Data.setData(DataArray())
 oChart.Data.setRowDescriptions(LabelsArray())
' Title
 oChart.HasMainTitle=True
 oChart.Title.string = CName
' Labels
 oChart.DataSourceLabelsInFirstColumn = false
 oChart.DataSourceLabelsInFirstRow=true
' Legend
 oSheet.getCharts.getByName(CName).getEmbeddedObject. _
     hasLegend = False
' Percentages
'     Last DrawPage is oSheet.getDrawPage().getCount()-1
 oSheet.drawpage.getByIndex(oSheet.getDrawPage().getCount()-1). _
     model.diagram.setPropertyValue("DataCaption",6)
     'PERCENT = 2 and TEXT = 4
 oChart.unlockControllers()
End Sub


Last edited by ChrisK on Mon Apr 10, 2006 7:26 am; edited 12 times in total
Back to top
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Sat Mar 25, 2006 9:15 am    Post subject: Reply with quote

ChrisK,
Thank you for this code.
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
ChrisK
General User
General User


Joined: 25 Jan 2006
Posts: 17

PostPosted: Tue Mar 28, 2006 2:18 pm    Post subject: Problem Reply with quote

After getting this code working with no problem, I am trying to use but I continue to get a "Basic Runtime Error: Object Variable Not Set" error in the line

oChart.Data.setData(DataArray())

can anyone try thgis macro out and tell me what I'm doing wrong?

I've spent all day trying to get thgis to work and it's getting pretty frustrating.
Thank you................Chris
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed Mar 29, 2006 4:02 am    Post subject: Reply with quote

Well, I havn't got it completely working yet - and not sure I can with this approach. But I can find a couple of errors and with these fixed at least run it to completion.

The big problem is that the CharData object wants a sequence of sequences for its data, not a sequence of doubles.

I am not sure you understood this, given your comment about dataArray being an array of values. It needs to be an array of arrays of values

Code:

Sub Main
dim aryData( 0 to 0 ) as variant
dim arySeriesdata( 6 ) as double
dim arylabels( 6 ) as string

  arySeriesdata(0) = 100
  arySeriesdata(1) = 120
  arySeriesdata(2) = 200
  arySeriesdata(3) = 220
  arySeriesdata(4) = 300
  arySeriesdata(5) = 330
  arySeriesdata(6) = 400
 
  arydata( 0 ) = arySeriesdata()

  aryLabels(0) = "First"
  aryLabels(1) = "Second"
  aryLabels(2) = "Third"
  aryLabels(3) = "Fourth"
  aryLabels(4) = "Fifth"
  aryLabels(5) = "Sixth"
  aryLabels(6) = "Seventh"

  PieChart(aryData(), aryLabels(), "Chart1", "Sheet1", 0)

End Sub


Now I needed to make two small changes to your code. In both cases just needed to add the end if

Code:

 If IsMissing(ChartNum) Then
    ChartNum = 0
    end if


Code:

 If oSheet.getCharts().hasByName(CName) Then
    oSheet.getCharts().removeByName(CName)
    end if


Now, with this I can get it to run to completion and to create a chart, but not the chart I expected. I get only one data point and one label. Kind of a useless pie chart, indeed. Very Happy

Not sure how to proceed, if I go back and look at the dev. guide on this there might be a hint as to the problem:

Developers Guide - Data Access 10.3.2 wrote:

Data can only be accessed for reading when a chart resides inside a spreadsheet document and was inserted as a table chart, that is, the table chart obtains its data from cell ranges of spreadsheets. To change the underlying data, modify the content of the spreadsheet cells. For OLE charts, that is, charts that were inserted as OLE2Shape objects, modify the data.


So this might mean that when embedded in a sheet like this the chart is always going to want a range of cells. I suppose the next step then for this code would be to switch to embedding it as an OLE2 object.

I hope that helps.

EDIT - actually it may be my choice of data that is causing the chart to be...well, one big circle..
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ChrisK
General User
General User


Joined: 25 Jan 2006
Posts: 17

PostPosted: Wed Mar 29, 2006 8:29 am    Post subject: A Solution! Reply with quote

Drew
Thank you so much for the help. Your reply got me thinking and in the fogginess of my mind I remembered something. I read somewhere here that an array has to be defined as a n x 1 (or 0 works too) array to work properly with charts. Therefore the following works with PieChart:
Code:

Sub RunPC
Dim DataArray(4,0)
dim DataLabelsArray(4)

DataArray(0,0)=5000
DataArray(1,0)=4000
DataArray(2,0)=3000
DataArray(3,0)=2000
DataArray(4,0)=1000

DataLabelsArray(0) = "First"
DataLabelsArray(1) = "Second"
DataLabelsArray(2) = "Third"
DataLabelsArray(3) = "Fourth"
DataLabelsArray(4) = "Fifth"

PieChart(DataArray(), DataLabelsArray(), "Chart1", "Sheet1", 0)
End Sub

Bye..................Chris
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Wed Mar 29, 2006 9:09 am    Post subject: Reply with quote

Outstanding...and thank you. I have been needing to geneerate a graph from within Base, so far I was going down the path of linking data to a range of cells in a new sheet. Then charting off of the range, but this is much cleaner for what I need.

Thanks again
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
ChrisK
General User
General User


Joined: 25 Jan 2006
Posts: 17

PostPosted: Thu Mar 30, 2006 10:00 am    Post subject: Add Element to oDataArray and a bug Reply with quote

I changed the above PieChart Macro so that it only accepts one dimensional arrays for the data and labels. The macro creates a N x 0 array for the PieDiagram to use instead of requiring the user to create a N x 0 array.

A recent problem I have found is that if one deletes a sheet using:
ThisComponent.Sheets.removeByName("Sheet1")
after producing the chart, even if you are not using Sheet1 as a source for the array, the data range is reset to cell A1. If you delete Sheet1 before producing the chart all is fine.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group