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

[vasanth]

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

[Sridhar Sukumar]

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: 

https://www.syncfusion.com/downloads/support/directtrac/general/ze/PivotTable-2095169420.zip 

 

Please let us know whether your requirement is fulfilled. 

  

Regards, 

Sridhar S. 

[vasanth]

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

[Sridhar Sukumar]

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:   https://www.syncfusion.com/downloads/support/directtrac/general/ze/PivotTable-319814567.zip    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.