Not able to add filters to pivot table in aspose.cells

Aspose.Cells Product Family
, ,
1

Hi,
I am trying to apply filter in pivot table in .NET but I am unable to do it. Can someone please write code in order to add filter in the pivot table?
I have attached screenshots of data source and the pivot table(as I require).

Excel.png (115.0 KB)
Pivot Table.png (109.5 KB)

2

@Shivammirje1998,
Please share your desired Excel output file also which is shown in your images for our reference.

3

@ahsaniqbalsidiqui
I am not able to upload excel file here. How can I share?

4

@Shivammirje1998,

Please zip the Excel file and then upload the zipped archive using “Upload” button (while replying) here.

5

Thanks @Amjad_Sahi.

Please find the attached excel. :slight_smile:

Sample.zip (31.2 KB)

6

@Shivammirje1998,

See the following sample code to accomplish your task. I used your file as template file and used its data (in the first sheet) to be set as data source for the pivot table created in another (new) sheet:
e.g.
Sample code:

            Workbook workbook = new Workbook("e:\\test2\\Sample.xlsx");

            Worksheet sheet = workbook.Worksheets.Add("NewSheet");
            PivotTableCollection pivotTables = sheet.PivotTables;

            // Adding a PivotTable to the worksheet
            int index = pivotTables.Add("=Masterfile!$A$1:$D$8", "A1", "PivotTable3");

            // Accessing the instance of the newly added PivotTable
            Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 1);
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 3);

            //Add the following code to move PivotTable.DataField to column area
        if (pivotTable.DataField != null)
        {
            pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
        }

        pivotTable.CalculateData();

            
            // Saving the Excel file
            workbook.Save("e:\\test2\\pivotTable_test_out.xlsx");

Hope, this helps a bit.

7

Thank you so much @Amjad_Sahi.
It worked perfectly. :slight_smile:

8

@Shivammirje1998,

Good to know that the suggested code segment works for your needs. In the event of further queries or issue, feel free to write us back.

9

Yeah sure :slight_smile:

10

Hi,
In the same excel, I want the data values in thousands. But how do I convert the values?
I have attached the excel file for reference.
Sample (2).zip (33.5 KB)
Thanks in advance.

11

@Shivammirje1998,

Please try to use the following line of code for your needs:
e.g.
Sample code:

pivotTable.DataFields[1].DisplayName = "Sum of Total Sales (in thousands)";

12

Yeah I got that but I am saying that the column data should also be divided by 1000.

13

@Shivammirje1998,

Please try the following line of code to set the numbers formatting accordingly:
e.g.
Sample code:

.....
pivotTable.DataFields[1].NumberFormat = "0.00";

Hope, this helps a bit.

14

Thanks.:slight_smile:

15

@Shivammirje1998,

You are welcome.

16

Hi @Amjad_Sahi,

If in the data source data is 1000 and in pivot table I want that data to be displayed as 1.0 (in thousands) or 10.0 (in hundreds) then is it possible?

17

How could you do this in MS Excel manually via any option/settings for numbers formatting in Pivot table, please elaborate it with sample Excel file and screenshots? We will check on how to do the same via Aspose.Cells APIs.

PS. please zip the files prior attaching here.

18

Thanks @Amjad_Sahi but I got resolved with custom formatting.
I have new doubts.

  1. Can we apply conditional formatting for pivot table like cell value >= 30000?
  2. Can we sort pivot table data columns in descending order?
20

Hi @Amjad_Sahi,
PFA sample files.
I want to add conditional formatting on data columns of pivot table in the excel file so that cells with value greater than 0.5 should have font colour as red and background color as yellow. Also except the grand total cells.
Please tell me how can I do it in C# code.
Sample.zip (31.6 KB)

21

@Shivammirje1998,

Please perform and apply your conditional formatting upon Pivot table in MS Excel manually to re-save the file and provide us here, we will check on how to do it via Aspose.Cells APIs.