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
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 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