We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Could not set the default value in pivot table dropdown in XLSX excel file


Hi..,

We are using Syncfusion.XlsIO and Syncfusion.XlsIO.Implementation.PivotTables for Generating reports in our report having pivot table dropdown for filters in excel sheets which are the sheets we need to  set the default value in dropdown filters but we could not able to set default value in the dropdown .so we need the help from your team.

We tried the codes has been noted below for your FYR.

   I,                  string mCurrency = "USD";
                        string sDefaultPartnershipType = "FDUMax";
                        IPivotFilter CurrencyFilterValue = pivotTable.Fields["CURRENCY"].PivotFilters.Add();
                        CurrencyFilterValue.Value1 = mCurrency;
                        IPivotFilter ParentFilterValue = pivotTable.Fields["PARTNERSHIP TYPE"].PivotFilters.Add();
                        ParentFilterValue.Value1 = sDefaultPartnershipType;

II,                    pivotTable.Fields["CURRENCY"].FilterValue = mCurrency;
                        pivotTable.Fields["PARTNERSHIP TYPE"].FilterValue = sDefaultPartnershipType;

Above noted two of logic also we could not get the Output.

And we need to share one more issue Too.

That is we could not set the password of the sheet.

Please help us to solve the issue.

Thanks,
Vasanth R

Attachment: Report_de8b8674.zip

3 Replies

SS Sridhar Sukumar Syncfusion Team April 14, 2017 09:22 AM UTC

Hi Vasanth, 
 
Thank you for contacting Syncfusion support. 
 
We have prepared a sample to set default value in pivot table filters dropdown and set password for “Data” worksheet which can be downloaded from the following location 
 
Sample link: 
 
Please let us know whether your requirement is fulfilled. 
  
Regards, 
Sridhar S. 



VA vasanth April 17, 2017 08:50 AM UTC

Hi Sridhar,

  Our requirement has not been fulfilled.
  
   We are using .Net core Console Application and i also noted below which reference currently using
   
    "Syncfusion.XlsIO.AspNet.Core": "14.4600.0.20-preview2-final".

   we need to  set the default filter value in existing excel sheet.
   but your application code look as set the value in new sheet.Which you given for sample.
   one more thing is even we tried your logic of code also we could not set the filter drop down default value (creating new sheet) 
   and we need whole Excel workbook password protection.
   we also attached the output of the file which is yet to be acccomplish.

  Below i given which code we are currently implementing for Default value
                IWorksheet oSheet = null;
                int rowCount = 2701 + 1;
                IWorksheet datasheet = workbook.Worksheets["Data"];
                IPivotCache cache = workbook.PivotCaches.Add(datasheet.UsedRange);
                IRange objRange = datasheet.Range["Data!R1C1:R" + rowCount.ToString() + "C39", true];
                oSheet = workbook.Worksheets["Upsell To Date"];

         if (oSheet != null)
                {
                    for (int ptblCount = 0; ptblCount < 1; ptblCount++)
                    {
                        IWorksheet pivotsheet = workbook.Worksheets.Create("Pivot table");
                        IPivotTable pivotTableww = pivotsheet.PivotTables.Add("PivotNewTable", oSheet["A1"], cache);


                        IPivotTable pivotTable = oSheet.PivotTables[ptblCount];
                        PivotCacheImpl pivotCache = (workbook.PivotCaches[pivotTable.CacheIndex] as PivotCacheImpl);
                        pivotCache.SourceRange = objRange;
                        pivotCache.IsRefreshOnLoad = true;



                        pivotTableww.Fields["CURRENCY"].Axis = PivotAxisTypes.Page;
                        pivotTableww.Fields["PARTNERSHIP TYPE"].Axis = PivotAxisTypes.Page;

                        pivotTableww.Fields[2].Axis = PivotAxisTypes.Row;
                        pivotTableww.Fields[6].Axis = PivotAxisTypes.Row;
                        pivotTableww.Fields[3].Axis = PivotAxisTypes.Column;

                        IPivotFilter CurrencyFilterValue = pivotTable.Fields["CURRENCY"].PivotFilters.Add();
                        CurrencyFilterValue.Value1 = mCurrency;
                        IPivotFilter ParentFilterValue = pivotTable.Fields["PARTNERSHIP TYPE"].PivotFilters.Add();
                        ParentFilterValue.Value1 = sDefaultPartnershipType;
                    }
                }


Thanks,
Vasanth R

Attachment: Report__81dc1354.zip


SS Sridhar Sukumar Syncfusion Team April 18, 2017 10:23 AM UTC

Hi Vasanth,  
 
Please find the response for your queries from below table. 
 
Query 
Response 
 Our requirement has not been fulfilled. 
   
   We are using .Net core Console Application and i also noted below which reference currently using 
    
    "Syncfusion.XlsIO.AspNet.Core": "14.4600.0.20-preview2-final". 
 
   we need to  set the default filter value in existing excel sheet. 
   but your application code look as set the value in new sheet.Which you given for sample. 
   one more thing is even we tried your logic of code also we could not set the filter drop down default value (creating new sheet)  
 
We are unable to reproduce the dropdown default value issue at our side. We have prepared a sample to set the default filter value in existing pivot table sheet which sample can be downloaded from the following link 
  
Sample Link:  
 
If your requirement is not met, then kindly modify and share us the issue reproducing sample. This will be helpful for us to provide prompt solution at the earliest.  
 
we need whole Excel workbook password protection. 
XlsIO does not support security features for ASP.NET core platform. However, we have added it to our Feature request list and it will be implemented in any of our upcoming releases. 
 
Regards, 
Sridhar S. 


Loader.
Live Chat Icon For mobile
Up arrow icon