We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Add filter into a pivot table

hello,



I test pivot table and I don’t know how can I add a filter into my pivot table.

I want to add a filter in my pivot table with a value from my column “D”



For exemple I would like filter my pivot table with the value “Feb 16”





Can you help me ? please.



Have a nice sunny day and thank in advances.













'Instantiating an Workbook object

Dim workbook As New Workbook()

'Obtaining the reference of the first worksheet

Dim sheet As Worksheet = workbook.Worksheets(0)

'Name the sheet

sheet.Name = “Data”

Dim cells As Cells = sheet.Cells



'Setting the values to the cells

Dim cell As Cell = cells(“A1”)

cell.PutValue(“Model”)

cell = cells(“B1”)

cell.PutValue(“Status ID”)

cell = cells(“C1”)

cell.PutValue(“Month ID”)

cell = cells(“D1”)

cell.PutValue(“Month MONTH”)

cell = cells(“E1”)

cell.PutValue(“Car Count”)





cell = cells(“A2”)

cell.PutValue(“VW”)

cell = cells(“A3”)

cell.PutValue(“VW”)

cell = cells(“A4”)

cell.PutValue(“VW”)

cell = cells(“A5”)

cell.PutValue(“VW”)

cell = cells(“A6”)

cell.PutValue(“AUDI”)

cell = cells(“A7”)

cell.PutValue(“AUDI”)

cell = cells(“A8”)

cell.PutValue(“AUDI”)

cell = cells(“A9”)

cell.PutValue(“AUDI”)

cell = cells(“A10”)

cell.PutValue(“AUDI”)

cell = cells(“A11”)

cell.PutValue(“AUDI”)

cell = cells(“A12”)

cell.PutValue(“AUDI”)

cell = cells(“A13”)

cell.PutValue(“AUDI”)

cell = cells(“A14”)

cell.PutValue(“AUDI”)

cell = cells(“A15”)

cell.PutValue(“AUDI”)

cell = cells(“A16”)

cell.PutValue(“AUDI”)

cell = cells(“A17”)

cell.PutValue(“AUDI”)

cell = cells(“A18”)

cell.PutValue(“AUDI”)

cell = cells(“A19”)

cell.PutValue(“AUDI”)



cell = cells(“B2”)

cell.PutValue(“Old”)

cell = cells(“B3”)

cell.PutValue(“Old”)

cell = cells(“B4”)

cell.PutValue(“New”)

cell = cells(“B5”)

cell.PutValue(“New”)

cell = cells(“B6”)

cell.PutValue(“Old”)

cell = cells(“B7”)

cell.PutValue(“Old”)

cell = cells(“B8”)

cell.PutValue(“Old”)

cell = cells(“B9”)

cell.PutValue(“Old”)

cell = cells(“B10”)

cell.PutValue(“Old”)

cell = cells(“B11”)

cell.PutValue(“Old”)

cell = cells(“B12”)

cell.PutValue(“Old”)

cell = cells(“B13”)

cell.PutValue(“Old”)

cell = cells(“B14”)

cell.PutValue(“Old”)

cell = cells(“B15”)

cell.PutValue(“Old”)

cell = cells(“B16”)

cell.PutValue(“Old”)

cell = cells(“B17”)

cell.PutValue(“Old”)

cell = cells(“B18”)

cell.PutValue(“Old”)

cell = cells(“B19”)

cell.PutValue(“Old”)



cell = cells(“C2”)

cell.PutValue(“201601”)

cell = cells(“C3”)

cell.PutValue(“201602”)

cell = cells(“C4”)

cell.PutValue(“201601”)

cell = cells(“C5”)

cell.PutValue(“201602”)

cell = cells(“C6”)

cell.PutValue(“201501”)

cell = cells(“C7”)

cell.PutValue(“201502”)

cell = cells(“C8”)

cell.PutValue(“201503”)

cell = cells(“C9”)

cell.PutValue(“201504”)

cell = cells(“C10”)

cell.PutValue(“201505”)

cell = cells(“C11”)

cell.PutValue(“201506”)

cell = cells(“C12”)

cell.PutValue(“201507”)

cell = cells(“C13”)

cell.PutValue(“201508”)

cell = cells(“C14”)

cell.PutValue(“201509”)

cell = cells(“C15”)

cell.PutValue(“201510”)

cell = cells(“C16”)

cell.PutValue(“201511”)

cell = cells(“C17”)

cell.PutValue(“201512”)

cell = cells(“C18”)

cell.PutValue(“201601”)

cell = cells(“C19”)

cell.PutValue(“201602”)



cell = cells(“D2”)

cell.PutValue(“Jan 16”)

cell = cells(“D3”)

cell.PutValue(“Feb 16”)

cell = cells(“D4”)

cell.PutValue(“Jan 16”)

cell = cells(“D5”)

cell.PutValue(“Feb 16”)

cell = cells(“D6”)

cell.PutValue(“Jan 15”)

cell = cells(“D7”)

cell.PutValue(“Feb 15”)

cell = cells(“D8”)

cell.PutValue(“Mar 15”)

cell = cells(“D9”)

cell.PutValue(“Avr 15”)

cell = cells(“D10”)

cell.PutValue(“May 15”)

cell = cells(“D11”)

cell.PutValue(“Jun 15”)

cell = cells(“D12”)

cell.PutValue(“Jul 15”)

cell = cells(“D13”)

cell.PutValue(“Aug 15”)

cell = cells(“D14”)

cell.PutValue(“Sep 15”)

cell = cells(“D15”)

cell.PutValue(“Oct 15”)

cell = cells(“D16”)

cell.PutValue(“Nov 15”)

cell = cells(“D17”)

cell.PutValue(“Dec 15”)

cell = cells(“D18”)

cell.PutValue(“Jan 16”)

cell = cells(“D19”)

cell.PutValue(“Feb 16”)



'Dim cellStyle As New Style

'cellStyle.Number = 0

cell = cells(“E2”)

'Getting the Style of the A1 Cell

cell.PutValue(“4”, True)

cell = cells(“E3”)

cell.PutValue(“7”, True)

cell = cells(“E4”)

cell.PutValue(“2”, True)

cell = cells(“E5”)

cell.PutValue(“2”, True)

cell = cells(“E6”)

cell.PutValue(“5”, True)

cell = cells(“E7”)

cell.PutValue(“5”, True)

cell = cells(“E8”)

cell.PutValue(“5”, True)

cell = cells(“E9”)

cell.PutValue(“5”, True)

cell = cells(“E10”)

cell.PutValue(“5”, True)

cell = cells(“E11”)

cell.PutValue(“6”, True)

cell = cells(“E12”)

cell.PutValue(“6”, True)

cell = cells(“E13”)

cell.PutValue(“6”, True)

cell = cells(“E14”)

cell.PutValue(“6”, True)

cell = cells(“E15”)

cell.PutValue(“6”, True)

cell = cells(“E16”)

cell.PutValue(“6”, True)

cell = cells(“E17”)

cell.PutValue(“6”, True)

cell = cells(“E18”)

cell.PutValue(“6”, True)

cell = cells(“E19”)

cell.PutValue(“6”, True)





'Adding a new sheet

Dim sheet2 As Worksheet = workbook.Worksheets(workbook.Worksheets.Add())

'Naming the sheet

sheet2.Name = “Pivot Tables”

'Getting the pivottables collection in the sheet

Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = sheet2.PivotTables

'Adding a PivotTable to the worksheet

Dim index As Integer = pivotTables.Add("=Data!A1:E19", “A1”, “Data”)

'Accessing the instance of the newly added PivotTable

Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(index)

'Showing the grand totals

pivotTable.RowGrand = True

pivotTable.ColumnGrand = True

'Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat = True

'Draging the first field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0)

'Draging the third field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1)

'Drag ing the second field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 3)

'Draging the fourth field to the column area.

'pivotTable.PivotFilters(0).Equals(“Feb 16”)

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 4)

'Draging the fifth field to the data area.



Dim sheetIndex As Integer = workbook.Worksheets().Add(SheetType.Chart)

Dim sheet3 As Worksheet = workbook.Worksheets(sheetIndex)

sheet3.Name = “PivotChart”



Dim chartIndex As Integer = sheet3.Charts.Add(Charts.ChartType.ColumnStacked, 0, 5, 28, 16)

Dim chart As Charts.Chart = sheet3.Charts(chartIndex)



chart.PivotSource = “Pivot Tables!Data”

chart.HidePivotFieldButtons = False



workbook.Save(“C:\demo\pivotTable_test.xls”)

Hi,


Thanks for providing us sample code and some details.

Please try to add the following lines of code (in bold) for your requirements. It works fine as I tested:
e.g
Sample code:

……………

'pivotTable.PivotFilters(0).Equals("Feb 16")
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 4)
'Draging the fifth field to the data area.

Dim pageField As PivotField = pivotTable.PageFields(0)

Dim pageItemCount As Integer = pageField.PivotItems.Count

For i As Integer = 0 To pageItemCount - 1

If "Feb 16".Equals(pageField.PivotItems(i).Value) Then

pageField.CurrentPageItem = CShort(Fix(i))

End If

Next i

pivotTable.RefreshData()

pivotTable.CalculateData()

Dim sheetIndex As Integer = workbook.Worksheets().Add(SheetType.Chart)
Dim sheet3 As Worksheet = workbook.Worksheets(sheetIndex)
sheet3.Name = "PivotChart"

…….

Let us know if you still have any issue.

Thank you.

Hello Amjad !

It’s marvelous thanks you very much.

If I want sort by a PivotItemFiels (with isAscendSort) I have an null exception.

Can you tell me the good practice fort sorting by a coloumn ?

Thanks in advances

I have found my error !

Sorry !

Have a nice day.

Hi,


Good to know that your have sorted it out. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.