Adding new separate Data Ranges to Chart by macros

[Сергей Торохов <st...@gmail.com>]

Hello!

As currently there is no way in AOO to quick add new DataRange to existing
Chart
I'm tring to write OO.basic macros to add New Data Series with fisrt Column
of new Range assotiated as "X-Range Values"
of new Data Series.

But I encounted the problem that after adding Data Range

As example
(Chart_test.ods:
https://drive.google.com/open?id=0B8H0tc8vHeRKbTNWUWlBY3BvYjA):

Range "A1:B4" contains one series of data and "D1:E5" contains another
with first columns of this ranges associated as "X-Range Values":

A1:B4:         D1:E5:
> x1    y1        x2    y2
>   5      3          7     5
>   9      2        10     2
> 12      5        12     1
>                    14     3


I created the Calc Sheet with this data and "static" Chart (see link to
test file without macros above)
and add to this file next macros (also as txt-file:
https://drive.google.com/open?id=0B8H0tc8vHeRKaDFUbHZHaTRaZUk):


' Chart test based on "Listing 475. Create a simple chart"
> ' from "OpenOffice.org Macros Explained" Andrew D. Pitonyak, 2015
>
> ' Select Range "D1:E4" or "D1:E5" before runing this Macros
> Sub AddRangeToChart
>     Dim oSheet 'Sheet containing the chart
>     Dim sName$ 'Chart name
>     Dim sData$ 'Data Range
>     Dim oAddress 'Address of data to plot
>     Dim oRect 'How big is the chart
>     Dim oCharts 'Charts in the sheet
>     Dim oChart 'Created chart
>     Dim oChartDoc 'Embedded chart object
>     Dim oDiagram 'Inserted diagram (data).
>
>     sName = "testChart"
>     oDoc = ThisComponent
>     oSheet = oDoc.CurrentController.getActiveSheet()
>     sData = "A1:B4"
>     oAddress = oSheet.getCellRangeByName(sData).getRangeAddress()
>     oCharts = oSheet.getCharts()
>
>     If NOT oCharts.hasByName(sName) Then
>         oRect = createObject("com.sun.star.awt.Rectangle")
>         oRect.X = 2250
>         oRect.Y = 11200
>         oRect.Width = 12000
>         oRect.Height= 8000
>         ' The rectangle identifies the dimensions in 1/100 mm.
>         ' The address is the location of the data.
>         ' True indicates that column headings should be used.
>         ' False indicates that Row headings should not be used.
>         oCharts.addNewByName(sName, oRect, Array(oAddress), True, False)
>     End If
>
>     oChart = oCharts.getByName( sName )
>     oChart.setRanges(Array(oAddress))
>     oChartDoc = oChart.getEmbeddedObject()
>
>     ' Create a diagram.
>     oDiagram = oChartDoc.createInstance( "com.sun.star.chart.XYDiagram" )
>     oChartDoc.setDiagram( oDiagram )
>     oDiagram = oChartDoc.getDiagram()
>     oDiagram.SplineType = 1
>
>     ' Modify an existing Chart
>     '  Select Range "D1:E4" or "D1:E5" before runingthis  Macros
>     oAddress2 = oDoc.CurrentSelection.getRangeAddress
>
>     If oCharts.hasByName(sName) Then
>       oChart = oCharts.getByName(sName)
>       oData = Array(oAddress, oAddress2)
>       oChart.setRanges(oData)
>       oDiagram.SymbolType = -1
>     End If
>
> End Sub
>

This macros create Chart for "A1:B4" and add Data Range for selected by
user Range.

I select "D1:E4" Range and Run macros. As a result I get Chart with three
lines.
If I select "D1:E5" and Run macros I get one line.

Note that new Data Ranges may have other number of rows than initial.

I also tried tried to experiment with setData() method to modify Range Data
of Chart directly
but it convert Chart to OLE-Object with it's own "Chart Data Table" not
connected with Sheet.

Well, is there the way to add to Chart new Series of Selected Data Range
with
first Column of new Range as "X-Range Values" ("Data Ranges..." Chart's
Properties)
instead of first Column of old ones? Or is there some OO.Basic-method set
"X-Range Values" directly
for separate Data Series?

Does it planing to add similar feature to quick add new arbitrary Data
Series to existing Chart?
It's too slowly add them manually through "Data Ranges" Chart's Properties
when there is many independent Data Set.


Thank you in Advance!

--
Sergey Torokhov