How to set Data Field name in Column Field

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
crsharat
Posts: 3
Joined: Sat Apr 22, 2017 11:39 am

How to set Data Field name in Column Field

Post by crsharat »

I search all over the forums and some how successfully created the pivot table, but the data representation I wanted is not upto my expectations.
The following is the code which I implemented.

Code: Select all

REM  *****  BASIC  *****

Sub Main
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
doc = ThisComponent
sheetName1 = "PivotTable1"
If doc.Sheets.hasByName(sheetName1) THEN
	doc.Sheets.removeByName(sheetName1)
END If
doc.Sheets().insertNewByName(sheetName1, 1)


datasheet = doc.Sheets.getByName("initialData")
sheet1 = doc.Sheets.getByName("PivotTable1")
oController = doc.CurrentController
oController.setActiveSheet(datasheet)

oSheetObj = oController.ActiveSheet
DataCellRange = osheetObj.getCellRangeByName("A1:E21")
RangeAddress = DataCellRange.RangeAddress
oController.setActiveSheet(sheet1)
oSheetObj = oController.ActiveSheet
Tables = oSheetObj.DataPilotTables()
Descriptor = Tables.createDataPilotDescriptor()
Descriptor.ShowFilterButton = False
Descriptor.DrillDownOnDoubleClick = True
Descriptor.setSourceRange(RangeAddress)
Fields = Descriptor.getDataPilotFields            

Field1 = Fields.getByIndex(0)
Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN

Field1 = Fields.getByIndex(1)
Field1.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW


Field3 = Fields.getByIndex(2)
Field3.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
Field3.Function = com.sun.star.sheet.GeneralFunction.SUM

Field4 = Fields.getByIndex(3)
Field4.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
Field4.Function = com.sun.star.sheet.GeneralFunction.SUM


Descriptor.RowGrand = "FALSE"
Cell = oSheetObj.getCellrangeByName("A1")
Tables.insertNewByName("NewDataPilot", Cell.CellAddress, Descriptor)


Url = ConvertToURL("/home/user/finalData.xlsx")
FileProperties(0).Name = "Overwrite"
FileProperties(0).Value = True
FileProperties(1).Name = "FilterName"
FileProperties(1).Value = "Calc Office Open XML"
doc.storeAsURL(Url, FileProperties1())
End Sub
Please check the screenshots link for the pivot table that I generated and which I actually wanted to generate
Expected : https://www.dropbox.com/s/58an5cvtptwvq ... t.png?dl=0
Generated : https://www.dropbox.com/s/knmza81w44ysv ... t.png?dl=0

Also the data was corrupted when I tried to open the file finalData.xlsx in MS Excel 2010.
Download the screenshots, to see the details
unspecified.png
unspecified.png (5.85 KiB) Viewed 2654 times
unspecified2.png
MS Excel remove the pivot cache so now the pivot table is not editable . I tried the filtername "Calc MS Excel 2007 XML" also but the result is same.

I'm just a newbie so I dont know how to fix this please let me know If anyone knows about it.

InitialData Link: https://www.dropbox.com/s/5clnazvbxflre ... a.csv?dl=0
FinalData Link: https://www.dropbox.com/s/nw7iddxbm3dxr ... .xlsx?dl=0

Thanks
Last edited by crsharat on Sat Apr 22, 2017 2:50 pm, edited 1 time in total.
LibreOffice 5.0.6.2 00m0(Build:2) Ubuntu 15.10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to set Data Field name in Column Field

Post by Villeroy »

[Tutorial] Introduction into object inspection with MRI
A recorded macro:

Code: Select all

Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oDataPilotTables As Variant
  Dim oObj2 As Variant
  Dim oDataFields As Variant
  Dim oObj3 As Variant
  Dim sName As String
  Dim oColumnFields As Variant
  Dim oObj4 As Variant
  Dim sName2 As String

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Pivot Table_Sheet1_1")
  oDataPilotTables = oObj1.getDataPilotTables()
  
  oObj2 = oDataPilotTables.getByIndex(0)
  oDataFields = oObj2.getDataFields()
  oObj3 = oDataFields.getByIndex(0)
  
  sName = oObj3.getName()
  oObj3.setName("Foo Bar")
  oColumnFields = oObj2.getColumnFields()
  
  oObj4 = oColumnFields.getByIndex(0)
  sName2 = oObj4.getName()
  oObj4.setName("Blah Blah")
  
End Sub
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
crsharat
Posts: 3
Joined: Sat Apr 22, 2017 11:39 am

Re: How to set Data Field name in Column Field

Post by crsharat »

Villeroy wrote:[Tutorial] Introduction into object inspection with MRI
A recorded macro:

Code: Select all

Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oDataPilotTables As Variant
  Dim oObj2 As Variant
  Dim oDataFields As Variant
  Dim oObj3 As Variant
  Dim sName As String
  Dim oColumnFields As Variant
  Dim oObj4 As Variant
  Dim sName2 As String

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Pivot Table_Sheet1_1")
  oDataPilotTables = oObj1.getDataPilotTables()
  
  oObj2 = oDataPilotTables.getByIndex(0)
  oDataFields = oObj2.getDataFields()
  oObj3 = oDataFields.getByIndex(0)
  
  sName = oObj3.getName()
  oObj3.setName("Foo Bar")
  oColumnFields = oObj2.getColumnFields()
  
  oObj4 = oColumnFields.getByIndex(0)
  sName2 = oObj4.getName()
  oObj4.setName("Blah Blah")
  
End Sub
Sorry this is not what I meant. Please check the screenshots
LibreOffice 5.0.6.2 00m0(Build:2) Ubuntu 15.10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to set Data Field name in Column Field

Post by Villeroy »

Code: Select all

Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oDataPilotTables As Variant
  Dim oObj2 As Variant
  Dim oDataLayoutField As Variant
  Dim nOrientation As Long

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Pivot Table_Sheet1_1")
  oDataPilotTables = oObj1.getDataPilotTables()
  
  oObj2 = oDataPilotTables.getByIndex(0)
  oDataLayoutField = oObj2.getDataLayoutField()
  nOrientation = oDataLayoutField.Orientation

'oDataLayoutField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW

oDataLayoutField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN
End Sub
Setting the Orientation from within MRI fails but the macro code works from the IDE. May be there is a bug in 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
crsharat
Posts: 3
Joined: Sat Apr 22, 2017 11:39 am

Re: How to set Data Field name in Column Field

Post by crsharat »

Villeroy wrote:

Code: Select all

Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oDataPilotTables As Variant
  Dim oObj2 As Variant
  Dim oDataLayoutField As Variant
  Dim nOrientation As Long

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Pivot Table_Sheet1_1")
  oDataPilotTables = oObj1.getDataPilotTables()
  
  oObj2 = oDataPilotTables.getByIndex(0)
  oDataLayoutField = oObj2.getDataLayoutField()
  nOrientation = oDataLayoutField.Orientation

'oDataLayoutField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW

oDataLayoutField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN
End Sub
Setting the Orientation from within MRI fails but the macro code works from the IDE. May be there is a bug in MRI.
You are awesome thanks for the solution. Anyway one problem is solved.

Now when we save the sheet as xlsx, it becomes corrupted in MS Excel 2010. Do you know how to solve that ?
LibreOffice 5.0.6.2 00m0(Build:2) Ubuntu 15.10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to set Data Field name in Column Field

Post by Villeroy »

LibreOffice is not a replacement for any other program. Do yourself a favour and use nothing but Microsoft products for Microsoft file formats, protocols, interfaces etc.
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
Post Reply