Apache OpenOffice (AOO) Bugzilla – Issue 63892
Chart Data is reset after Sheet Deletion
Last modified: 2013-02-24 21:22:31 UTC
If I run the following code in OOo 2.0 a chart is created but after the Delete Sheet macro is run the chart no longer has data displayed. It also occurs if the ThisComponent.Sheets.removeByName("Sheet1") line is just added to the RunPC macro. It seems the data range of the chart is changed to Cell A1. Thank you Chris 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) DeleteSheet("Sheet2") End Sub 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 ' 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 Sub DeleteSheet(SName as string) If ThisComponent.Sheets.hasByName(SName) Then _ ThisComponent.Sheets.removeByName(SName) End Sub
*** Issue 63893 has been marked as a duplicate of this issue. ***
->NN: I think the problem is, that in the PieChart routine the chart is added to the TableChartCollection, which is a collection of charts that get their data from calc. If you comment out the deletion-call: After inserting the chart, it has already a range of "Sheet1:A1", (but this is maybe just what the UI says). If you press F9, the chart does not change, i.e. there is no data update. However, when you delete Sheet2 (even via UI), the chart gets new (empty) data. Is there a way to fix this, or does the Basic code have to be changed, such that the chart is not added to the TableChart collection, but rather inserted as a "simple" OLE object?
@chrisk1 Please attach a sample document with integrated macro! Please specify your Platrorm, OS and OOo version!
Closing due to reporter's inactivity
.
Created attachment 74673