[Solved] Create pivot table with Basic macro

Creating a macro - Writing a Script - Using the API

[Solved] Create pivot table with Basic macro

Postby bdonauba » Thu Jul 27, 2017 10:53 am

Hello,

I have the need to create a pivot table with a macro. I'm quite far. My test macro creates the pivot table with filter and subtotals and so on. But the layout is not as expected. I can adjust it with the data pilot layout dialogue but it is the aim to automate the task. It has something to do with the virtual "data" field (that's the name I gave it). It is none of my columns.

If I drag it in the layout dialogue to "column fields" I get a horizontal orientation of my fields. If drag it in "row fields" I get an vertical alignment.

My questions are: How can I reference this "data" tag from my basic macro? How can I move it between "row fields" and "vertical fields" from my basic macro?

Here is my test macro. Perhaps it is useful for others trying to create pivot table via API:

Code: Select all   Expand viewCollapse view
Sub makePivot

dim doc
doc = thisComponent
dim sheets
sheets = doc.Sheets

dim pivotSheetNameOverview as string
pivotSheetNameOverview="Ueberblick"

dim oController
oController = ThisComponent.CurrentController
dim oSheetObj
'oSheetObj = oController.ActiveSheet
oSheetObj = Sheets.getByName("Unvollständigmeldungen")


dim DataCellRange
DataCellRange = oSheetObj.getCellRangeByName("Unvollständigmeldungen.A:Unvollständigmeldungen.M")
dim RangeAddress
RangeAddress = DataCellRange.RangeAddress
dim Tables
Tables = oSheetObj.DataPilotTables()   'Tables has all the DataPilot Tables in the Active Sheet

'This part of the code just removes the table if it already exists. Prevents error from running the code several times
If Tables.hasByName(pivotSheetNameOverview) THEN
   Tables.removeByName(pivotSheetNameOverview)
End If



dim Descriptor
Descriptor = Tables.createDataPilotDescriptor()      'Descriptor contains the description of a DataPilot Table
Descriptor.setSourceRange(RangeAddress)     'RangeAddress is defined above to cover A1:C10       
Descriptor.ShowFilterButton = True                   'Don't show the Filter Button
Descriptor.IgnoreEmptyRows = True
Descriptor.RowGrand = true
Descriptor.ColumnGrand = false
Descriptor.DrillDownOnDoubleClick = true
'Descriptor.GrandTotalName="myName"


dim fields
Fields = Descriptor.getDataPilotFields()         


dim fieldVerursacher
fieldVerursacher = Fields.getByIndex(3)   'The first column of the data range has index 0
fieldVerursacher.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW                                   
Dim generalFunctionSeqAuto(0)
generalFunctionSeqAuto(0)=com.sun.star.sheet.GeneralFunction.AUTO
fieldVerursacher.Subtotals = generalFunctionSeqAuto



dim fieldGruppe
fieldVerursacher = Fields.getByIndex(4)   'The first column of the data range has index 0
fieldVerursacher.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW                                   

'dim fieldDaten
'fieldVerursacher = Fields.getByIndex(4)   'The first column of the data range has index 0
'fieldVerursacher.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW                                   


dim fieldABNummer
fieldABNummer = Fields.getByIndex(0)
fieldABNummer.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
fieldABNummer.Function = com.sun.star.sheet.GeneralFunction.COUNT
fieldABNummer.setName("gesamt")

dim fieldAktiv
fieldAktiv = Fields.getByIndex(12)
fieldAktiv.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
'fieldAktiv.UsedHierarchy=0
fieldAktiv.Function = com.sun.star.sheet.GeneralFunction.SUM
fieldAktiv.setName("aktiv")


if(Sheets.hasByName(pivotSheetNameOverview)) then
  sheets.removeByName(pivotSheetNameOverview)
end if

dim sheet
sheets.insertNewByName(pivotSheetNameOverview, sheets.Count)
Sheet = Sheets.getByName(pivotSheetNameOverview)

dim cell
Cell = sheet.getCellrangeByName("A1")
Tables.insertNewByName(pivotSheetNameOverview, Cell.CellAddress, Descriptor)

'dim names
'names=Sheets.getByName("Unvollständigmeldungen").DataPilotTables().getElementNames()
'names=Sheets.getByName("Ueberblick").DataPilotTables().getElementNames()

dim hasElem
hasElem=Tables.hasElements()

wait 2000

Dim table
table=Sheets.getByName("Ueberblick").DataPilotTables().getByName(pivotSheetNameOverview)

dim filterDescription
filterDescription = table.getFilterDescriptor()
Dim filterFields(1) as New com.sun.star.sheet.TableFilterField

dim filterField

filterField=filterFields(0)
filterFields(0).Field = 3
filterFields(0).Operator = com.sun.star.sheet.FilterOperator.NOT_EMPTY
'filterFields(0).IsNumeric = False
'filterFields(0).NumericValue = 2
'filterFields(0).StringValue = ""

filterFields(1).Field = 3
filterFields(1).Operator = com.sun.star.sheet.FilterOperator.NOT_EQUAL
filterFields(1).IsNumeric = False
'filterFields(1).NumericValue = 2
filterFields(1).StringValue = "keiner"


filterDescription.FilterFields = FilterFields
wait 2000

setColWidth(0, 6, sheet)
setColWidth(1, 6, sheet)
setColWidth(2, 3, sheet)

End Sub
Last edited by Hagar Delest on Thu Jul 27, 2017 11:07 pm, edited 1 time in total.
Reason: tagged [Solved].
bdonauba
 
Posts: 6
Joined: Fri Jan 18, 2008 11:36 am

Re: create pivot table with basic macro

Postby gerard24 » Thu Jul 27, 2017 11:47 am

Try :

Code: Select all   Expand viewCollapse view
oField = oTableDescr.getDataPilotFields().getByName("Data")
oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN

just before or after the "wait 2000"
LibreOffice 5.2.6 on Windows Vista
gerard24
Volunteer
 
Posts: 913
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Create pivot table with Basic macro

Postby bdonauba » Thu Jul 27, 2017 1:17 pm

It worked.
Thank you for your help!
bdonauba
 
Posts: 6
Joined: Fri Jan 18, 2008 11:36 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 10 guests