[Solved] Libreoffice Calc macro DataPilotFieldLayoutInfo

Creating a macro - Writing a Script - Using the API

[Solved] Libreoffice Calc macro DataPilotFieldLayoutInfo

Postby ermejooo » Thu Sep 14, 2017 6:50 pm

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
Attachments
testPivot.ods
(15.02 KiB) Downloaded 64 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
ermejooo
 
Posts: 3
Joined: Wed Sep 13, 2017 4:59 pm

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Postby Villeroy » Thu Sep 14, 2017 9:57 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Postby ermejooo » Sat Sep 16, 2017 7:35 pm

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
ermejooo
 
Posts: 3
Joined: Wed Sep 13, 2017 4:59 pm

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Postby Villeroy » Sat Sep 16, 2017 7:52 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Libreoffice calc macro DataPilotFieldLayoutInfo

Postby ermejooo » Mon Sep 18, 2017 6:13 pm

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
Libreoffice 5.3.4.2 on Windows 10
ermejooo
 
Posts: 3
Joined: Wed Sep 13, 2017 4:59 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests