How to make PIVOT table in excel automation and how to drag fields into Layouts( value, sum, columns, filter)

excel

#1

like this.


#2

Hello!

Refer to this:

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Pivot-Table-Create-Pivot-Table-in-Excel-with-C-/VB.NET.html

Hope It Helps :slight_smile:

Regards,


#3

To create your pivot table use:

Try
workbook = CType(GetObject(strWorkbookPath,),Microsoft.Office.Interop.Excel.Workbook)
pivotTable = workbook.PivotCaches.Create(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, SourceData:= strNameSheetOrigin & “!” & strRangeRCOrigin , Version:=6).CreatePivotTable(TableDestination:= strNameSheetDestiny & “!” & strRangeRCDestiny, TableName:= strPivotName , DefaultVersion:=6)
Catch ex As Exception
End Try

To add a pivot field filter use:

Try
workbook = CType(GetObject(strWorkbookPath,),Microsoft.Office.Interop.Excel.Workbook)
worksheet = CType(workbook.Sheets(strSheetName),Microsoft.Office.Interop.Excel.Worksheet)
pivotTable = CType(worksheet.PivotTables(strPivotName),Microsoft.Office.Interop.Excel.PivotTable)
pivotTable.AddFields(,strPivotFieldName,True)
Catch ex As Exception
End Try

To add a pivot field filter use:

Try
workbook = CType(GetObject(strWorkbookPath,),Microsoft.Office.Interop.Excel.Workbook)
worksheet = CType(workbook.Sheets(strSheetName),Microsoft.Office.Interop.Excel.Worksheet)
pivotTable = CType(worksheet.PivotTables(strPivotName),Microsoft.Office.Interop.Excel.PivotTable)
pivotTable.AddFields(,strPivotFieldName,True)
Catch ex As Exception
End Try

To add a pivot field Rows use:

Try
workbook = CType(GetObject(strWorkbookPath,),Microsoft.Office.Interop.Excel.Workbook)
worksheet = CType(workbook.Sheets(strSheetName),Microsoft.Office.Interop.Excel.Worksheet)
pivotTable = CType(worksheet.PivotTables(strPivotName),Microsoft.Office.Interop.Excel.PivotTable)
pivotTable.AddFields(strPivotFieldName,True)
Catch ex As Exception
End Try

To add a pivot field Column use:

Try
workbook = CType(GetObject(strWorkbookPath,),Microsoft.Office.Interop.Excel.Workbook)
worksheet = CType(workbook.Sheets(strSheetName),Microsoft.Office.Interop.Excel.Worksheet)
pivotTable = CType(worksheet.PivotTables(strPivotName),Microsoft.Office.Interop.Excel.PivotTable)
pivotTable.AddFields(,strPivotFieldName,True)
Catch ex As Exception
End Try

To add a calculated field in your pivot table use:

Try
workbook = CType(GetObject(strWorkbookPath,),Microsoft.Office.Interop.Excel.Workbook)
worksheet = CType(workbook.Sheets(strSheetName),Microsoft.Office.Interop.Excel.Worksheet)
pivotTable = CType(worksheet.PivotTables(strPivotName),Microsoft.Office.Interop.Excel.PivotTable)
pivotField = pivotTable.CalculatedFields.Add(strFieldName,strFormula,True)
pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
Catch ex As Exception
End Try


#4

variable/type

workbook/ Microsoft.Office.Interop.Excel.Workbook
worksheet/ Microsoft.Office.Interop.Excel.Worksheet
pivotTable/ Microsoft.Office.Interop.Excel.PivotTable
pivotField/ Microsoft.Office.Interop.Excel.PivotField


#5

Regards