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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Thread ID:

Created:

Updated:

Platform:

Replies:

129985 Apr 13,2017 11:02 AM UTC Apr 18,2017 10:23 AM UTC ASP.NET Core 3
loading
Tags: XlsIO
vasanth
Asked On April 13, 2017 11:02 AM UTC


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 [Syncfusion]
Replied On 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. 


vasanth
Replied On 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

Sridhar Sukumar [Syncfusion]
Replied On 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. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

or the page will be automatically redirected to sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;