Top > OOoBasic > Calc > chart2 > ex3

データ範囲の変更 Edit

あるシート上で作成したチャートをコピーして別のシートに貼り付けるとデータ範囲は元のシートのままになっています。そのチャートのデータ範囲をそのシートに変更するもの。

データ範囲を保持している LabeledDataSequence から範囲を取得 (Sheet1.A1:A4 などの形式)、その範囲を変更してデータを作成しなおしています。Role (ラベルや x, y などの指定) は再利用。新しいデータは下記の CreateDataSequence を利用すれば範囲指定と Role 指定から簡単に作成できます。

  0
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
Sub ChartSeriesModify
  oSheet = ThisComponent.getCurrentController().getActiveSheet()
  sSheetName = oSheet.Name
  oCharts = oSheet.getCharts()
  oChart = oCharts.getByIndex(0).getEmbeddedObject()
  oChart.lockControllers()
 
  oDiagram = oChart.getFirstDiagram()
  oDataProvider = oChart.getDataProvider()
 
  oCoords = oDiagram.getCoordinateSystems()
  oCoord = oCoords(0)
 
  oChartTypes = oCoord.getChartTypes()
  oChartType = oChartTypes(0)
 
  ' data series retain all of data used in the chart
  oDataSeries = oChartType.getDataSeries()
 
  For i = 0 To UBound(oDataSeries) step 1
    oSeries = oDataSeries(i)
    oSeqes = oSeries.getDataSequences()
    Dim aData(UBound(oSeqes)) As Object
   
    For j = 0 To UBound(oSeqes) step 1
      oSeq = oSeqes(j)
     
      oNewSeq = CreateUnoService(_
          "com.sun.star.chart2.data.LabeledDataSequence")
     
      oNewValues = nothing
      oNewLabel = nothing
     
      ' get current data
      oLabel = oSeq.getLabel()
      oValues = oSeq.getValues()
     
      If NOT IsNull(oLabel) Then
        sLabelRole = oLabel.Role
        sLabelDesc = oLabel.getSourceRangeRepresentation()
        sAddr = split(sLabelDesc, ".")
        sLabelDesc = sSheetName & "." & sAddr(1) ' on the current sheet
       
        ' create new data with current sheet name
        oNewLabel = CreateDataSequence(oDataProvider, sLabelDesc, sLabelRole)
        oNewSeq.setLabel(oNewLabel)
      End If
      If NOT IsNull(oValues) Then
        sValRole = oValues.Role
        sValDesc = oValues.getSourceRangeRepresentation()
        sAddr = split(sValDesc, ".")
        sValDesc = sSheetName & "." & sAddr(1) ' on the current sheet
       
        oNewValues = CreateDataSequence(oDataProvider, sValDesc, sValRole)
        oNewSeq.setValues(oNewValues)
      End If
     
      aData(j) = oNewSeq
    Next
   
    ' set new data to existing series
    oSeries.setData(aData)
    oDataSeries(i) = oSeries
  Next
 
  ' apply new data
  oChartType.setDataSeries(oDataSeries)
  oChart.unlockControllers()
End Sub
 
 
 
' creat new DataSequence from range representaion
' that provides real data and its role in the series
' oDataProvider: com.sun.star.chart2.data.XDataProvider
' sRangeRepresentation: range address e.g. Sheet1.A1:B2
' sRole: role is defined in com.sun.star.chart2.data.DataSequenceRole
Function CreateDataSequence( _
    oDataProvider As Object, _
    sRangeRepresentation As String, sRole As String ) As Object
 
  Dim oDataSequence As Object
  On Error GoTo Handler
  ' create .chart2.data.DataSequence from range representation
  If oDataProvider._
      createDataSequenceByRangeRepresentationPossible(sRangeRepresentation) Then
    oDataSequence = oDataProvider._
        createDataSequenceByRangeRepresentation(sRangeRepresentation)
    If NOT IsNull(oDataSequence) Then
      oDataSequence.Role = sRole
    End If
  End If
 
  Handler:
    CreateDataSequence = oDataSequence
End Function

Reload   New Lower page making Edit Freeze Diff Upload Copy Rename   Front page List of pages Search Recent changes Backup   Help   RSS of recent changes