ScriptForge - (SF_Calc) new CreatePivotTable() method

Create a new pivot table with the properties defined by the arguments.
If a pivot table with the same name exists already in the
targeted sheet, it will be erased without warning.

Parameters:
  PivotTableName: The user-defined name of the new pivottable
  SourceRange: The range as a string containing the raw data.
    The first row of the range is presumed to contain
    the field names of the new pivot table
  TargetCell: the top left cell or the range as a string
  where to locate the pivot table.
    Only the top left cell of the range will be considered.
  DataFields: A single string or an array of
    field name + function to apply, formatted like:
      Array("FieldName[;Function]", ...)
    The allowed functions are: Sum, Count, Average, Max,
      Min, Product, CountNums, StDev, StDevP, Var, VarP and Median.
    The default function is:
      When the values are all numerical, Sum is used, otherwise Count.
  RowFields: A single string or an array of the field
    names heading the pivot table rows
  ColumnFields: A single string or an array of the field
    names heading the pivot table columns
  FilterButton: When True (default), display a "Filter"
    button above the pivot table
  RowTotals: When True (default), display a separate
    column for row totals
  ColumnTotals: When True (default), display a
    separate row for column totals

Returns:
  Return the range where the new pivot table is deployed.

The method may be used in Basic and Python user scripts.

Change-Id: I99df23e1b1b97b17a747ae15a079d7e2f5655b41
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/133131
Tested-by: Jean-Pierre Ledure <jp@ledure.be>
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <jp@ledure.be>

2 files changed, 204 insertions, 0 deletions

diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py
index 13a4a5e8a7aa..ce6bbae81691 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -2114,6 +2114,12 @@ class SFDocuments:
         def CreateChart(self, chartname, sheetname, range, columnheader = False, rowheader = False):
             return self.ExecMethod(self.vbMethod, 'CreateChart', chartname, sheetname, range, columnheader, rowheader)
 
+        def CreatePivotTable(self, pivottablename, sourcerange, targetcell, datafields = ScriptForge.cstSymEmpty,
+                             rowfields = ScriptForge.cstSymEmpty, columnfields = ScriptForge.cstSymEmpty,
+                             filterbutton = True, rowtotals = True, columntotals = True):
+            return self.ExecMethod(self.vbMethod, 'CreatePivotTable', pivottablename, sourcerange, targetcell,
+                                   datafields, rowfields, columnfields, filterbutton, rowtotals, columntotals)
+
         def DAvg(self, range):
             return self.ExecMethod(self.vbMethod, 'DAvg', range)
 
diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba
index af702126d692..bc17d4abe578 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -1287,6 +1287,204 @@ CatchDuplicate:
 End Function    '   SFDocuments.SF_Calc.CreateChart
 
 REM -----------------------------------------------------------------------------
+Public Function CreatePivotTable(Optional ByVal PivotTableName As Variant _
+									, Optional ByVal SourceRange As Variant _
+									, Optional ByVal TargetCell As Variant _
+									, Optional ByRef DataFields As Variant _
+									, Optional ByRef RowFields As Variant _
+									, Optional ByRef ColumnFields As Variant _
+									, Optional ByVal FilterButton As Variant _
+									, Optional ByVal RowTotals As Variant _
+									, Optional ByVal ColumnTotals As Variant _
+							) As String
+''' Create a new pivot table with the properties defined by the arguments.
+'''	If a pivot table with the same name exists already in the targeted sheet, it will be erased without warning.
+'''	Args:
+'''		PivotTableName: The user-defined name of the new pivottable
+'''		SourceRange: The range as a string containing the raw data.
+'''			The first row of the range is presumed to contain the field names of the new pivot table
+'''		TargetCell: the top left cell or the range as a string where to locate the pivot table.
+'''			Only the top left cell of the range will be considered.
+'''		DataFields: A single string or an array of field name + function to apply, formatted like:
+'''				Array("FieldName[;Function]", ...)
+'''			The allowed functions are: Sum, Count, Average, Max, Min, Product, CountNums, StDev, StDevP, Var, VarP and Median.
+'''			The default function is: When the values are all numerical, Sum is used, otherwise Count
+'''		RowFields: A single string or an array of the field names heading the pivot table rows
+'''		ColumnFields: A single string or an array of the field names heading the pivot table columns
+'''		FilterButton: When True (default), display a "Filter" button above the pivot table
+'''		RowTotals: When True (default), display a separate column for row totals
+'''		ColumnTotals: When True (default), display a separate row for column totals
+'''	Returns:
+'''		Return the range where the new pivot table is deployed.
+'''	Examples:
+'''		Dim vData As Variant, oDoc As Object, sTable As String, sPivot As String
+'''		vData = Array(Array("Item", "State", "Team", "2002", "2003", "2004"), _
+'''				Array("Books", "Michigan", "Jean", 14788, 30222, 23490), _
+'''				Array("Candy", "Michigan", "Jean", 26388, 15641, 32849), _
+'''				Array("Pens", "Michigan", "Jean", 16569, 32675, 25396), _
+'''				Array("Books", "Michigan", "Volker", 21961, 21242, 29009), _
+'''				Array("Candy", "Michigan", "Volker", 26142, 22407, 32841))
+'''		Set oDoc = ui.CreateDocument("Calc")
+'''		sTable = oDoc.SetArray("A1", vData)
+'''		sPivot = oDoc.CreatePivotTable("PT1", sTable, "H1", Array("2002", "2003;count", "2004;average"), "Item", Array("State", "Team"), False)
+
+Dim sPivotTable As String				'	Return value
+Dim vData As Variant					'	Alias of DataFields
+Dim vRows As Variant					'	Alias of RowFields
+Dim vColumns As Variant					'	Alias of ColumnFields
+Dim oSourceAddress As Object			'	Source as an _Address
+Dim oTargetAddress As Object			'	Target as an _Address
+Dim vHeaders As Variant					'	Array of header fields in the source range
+Dim oPivotTables As Object				'	com.sun.star.sheet.XDataPilotTables
+Dim oDescriptor As Object				'	com.sun.star.sheet.DataPilotDescriptor
+Dim oFields As Object					'	ScDataPilotFieldsObj - Collection of fields
+Dim oField As Object					'	ScDataPilotFieldsObj - A single field
+Dim sField As String					'	A single field name
+Dim sData As String						'	A single data field name + function
+Dim vDataField As Variant				'	A single vData element, split on semicolon
+Dim sFunction As String					'	Function to apply on a data field (string)
+Dim iFunction As Integer				'	Equivalent of sFunction as com.sun.star.sheet.GeneralFunction2 constant
+Dim oOutputRange As Object				'	com.sun.star.table.CellRangeAddress
+Dim i As Integer
+
+Const cstThisSub = "SFDocuments.Calc.CreatePivotTable"
+Const cstSubArgs = "PivotTableName, SourceRange, TargetCell, DataFields, [RowFields], [ColumnFields]" _
+					& ", [FilterButton=True], [RowTotals=True], [ColumnTotals=True]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	sPivotTable = ""
+
+Check:
+	If IsMissing(RowFields) Or IsEmpty(RowFields) Then RowFields = Array()
+	If IsMissing(ColumnFields) Or IsEmpty(ColumnFields) Then ColumnFields = Array()
+	If IsMissing(FilterButton) Or IsEmpty(FilterButton) Then FilterButton = True
+	If IsMissing(RowTotals) Or IsEmpty(RowTotals) Then RowTotals = True
+	If IsMissing(ColumnTotals) Or IsEmpty(ColumnTotals) Then ColumnTotals = True
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not _IsStillAlive(True) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(PivotTableName, "PivotTableName", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(SourceRange, "SourceRange", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(TargetCell, "TargetCell", V_STRING) Then GoTo Finally
+		If IsArray(DataFields) Then
+			If Not ScriptForge.SF_Utils._ValidateArray(DataFields, "DataFields", 1, V_STRING, True) Then GoTo Finally
+		Else
+			If Not ScriptForge.SF_Utils._Validate(DataFields, "DataFields", V_STRING) Then GoTo Finally
+		End If
+		If IsArray(RowFields) Then
+			If Not ScriptForge.SF_Utils._ValidateArray(RowFields, "RowFields", 1, V_STRING, True) Then GoTo Finally
+		Else
+			If Not ScriptForge.SF_Utils._Validate(RowFields, "RowFields", V_STRING) Then GoTo Finally
+		End If
+		If IsArray(ColumnFields) Then
+			If Not ScriptForge.SF_Utils._ValidateArray(ColumnFields, "ColumnFields", 1, V_STRING, True) Then GoTo Finally
+		Else
+			If Not ScriptForge.SF_Utils._Validate(ColumnFields, "ColumnFields", V_STRING) Then GoTo Finally
+		End If
+		If Not ScriptForge.SF_Utils._Validate(FilterButton, "FilterButton", ScriptForge.V_BOOLEAN) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(RowTotals, "RowTotals", ScriptForge.V_BOOLEAN) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(ColumnTotals, "ColumnTotals", ScriptForge.V_BOOLEAN) Then GoTo Finally
+	End If
+	'	Next statements must be outside previous If-block to force their execution even in case of internal call
+	If IsArray(DataFields) Then vData = DataFields Else vData = Array(DataFields)
+	If IsArray(RowFields) Then vRows = RowFields Else vRows = Array(RowFields)
+	If IsArray(ColumnFields) Then vColumns = ColumnFields Else vColumns = Array(ColumnFields)
+
+Try:
+
+	Set oSourceAddress = _ParseAddress(SourceRange)
+	vHeaders = GetValue(Offset(SourceRange, 0, 0, 1))		'	Content of the first row of the source
+	Set oTargetAddress = _Offset(TargetCell, 0, 0, 1, 1)	'	Retain the top left cell only
+	Set oPivotTables = oTargetAddress.XSpreadsheet.getDataPilotTables()
+
+	'	Initialize new pivot table
+	Set oDescriptor = oPivotTables.createDataPilotDescriptor()
+	oDescriptor.setSourceRange(oSourceAddress.XCellRange.RangeAddress)
+	Set oFields = oDescriptor.getDataPilotFields()
+
+	'	Set row fields
+	For i = 0 To UBound(vRows)
+		sField = vRows(i)
+		If Len(sField) > 0 Then
+			If Not ScriptForge.SF_Utils._Validate(sField, "RowFields", V_STRING, vHeaders) Then GoTo Finally
+			Set oField = oFields.getByName(sField)
+			oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
+		End If
+	Next i
+
+	'	Set column fields
+	For i = 0 To UBound(vColumns)
+		sField = vColumns(i)
+		If Len(sField) > 0 Then
+			If Not ScriptForge.SF_Utils._Validate(sField, "ColumnFields", V_STRING, vHeaders) Then GoTo Finally
+			Set oField = oFields.getByName(sField)
+			oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN
+		End If
+	Next i
+
+	'	Set data fields
+	For i = 0 To UBound(vData)
+		sData = vData(i)
+		'	Minimal parsing
+		If Right(sData, 1) = ";" Then sData = Left(sData, Len(sData) - 1)
+		vDataField = Split(sData, ";")
+		sField = vDataField(0)
+		If UBound(vDataField) > 0 Then sFunction = vDataField(1) Else sFunction = ""
+		'	Define field properties
+		If Len(sField) > 0 Then
+			If Not ScriptForge.SF_Utils._Validate(sField, "DataFields", V_STRING, vHeaders) Then GoTo Finally
+			Set oField = oFields.getByName(sField)
+			oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA
+			'	Associate the correct function
+			With com.sun.star.sheet.GeneralFunction2
+				Select Case UCase(sFunction)
+					Case ""			:	iFunction = .AUTO
+					Case "SUM"		:	iFunction = .SUM
+					Case "COUNT"	:	iFunction = .COUNT
+					Case "AVERAGE"	:	iFunction = .AVERAGE
+					Case "MAX"		:	iFunction = .MAX
+					Case "MIN"		:	iFunction = .MIN
+					Case "PRODUCT"	:	iFunction = .PRODUCT
+					Case "COUNTNUMS":	iFunction = .COUNTNUMS
+					Case "STDEV"	:	iFunction = .STDEV
+					Case "STDEVP"	:	iFunction = .STDEVP
+					Case "VAR"		:	iFunction = .VAR
+					Case "VARP"		:	iFunction = .VARP
+					Case "MEDIAN"	:	iFunction = .MEDIAN
+					Case Else
+						If Not ScriptForge.SF_Utils._Validate(sFunction, "DataFields/Function", V_STRING _
+								, Array("Sum", "Count", "Average", "Max", "Min", "Product", "CountNums" _
+										, "StDev", "StDevP", "Var", "VarP", "Median") _
+								) Then GoTo Finally
+				End Select
+			End With
+			oField.Function2 = iFunction
+		End If
+	Next i
+
+	'	Remove any pivot table with same name
+	If oPivotTables.hasByName(PivotTableName) Then oPivotTables.removeByName(PivotTableName)
+
+	'	Finalize the new pivot table
+	oDescriptor.ShowFilterButton = FilterButton
+	oDescriptor.RowGrand = RowTotals
+	oDescriptor.ColumnGrand = ColumnTotals
+	oPivotTables.insertNewByName(PivotTableName, oTargetAddress.XCellRange.getCellByPosition(0, 0).CellAddress, oDescriptor)
+
+	'	Determine the range of the new pivot table
+	Set oOutputRange = oPivotTables.getByName(PivotTableName).OutputRange
+	With oOutputRange
+		sPivotTable = _Component.getSheets().getCellRangeByPosition(.StartColumn, .StartRow, .EndColumn, .EndRow, .Sheet).AbsoluteName
+	End With
+
+Finally:
+	CreatePivotTable = sPivotTable
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	GoTo Finally
+End Function    '   SFDocuments.SF_Calc.CreatePivotTable
+
+REM -----------------------------------------------------------------------------
 Public Function DAvg(Optional ByVal Range As Variant) As Double
 '''	Get the average of the numeric values stored in the given range
 '''	Args: