Source of OOoBasic/Calc/chart2/ex3

* データ範囲の変更 [#c6781087]

#contents

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

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

#code(basic){{
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 
}}