Copy Chart from Calc to writer document

[estatistics]

From here https://ask.libreoffice.org/t/using-lil ... nt/67507/8

I have given the following Macro starbase code (see attachment)
It tries to copy a chart from Libre Calc to Libre writer.
However, it says that no chart exists with such name.
(see my other post - cviewtopic.php?f=9&t=106011 )

Code: Select all

Sub CopyChartToWriter 'copy the chart from the Calc sheet to the Writer document
	dim oDoc as object, oSheet as object, vChart as variant, data as object
	oDoc=ThisComponent
	oSheet=oDoc.Sheets.getByName("Graph_stat") 'sheet with chart
	vChart=getChart("stat1", oSheet) 'the chart named for example by right-mouse and Name, or from menu Format/ Name
	print  vChart
	if NOT isObject(vChart) then 'the chart doesn't exists
		msgbox vChart & " - doesn't exists!" 
		exit sub
	end if
	oDoc.CurrentController.ActiveSheet=oSheet
	oDoc.CurrentController.Select(vChart)
	data=oDoc.CurrentController.getTransferable() 'LibreOffice internal Ctrl+C

	rem Writer document
	dim oDocWriter as object, sUrl$, oVCur as object
	sUrl="private:factory/swriter" 'new writer document

	'sUrl=ConvertToUrl("d:/documents/example.odt") 'or your writer file
	oDocWriter=StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, array()) 'open Writer document

	rem paste chart to the end of the visible cursor
	oVCur=oDocWriter.CurrentController.getViewCursor 'visible cursor
	oVCur.collapseToEnd
	oDocWriter.CurrentController.Select(oVCur) 'select visible cursor
	oDocWriter.CurrentController.insertTransferable(data) 'Libre internal Ctrl+V
End Sub


Function getChart(s$, oSheet as object) as variant 'returns the chart as object (if chart exists); or returns the chart name as string
	dim i&, o as object
	for i=0 to oSheet.DrawPage.Count-1
		o=oSheet.DrawPage.getByIndex(i)
		'xray oSheet.DrawPage
		if o.PersistName=s then 'the needy property from the xray on previous line -> PersistName is setted from the function CreateCalcWithSimpleChart
			getChart=o 'return the chart as object
			exit function
		end if
	next i
	getChart=s 'return the name of the chart as string
End Function

[Bidouille]

Why this thread has not been posted in Macro?

Use Xray for your object.
PersistName returns "Object 1".
It's Name property who retruns "stat1"

[estatistics]

Why this thread has not been posted in Macro?

Use Xray for your object.
PersistName returns "Object 1".
It's Name property who retruns "stat1"

a) What is your code to check PersistName ?
b) How you set the PersistName as well Description and Title of chart ?

Thanks!

[estatistics]

I was able to access Ttitle and Description by using:

’ Doc.getDrawPages.getByIndex(0).getByIndex(0).Title
’ Doc.getDrawPages.getByIndex(0).getByIndex(0).Description

However, I thought that these attributes will be UPON the object and NOT inside the Object, in order to be able to call the object by its name and not by its numbered position.

However, I still struggling to find a way to get the object by its name than its numbered position.

I found how I can access PersistName.
xray xray Doc.getSheets().getbyName( "Graphs_stat" ).getDrawPage().getByIndex(0).PersistName

[Villeroy]

Basic code recorded by MRI extension:

Code: Select all

Sub Snippet
  Dim oDrawPages As Variant
  Dim oObj1 As Variant
  Dim oObj2 As Variant
  Dim sName As String
  Dim sName2 As String
  Dim sPersistName As String

  oDrawPages = ThisComponent.getDrawPages()
  oObj1 = oDrawPages.getByIndex(0)
  oObj2 = oObj1.getByIndex(0)
  
  sName = oObj2.getName()
  oObj2.setName("BLAH")
  sName2 = oObj2.getName()
  
  sPersistName = oObj2.PersistName
End Sub

[Villeroy]

A sheet has a Charts collection where the chart is accessible by its PersistName "Object 1".

Code: Select all

Sub Snippet
  Dim oDrawPages As Variant
  Dim oObj1 As Variant
  Dim oSheets As Variant
  Dim oObj2 As Variant
  Dim oCharts As Variant
  Dim oObj3 As Variant
  Dim sName As String

  oDrawPages = ThisComponent.getDrawPages()
  oObj1 = oDrawPages.getByIndex(0)
  oSheets = ThisComponent.getSheets()
  
  oObj2 = oSheets.getByName("Sheet1")
  oCharts = oObj2.getCharts()
  oObj3 = oCharts.getByName("Object 1")
  
  sName = oObj3.getName()
End Sub

Recorded by MRI

[estatistics]

The below code is working - It can copy charts from calc sheet to Writer document.

However:
1) This is done by getByIndex(0) NOT by name of the object
2) As I understood well, Draw objects are accessed after you call getDrawPages.getByIndex(0) and not before.
3) So Drawobjects can be called by any (unique) name inside a Sheet?
4) For Example:

a) Charts (which store Charts in a sheet) provide a list with object names as string e.g .Charts.getByName("xx") or Charts.getByIndex(0)
b) I havent found something similar with DrawPages.
c) The weird thing IS that Draw Objects DO have names e.g ."Object 1" but initially, DrawPages nowhere stores them as an index string. This is accessed after specifying index of Chart eg. getByIndex()

What I would like to do:

I would like to itirate through name of objects (charts) BEFORE Index of Drawpages.
Why? Because it is more "safe" solution.
If Calc document have other draw objects, this may create confusion. I would like all calc charts to be named with specific names.

So, Is there a solution ?
How can i be assured that only named-specific charts will be appended to the Writer document?

I tried to use charts.getByName("blablabla") but Doc.CurrentController.select refuse to select it.

Is there any other way to copy paste a chart object as Chart in libre writer from Calc except Drawpages?
Anything else?

Code: Select all

' XrayTool that helps to investigate VBA objects. 
' Found here: https://berma.pagesperso-orange.fr/index2.html 
' Download word document and click the button inside to install 
' it.

Sub LoadingLibraries
 BasicLibraries.LoadLibrary("XrayTool")
End Sub


Sub CopyChartstoWriter
'Definitions of variables


Dim oSheet    'Sheet containing the chart

' Doc.getDrawPages.getByIndex(0).getByIndex(0).Title
' Doc.getDrawPages.getByIndex(0).getByIndex(0).Description
' oSheet.getDrawPage().getByIndex(0).PersistName
' oCharts     = oSheet.getCharts() | oChart = oCharts.getByName("Object 1").EmbeddedObject  
  
Doc               = ThisComponent
oSheets           = ThisComponent.getSheets()
oSheet            = oSheets.getbyName( "Graphs_stat" ) 
oDrawPage         = oSheet.getDrawPage()
oZero             = oDrawPage.getByIndex(0)
 
' oZero.PersistName = "Object 1"
Component         = oZero.EmbeddedObject.Component


' Check if oZero is a chart 
Component.supportsService("com.sun.star.chart.ChartDocument") 
Doc.CurrentController.select(oZero)      'Select the chart
data  =  Doc.CurrentController.getTransferable() 'LibreOffice internal Ctrl+C



'Writer document
Dim oDocWriter as object
Dim sUrl$ 
Dim oVCur as object

	sUrl="private:factory/swriter" 'new writer document
	'sUrl=ConvertToUrl("d:/documents/example.odt") 'or your writer file
	oDocWriter = StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, array()) 'open Writer document

	'paste chart to the end of the visible cursor
	oVCur      = oDocWriter.CurrentController.getViewCursor  'visible cursor
	oVCur.collapseToEnd
	oDocWriter.CurrentController.Select(oVCur)               'select visible cursor
	oDocWriter.CurrentController.insertTransferable(data)    'Libre internal Ctrl+V

end sub 

[estatistics]

I am thinking to store all drawobjects of a page somewhere eg. in an array, and then do a check if their names are same as expected.

But here is the other problem…
Macros cant change the name of the chart in real!
They remain empty!
So I am double stuck now!


Note this:
xray ThisComponent.getSheets().getbyName( "Graphs_stat" ).getDrawPage().getByIndex(0).Title
xray ThisComponent.getDrawPages.getByIndex(0).getByIndex(0).PersistName

[estatistics]

Finally, I did it.
I could set Title and Description Title, but I could not set the Name of the Chart (right click on chart --> name -> empty string).
Note that I try to set the name of chart using

Code: Select all

		oCharts.addNewByName(sName, oRect, Array(oAddress), True, True) 

Code: Select all

         
    oSheets  = ThisComponent.getSheets()
	For i = 0 to 1 step 1   
		genChartName = "stat_" & i
          	DrawObject  = oSheet.getDrawPage().getByIndex(i)
		DrawObject.Title       = genChartName
    DrawObject.Description        = genChartName
        
	next i

[Villeroy]

oDoc.Sheets.getByIndex(0).DrawPage and oDoc.DrawPages.getByIndex(0) return the same object. Only the sheets can be accessed by name.
I think property "PersistName" of a chart is meant to be read-only but someone implemented it as a read-write property. Looks like a bug to me. The names are always "Object 1", "Object 2" etc. I'd rather use a professional statistics application or Python library instead of programming Calc charts. This API is an ugly nightmare.

https://bugs.documentfoundation.org/sho ... ?id=144219

[estatistics]

@Villeroy You may forgot to upload attachment in bugzila. I could not find it.

[Villeroy]

Sorry, I was distracted by a phone call.
Done.