How to set Data Field name in Column Field

Creating a macro - Writing a Script - Using the API

How to set Data Field name in Column Field

Postby crsharat » Sat Apr 22, 2017 12:38 pm

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   Expand viewCollapse view
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 859 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
crsharat
 
Posts: 3
Joined: Sat Apr 22, 2017 11:39 am

Re: How to set Data Field name in Column Field

Postby Villeroy » Sat Apr 22, 2017 2:01 pm

[Tutorial] Introduction into object inspection with MRI
A recorded macro:
Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to set Data Field name in Column Field

Postby crsharat » Sat Apr 22, 2017 2:36 pm

Villeroy wrote:[Tutorial] Introduction into object inspection with MRI
A recorded macro:
Code: Select all   Expand viewCollapse view
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
crsharat
 
Posts: 3
Joined: Sat Apr 22, 2017 11:39 am

Re: How to set Data Field name in Column Field

Postby Villeroy » Sat Apr 22, 2017 3:03 pm

Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to set Data Field name in Column Field

Postby crsharat » Sat Apr 22, 2017 3:10 pm

Villeroy wrote:
Code: Select all   Expand viewCollapse view
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
crsharat
 
Posts: 3
Joined: Sat Apr 22, 2017 11:39 am

Re: How to set Data Field name in Column Field

Postby Villeroy » Sat Apr 22, 2017 3:20 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests