We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Remove subtotal & grand total from Column field in pivot sheet

I have created pivot sheet but in my column field I am having subtotal,grandtotal in column field kindly help me to remove grand total sub total. In my data sheet I am having subtotal & grand total.

Row field Principal name

Column field Ageing

Pivot Sheet looks like

Ageing

principal a 30 60 Grand Total: Sub Total:

principal

Code

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

pivotTableSheet.PivotTables.Clear();

DataTable dt = ds.Tables["OutstandingDAMSKData"];

PivotTable pt = pivotTableSheet.PivotTables[pivotTableSheet.PivotTables.Add("Data!A" + DataUtil.GetString(startIdx) + ":" + obj.ExcelWorkBook.Worksheets["Data"].Cells[dt.Rows.Count + 6, dt.Columns.Count - 1].Name, 3, 0, "PivotTable")];

pt.PageFields.AddByBaseIndex(dt.Columns["port_name"].Ordinal);

pt.PageFields.AddByBaseIndex(dt.Columns["nomination_type_name"].Ordinal);

pt.PageFields.AddByBaseIndex(dt.Columns["agent_name"].Ordinal);

pt.PageFields.AddByBaseIndex(dt.Columns["voyage_no_pasp"].Ordinal);

pt.PageFields.AddByBaseIndex(dt.Columns["vessel_name"].Ordinal);

pt.RowFields.AddByBaseIndex(dt.Columns["principal_name"].Ordinal);

pt.ColumnFields.AddByBaseIndex(dt.Columns["Ageing"].Ordinal);

pt.DataFields.AddByBaseIndex(dt.Columns["appointment_no_pasp"].Ordinal);

pt.DataFields.AddByBaseIndex(dt.Columns["amount_advanced"].Ordinal);

pt.RowFields[0].IsAutoSort = true;//Set auto-sorting on

pt.RowFields[0].IsAscendSort = false;//Descending.

pt.RowFields[0].AutoSortField = 0;//By first data field

pt.ColumnFields[0].IsAutoSort = true;//Set auto-sorting on

pt.ColumnFields[0].IsAscendSort = true;//Ascending.

pt.DataFields[0].DisplayName = "Calls";

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

pt.DataFields[1].NumberFormat = "$#,##0.00;$-#,##0.00";//"[$$-409]#,##0.00";

pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);

pt.ColumnGrand = true;

pt.RowGrand = true;

pt.AutoFormatType = PivotTableAutoFormatType.Classic;

Hi,


Thanks for sharing a code snippet with some details.

Please give us a sample file by manually created in MS Excel to show your desired Pivot Table with your unwanted fields excluded in the report. You may give us some more details with screen shots about your desired needs here. Also, I cannot evaluate your code much as I have to evaluate your source data in the DataTable, we appreciate if you could create a sample console application (runnable), zip it and post it here to show what is your resultant file. Also provide your input files (if there is any) and output file by Aspose.Cells. We will check it soon.

Thank you.