wizards/source/scriptforge/python/scriptforge.py | 6
wizards/source/sfdocuments/SF_Calc.xba | 198 +++++++++++++++++++++++
2 files changed, 204 insertions(+)
New commits:
commit bf51fd3de339555567b76cbe9a2de54fdfd18bd3
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Mon Apr 18 18:11:00 2022 +0200
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Mon Apr 18 18:53:53 2022 +0200
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 <j...@ledure.be>
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
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
@@ -1286,6 +1286,204 @@ CatchDuplicate:
GoTo Finally
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
