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

Help
1

like this.

image
image1366×768 81.7 KB

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

1 Like
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

6

@ChimeraPlayerN1N30N3, Do you have a sample work flow for this? It would be really helpful if you can post that,

Thank you
Priyanka

7
  1. Select any cell inside your source data.
  2. On the Insert tab of the Ribbon select Pivot Table
  3. Excel will automatically detect the range of cells and put it in your Table/Range field .
  4. Ok, now you’re ready to build your choose the fields.
  5. Ok, so far so good.

Hope this information will help you. If you want to enhance your skills then you can also do advance excel course

8

Do you have a sample work flow for this? It would be really helpful if you can post that,

9

Do you have sample flow for this? Please share if possible ?it will be really helpful

10

Hello All,
Here I have a Video with code of how I create PivotTabel:

VBA Code free:

Thanks,
Cristian