Pivot Tables always label first column as 'Row Labels' when saving in .xlsx format

I’m trying to create a pivot table in a worksheet in the “tabular” format ( where column headers are present for all columns ) but I can’t get Aspose.Cells to stop using what seems to be the “compact” format.


I’ve read through this thread and tried setting the field’s ShowCompact and ShowInOutlineForm both to false but still get the undesired behavior. That thread identified this issue as fixed but it seems to have regressed.

I’ve attached the complete source code (C# - had to rename to .txt to upload) I can use to reliably reproduce this problem using Aspose.Cells 8.3.2.0.

Thanks!

Hi Chris,

Thanks for your posting and using Aspose.Cells.

I have attached the output.xlsx file generated by your code with the latest version: Aspose.Cells
for .NET v8.3.2.2
. Please download it and open it in Microsoft Excel and fix the issue with Pivot Table manually and save it with different name and attach it here. It will help us look into your issue more precisely.

If it was working with any older version, then please let us know the older version number, so we could also test it on older version and report this issue as regression.

C#


private static void CreateDataSheet(Workbook workbook)

{

var dataSheet = workbook.Worksheets.Add(“Data”);


dataSheet.Cells[0, 0].Value = “Label”;

dataSheet.Cells[1, 0].Value = “Cats”;

dataSheet.Cells[2, 0].Value = “Dogs”;


dataSheet.Cells[0, 1].Value = “Value”;

dataSheet.Cells[1, 1].Value = “10”;

dataSheet.Cells[2, 1].Value = “20”;

}


private static void CreatePivotTableSheet(Workbook workbook)

{

var pivotSheet = workbook.Worksheets.Add(“Pivot”);

var pivotTableIndex = pivotSheet.PivotTables.Add("=Data!A1:B3", “A1”, “PivotTable1”);

var pivotTable = pivotSheet.PivotTables[pivotTableIndex];


var labelFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Row, “Label”);

var labelField = pivotTable.RowFields[labelFieldIndex];


pivotTable.AddFieldToArea(PivotFieldType.Data, “Value”);


pivotTable.RefreshData();

pivotTable.CalculateData();


// These should cause us to use the “tabular” form but we’ll still get the “Row Labels” behavior.

labelField.ShowCompact = false;

labelField.ShowInOutlineForm = false;

}


void Main()

{

var workbook = new Workbook();

CreateDataSheet(workbook);

CreatePivotTableSheet(workbook);


workbook.Save(“output.xlsx”, SaveFormat.Xlsx);


}



Hi,


I’ve attached the updated workbook as output2.xlsx with the pivot table changed to tabular format.

I personally do not have any experience with this ever working properly with any version of Aspose.Net. That reference was just from reading the other thread I linked to earlier.

Thanks,
–Chris

Hi Chris,

Thanks for your sample expected file and using Aspose.Cells.

The fix to old thread is still working with the latest version: Aspose.Cells
for .NET v8.3.2.2
how your code is not working as expected.

We have therefore 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-43369 - Pivot Tables always label first column as ‘Row Labels’ when saving in .xlsx format

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.3.2.7 and let us know your feedback.

There are some tips for you:

1. Adds PivotTable.ShowInCompactForm() method, Layouts the PivotTable in compact form.

2. Adds PivotTable.ShowInOutlineForm() method, Layouts the PivotTable in outline form.

3. Adds PivotTable.ShowInTabularForm() method, Layouts the PivotTable in tabular form.

4. If you want to use the “tabular” form, please call the method named “ShowInTabularForm()”.

So, please change the code as follows:

pivotTable.RefreshData();
pivotTable.CalculateData();

to:

pivotTable.ShowInTabularForm();
pivotTable.RefreshData();
pivotTable.CalculateData();

Thanks! The Tabular format appears to be applied properly in 8.3.2.7.


( Now if only Using PivotTable.Format() with a PivotField - #7 by license.manager - Free Support Forum - aspose.com could also be solved… )

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. For your other issue, it is currently unresolved and once it will be fixed or we have some other update for you, we will let you know asap. Also, you can post in the original thread anytime to get update regarding your issue.

Let us know if you encounter any other issue, we will be glad to look into it and help you further.

The issues you have found earlier (filed as CELLSNET-43369) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.