#include-once
#include <Excel.au3>

#CS
	Title:   		Create Excel Pivot tables and Charts using Autoitv3
	Filename:  		ExcelPivot.au3
	Description: 	A collection of functions for creating Excel Pivot Tables and Charts
	Author:   		SudeepJD
	Version:  		V0.2
	Last Update: 	10/20/2020
	Requirements: 	AutoIt3 3.2 or higher,
					Excel
	Changelog:
					---------10/06/2020---------- v0.1
					Initial release.
					---------10/20/2020---------- v0.2
					Added the SavedData Parameter when the Pivot is saved, so it does not give a problem on Data refreshes.
					---------11/19/2020---------- v0.3
					Fixed a bug on the Chart title which was throwing an error when there was no title
					Added in Additional Error Checking if the pivotname is not found in all functions

#CE

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_CreateCache()
; Description ...:	Creates the Cache for the Excel Pivot
; Syntax.........:	_ExcelPivot_CreateCache($oBook, $sSheetName, $sRange)
; Parameters ....:	$oBook			- The Excel Workbook.
;					$sSheetName		- Name of the Sheet on which the data is
;					$sRange			- Range of data to which needs to be pivoted
; Return values .: 	On Success		- Returns the handle to the Pivot Cache
; Remarks .......:  The reason for keeping the separate is that the same cache can be used
;                   across pivot tables, if more than one are required.
;============================================================================================
Func _ExcelPivot_CreateCache($oBook, $oDSheet, $sRange = "")

	;Get the Sheet Object
	If IsString($oDSheet) Then $oDSheet = $oBook.Sheets($oDSheet)

	;Define Data Range
	If $sRange <> "" Then
		$PRange = $oDSheet.Range($sRange)
	Else
		$oLastRow = $oDSheet.Cells($oDSheet.Rows.Count, 1).End(-4162).Row
		$oLastCol = $oDSheet.Cells(1, $oDSheet.Columns.Count).End(-4159).Column
		$PRange   = $oDSheet.Cells(1, 1).Resize($oLastRow, $oLastCol)
	EndIf

	;Define Pivot Cache
	$PCache = $oBook.PivotCaches.Create(1, $PRange)
	Return $PCache
EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_CreateTable
; Description ...:	Creates the Pivot table from the Cache
; Syntax.........:	_ExcelPivot_CreateTable($pCache, $oSheet, $sCell, $sName)
; Parameters ....:	$pCache			- The pivot data cache handle
;					$oBook			- The Excel Workbook.
;					$sSheetName		- Name of the Sheet on which to create the pivot table
; 					$sCell			- Cell Address where the pivot needs to be placed
;					$sName			- Name of the Pivot table
; Return values .: 	On Success		- Returns the handle to the Pivot Table
;============================================================================================
Func _ExcelPivot_CreateTable($pCache, $oSheet, $sCell, $sName)
	If Not IsObj($oSheet) Then
		ConsoleWriteError("CreateTable: The Sheet should be an Object" & @CRLF)
		Return
	EndIf

	$pTable = $pCache.CreatePivotTable($oSheet.Range($sCell), $sName)
	$pTable.SaveData = True
	Return $pTable
EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_RefreshTable
; Description ...:	Refresh the table with new data cache
; Syntax.........:	_ExcelPivot_RefreshTable($pCache, $oSheet, $sName)
; Parameters ....:	$pCache			- The pivot data cache handle
;					      $oSheet    		- Name of the Sheet on which to create the pivot table
;					      $sName			- Name of the Pivot table
;============================================================================================
Func _ExcelPivot_RefreshTable($pCache, $oSheet, $sName)

	If Not IsObj($oSheet) Then
		ConsoleWriteError("RefreshTable: The Sheet should be an Object" & @CRLF)
		Return
	EndIf

	$found = False
	For $p in $oSheet.PivotTables
		If $p.Name = $sName Then
			$found = True
			ExitLoop
		EndIf
	Next

	If $found = False Then
		ConsoleWriteError("RefreshTable: Pivot with Name " & $sName & " could not be found" & @CRLF)
		Return
	EndIf

	$oSheet.PivotTables($sName).ChangePivotCache($pCache)
	$oSheet.PivotTables($sName).SaveData = True
	$oSheet.PivotTables($sName).PivotCache.Refresh
EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_AddField
; Description ...:	Adds the fields into the pivot for data representation
; Syntax.........:	_ExcelPivot_AddField($oSheet, $sName, $sField, $sOrient, $sFunc, $iPos)
; Parameters ....:	$oSheet			- The Sheet Object that the pivot table is on
;					$sName			- The Name of the Pivot table
;					$sField			- Field to add in
; 					$sOrient		- The section that the field needs to be added to
;										Filter, Row, Column, Value
;					$sFunc			- The function to apply to the Value, ignored if sOrient <> Value
;					$iPos			- The position of the item in the stack
;                   $sCalc          - Calculation Modifier to be added to Column
;                   $sBaseField     - BaseField on which the calculation will be done - Column Field
;                   $sBaseItem      - BaseItem to Modify Calculation from if required by $sCalc
;============================================================================================
Func _ExcelPivot_AddField($oSheet, $sName, $sField, $sOrient, $sFunc = "Count", $iPos = 1, $sCalc = "", $sBaseField = "", $sBaseItem = "")

	If Not IsObj($oSheet) Then
		ConsoleWriteError("AddField: The Sheet should be an Object" & @CRLF)
		Return
	EndIf

	$found = False
	For $p in $oSheet.PivotTables
		If $p.Name = $sName Then
			$found = True
			ExitLoop
		EndIf
	Next
	If $found = False Then
		ConsoleWriteError("AddField: Pivot with Name " & $sName & " could not be found" & @CRLF)
		Return
	EndIf

	;Which Field should the filter be placed into
	Switch $sOrient
	Case "Row"
		$iOrient = 1
	Case "Column"
		$iOrient = 2
	Case "Filter"
		$iOrient = 3
	Case "Value"
		$iOrient = 4
	EndSwitch

	;What Function should be added to it
	Switch $sFunc
	Case "Count"
		$iFunc = -4112
	Case "Sum"
		$iFunc = -4157
	Case "Product"
		$iFunc = -4149
	Case "Avg"
		$iFunc = -4106
	Case "DistinctCount"
		$iFunc = 11
	Case "CountNums"
		$iFunc = -4113
	EndSwitch

	;What Function should be added to it
	Switch $sCalc
	Case "None"
		$iCalc = -4143
	Case "DiffFrom" ;Needs Field and Item
		$iCalc = 2
	Case "PercentOf" ;Needs Field and Item
		$iCalc = 3
	Case "PercentDiffFrom" ;Needs Field and Item
		$iCalc = 4
	Case "Running Total" ; Needs Field
		$iCalc = 5
	Case "PercentageOfRow"
		$iCalc = 6
	Case "PercentageOfCol"
		$iCalc = 7
	Case "PercentageOfTotal"
		$iCalc = 8
	Case "PercentageOfParentRow"
		$iCalc = 10
	Case "PercentageOfParentCol"
		$iCalc = 11
	Case "PercentageOfParent" ;Needs Field
		$iCalc = 12
	Case "PercentageRunningTotal";Needs Field
		$iCalc = 13
	Case "RankAscending"
		$iCalc = 14
	Case "RankDescending"
		$iCalc = 15
	Case Else
		$iCalc = Null
	EndSwitch

	With $oSheet.PivotTables($sName).PivotFields($sField)
		.Orientation = $iOrient
		.Position = $iPos
		If $iOrient = 4 Then .Function = $iFunc
		If $iCalc <> Null Then
		 .Calculation = $iCalc
		 If ($iCalc >=2 And $iCalc<=5) Or $iCalc=12 Or $iCalc<=13 Then
			If $sBaseField = "" Then
				ConsoleWriteError("This Calculation Needs a Base Field")
				Return
			Else
				.BaseField = $sBaseField
			EndIf
		 EndIf

		 If $iCalc >=2 And $iCalc<=4 Then
			If $sBaseItem = "" Then
				ConsoleWriteError("This Calculation Needs a Base Item")
				Return
			Else
				.BaseItem = $sBaseItem
			EndIf
		 EndIf
		 If $iCalc = 3 Or $iCalc = 4 Or ($iCalc >= 6 And $iCalc <= 13) Then
			;ConsoleWrite("Set Number Format Per")
			;.NumberFormat = "0.00%"
		 EndIf
		EndIf
	EndWith
EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_ClearFilter
; Description ...:	Clears the filters in the pivot table
; Syntax.........:	_ExcelPivot_ClearFilter($oSheet, $sPivot, $sField)
; Parameters ....:	$oSheet			- The Sheet Object that the pivot table is on
;					$sName			- The Name of the Pivot table
;					$sField			- Field for which the filters need to be cleared,
; 									  If empty then all the filters are cleared.
;============================================================================================
Func _ExcelPivot_ClearFilter($oSheet, $sName, $sField = "")

	If Not IsObj($oSheet) Then
		ConsoleWriteError("ClearFilter: The Sheet should be an Object" & @CRLF)
		Return
	EndIf

	$found = False
	For $p in $oSheet.PivotTables
		If $p.Name = $sName Then
			$found = True
			ExitLoop
		EndIf
	Next
	If $found = False Then
		ConsoleWriteError("RefreshTable: Pivot with Name " & $sName & " could not be found" & @CRLF)
		Return
	EndIf

	If $sField = "" Then
		$oSheet.PivotTables($sName).ClearAllFilters
	Else
		$oSheet.PivotTables($sName).PivotFields($sField).ClearAllFilters
	EndIf
EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_Filter
; Description ...:	Modifies the filter in the pivot table
; Syntax.........:	_ExcelPivot_Filter($oSheet, $sName, $sField)
; Parameters ....:	$oSheet			- The Sheet Object that the pivot table is on
;					$sName			- The Name of the Pivot table
;					$sField			- Field for which the filters need to be cleared,
; 									  If empty then all the filters are cleared.
;============================================================================================
Func _ExcelPivot_Filter($oSheet, $sName, $sField, $sValue)

	If Not IsObj($oSheet) Then
		ConsoleWriteError("Filter: The Sheet should be an Object" & @CRLF)
		Return
	EndIf

	$found = False
	For $p in $oSheet.PivotTables
		If $p.Name = $sName Then
			$found = True
			ExitLoop
		EndIf
	Next
	If $found = False Then
		ConsoleWriteError("RefreshTable: Pivot with Name " & $sName & " could not be found" & @CRLF)
		Return
	EndIf

	With $oSheet.PivotTables($sName)
		.ManualUpdate = True
		.PivotFields($sField).ClearAllFilters
		For $i = 1 To .PivotFields($sField).PivotItems.Count
		 If .PivotFields($sField).PivotItems($i).Name = $sValue Then
			.PivotFields($sField).PivotItems($i).Visible = True
		 Else
			.PivotFields($sField).PivotItems($i).Visible = False
		 EndIf
		Next
		.ManualUpdate = False
	EndWith

EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_Sort
; Description ...:	Sort the Data in the Pivot Table
; Syntax.........:	_ExcelPivot_Sort($oSheet, $sName, $sField, $sTitle, $sDir)
; Parameters ....:	$oSheet			- The Sheet Object that the pivot table is on
;					$sName			- The Name of the Pivot table
;					$sField			- Field for which the data needs to be sorted
;                   $sTitle         - The Title of the column the sort needs to be applied On
;                   $sDir           - Asc or Desc
;============================================================================================
Func _ExcelPivot_Sort($oSheet, $sName, $sField, $sTitle, $sDir = "Asc")

	If Not IsObj($oSheet) Then
		ConsoleWriteError("Sort: The Sheet should be an Object" & @CRLF)
		Return
	EndIf

	$found = False
	For $p in $oSheet.PivotTables
		If $p.Name = $sName Then
			$found = True
			ExitLoop
		EndIf
	Next
	If $found = False Then
		ConsoleWriteError("Sort: Pivot with Name " & $sName & " could not be found" & @CRLF)
		Return
	EndIf

	Switch $sDir
	Case "Asc"
		$iDir = 1
	Case "Desc"
		$iDir = 2
	EndSwitch

	$oSheet.PivotTables($sName).PivotFields($sField).AutoSort($iDir, $sTitle)
EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_GetRange
; Description ...:	Select Sections of the Pivot Table
; Syntax.........:	_ExcelPivot_GetRange($oSheet, $sName, $sSection = "Data", $sField="", $sItem = "")
; Parameters ....:	$oSheet			- The Sheet Object that the pivot table is on
;					$sName			- The Name of the Pivot table
;					$sSection		- Section of the range to be returned
; 					$sField			- Field in the Pivot to be selected
;					$sItem			- Item in the field to be selected
; Return ........:  The Range Object of that selection
; Remarks .......:  Section was built from https://peltiertech.com/referencing-pivot-table-ranges-in-vba/
;============================================================================================
Func _ExcelPivot_GetRange($oSheet, $sName, $sSection = "DataBody", $sField="", $sItem = "")

	If Not IsObj($oSheet) Then
		ConsoleWriteError("GetRange: The Sheet should be an Object" & @CRLF)
		Return
	EndIf

	$found = False
	For $p in $oSheet.PivotTables
		If $p.Name = $sName Then
			$found = True
			ExitLoop
		EndIf
	Next
	If $found = False Then
		ConsoleWriteError("GetRange: Pivot with Name " & $sName & " could not be found" & @CRLF)
		Return
	EndIf

	$pivot = $oSheet.PivotTables($sName)

	Switch $sSection
	Case "Table1"		    ; Table Range without Filter
		Return $pivot.TableRange1
	Case "Table2"		    ; Table Range with Filter
		Return $pivot.TableRange2
	Case "Rows"				; The Row Labels
		Return $pivot.RowRange
	Case "Columns"			; Labels on the top of columns
		Return $pivot.ColumnRange
	Case "DataLabel"			; DataLabel
		Return $pivot.DataLabelRange
	Case "DataBody"			; Data Only
		Return $pivot.DataBodyRange
	Case "PageRange"			; Filter
		Return $pivot.PageRange
	Case "PivotFieldLabel"	; Pivot Field Label - All the Labels, the name itself
		Return $pivot.PageFields($sField).LabelRange
	Case "PivotFieldData"	; Pivot Field Data - All the Column Names
		Return $pivot.PageFields($sField).DataRange
	Case "PivotItemLabel"	; Specific label in a PivotField
		Return $pivot.PageFields($sField).PivotItems($sItem).LabelRange
	Case "PivotItemData"	    ; Specific Data in the PivotField
		Return $pivot.PageFields($sField).PivotItems($sItem).DataRange
	EndSwitch
EndFunc

; #FUNCTION# ;===============================================================================
;
; Name...........:	_ExcelPivot_AddChart
; Description ...:	Adds a linked pivot chart to the pivot table.
; Syntax.........:	_ExcelPivot_AddChart($oBook, $oSheet, $sTable, $sType, $sTitle, $sLoc, $iWidth)
; Parameters ....:	$oBook			- The Book in which the chart needs to be added
;					$oSheet			- The sheet name or object
;					$sTable			- The linked Pivot table from which the chart needs to be created
; 				$sType		    - The type of chart
;										ColumnClustered, ColumnStacked, Line, Pie, or the integer value of the chart type
;					$sTitle			- Chart Title
;					$sLoc			- The String Address of the cell where the chart will be placed
;					$iWidth			- The width of the chart
; Return .......:   Handle to the Chart
;============================================================================================
Func _ExcelPivot_AddChart($oBook, $oSheet, $sTable, $sType = "ColumnClustered", $sTitle = "", $sLoc = Default, $iWidth = Default)

	;Get the Object of the Sheet if it is not one
	If IsString($oSheet) Then $oSheet = $oBook.Sheets($oSheet)

	$found = False
	For $p in $oSheet.PivotTables
		If $p.Name = $sTable Then
			$found = True
			ExitLoop
		EndIf
	Next
	If $found = False Then
		ConsoleWriteError("AddChart: Pivot with Name " & $sTable & " could not be found" & @CRLF)
		Return
	EndIf

	;Get the int for the Chart Type
	Switch $sType
	Case "ColumnClustered"
		$iType = 51
	Case "ColumnStacked"
		$iType = 52
	Case "BarClustered"
		$iType = 57
	Case "BarStacked"
		$iType = 58
	Case "Line"
		$iType = 4
	Case "Pie"
		$iType = 5
	Case Else
		$iType = Int($sType)
	EndSwitch

	;Select the Pivot Table
	$oSheet.PivotTables($sTable).TableRange1.Item(1,1).Select

	;Add the Chart
	$oChart = $oBook.Charts.Add
	$oChart.Location(2, $oSheet.Name)

	;After the move the reference is lost, get it back.
	$oChart = $oBook.ActiveChart.Parent

	$oChart.Chart.ChartType = $iType

	If $sLoc <> Default Then
		$oChart.Left = $oSheet.Range($sLoc).Left
		$oChart.Top = $oSheet.Range($sLoc).Top
	EndIf

	If $iWidth <> Default Then $oChart.Width = $iWidth

	$oBook.Sheets("Pivot").ChartObjects(1).Chart.SetElement(2)
	$oBook.Sheets("Pivot").ChartObjects(1).Chart.ChartTitle.Text = "Paretto"

	If $sTitle <> "" Then
		If Not $oChart.Chart.HasTitle Then $oChart.Chart.SetElement(2)
		$oChart.Chart.ChartTitle.Text = $sTitle
	EndIf

	Return $oChart
EndFunc