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.