Description |
Provides useful functions for creating and working with pivot tables in MS Excel workbooks. Please note these activities are not mandatory to be used within Excel application scope. The Excel sheet gets attached to the UiPath with the workbook name provided as input. |
Prerequisites |
NA |
Execution |
Kindly import the NuGet package to use the activities |
Inputs |
CreatePivotTable
WorksheetName (string) – Optional. Default to be the active Worksheet
WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.
SourceRange (string) – Required. Like: A1:R100
DestinationWorksheet (string) – Optional. If this argument is omitted, it will be default to be the active worksheet. If the destination worksheet doesnot exist, it will create one
DestinationRange (string) – Optional. If this argument is omitted, it will put the pivot table in A1 of a new created worksheet and return the name of that worksheet. If it's in the current worksheet, just give the address of the start position like: A100. If you want to create a new worksheet to put the pivot table, give an address like this: "NewWorksheetName!A100"
PivotTableName (string) – Required. The name of the newly created pivot table
MultipleRange (bool) – True if multiple ranges are involved
PivotTableFilterbyMultiValues
WorksheetName (string) – Optional. Default to be the active Worksheet WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.
PivotField (string) – Required. Filter by which field Values (string) – Required. The value could be seperated by comma
MoveToFilter (bool) – Optional. Default to be false which means not to move the field to filter Show (bool) – Optional. Show or hide these values. Default to be shown.
PivotTableUnfilter
WorksheetName (string) – Optional. Default to be the active Worksheet
WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.
PivotTableName (string) – Optional. Default to be the first pivot table in the worksheet
PivotFields (string) – Optional. Unfilter certain fields, like {field1}{field5}{field19}. Default to be clear all filters.
PivotTableChangeDataSource
WorksheetName (string) – Optional. Default to be the active Worksheet
WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.
SourceWorksheetName (string) – Optional. where the Pivot table's data source belongs to. Default to be the original source's worksheet
SourceNewRange (string) – Required.
PivotTableAddorResetRows
WorksheetName (string) – Optional. Default to be the active Worksheet WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.
PivotTableName (string) – Optional. The name of the newly created pivot table. Default to be the first Pivot Table in the worksheet
FieldsName (string) – Required. Like: {Field1}{Field4}{Field100}
AddOrReset (bool) – Optional. Default to be "Reset”, Which is false
PivotTableAddorResetColumns
WorksheetName (string) – Optional. Default to be the active Worksheet
WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.
PivotTableName (string) – Optional. The name of the newly created pivot table. Default to be the first Pivot Table in the worksheet
FieldsName (string) – Required. Like: {Field1}{Field4}{Field100}
AddOrReset (bool) – Optional. Default to be "Reset", Which is false
PivotTableAddorResetValues
WorksheetName (string) – Optional. Default to be the active Worksheet
WorkbookName (string) – Optional. Default to be the active Workbook. Use *full path* and filename to workbook if using multiple instances of Excel.
PivotTableName (string) – Optional. The name of the newly created pivot table. Default to be the first Pivot Table in the worksheet Caption (string) – Optional. Create the name of the value, like "Sum of terminal". Default caption FunctionIndex (int) – Optional. The function performed in the added data field. Default to function
Position (int) – Optional. The Position of the Field. Default to append
FieldsName (string) – Required. Like: {Field1}{Field4}{Field100}
AddOrReset (bool) – Optional. Default to be "Reset", Which is false
OpenWorkbookByPassword
WorkbookFullPath (string) - Required.Full Path of Workbook
Password (string) - Optional. If no password, just leave it empty.
MaxTimeForWaiting (int) - Optional. Default to be 120s.
CloseAllInstances
Nil
|
Outputs |
CreatePivotTable Nil
PivotTableFilterbyMultiValues Nil
PivotTableUnfilter Nil
PivotTableChangeDataSource Nil
PivotTableAddorResetRows Nil
PivotTableAddorResetColumns Nil
PivotTableAddorResetValues Nil
OpenWorkbookByPassword
WorkbookFullPath (string) - Required.Full Path of Workbook Password (string) - Optional. If no password, just leave it empty. MaxTimeForWaiting (int) - Optional. Default to be 120s.
CloseAllInstances Nil
|
Components Included |
Process Main NuGet Package MSExcelPictureOperations |
List of Functionality |
CreatePivotTable
For creating a pivot table. SourceRange should include the header. "DestinationWorksheet" default to be the "WorksheetName". "DesinationRange" default to be the appended row. If the worksheet doesnot exist, it will create a new one.
PivotTableFilterbyMultiValues
Pivot table, Set it filtered by multiple values in a Field. The Values can be something like: "{200025}{200035}{200045}". Wildcard cannot be used here. If the value doesn't exist, it will be skipped. Notice this method will not move PivotField to Filter unless you set MoveToFilter to be True.
PivotTableUnfilter
If PivotFields is empty, clear all filters; If PivotFields is like: {Field1}{Field5}. Just remove "Fields1" and "Fields5" from filter.
PivotTableChangeDataSource Change the pivot table data source. The source must come from the same workbook, but could be from different worksheet.
PivotTableAddorResetRows This function can : Add fields to Rows -> Set flgAddOrReset to True Reset fields in Rows -> Set flgAddOrReset to False Remove fields in Rows -> Set FieldsName to Empty and Set flgAddOrReset to False
PivotTableAddorResetColumns This function can : Add fields to Rows -> Set flgAddOrReset to True Reset fields in Rows -> Set flgAddOrReset to False Remove fields in Rows -> Set FieldsName to Empty and Set flgAddOrReset to False
PivotTableAddorResetValues Each field can only have one caption and function. The new function or caption will make a change on the existing one.
This function can : Add fields to Values-> Set flgAddOrReset to True Reset fields in Values-> Set flgAddOrReset to False Remove all fields in Values-> Set FieldsName to Empty and Set flgAddOrReset to False
xlAverage -4106 Average. xlCount -4112 Count. xlCountNums -4113 Count numerical values only. xlDistinctCount 111 Count using Distinct Count analysis. xlMax -4136 Maximum. xlMin -4139 Minimum. xlProduct -4149 Multiply. xlStDev -4155 Standard deviation, based on a sample. xlStDevP -4156 Standard deviation, based on the whole population. xlSum -4157 Sum. xlUnknown 1000 No subtotal function specified. xlVar -4164 Variation, based on a sample. xlVarP -4165 Variation, based on the whole population.
OpenWorkbookByPassword The handler is from the default instance which is the first instance. This function could open the Excel file directly from Remove Server by using the http or https path or UNC path, while the standard Excel VBO could only use UNC path.
CloseAllInstances - Closes all instances of excel which have been opened by this business object, either through "Create Instance" or "Open Instance" and which have not been discarded through "Close Instance" or "Release Instance" |
Disclaimer: References in this site to UiPath products, activities or services or the use of any UiPath trade name are made solely to indicate compatibility of products available on RPA Marketplace with UiPath products, and do not constitute or imply the development, endorsement, recommendation or approval by UiPath of the same.
No posts found