CODEWITHSUNDEEP
c#excelpivot-tableoffice-interop
HaR
HaR

Reputation: 1067

Activating Show Details for Grand Total of Pivot Table in C#

I'm trying to automate the creation of "Show Detail" of a Pivot Table from Excel using C#

using Excel = Microsoft.Office.Interop.Excel;

My code:

var excelApp = new Excel.Application();
excelApp.Visible = true;
Excel.Workbook workbook = excelApp.Workbooks.Open(@"D:\path_to_excel_file.xlsm");
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets["Sheet_Name"];

Excel.PivotTable pivot = worksheet.PivotTables("defined_pivot_table_name");
pivot.DataBodyRange.ShowDetail = true;

The code works, but it displays only the details of the first "Total" value. But what I want is to get the "Show Details" of the "Grand Total". In this case for this pivot table, it will create a new sheet with the 4 elements, but what I want is for the Grand Total (202).

enter image description here

I've tried selecting it first by pivot.PivotSelect("Grand Total"); but still no results. I've also checked pivot.RowGrand and pivot.ColumnGrand which both return True.

Upvotes: 1

Views: 1584

Answers (2)

Asger
Asger

Reputation: 3877

If your pivottable has both, RowGrand AND ColumnGrand, then it also has a grand total

if (pivot.RowGrand && pivot.ColumnGrand)

Then you can use the last cell of the pivottable's TableRange1 to generate the details by ShowDetail

int countRows = pivot.TableRange1.Rows.Count;
int countColumns = pivot.TableRange1.Columns.Count;
pivot.TableRange1.Cells[countRows, countColumns].ShowDetail = true;

Upvotes: 1

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Maybe this can help

// Access the pivot table by its name in the collection. 
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];


// Access the pivot field by its name in the collection. 
PivotField field = pivotTable.Fields["Category"];


// Display multiple subtotals for the field.   
field.SetSubtotal(PivotSubtotalFunctions.Sum | PivotSubtotalFunctions.Average);

// Show all subtotals at the bottom of each group. 
pivotTable.Layout.ShowAllSubtotals(false);

// Hide grand totals for rows.
pivotTable.Layout.ShowRowGrandTotals = False

// Hide grand totals for columns.
pivotTable.Layout.ShowColumnGrandTotals = False

// custom label for grand totals
pivotTable.View.GrandTotalCaption = "Total Sales";

Upvotes: 1

Related Questions