Page 1 of 1

[Solved] Libreoffice Calc macro DataPilotFieldLayoutInfo

PostPosted: Thu Sep 14, 2017 6:50 pm
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/common/ref/com/sun/star/sheet/DataPilotFieldLayoutInfo.html

Can someone help me ?

Thank you

Code: Select all   Expand viewCollapse view
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

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

PostPosted: Thu Sep 14, 2017 9:57 pm
by Villeroy
Do you use any object inspector? XRay or MRI?

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

PostPosted: Sat Sep 16, 2017 7:35 pm
by ermejooo
Villeroy wrote:Do you use any object inspector? XRay or MRI?

I don't use any add-on

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

PostPosted: Sat Sep 16, 2017 7:52 pm
by Villeroy
You should.

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

PostPosted: Mon Sep 18, 2017 6:13 pm
by ermejooo
Thanks for the tip, with XRay inspection help, i solved my problem adding this code:

Code: Select all   Expand viewCollapse view
   Dim dpfli as new com.sun.star.sheet.DataPilotFieldLayoutInfo
   with dpfli
      .LayoutMode = 1 'OUTLINE_SUBTOTALS_TOP
      .AddEmptyLines = true
   end with
   ofield1.LayoutInfo = dpfli