Hi,
Hi Jithendra,
Thanks for your posting and using Aspose.Cells.
Please provide us your expected output Excel file which you can create manually using MS-Excel and attach with this post for our reference. It will help us investigate your issue and we will provide you a sample code to achieve your expected output using Aspose.Cells API.
For your other question, please create a separate thread and provide your sample files there, we will look into your issue in your new thread and log it in our database if it is a new feature or provide you a sample code if this feature already exist.
Thanks for your cooperation.
Hi,
Thank you for your quick response. Please find the attached expected excel file for your reference. In this the 'Pivot_Using_Code' sheet is generated by the code which i have posted. The pivot sheet 'Pivot_Expected' is the expected result. The main difference between these two sheets is that, the first row is populated as 'Row Labels' in the code generated pivot, but it should be 'Employee Name'. In the code generated Pivot sheet, all the fields in the first column position are getting populated as 'Row Labels'. I.e. if i drop the first row labels field as 'Employee Dept' then this is also populated as 'Row labels', instead of "Employee Dept. I.e, it should populate with the provided filed column name.
Please let me know, if you need any additional information.
Thanks,
Jithendrav
Hi Jithendrav,
Thanks for your sample file and using Aspose.Cells.
We will look into it and let you know the sample code to achieve your desired Pivot Table results.
Hi Jithendrav,
Thanks for using Aspose.Cells.
Please see the following code to make your actual pivot table as expected pivot table. I have attached the output xlsx file generated by it for your reference.
C#
Workbook workbook = new Workbook(“TestPivot.xlsx”);
PivotTable ptA = workbook.Worksheets[“Pivot_Using_Code”].PivotTables[0];
ptA.RowFields[0].ShowCompact = false;
ptA.RowFields[0].ShowInOutlineForm = false;
workbook.Save(“output.xlsx”);
Hi,
Thank you for your response. Still i did not see the expected result in 'Pivot_Using_Code' sheet. Please find the attached screenshot (highlighted the issue). I am still able to see 'Row Lables' as first column header instead of 'Employee Name'.
Please let me know, if you need any additional information.
Thanks,
Jithendra
Hi Jithendra,
Thanks for your screenshot and using Aspose.Cells.
I have tested this issue with the following code which is same as yours and found XLS format is correct while XLSX format has issues as you mentioned in the earlier post.
We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as CELLSNET-42933.
I have attached both the xls and xlsx output files for a reference.
C#
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
Workbook workBook = new Workbook(@“TestPivot.Xlsx”, loadOptions);
if (workBook.Worksheets[“Pivot”] != null)
{
workBook.Worksheets.RemoveAt(“Pivot”);
workBook.Worksheets.RemoveAt(“Evaluation Warning”);
}
Worksheet sheet = workBook.Worksheets.Add(“Pivot”);
Worksheet sourceSheet = workBook.Worksheets[“Source”];
Cells cell = sourceSheet.Cells;
Range cellRange = cell.MaxDisplayRange;
string sourcedata = cellRange.RefersTo.ToString();
PivotTableCollection pivotTables = sheet.PivotTables;
int iPivotIndex = sheet.PivotTables.Add(sourcedata, “A3”, “PivoteTable”);
PivotTable pt = pivotTables[iPivotIndex];
pt.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Employee Name”);
pt.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “Employee Dept”);
pt.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, “Salary”);
pt.DataFields[“Salary”].Function = ConsolidationFunction.Sum;
// pt.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, pt.DataField);
// pt.ColumnFields[0].DisplayName = “Values”;
// uncheck subtotal country rows
PivotFieldCollection pivotFields = pt.RowFields;
pivotFields[0].IsAutoSubtotals = false;
pt.IsAutoFormat = true;
pt.AutoFormatType = PivotTableAutoFormatType.Classic;
pt.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
for (int i = 0; i < (pt.RowFields.Count - 1); i++)
{
pt.RowFields[i].ShowCompact = false;
pt.RowFields[i].ShowInOutlineForm = true;
pt.RowFields[i].ShowSubtotalAtTop = false;
}
for (int i = 0; i < (pt.ColumnFields.Count - 1); i++)
{
pt.RowFields[i].ShowCompact = false;
pt.RowFields[i].ShowInOutlineForm = true;
pt.RowFields[i].ShowSubtotalAtTop = false;
}
// pt.ColumnFields[0].IsAutoSort = true;
pt.IsGridDropZones = true;
sheet.PivotTables[0].RefreshData();
sheet.PivotTables[0].CalculateData();
sheet.PivotTables[0].CalculateRange();
sheet.AutoFitColumns();
workBook.AcceptAllRevisions();
//XLSX is not correct
workBook.Save(@“outTestPivot.xlsx”, SaveFormat.Xlsx);
//XLS is correct
workBook.Save(@“outTestPivot.xls”, SaveFormat.Excel97To2003);
Hi,
Is there any update on this?
Thanks,
Jithendra
Hi Jithendra,
Hi Jithendra,
Thanks for using Aspose.Cells.
We are working over this issue and hopefully, this issue (CELLSNET-42933) will be fixed in about September 5, 2014.
Once, there is some fix or other update for you, we will let you know asap.
Hi,
Thanks for your using Aspose.Cells.
Please download and try the latest fix: Aspose.Cells for .NET v8.2.0.2 and let us know your feedback.
Hi,
Thank you for your quick response. Now the problem is solved.
Thanks,
Jithendra
Hi,
The issues you have found earlier (filed as CELLSNET-42933) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
Is it possible that this bug was re-introduced sometime after this fix? I’m currently evaluating using 8.3.2.1 and am seeing the same described symptoms:
Hi Chris,
The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan