Not able to avoid 'Row Labels' when created pivot using aspose

Aspose.Cells Product Family
1

Hi,

I am trying to create Pivot by using excel sheet data (please find the attachment of the same). I have set PivotTableAutoFormatType as ‘Classic’ (please find the attached code). Still i am getting the first row as ‘Row Labels’. If I try to save this pivot file as .xls then i am not facing this issue, but if I try to save this file as .xlsx then i am facing this issue. Even in .xlsx, if i open the pivot and change the design as tabular or outlined then this go off. I have use the below code to over come this issue.
pt.RowFields[i].ShowCompact = false;
pt.RowFields[i].ShowInOutlineForm = true;
pt.RowFields[i].ShowSubtotalAtTop = false;

But it does not work. could you please help me on this.

I need help on, how to create pivot in one work book work sheet by using the another work book work sheet data as source data. I.e. To create pivot in workbook2 --> (WorkSheet) Piovt by using the data from workbook1 --> (worksheet) source as data source.

Please let me know, if you need any additional information.

Thanks
Jithendra

2

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.

3

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

4

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.

5

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”);



6

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

7

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);



8

Hi,

Is there any update on this?

Thanks,

Jithendra

9

Hi Jithendra,


Unfortunately, we haven’t yet received any updates in reference to the ticket attached with this thread. We have recorded a note for the concerned development team member to provide an insight of the issue. As soon as we receive updates, we will post here for your kind reference.

Please accept our sincere apologies for the inconvenience caused to you.
10

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.

11

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.

12

Hi,

Thank you for your quick response. Now the problem is solved.

Thanks,

Jithendra

13

Hi,


Thanks for your feedback.

Good to know that your issue is resolved by the new fix, we have closed your ticket now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
14

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.
15

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:


If I save a workbook in FileFormatType.Xlsx my pivot table won’t show the field name when I use these settings (“Row Labels” is always displayed):

field.ShowCompact = false;
field.ShowInOutlineForm = false;

However, if I save in FileFormatType.Excel97To2003 or FileFormatType.Excel2003XML, the field name is shown with these settings.

Thanks!
16

Hi Chris,


Thank you for contacting Aspose support.

In order to properly investigate your presented scenario, we need your complete source code (that could replicate the said problem) along with the sample spreadsheet. It would be appropriate that you should create a new thread in Aspose.Cells support forum while providing the above requested. This way, we can investigate your presented scenario as distinct incident, and attach appropriate ticket(s) to your thread for the sake of future communication in this regard.
17

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