OpenOffice Automation Delphi how create a scattered chart from non-adjacent columns

Question

Hello, can someone please help me on how to create a XYdiagram from non-adjacent columns? I want to do this in Delphi using the OOoTools.pas interface. This is the working code where I can only select adjacent columns: User The_Fox helped me a lot with some issues I had, thanks for that.

procedure TForm1.ProcessNewChart(aFilename: String);
Var
                  oTheFile, oAllSheets, oMySheet, oCharts, oChart,
                  oCellRangeAddress, oRectangle, oSize, oChartDoc,
                  oTitleTextShape, oDiagram, oMySymbolType : Variant;
begin
ConnectOpenOffice;

   (* Get a handle to the file *)
   oTheFile := OpenSheet(aFilename, True);
   (* Get a handle to the sheets of the Calc file *)
   oAllSheets:= oTheFile.Sheets;

   (* Select the first sheet to work with *)
   oMySheet:= oAllSheets.getByIndex(0);  // first sheet of the spreadsheet

   (* Create a handle to the the charts object *)
   oCharts := oMySheet.getCharts;

   (* Specify the position and dimensions of the to be created chart *)
   oRectangle := oMySheet.Bridge_GetStruct('com.sun.star.awt.Rectangle');
   oRectangle.X      := 8000; // X position
   oRectangle.Y      := 1000; // Y position
   oRectangle.Width  := 15000;// width
   oRectangle.Height := 5000; // height

   (* Specify the Cell ranges where to create a chart from
   The first column specifies the X axis
   The rest specifies the Y values
   The first row specifies the labels of the series
   *)
   oCellRangeAddress :=      oTheFile.Bridge_getStruct('com.sun.star.table.CellRangeAddress');
   oCellRangeAddress.Sheet       := 0; // First sheet of the file
   oCellRangeAddress.StartColumn := 1; // was 10
   oCellRangeAddress.StartRow    := 6;
   oCellRangeAddress.EndColumn   := 10;
   oCellRangeAddress.EndRow      := 71;

  (* Create the Chart *)
  oCharts.addNewByName('MyGraph',oRectangle,VarArrayOf(oCellRangeAddress),True, True);

  (* Now place the chart on the sheet *)
  oChart := oCharts.getByName('MyGraph').EmbeddedObject;

  (* Set The chart type (scatter) *)
  oChart.Diagram := oChart.createInstance('com.sun.star.chart.XYDiagram');

  (* Turn the symbol of the data points off *)
  oChart.Diagram.SymbolType := -3;
  (* Set the spline method 0=none, 1 is cubic and 2 = spline B *)
  oChart.Diagram.SplineType := 0;

  (* Set the color of the font *)
  oChart.Diagram.wall.FillColor := RGB(150,150,150);

  (*Set the maximym Yaxis value*)
  oChart.Diagram.YAxis.Max := 40000;

  (* Set a Y axis title *)
  oChart.Diagram.HasYAxisTitle := True;
  oChart.Diagram.YAxisTitle.string := 'Values';

  (* Set an X axis title *)
  oChart.Diagram.HasXAxisTitle := True;
  oChart.Diagram.XAxisTitle.string := 'Logged Points';

  (* The first row contains the names of the columns *)
  oChart.DataSourceLabelsInFirstColumn := False;
  oChart.DataSourceLabelsInFirstRow := True;

  (* Rotate the X axis values *)
  oChart.Diagram.XAxis.TextRotation := 9000;// '90 degrés

  (* Set the character height of the labels *)
  oChart.Diagram.YAxis.CharHeight := 8;
  oChart.Diagram.XAxis.CharHeight := 8;

  (* Set The main title and color of the graph *)
  oChart.HasMainTitle := True;
  oChart.Title.String := 'VPC logged data visualization';
  oChart.Title.CharColor := RGB(200,0,0);

 DisconnectOpenOffice;
end;

Answer 1

I think you can do it by specifying more than 1 CellRangeAddress (note that you can give an array of CellRangeAddresses in addNewByName).

So my guess:

//labels for columns
oCellRangeAddress1 :=      oTheFile.Bridge_getStruct('com.sun.star.table.CellRangeAddress');
oCellRangeAddress1.Sheet       := 0; // First sheet of the file
oCellRangeAddress1.StartColumn := 1; 
oCellRangeAddress1.StartRow    := 6;
oCellRangeAddress1.EndColumn   := 1;
oCellRangeAddress1.EndRow      := 71;

//x-values
oCellRangeAddress2 :=      oTheFile.Bridge_getStruct('com.sun.star.table.CellRangeAddress');
oCellRangeAddress2.Sheet       := 0; // First sheet of the file
oCellRangeAddress2.StartColumn := 3; 
oCellRangeAddress2.StartRow    := 6;
oCellRangeAddress2.EndColumn   := 3;
oCellRangeAddress2.EndRow      := 71;

//first range of y-values
oCellRangeAddress3 :=      oTheFile.Bridge_getStruct('com.sun.star.table.CellRangeAddress');
oCellRangeAddress3.Sheet       := 0; // First sheet of the file
oCellRangeAddress3.StartColumn := 5; 
oCellRangeAddress3.StartRow    := 6;
oCellRangeAddress3.EndColumn   := 5;
oCellRangeAddress3.EndRow      := 71;

//second range of y-values
oCellRangeAddress3 :=      oTheFile.Bridge_getStruct('com.sun.star.table.CellRangeAddress');
oCellRangeAddress3.Sheet       := 0; // First sheet of the file
oCellRangeAddress3.StartColumn := 7; 
oCellRangeAddress3.StartRow    := 6;
oCellRangeAddress3.EndColumn   := 7;
oCellRangeAddress3.EndRow      := 71;

//Create the Chart
oCharts.addNewByName('MyGraph', oRectangle,
    VarArrayOf([
       oCellRangeAddress1, 
       oCellRangeAddress2, 
       oCellRangeAddress3,
       oCellRangeAddress4]),
    True, True);

Note that the columns are not adjacent anymore (1, 3, 5, 7).