Icon

Create Excel Pivots

Replicating Excel Function to Create Pivot Table into Excel Spreadsheet using KNIME for Pivot UI and Translation of Parameters into Python Variables and Aspose.Cells Module for Pivot Creation into the Spreadsheet. Python Code is extracted from the Author's GitHub Site.

Replicating the following Excel Functions-Menu Bar --> Insert-PivotTable-Select a Table or Range-New Worksheet (must be existing inoutput file)-Existing Worksheet Replicating the following Excel Functions-Pivot Table Tools --> Analyze-Toggle Field List-Choose fields to add to report-Drag fields between areas: Filter,Column, Row, Values Arrange the order of Field Items as youwould like to appear in the Pivot Table Sources & Output Setup-Sample Data-Exported Output Data File-Filepath Setup-Extract Sheet Names-Extract Python Codes from GitHub Replicating the following Excel Functions-Pivot Filter-Select Criteria (rest are unselected)-Unselect Criteria (rest are selected)-Enable/Disable Subtotal-Enable/Disable Repeat All Item Labels-Value Field Settings - Choose Type of Calculation Replicating the following Excel Functions-Grand Total for Column & Row-Pivot Table Design & Layout-Additional Feature: Prepping theWorksheet before and after placing the PivotTableNote: When a pivot is created it is possiblethat in its unfiltered state it will overlap withany row or column items in the sheet, toremediate this you can provide how manyrows/columns need to shift down and/orright before inserting the pivot then anotherset of numbers to shift them back to theirprevious positions or new positions. Proof of Concept-You can combine other Pivot Setups youhave made if you are planning to createmultiple Pivot Tables in your Worksheet-You can also leverage on using LoopNodes and Flow Variables if you'replanning on iterating the same PivotSetup but with different Parameters pereach iteration Transform all of the Pivot Setups into PythonVariables Create Pivot Table using Python - Aspose.Cells ModulePaste Value Pivot Table using Python - Aspose.Cells Modulehttps://pypi.org/project/aspose-cells/https://docs.aspose.com/cells/python-java/ Additional Step: Deleting Watermark SheetIf Aspose.Cells License exist, then skip thisnode Create Excel PivotsKNIME Workflow created by myDANhttps://mydigitalautoneeds.wordpress.comhttps://github.com/MYDANblogReminder: This workflow requires Anaconda /Python 3 with the following modules installed:Pandas, Openpyxl, Aspose.Cells Proof of Concept-Pivot Setup can be provided from anexternal file source as long as it retainsthe same selection of parameters and itsposition-Can apply Column Expression NodeSyntax on each parameter cell in thesheet Sample Data Select Item List toInsert in Pivot FieldsOutput Ports(1) Filter Field(2) Column Field(3) Row Field(4) Value FieldDouble-Click the Nodeto Update Values PrerequisitePython 3 - PandasExport Sample Datato Spreadsheet Update Source,Pivot Sheet &Cell LocationDouble-Click the Nodeto Update Values Sort Filter Field ListDouble-Click Nodeto Configure Sort Column Field ListDouble-Click Nodeto Configure Sort Row Field ListDouble-Click Nodeto Configure Sort Value Field ListDouble-Click Nodeto Configure Insert Filter CriteriaFilter Selection TypeEnable/Disable SubtotalEnable/Disable Repeating Item LabelsSort Field ItemsRight-Click Node thenSelect Interactive Viewto Update Values Insert Filter CriteriaFilter Selection TypeEnable/Disable SubtotalEnable/Disable Repeating Item LabelsSort Field ItemsRight-Click Node thenSelect Interactive Viewto Update Values Insert Filter CriteriaFilter Selection TypeRight-Click Node thenSelect Interactive Viewto Update Values Calculation TypeRight-Click Node thenSelect Interactive Viewto Update Values Enable/Disable Grand TotalSelect Pivot Layout and StylePrior and After Pivot Placement SetupInput Ports(1) Filter Field(2) Column Field(3) Row Field(4) Value FieldDouble-Click the Nodeto Update ValuesDouble-Click the Nodeto Update the Value Insert Pivot TablesOutput Port:Pivot CoordinatesNote: 1st Input port of thisnode is reserved for"Translate Pivot Configurations toPython Variables"Component Node - 1st Output PortPrerequisites:• Python3 - Aspose-cells v21.6.0 and above• flow_variables['output_Working_filePath']• flow_variables['Python Script - Create Pivot'] • Python Configuration Component Node(connect to input port #1) Paste Value All Formulaand Pivot TablesNote: 1st Input port of thisnode is reserved for"Translate Pivot Configurations toPython Variables"(if output has both pivots and formula)or"Translate Formula Configurations toPython Variables"(if output has formula only)Component Node - 2nd Output PortPrerequisites:• Python3 - Aspose-cells v21.6.0 and above• flow_variables['output_Working_filePath']• flow_variables['Python Script - Paste Value'] • Python Configuration Component Node(connect to input port #1) Input Port:(1) Pivot Settings NodeOutput Ports:(1) Python Script - Insert Pivot Tables(2) Pivot Script - Paste ValuesPrerequisites:Flow Variable• flow_variables['Sheet Name_Sheet_Position']Python 3 - PandasCollate AllPivot Table SetupsPlaceholder for2nd Pivot TableSetupPlaceholder for3rd Pivot TableSetup Delete SheetEvaluation WarningNote: Aspose.Cells requires a Licenseto remove the watermark sheet, as analternative, this node will delete thewatermark sheetPrerequisites:• Python 3 - Openpyxl• flow_variables['output_Working_filePath'] Read ExternalPivot Setup FileOutput Port:(1) Translate PivotConfigurations toPython VariablesComponent NodeCurrently savedin the same locationas the output fileTable Creator PivotTable Fields Excel Writer Pivot Source& Location Column Resorter Column Resorter Column Resorter Column Resorter Column FieldSettings Row Field Settings Filter FieldSettings Value FieldSettings Pivot Settings: GrandTotal, Design, Others Insert OutputFilepath Python Script(legacy) Python Script(legacy) Translate Pivot Configurationsto Python Variables Extract Sheet Names,Python Script from GitHub Concatenate Empty Table Creator Empty Table Creator Python Source(legacy) Excel Reader Export Pivot Setup toExternal Configuration File Replicating the following Excel Functions-Menu Bar --> Insert-PivotTable-Select a Table or Range-New Worksheet (must be existing inoutput file)-Existing Worksheet Replicating the following Excel Functions-Pivot Table Tools --> Analyze-Toggle Field List-Choose fields to add to report-Drag fields between areas: Filter,Column, Row, Values Arrange the order of Field Items as youwould like to appear in the Pivot Table Sources & Output Setup-Sample Data-Exported Output Data File-Filepath Setup-Extract Sheet Names-Extract Python Codes from GitHub Replicating the following Excel Functions-Pivot Filter-Select Criteria (rest are unselected)-Unselect Criteria (rest are selected)-Enable/Disable Subtotal-Enable/Disable Repeat All Item Labels-Value Field Settings - Choose Type of Calculation Replicating the following Excel Functions-Grand Total for Column & Row-Pivot Table Design & Layout-Additional Feature: Prepping theWorksheet before and after placing the PivotTableNote: When a pivot is created it is possiblethat in its unfiltered state it will overlap withany row or column items in the sheet, toremediate this you can provide how manyrows/columns need to shift down and/orright before inserting the pivot then anotherset of numbers to shift them back to theirprevious positions or new positions. Proof of Concept-You can combine other Pivot Setups youhave made if you are planning to createmultiple Pivot Tables in your Worksheet-You can also leverage on using LoopNodes and Flow Variables if you'replanning on iterating the same PivotSetup but with different Parameters pereach iteration Transform all of the Pivot Setups into PythonVariables Create Pivot Table using Python - Aspose.Cells ModulePaste Value Pivot Table using Python - Aspose.Cells Modulehttps://pypi.org/project/aspose-cells/https://docs.aspose.com/cells/python-java/ Additional Step: Deleting Watermark SheetIf Aspose.Cells License exist, then skip thisnode Create Excel PivotsKNIME Workflow created by myDANhttps://mydigitalautoneeds.wordpress.comhttps://github.com/MYDANblogReminder: This workflow requires Anaconda /Python 3 with the following modules installed:Pandas, Openpyxl, Aspose.Cells Proof of Concept-Pivot Setup can be provided from anexternal file source as long as it retainsthe same selection of parameters and itsposition-Can apply Column Expression NodeSyntax on each parameter cell in thesheet Sample Data Select Item List toInsert in Pivot FieldsOutput Ports(1) Filter Field(2) Column Field(3) Row Field(4) Value FieldDouble-Click the Nodeto Update Values PrerequisitePython 3 - PandasExport Sample Datato Spreadsheet Update Source,Pivot Sheet &Cell LocationDouble-Click the Nodeto Update Values Sort Filter Field ListDouble-Click Nodeto Configure Sort Column Field ListDouble-Click Nodeto Configure Sort Row Field ListDouble-Click Nodeto Configure Sort Value Field ListDouble-Click Nodeto Configure Insert Filter CriteriaFilter Selection TypeEnable/Disable SubtotalEnable/Disable Repeating Item LabelsSort Field ItemsRight-Click Node thenSelect Interactive Viewto Update Values Insert Filter CriteriaFilter Selection TypeEnable/Disable SubtotalEnable/Disable Repeating Item LabelsSort Field ItemsRight-Click Node thenSelect Interactive Viewto Update Values Insert Filter CriteriaFilter Selection TypeRight-Click Node thenSelect Interactive Viewto Update Values Calculation TypeRight-Click Node thenSelect Interactive Viewto Update Values Enable/Disable Grand TotalSelect Pivot Layout and StylePrior and After Pivot Placement SetupInput Ports(1) Filter Field(2) Column Field(3) Row Field(4) Value FieldDouble-Click the Nodeto Update ValuesDouble-Click the Nodeto Update the Value Insert Pivot TablesOutput Port:Pivot CoordinatesNote: 1st Input port of thisnode is reserved for"Translate Pivot Configurations toPython Variables"Component Node - 1st Output PortPrerequisites:• Python3 - Aspose-cells v21.6.0 and above• flow_variables['output_Working_filePath']• flow_variables['Python Script - Create Pivot'] • Python Configuration Component Node(connect to input port #1) Paste Value All Formulaand Pivot TablesNote: 1st Input port of thisnode is reserved for"Translate Pivot Configurations toPython Variables"(if output has both pivots and formula)or"Translate Formula Configurations toPython Variables"(if output has formula only)Component Node - 2nd Output PortPrerequisites:• Python3 - Aspose-cells v21.6.0 and above• flow_variables['output_Working_filePath']• flow_variables['Python Script - Paste Value'] • Python Configuration Component Node(connect to input port #1) Input Port:(1) Pivot Settings NodeOutput Ports:(1) Python Script - Insert Pivot Tables(2) Pivot Script - Paste ValuesPrerequisites:Flow Variable• flow_variables['Sheet Name_Sheet_Position']Python 3 - PandasCollate AllPivot Table SetupsPlaceholder for2nd Pivot TableSetupPlaceholder for3rd Pivot TableSetup Delete SheetEvaluation WarningNote: Aspose.Cells requires a Licenseto remove the watermark sheet, as analternative, this node will delete thewatermark sheetPrerequisites:• Python 3 - Openpyxl• flow_variables['output_Working_filePath'] Read ExternalPivot Setup FileOutput Port:(1) Translate PivotConfigurations toPython VariablesComponent NodeCurrently savedin the same locationas the output fileTable Creator PivotTable Fields Excel Writer Pivot Source& Location Column Resorter Column Resorter Column Resorter Column Resorter Column FieldSettings Row Field Settings Filter FieldSettings Value FieldSettings Pivot Settings: GrandTotal, Design, Others Insert OutputFilepath Python Script(legacy) Python Script(legacy) Translate Pivot Configurationsto Python Variables Extract Sheet Names,Python Script from GitHub Concatenate Empty Table Creator Empty Table Creator Python Source(legacy) Excel Reader Export Pivot Setup toExternal Configuration File

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: UserID
Created at: 2024-03-01
On NodePit since: 2024-03-02
Last update: 2024-04-27
Created with KNIME version: v4.7.8
Tags: ExcelPivotPython

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!