Hi
I am having the below 3 issues when using PivotTable with Aspose.
I have a Template.xlsx file with a PivotTable whose DataSource is set to a named range ‘tblOpenPositions’.
This Pivot table has a custom sort set and also 3 conditional format rules.
First I import the MSDStyles worksheet from MSDStyles.xlsx into Template.xlsx so that I can get the custom styles defined for table and pivottable.
I open the file using Aspose and then import data and update the DataSource range. I then call
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.CalculateRange();
pivotTable.PivotTableStyleName = “MSDPivotStyle”;
and save the xlsx file the pivottable in the saved file does not have the MSDPivotStyle applied and it is not sorted and also the conditional formats have blanked out all cells in the column (columns B,C and D) as opposed to only only some cells in the columns.
The attached MSDStyles.xlsx file has the custom styles defined for table and pivottable.
The attached PivotTable Issues - Aspose.xlsx has 2 sheets.
Good Pivot : This should be how the pivot should look like after importing data and saving file.
Bad Pivot: This is how the pivot looks like after using aspose.
Please let me know what would be the correct way to go about importing and applying a custom style from a different workbook., and how to maintain the sorting and conditional formats as defined on the pivot in my template.
@aferriere2,
We are working on this issue and need a little more information. You may share the Template.xlsx file with us. Also using these files perform all the tasks using Excel ONLY and produce Good Pivot table. Share all the steps in Excel with us which you perform to create the required output as these will help us to create the expected output file using Excel and then compare it with the output created by Aspose.Cells.
Also, share a complete runnable console application which you have used to perform this task using Aspose.Cells for our analysis.
Hi
Are you available for a quick webex call tomorrow?
This way I can share my screen with you and quickly show you the issue.
I am on EST timezone. Please let me know what time would work best for you?
@aferriere2,
I am afraid, we do not provide support via screen sharing. The best way to get help or reach us is via forums. Kindly do as directed (in my previous post) to provide the requested artifacts, so we could evaluate your issue on our end.
@aferriere2,
I am afraid that no such option is available via paid support as well. However, you may record a video (if required) and share with us. We will review it and provide our feedback.
Hi
Attached is the sample code to re-produce the issue.
Program.cs has instructions on how to create the correct output in excel manually
Please let me know if you need any additional info from my end
@aferriere2,
Thank you for the sample code and steps to reproduce this issue. We are working on it and request you to spare us little time to analyze this issue. We will write back here soon to share our feedback.
@aferriere2,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
@aferriere2,
About the custom sort, we can’t support sorting the PivotField via DataField for now. We will support it later on.
About the style, Please use the following code to test it:
Workbook workbook = null;
workbook = new Workbook(filePath + "Template.xlsx");
//Copy MSD Styles sheet to template file
Workbook workbookStyle = null;
workbookStyle = new Workbook(filePath + "MSDStylesAspose.xlsx");
// Copy the first sheet of the first book into second book.
var wsStyle = workbook.Worksheets.Add();
workbook.Worksheets[wsStyle].Copy(workbookStyle.Worksheets["MSDStyles"]);
var tblName = "tblOpenPositions";
var dataSet = new DataSet();
dataSet.ReadXml(filePath + "MyDataset.xml",XmlReadMode.ReadSchema);
dataSet.Tables[0](https://issue.nanjing.dynabic.com/issues/CELLSNET-46793#fn0).TableName = tblName;
var designer = new WorkbookDesigner { Workbook = workbook };
designer.SetDataSource(dataSet.Tables[0](https://issue.nanjing.dynabic.com/issues/CELLSNET-46793#fn0));
// Process the smart markers
designer.Process(true);
var pivotWorksheet = workbook.Worksheets["PivotOpenPositions"];
var pivotTables = pivotWorksheet.PivotTables;
var pivotTable = pivotTables[tblName];
workbook.CalculateFormula();
//you should set the style name firstly, and then refresh and calculate the PivotTable
pivotTable.PivotTableStyleName = "MSDPivotStyle";
pivotTable.RefreshDataOnOpeningFile = false;
pivotTable.RefreshData();
//PivotTable.CalculateData() contains PivotTable.CalculateRange(), so you don't need to call it again.
pivotTable.CalculateData();
workbook.Save(filePath + "out.xlsx", SaveFormat.Xlsx);
Confirmed that specifying the style name before calling RefreshData & CalculateData works fine.
Regarding adding support for sorting via DataField are you looking to add support by end of year or it would be longer than that ?
Also how about Conditional Formatting not working? Are you still researching that?
In the test code I sent, Columns B,C,D are all blank (Only the cells in the ExposureIndustry group of these columns should be blank)
Good to know that the suggested code segment figures your issue regarding custom styles/formattings.
It might be longer than it as there is no ETA for the feature (data sorting via data field) available. Once we have any new information on it, we will let you know.
To inform you regarding the feature of sorting via data field, we will support sorting PivotField by DataField before the end of fourth quarter of 2019.
Confirmed the latest version fixes the issue.
Will you be making this version 19.6.5 available via nuget as well ?
I still see the latest version as 19.6.0 on nuget.