Code: Select all
Module Module1
Public oSM As Object 'Root object for accessing OpenOffice from VB
Public oDesk As Object 'First objects from the API
Public arg(1)
Sub Main()
'Instantiate OOo : this line is mandatory with VB for OOo API
oSM = CreateObject("com.sun.star.ServiceManager")
'Create the first and most important service
oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
LoadChartDoc()
End Sub
Sub LoadChartDoc()
Dim oDoc As Object
Dim Charts As Object
Dim chart As Object
Dim rect As Object
Dim rangeAddress(0) As Object
Dim oSheet As Object
Dim oDiagram As Object
Dim oCoords As Object
Dim oCoord As Object
Dim oChartTypes As Object
Dim oChartType As Object
Dim oSeries As Object
arg(0) = MakePropertyValue("Hidden", False)
arg(1) = MakePropertyValue("MacroExecutionMode", 4)
'Open an existing doc (pay attention to the syntax for first argument)
oDoc = oDesk.loadComponentFromURL("file:///C:/......./StackedBarColors.ods", "_blank", 0, arg)
oSheet = oDoc.getSheets().getByIndex(0)
rect = oSM.Bridge_GetStruct("com.sun.star.awt.Rectangle")
rangeAddress(0) = oSM.Bridge_GetStruct("com.sun.star.table.CellRangeAddress")
Charts = oSheet.getCharts()
rect.x = 2200
rect.Y = 4000
rect.Width = 35000
rect.Height = 15000
rangeAddress(0).Sheet = 0
rangeAddress(0).StartColumn = 1
rangeAddress(0).StartRow = 3
rangeAddress(0).EndColumn = 13
rangeAddress(0).EndRow = 5
If Charts.HasByName("LineChart") Then
Charts.RemoveByName("LineChart")
End If
Call Charts.addNewByName("LineChart", rect, rangeAddress, True, True)
chart = Charts.getByName("LineChart").embeddedObject
'set as stackable bar
chart.Diagram = oSM.createInstance("com.sun.star.chart.StackableDiagram")
chart.Diagram.Stacked = True
chart.Diagram.Percent = False
'set data series in rows
chart.Diagram.DataRowSource = 0
oDiagram = chart.getFirstDiagram()
oCoords = oDiagram.getCoordinateSystems()
oCoord = oCoords(0)
oChartTypes = oCoord.getChartTypes()
oChartType = oChartTypes(0)
oSeries = oChartType.getDataSeries()
oSeries(0).Color = RGB(0, 128, 0)
oSeries(1).Color = RGB(0, 0, 255)
End Sub
Function MakePropertyValue(ByVal cName, ByVal uValue) As Object
Dim oPropertyValue As Object
oPropertyValue = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oPropertyValue.Name = cName
oPropertyValue.Value = uValue
MakePropertyValue = oPropertyValue
End Function
The program runs, loads the spreadsheet, creates the chart and inserts it, and changes the chart to stacked, but when it tries to access the dataseries, it gets to the line
Code: Select all
oCoords = oDiagram.getCoordinateSystems()
System.Runtime.InteropServices.SafeArrayTypeMismatchException was unhandled
Specified array was not of the expected type
I have searched and searched, and though there are many links to discussions of that particular error, I haven't found a solution that works in this case.
I did do the trick of adding references to the cli dlls from the SDK (which may also be extracted from the OOo installation CAB file), which allows variables to be declared as UNO objects. When trying that, I've just managed to get myself tied up in knots. For instance, if I declare oDiagram thus
Code: Select all
Dim oDiagram As unoidl.com.sun.star.chart2.XDiagram
Error 1 'getCoordinateSystems' is not a member of 'unoidl.com.sun.star.chart2.XDiagram'.
Which is true: getCoordinateSystems is a member of XCoordinateSystemContainer. However, the FirstDiagram (an XDiagram), is supposed to have an XCoordinateSystemContainer interface. There seem to be some casting and interface query problems here which I have not been able to overcome, though I have repeatedly tried.
I have though, using the SDK, been able to translate this into c++ where it does work. After the song and dance of connecting to the office and loading the spreadsheet, I do (oDoc is the XComponent returned by loadComponentFromURL).
Code: Select all
//query for a XSpreadsheetDocument interface
Reference< XSpreadsheetDocument > oSheetDoc (oDoc, UNO_QUERY);
//use it to get the XSpreadsheets interface
Reference< XSpreadsheets > oSheets = oSheetDoc->getSheets();
//use getByName to get a reference (type Any)
Any oSheet = oSheets->getByName( OUString::createFromAscii("Sheet1"));
Reference< XSpreadsheet > oSpSheet (oSheet, UNO_QUERY);
Sequence<CellRangeAddress> rangeAddress(1);
rangeAddress[0].Sheet = 0;
rangeAddress[0].StartColumn = 1;
rangeAddress[0].StartRow = 3;
rangeAddress[0].EndColumn = 13;
rangeAddress[0].EndRow = 5;
com::sun::star::awt::Rectangle rect;
rect.X = 2200;
rect.Y = 4000;
rect.Width = 35000;
rect.Height = 15000;
Reference<XTableChartsSupplier> chartsheet(oSheet, UNO_QUERY);
OUString chartName = OUString::createFromAscii("LineChart");
Reference<XTableCharts> oChartCollection = chartsheet->getCharts();
if (oChartCollection->hasByName(chartName))
oChartCollection->removeByName(chartName);
oChartCollection->addNewByName(chartName, rect, rangeAddress, 1, 1);
Reference<XTableChart> oTableChart(oChartCollection->getByName(chartName), UNO_QUERY);
Reference<XEmbeddedObjectSupplier> xEmbeddedObjectSupplier(oTableChart, UNO_QUERY);
Reference<XInterface> chart = xEmbeddedObjectSupplier->getEmbeddedObject();
Reference<com::sun::star::chart::XChartDocument> xChartDoc(chart, UNO_QUERY);
// Change the chart type to the specified type
Reference<XMultiServiceFactory> xMultiServiceFactory(xChartDoc, UNO_QUERY);
OUString chartType;
chartType = OUString::createFromAscii("com.sun.star.chart.BarDiagram");
Reference<XInterface> newChart = xMultiServiceFactory->createInstance(chartType);
Reference<com::sun::star::chart::XDiagram> xDiagram(newChart, UNO_QUERY);
xChartDoc->setDiagram(xDiagram);
Reference< XPropertySet > xPropertySet(xDiagram, UNO_QUERY);
xPropertySet->setPropertyValue(OUString::createFromAscii("DataRowSource"), (Any) (sal_Int8) 0);
xPropertySet->setPropertyValue(OUString::createFromAscii("Stacked"),(Any) sal_True);
xPropertySet->setPropertyValue(OUString::createFromAscii("Percent"),(Any) sal_False);
Reference< com::sun::star::chart2::XChartDocument > xChartDocument2(chart, UNO_QUERY);
Reference< com::sun::star::chart2::XDiagram > xFirstDiagram = xChartDocument2->getFirstDiagram();
Reference< XCoordinateSystemContainer > xCoordinateSystemContainer(xFirstDiagram, UNO_QUERY);
Sequence< Reference<XCoordinateSystem> > xCoordinateSystems = xCoordinateSystemContainer->getCoordinateSystems();
Reference<XCoordinateSystem> xCoordinateSystem = xCoordinateSystems[0];
Reference< XChartTypeContainer > xChartTypeContainer(xCoordinateSystem, UNO_QUERY);
Sequence< Reference<XChartType> > xChartTypes = xChartTypeContainer->getChartTypes();
Reference<XChartType> xChartType = xChartTypes[0];
Reference< XDataSeriesContainer > xDataSeriesContainer(xChartType, UNO_QUERY);
Sequence< Reference<XDataSeries> > xDataSeries = xDataSeriesContainer->getDataSeries();
long nSeries = rangeAddress[0].EndRow - rangeAddress[0].StartRow;
Sequence<Reference<XDataSeries>> xSeries(nSeries);
Sequence<Reference< XPropertySet >> xSeriesPropertySet(nSeries);
long i;
for(i = 0;i < nSeries; i++)
{
xSeries[i] = Reference<XDataSeries>(xDataSeries[i],UNO_QUERY);
xSeriesPropertySet[i] = Reference<XPropertySet>(xSeries[i],UNO_QUERY);
}
xSeriesPropertySet[0]->setPropertyValue(OUString::createFromAscii("Color"),(Any) 0x00ff00L);
xSeriesPropertySet[1]->setPropertyValue(OUString::createFromAscii("Color"),(Any) 0x0000ffL );