WPF Spreadsheet not loading Pivot created with XlsIO

Question

Hello,The goal I would like to achieve is to create an xlsx file with a pivot table inside using the XlsIO library and then show this file in the SpreadsheetControl.At the moment I can create the xlsx file with the pivot and open it in an external app (tried Excel and WPS Office) and all is showing correctly.Instead, with the SpreadsheetControl the pivot table is showing only if before I open the file with Excel, click "save", and then load it in the control. Otherwise if I load the

generated xlsx file directly in the control the pivot tables are not shown.Is this an expected behavior or a problem?Thank you

Vijayarasan Sivanandham · Answer

Hi Fabrizio Alessandro,

Your requirement to load the pivot grid exported excel document in SfSpreadSheet
can be achieved by setting the ExportMode property as Cell in GridExcelExport. Please
refer the below code snippet,

GridExcelExport excelExport = new GridExcelExport(this.Target, Syncfusion.XlsIO.ExcelVersion.Excel2013);                               
  excelExport.ExportMode = ExportModes.Cell;
  excelExport.Export(savedialog.FileName);

UG Link: https://help.syncfusion.com/wpf/pivot-grid/exporting#export-to-excel

Please find the sample in the attachment and let us know if you have any
concerns in this.Regards,Vijayarasan S

Attachment: ExporttoPivotGridLoadedinSfSpreadSheet_9469e2e4.zip

Fabrizio Alessandro · Answer

Hello,I haven't a pivot grid to export, I am creating the pivot table directly with the XlsIO libraryThank you

Vijayarasan Sivanandham · Answer

Hi Fabrizio Alessandro,We have prepared the simple sample and checked the reported issue “PivotTable loaded properly in SfSpreadSheet” from our end. We regret to let you know that we are unable to replicate the reported issue. Please find the tested sample demo in the attachment. Could you please share the modified sample based on your scenario along with the Excel file which is used for reproducing the reported issue?It will be helpful for us to check on it and provide you with the solution at the earliest.Regards,Vijayarasan SAttachment: SfSpreadSheetDemo_1a924582.zip

Fabrizio Alessandro · Answer

Hello,Thank you for your reply, I have edited the sample to reproduce my problem.Clicking the button "PivotTable" it will create and save an xlsx file with a pivot table and opens it the SpreadsheetControl.You can see that the file is correctly loaded but it doesn't show the pivot table.If I try to open the created xlsx file with Excel, it shows the pivot table.I hope this makes the request clearer.Thank youAttachment: SyncfusionSpreadsheetPivot_e3a6a471.zip

Vijayarasan Sivanandham · Answer

Hi Fabrizio Alessandro,

We are currently checking your reported scenario with provided information and
we need two more business days to validate this. We will update you with
further details on June 01, 2022.Regards,Vijayarasan S

Vijayarasan Sivanandham · Answer

Hi Fabrizio Alessandro,

Your requirement to load the pivot table created with excel file in SpreadSheet
can be achieved by  calling the Layout
method in IPivotTable. Please refer the below code snippet,

private void OnPivotTable(object sender, RoutedEventArgs e)
  {
              using (ExcelEngine excelEngine = new())
              {
                 
  IApplication application = excelEngine.Excel;
                 
  application.DefaultVersion = ExcelVersion.Excel2013;
                  IWorkbook
  workbook = application.Workbooks.Open("PivotData.xlsx");
                  IWorksheet
  worksheet = workbook.Worksheets[0];
                  IWorksheet
  pivotSheet = workbook.Worksheets[1];
   
                  //Create Pivot cache with the given data range
                  IPivotCache
  cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
   
                  //Create "PivotTable1" with the cache at the specified
  range
                 
  IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
   
                  //Add Pivot table fields
  (Row and Column fields)
                 
  pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
                 
  pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
                 
  pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
   
                  //Add data field
                 
  IPivotField field = pivotTable.Fields[5];
                 
  pivotTable.DataFields.Add(field, "Sum", PivotSubtotalTypes.Sum);
   
                  //Layout the pivot table.
                  pivotTable.Layout();

workbook.SaveAs("PivotTable.xlsx");
              }
             
  spreadsheetControl.Open("PivotTable.xlsx");
  }

UG Link: https://help.syncfusion.com/file-formats/xlsio/working-with-pivot-tables#pivot-table-layout

Please find the modified sample in the attachment and let us know if you have
any concerns in this.Regards,Vijayarasan SAttachment: ModifiedSample_8aaa9f11.zip