Detect pivot table in Microsoft Excel using Interop c#
Here is some code for reference. Identify the overall Range occupied by PivotTables in the sheet and validate whether the cell is a part of the Range.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
private Excel.Range IdentifyPivotRanges(Excel.Range xlRange) { Excel.Range pivotRanges = null; Excel.PivotTables pivotTables = xlRange.Worksheet.PivotTables(); int pivotCount = pivotTables.Count; for (int i = 1; i <= pivotCount; i++) { Excel.Range tmpRange = xlRange.Worksheet.PivotTables(i).TableRange2; if (pivotRanges == null) pivotRanges = tmpRange; pivotRanges = this.Application.Union(pivotRanges, tmpRange); } return pivotRanges; } private void CheckCellsForPivot(Excel.Range xlRange) { Excel.Range pivotRange = IdentifyPivotRanges(xlRange); int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; for (int iRow = 1; iRow <= rowCount; iRow++) { for (int iCol = 1; iCol <= colCount; iCol++) { var cell = xlRange.Cells[iRow, iCol]; if (Application.Intersect(pivotRange, cell) != null) { int rowLocation = iRow; int colLocation = iCol; } } } } |
For refresh pivot table:
|
1 2 3 4 5 6 7 8 9 10 11 |
pivotSheet.Activate(); Microsoft.Office.Interop.Excel.PivotTables pivotTables = (Microsoft.Office.Interop.Excel.PivotTables)pivotSheet.PivotTables(missing); int pivotTablesCount = pivotTables.Count; if (pivotTablesCount > 0) { for (int i = 1; i <= pivotTablesCount; i++) { pivotTables.Item(i).RefreshTable(); //The Item method throws an exception } } |