[Solved] Libreoffice Calc macro DataPilotFieldLayoutInfo

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ermejooo
Posts: 3
Joined: Wed Sep 13, 2017 4:59 pm

[Solved] Libreoffice Calc macro DataPilotFieldLayoutInfo

Post by ermejooo »

Hello everyone,
i wrote a macro in oooBasic to create a simple pivot, but i'am not able to put subtotals in top of each item and put the empty row under each subtotal.
(manually right click on a pivot, edit layout, doubleclick on the first row field, Option, layouts TOP and check "Empty line after each item")

I would like to set in the first field the LayoutMode to "OUTLINE_SUBTOTALS_TOP" and set the "AddEmptyLines", but i'm stuck here: http://www.openoffice.org/api/docs/comm ... tInfo.html

Can someone help me ?

Thank you

Code: Select all

Sub Main

	'delete previus pivot
	Tables = ThisComponent.Sheets(0).DataPilotTables() 
	PilotEnumeration = Tables.createEnumeration
	While PilotEnumeration.hasMoreElements()
	   Tables.removeByName(PilotEnumeration.nextElement.getName)
	Wend
	
	oDataP = ThisComponent.Sheets(0).getDataPilotTables().createDataPilotDescriptor()  
	
	Dim aSourceAddress As New com.sun.star.table.CellRangeAddress 
	aSourceAddress.Sheet = 0
	aSourceAddress.StartColumn = 0 
	aSourceAddress.StartRow = 0 
	aSourceAddress.EndColumn = 2
	aSourceAddress.EndRow = 11  
	oDataP.setSourceRange(aSourceAddress) 
	
	oFields = oDataP.getDataPilotFields()
	
	oField1 = oFields.getbyindex(0) 
	oField1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW 

	'enable subtotals
	Dim gFSeqAuto(0) 
	gFSeqAuto(0)=com.sun.star.sheet.GeneralFunction.AUTO
	oField1.Subtotals = gFSeqAuto

	oField2 = oFields.getbyindex(1) 
	oField2.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW 
	
	oField3 = oFields.getbyindex(2)
	oField3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA 
	oField3.Function = com.sun.star.sheet.GeneralFunction.SUM 

	Dim aDestAddress As New com.sun.star.table.CellAddress    
	aDestAddress.Sheet = 0
	aDestAddress.Column =  0
	aDestAddress.Row = 15 
	
	ThisComponent.Sheets(0).DataPilotTables.insertNewByName("DataPilot", aDestAddress, oDataP)

End Sub
Attachments
testPivot.ods
(15.02 KiB) Downloaded 143 times
Last edited by Hagar Delest on Mon Sep 18, 2017 10:32 pm, edited 1 time in total.
Reason: tagged [Solved].
Libreoffice 5.3.4.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Post by Villeroy »

Do you use any object inspector? XRay or MRI?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ermejooo
Posts: 3
Joined: Wed Sep 13, 2017 4:59 pm

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Post by ermejooo »

Villeroy wrote:Do you use any object inspector? XRay or MRI?
I don't use any add-on
Libreoffice 5.3.4.2 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Post by Villeroy »

You should.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ermejooo
Posts: 3
Joined: Wed Sep 13, 2017 4:59 pm

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Post by ermejooo »

Thanks for the tip, with XRay inspection help, i solved my problem adding this code:

Code: Select all

	Dim dpfli as new com.sun.star.sheet.DataPilotFieldLayoutInfo
	with dpfli
		.LayoutMode = 1 'OUTLINE_SUBTOTALS_TOP
		.AddEmptyLines = true
	end with
	ofield1.LayoutInfo = dpfli
Libreoffice 5.3.4.2 on Windows 10
Post Reply