Pivot filed autoformat loading

Aspose.Cells Product Family
1

Hi Team

When I use the below code to generte Pivot Excel , the auto loading format appears as per the "ERROR excel" sheet and the "Error Image" attached with this mail.

Please advice with changes to the code that may be required to acheive the format as per the "Needed Excel" sheet and the "Needed_Image" .

Please find the attached Zip folder withe above mentioned Excel sheets and Image files.

Your Help in this regard is much appreciated!!!!

Please find the code used currently for your reference.

regards,samy

-------------------------------------------------------------------------------------------------------------------------------------------------------

Aspose.Cells.Worksheet pivotTableSheet = obj.ExcelWorkBook.Worksheets["Pivot"];

pivotTableSheet.PivotTables.Clear();

Aspose.Cells.PivotTable pt = pivotTableSheet.PivotTables[pivotTableSheet.PivotTables.Add("Data!A1:" + obj.ExcelWorkBook.Worksheets["Data"].Cells[ds.Tables["Pivot"].Rows.Count, ds.Tables["Pivot"].Columns.Count - 1].Name, 0, 0, "PivotTable")];

int index = 1;

pt.RowFields.AddByBaseIndex(index++); //agent_name

pt.ColumnFields.AddByBaseIndex(2); //nomination_type_name

pt.DataFields.AddByBaseIndex(10); //amount_advanced

pt.DataFields[0].DisplayName = "Amount USD";

pt.DataFields[0].DragToColumn = true;

pt.DataFields.AddByBaseIndex(0); //appointment_no_pasp

Aspose.Cells.PivotField datafieldnomination = pt.DataFields[1];

pt.DataFields[1].DisplayName = "Count";

pt.DataFields[1].DragToColumn = true;

datafieldnomination.IsAutoShow = false;

datafieldnomination.DragToColumn = true;

// Aspose.Cells.PivotField datafieldAmount_advanced = pt.DataFields[1];

// datafieldAmount_advanced.DragToColumn = true;

pt.AutoFormatType = Aspose.Cells.PivotTableAutoFormatType.Table2;

-----------------------------------------------------------------------------------------------------

2

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for sharing the sample code and files.

We will look into your issue and get back to you soon.

Thank You & Best Regards,

3

Hi Team

I have solved the issue, add the line of "pt.AddFieldToArea(PivotFieldType.Column, pt.DataField). It ll work !!!!!

Aspose.Cells.Worksheet pivotTableSheet = obj.ExcelWorkBook.Worksheets["Pivot"];

pivotTableSheet.PivotTables.Clear();

Aspose.Cells.PivotTable pt = pivotTableSheet.PivotTables[pivotTableSheet.PivotTables.Add("Data!A1:" + obj.ExcelWorkBook.Worksheets["Data"].Cells[ds.Tables["Pivot"].Rows.Count, ds.Tables["Pivot"].Columns.Count - 1].Name, 0, 0, "PivotTable")];

int index = 1;

pt.RowFields.AddByBaseIndex(index++); //agent_name

pt.RowFields[0].DisplayName = "Agent";

pt.ColumnFields.AddByBaseIndex(14); //Ageing

pt.DataFields.AddByBaseIndex(10); //amount_advanced

pt.DataFields[0].DisplayName = "Advanced USD";

pt.DataFields.AddByBaseIndex(0);

Aspose.Cells.PivotField datafieldnomination = pt.DataFields[1];

pt.DataFields[1].DisplayName = "Count";

pt.AddFieldToArea(PivotFieldType.Column, pt.DataField); //I have solved this issue.

Thanks,

SamyT